How to keep Imported table in sync with historic table

[ejpvi]

I am seeking some advice on how to tackle a project I am working on.

there may be some flaws in my logic, but My idea made sense at the time, but I am still missing a key piece to keep everything tied together.

We have PO information kept in Quickbooks that I am deriving Vendor Status reports in FMP. The information will be updated regularly from QB... but the users need to make their own notes pertaining to each line item that will remain in the historic table in FMP...when the Table of QB information is cleared and re-imported.. I am hoping I can sync it back up with the historic notes and information in the historic tables.

I envision 3 tables.

Table 1: Is information exported from Quickbooks Enterprise... same format, just updated information.. imported directly in to a table in Filemaker with matching field names. (The data will be deleted and re-imported whenever they want refreshed info)

Table 2: will represent an Overall PO notes

Table 3: Will represent individual Iine item notes and information per PO.

Table 1 I was going to link by PO number to Table 2....

Table 2 I was going to auto-serialize records as line item notes were entered and as they were created in Table 3....

It gets a little confusing because I can easily keep PO's separated and in Sync, but it is possible the same Item number with different information may be present multiple times in a PO. I am trying to figure how to keep those Line Items in Sync with the QB Data...which unfortunately, I am lacking any real identifiers for, since some info will be changed in QB.. and I run the risk of it not matching back up with my historic data.

I hope this makes sense.

I was hoping by keeping an Indexed serial key in the Overall PO and Individual Item table, I could keep everything separate and in sync.. but it doesn't solve how my imported data will know which Line Item goes with which key. If the data were re-sorted before import.... it would throw all of the historic info off.

Example:

PO #1

Item 0001

Item 0002

Item 0003

PO #2

Item 0001

Item 0001

Item 0001

Item 0002

There may be individual notes and due dates for Item 0001 in PO#2... how can I keep that data in sync with my historic info.. if the only identifiers I can really match up are item # and PO #

I feel I need to somehow serialize the data from QB.. but since it is pulled from QB and deleted in FMP.. I am not sure how to progress.

any advice would be appreciated.. I may be going about this all wrong.

thanks!