I am designing a web app that will sync to Quickbooks using OpenSync. I have it setup so that the web app is inserting to the ‘bill’ and ‘txnexpenselinedetail’ tables, and is working great.
Now I need to update the link between my web app and the Synced database so I can do things like check if it has been paid yet.
I don’t see anything in the documentation that indicates how I can map the temporary IDKEY that I use to do the insert to the new TxnID/IDKEY after syncing. I suppose I could search for various fields (i.e. vendor, amount, date, etc.) to match, but it would be great if I could just lookup what the new IDKEY is is for the temporary one I entered, and I can’t seem to find that anywhere.
Thanks in advance!
IDKEY will storage TxnID value, so you will be able to link both tables. I hope I answered your question.
Yes, I understand that. So when I “ADD” the item to the database to sync with Quickbooks, I assign a random ID number and put it in the TxnID field for the bills table, and in the IDKEY field on the expense table. After Open Sync syncs with Quickbooks that random number is replaced by an ID number generated by Quickbooks.
I want to know if there is a way I can search, after the sync, for the old random id number I generated to find out what the exact new TxnID is, so I can replace the link in my webapp’s database to make sure that the bill and the transaction in my web app are still linked together.
All the parent tables have a field called “UserData” which will be preserved. You can use that one in order to link your tables.
Fantastic, thank you!
you are very welcome
I’ve started testing and trying to get this to work, however, I’m finding it is not. I set the UserData equal to the random id that I also put into the TxnID. After I sync, the UserData field comes back as NULL (As do the CustomFields, which I had set data to). Is there something special I need to do force Open Sync to leave the UserData in place?
Any other options I can try?
cfuller I use the field “Memo” or POnumber to store information.
so for example I create an Invoice.. with a random id let say
in the Memo I put AT:MZ3i3m3i this way once invoice number is generated by the quickbooks API.
I will be able to locate the transaction by search for the “AT:MZ3i3m3i” in Memo.
i have the same problem that cfuller.
I’ve started testing and trying to get this to work, however, I’m finding it is not. I set the UserData equal to the random id that I also put into the TxnID. After I sync, the UserData field comes back as NULL (As do the CustomFields, which I had set data to).
I am having the exact same issue here. Bills just don’t seem to work with this UserData field. What should we do here? Is this something you’ll have a fix for in the next minor release? Or is there a configuration that can be added/changed to support this?
Also, what about the line items? Connecting via the idkey will get the correct bill, but line item to line item updates are still potentially a problem. How about extending the UserData field to all supported tables?
We have that field, but it is not supported yet. Sorry for the inconveniences.
Has there been any update on the best practice for this? I’m running into problems where vendors will have multiple invoices for the same amount, and I can not find a reliable way of relating items in my database to what is in OpenSync once the temporary ID gets changed to the QB Assigned TxnID. I’ve tried using the Memo field as one user suggeted, bt our accounting staff is very particular about the use of the memo field and does not like this option. This seems like a really simple problem and I would imagine someone has a fix.
After I “ADD” a record, how does my application know what the new ID is after the sync?
I am also having trouble with UserData field. How close is this to being corrected? It was one of the top features added to the 3.0 upgrade.
Support — PLEASE provide some support on how we can match these more accurately. There must be a simple way to do this.
You will have to find your own way to math those field. Sorry for the inconveniences.
You must be logged in to reply to this topic.