Buy Support
Incidents |
If you can't find your answer
in the FREE PUBLIC QDeveloper Forum, require URGENT Priority Support, or you need to send us
private or confidential information: |
Click Here
|
If you can't
login and post questions or you are having trouble viewing forum posts:
Click Here
|
Callback
Support |
If you live in USA, UK, Canada, Australia or New
Zealand, you can leave us details on your question and request us to call you back and discuss
them with you personally (charges apply). |
Click Here
|
Buy Support
Incidents |
If you can't find your answer
in the FREE PUBLIC QDeveloper Forum, require URGENT Priority Support, or you need to send us
private or confidential information: |
Click Here
|
|
How do I obtain the next value for PurchaseOrderLineItemRefListID? |
Author |
Message |
Tom |
|
Group | : Administrator |
Posts | : 5510 |
Joined | : 2006-02-17 |
|
Profile |
|
Posted : 2006-03-02 09:31:13 |
Originally, I was assuming that the reference ID fields were automatically generated by QBE when a record is inserted. However, your SQL Insert code examples show you inserting this value. My question: How do I obtain the correct Next value of the PurchaseOrderLineItemRefListID for inserting a PurchaseOrderLine record, and then inserting a PurchaseOrder record? |
|
|
|
Tom |
|
Group | : Administrator |
Posts | : 5510 |
Joined | : 2006-02-17 |
|
Profile |
|
Posted : 2006-03-02 09:36:08 |
I assume you are actually saying that the item isn't a part yet and you want to get the next ListID value to do the purchase order for the item? Well if that's the case you will need to create the Item first, otherwise if it's an existing item already you just need to find the ListID for for the Item by querying the Item table as shown below.
This is an example of how to add an ItemInventory Item. You can also use the stored procedure "sp_lastinsertid ItemInventory" immediately after the insert statement to retrieve a recordset that has the ListID of the new item you just inserted.
INSERT INTO ItemInventory ("Name", "IsActive", "SalesDesc", "SalesPrice", "IncomeAccountRefListID", "PurchaseDesc", "PurchaseCost", "COGSAccountRefListID", "PrefVendorRefListID", "AssetAccountRefListID", "ReorderPoint", "QuantityOnHand", "TotalValue", "InventoryDate") VALUES ('NewItemInventory', TRUE, 'SalesNew', 0.0013, '1A0000-933270542', 'PurchaseNew', 0.005, '1E0000-933270542', 'A40000-1197774038', '60000-933270541', 25.0, 50.0, 10.0, {d'2005-10-07'})
But before you add any new Inventory Items, you should check the ITEM table first to see if the new Inventory Item Name is not already used in QuickBooks. The ITEM table is a combined list of all Item tables in QuickBooks: ItemInventory, ItemNonInventory, ItemOtherCharge, ItemInventoryAssembly, ItemService, ItemFixedAsset, and ItemGroup; with minimum common fields.
When inserting a new ItemInventory item the Name can't already be, for example, a ItemInventory, ItemNonInventory, ItemOtherCharge, ItemInventoryAssembly, ItemService, ItemFixedAsset, or ItemGroup FullName already. It must be a unquie new ITEM FullName!
To see the item fullnames, you can run the following query in VB Demo:
SELECT ListID, FullName, Description, Type FROM Item
In my case, my Australian QuickBooks 2004 sample file returned the following items:
And like ITEM there's a ENTITY table that's the combined list of all entity tables in QuickBooks: Customers, Employees, Othernames, and Vendors; with minimum common fields.
So when inserting a new Customer name the FullName can't be, for example, a Customer, Employee, Othername, or Vendor already. It must be a unquie new ENTITY FullName in QuickBooks!
|
|
|
|
Tom |
|
Group | : Administrator |
Posts | : 5510 |
Joined | : 2006-02-17 |
|
Profile |
|
Posted : 2006-03-02 09:39:00 |
Once the new item is created you can then use it in the Purchase Order line. This example creates one PurchaseOrder with three lines. Note the FQSaveToCache field, set to TRUE (1) except on the last line.
INSERT INTO "PurchaseOrderLine" ("VendorRefListID", "RefNumber", "PurchaseOrderLineItemRefListID", "PurchaseOrderLineDesc", "PurchaseOrderLineQuantity", "PurchaseOrderLineRate", "PurchaseOrderLineAmount", "PurchaseOrderLineCustomerRefListID", "FQSaveToCache") VALUES ('10000-933272655', '1', '250000-933272656', 'See Attached 1', 1.0, 1.0, 1.11, '580000-1071526281', 1)
INSERT INTO "PurchaseOrderLine" ("VendorRefListID", "RefNumber", "PurchaseOrderLineItemRefListID", "PurchaseOrderLineDesc", "PurchaseOrderLineQuantity", "PurchaseOrderLineRate", "PurchaseOrderLineAmount", "PurchaseOrderLineCustomerRefListID", "FQSaveToCache") VALUES ('10000-933272655', '1', '250000-933272656', 'See Attached 2', 2.0, 2.0, 2.22, '580000-1071526281', 1)
INSERT INTO "PurchaseOrderLine" ("VendorRefListID", "RefNumber", "PurchaseOrderLineItemRefListID", "PurchaseOrderLineDesc", "PurchaseOrderLineQuantity", "PurchaseOrderLineRate", "PurchaseOrderLineAmount", "PurchaseOrderLineCustomerRefListID", "FQSaveToCache") VALUES ('10000-933272655', '1', '250000-933272656', 'See Attached 3', 3.0, 3.0, 3.33, '580000-1071526281', 0)
Where: VendorRefListID is the ListID for the Vendor found by doing : Select * from Vendor PurchaseOrderLineItemRefListID is the new Inventory Item ListID found by using: Select ListID, FullName, Description, Type FROM Item PurchaseOrderLineCustomerRefListID is the ListID for the customer found by doing : Select * from Customer |
|
|
|
|