| Help unifying the SalesOrderLine and Invoice Line Tables |
| Author |
Message |
|
|
| Posted : 2006-04-26 08:03:40 |
I am running QODBC v 6.00.00.155 with Quickbooks Premier - Contracter Edition (US) 2005 (planning to upgrade to 2006 late this year). I am sure that this is simple inexperience with QODBC/Quickbooks, but I cannot seem to find a way to create direct link between SalesOrderLine and InvoiceLine when linking with the tables from MS Access 2003. I know that the Sales Order number is visible in Quickbooks by way of templates, but there doesn't seem to be any hard reference in the tables. In the event that I am approaching this the wrong way, a brief synopsis of what I am trying to do.
Create a unified record for sales order displaying information regarding relevant invoices, purchase orders, sales reps, and vendors along with custom fields defined in MS Access.
The end goal does not require posting back to QB (it would be a nice option, but not required).
I have been backwards and forwards through the online table mapping, FAQs, and through the tables themselves, but the solution eludes me.
I am not terribly familiar with SQL, but have a good working knowledge of MS Access.
Any help would be greatly appreciated.
TIA |
|
|
|
| Tom |
 |
| Group | : Administrator |
| Posts | : 5510 |
| Joined | : 2006-02-17 |
|
| Profile |
|
| Posted : 2006-04-26 10:24:53 |
| The SalesOrderLinkedTnx table contains the "LinkedTxnTxnID" link from SalesOrderLine to InvoiceLine |
|
|
|
|
|
| Posted : 2006-04-27 06:13:26 |
Thanks for the quick response 
I will give that a try tonight. |
|
|
|
|
|
| Posted : 2006-04-27 08:37:06 |
OK, I think I have the Invoice data working...
How do I link the Sales Order back to the related Purchase Orders?
I tried to follow the same concept as the Invoicing, but it appears that there is no visible linked data for the Sales Order on the Purchase Order table and no visible PO on the Sales Order form (not the one Quickbooks generates anyway).
Once I have these two links, the rest should be fairly straight forward - I hope.
This task would have been a nightmare without some guiding wisdom. Thanks again for your help. |
|
|
|
| Tom |
| |
| Group | : Administrator |
| Posts | : 5510 |
| Joined | : 2006-02-17 |
|
| Profile |
|
| Posted : 2006-04-27 09:41:28 |
Ok, Sales Orders to Purchase Orders don't have a LinkedTxn table. But when I created the following Sales Order in QuickBooks:
and got QuickBooks to create a purchase order for me, it created this for me:
with the "Sale Order 4:" link in the memo line! This means I can find the purchase orders for a sales order by doing:
SELECT RefNumber, VendorRefFullname, PurchaseOrderLineDesc,
PurchaseOrderLineQuantity FROM PurchaseOrderLine
where Memo like 'Sales Order 4:%'
|
|
|
|
|
|
| Posted : 2006-04-28 04:19:16 |
First off, Thanks again for the response.
The only issue with using the memo line from the purchase order field is that the memo field is modifiable in quickbooks and might not alwasy carry the link.
Is there a more reliable method?
I would think that there would be some fixed method of viewing the purchase orders that relate to a given sales order from inside the quickbooks program. If that were the case, there would be some sort of fixed link residing somewhere in one (or a combination of many) table(s).
I just hate to rely on users to not modify the memo field (user error being the primary reason for designing this solution). 
Just hoping for a non-volatile solution. Any ideas?
Thanks again!
|
|
|
|
| Tom |
| |
| Group | : Administrator |
| Posts | : 5510 |
| Joined | : 2006-02-17 |
|
| Profile |
|
| Posted : 2006-04-28 08:28:01 |
| Sorry, this method is only default visable link that I could find. |
|
|
|
|
|
| Posted : 2006-04-28 09:41:20 |
Please don't appologize, you have helped me get a great deal farther and much faster than I could have done otherwise. I am simply expressing my hope of a utopian solution.
I am working on using the solution you gave me (with some new controls for Quickbooks) and will give it a try over the next few days.
If you come accross another alternative method, let me know.
Above all,
Thanks |
|
|
|
| Tom |
| |
| Group | : Administrator |
| Posts | : 5510 |
| Joined | : 2006-02-17 |
|
| Profile |
|
| Posted : 2006-05-04 09:52:48 |
Actually, the best solution would be to write the PO # back into the SalesOrder. This can be done by doing:
UPDATE SalesOrder SET PONumber='42' where TxnID='5BDD-1197759656'
|
|
|
|
