Method Community

Hi,

In CRM_VenderList, I tried to figure out how vender table and transaction are linked but can not get any idea. In CRM_VenderList screen, when I click a vender in left-section, "Transaction" tab in the right panel is updated immediately. I only know "Transaction" table is attached to this screen, but don't know exactly which field in Transaction table is linked to vender table. Can anyone help? Thanks.

Alex

Hi Alex,

The Transaction table's Entity field specifies the relationship between the Transaction and Vendor (Entity) tables.  

Hope this helps.

- Justin

Thanks, Justin.

It seems that "Vender" table has no any link connected to "Entity" table, is there anything been done automatically to connect them both?

I'm asking is if I can use the same configuration for my use case. I'm building an inventory system, and every product in this system need to be associated with a transaction (PO, SO, Invoice, ...) I'm wondering if I can connect my own "Product" table to Method's "Transaction" Table, so in my inventory management screen, I can show related transactions on it.

Or, should I need to connect to individual transaction tables ("Purchase Order", "Sales Order", ...) for this case?

Thanks.

Alex

Alex,

The Entity table contains all the information that the Customer and Vendor tables do. All Customers, Vendors, Employees and OtherNames are considered Entities by QuickBooks and reside in the Entity table. The Customer, Vendor, Employee and OtherName tables are actually just the Entity table with EntityType set to 'Customer' or 'Vendor', etc., so you shouldn't have to do any linking.

In the case of your inventory system, I'l need some more information. What exactly are your "Products"? Are they like Items that you would use on your Invoice Line table, or something else?

Let me know.

- Justin

Hi Justin,

Got it about Vender/Entity relationship. 

regarding my inventory system. We try to use "items" like in QB. It works like, for example in a Sales Order:

1. We create a Table "MyProduct", each record row defines one of product in inventory system. 

2. In a Sales Order screen, we try to link "MyProduct" table to "SalesOrderLine" table, which means several products are associated with one SalesOrderLine. (similar case applies to invoice, PurchaseOrder, Bill, etc.)

3. In our Inventory management screen, we want to show all transactions related to a single product.

My original idea is to find SalesOrder by way of SalesOrderLine. But, it seems that "Transaction" table has everything there, so I'm just wondering if I can connect "MyProduct" table to "Transaction" table, so it will work as "Vender" table to "Transaction" table.

---

Alex

Alex,

Why would you want several Products on one Sales Order Line?

Is your "Product" similar to an Item? What's the relationship there? Why not just use an Item, which syncs to QuickBooks?

- Justin

Justin,

it is all about to keep synchronize with Quickbooks desktop. In our inventory system, every product is associated with a QuickBooks Item, also, every our product has an uniqe ID, and we need to track this product ID in all kinds of transactions. So, after sync Method data back to Quickbooks, accounting person can know inventory status of each QB items. For Warehouse/Sales person, they can work with Inventory system (in Method) to know relationship among every product and all kinds of orders. I once want to use Item to do it, but that means, whever we buy a product, then need to create a new item, which seems not a right solution to us, since we can not create unlimited item in QB, and Accounting person does not like to see so many items.

I don't know if that is the right way to do it, please let me know any solutions. Thanks.

--

Alex

Alex,

I have been reading this dialogue betwen you and Justin and have some thoughts...

Are you talking about needing to track items purchased,/sold by a Serial number(product id)? which is unique?

Items in QB is the product, but in Method, your product table would be at productid level? So on a SalesOrderLine,instead of several products on one SO Line  I can see one line per Productid, but the Item would be the same. This woul sync nicely with QB.  Your SOline in Method would inclue both the Item and Productid.

Consider adding a custom Field to QB to the Item lists. Called ProductID.  The field would automatically be added to the item table in Method. And woud sync.  This way if you want to show the product ID on a SO, invoie, PO or Bill in QB you can do so by managing your QB templates.  Your productid inventory can still be maintained in Method.

The transaction table is great It is a special QB table. So great caution on additng fields to it.  You can not write directly to this table. 

Does tha help or am I off base with what you are trying to achieve here.

Fran

Hi Fran,

Yes, you are right, and thank you for detail answer. 

And, yes, to associate single SOLine with my own Product will do the job. Sorry that I did not mention a key part that my client requests, that is "Quantity" for every item line can be adjusted (1 or more.) The request is, in my client's case, one PurchaseOrder can have hundred products, and they are under same item name (in QBs), and each product has their own unique ID (in Method product table.) Normally, I can just list them in individual item line, that is easy, however, they want me to put them in single item line but just change quantity. so, that is why I say I need to associate multiple product to a single SOLine. 

---

Alex