Doing Lookups between two tables
I have a file with two tables:
Vendor Invoice Statement
AP Paid History
I have some vendors for whom I may have over a 100 invoices open at any given time. They provide their statements to me by Excel worksheets. I did this in ver 8.0. However, because I changes in our AP system I decided to re-write it 11.
I loaded their Excel data into a table (VendorStmt) and I pull a history from our AP history to a text file that I load into the other table. The actual order I use is AP History first, then Vendor Statement.
The data from our AP history is pure text, no delimiters. I load the data into a field DATA_IMPORT. From here I parse the data. I have used Text fields with a calculation type, using Middle (field, position, length). This part works perfectly. One of the fields is the invoice number. This is the field that is used to match to the other table.
The vendor statement has: AcctNum, InvoNum, InvoDate, InvoAmt. In each table I have the InvoNum field as unique. In the Relationship tab I have the two invoice number fields linked. In the VendorStmt table there is one field, VoucherNum, that is a look up field.
If the InvoNum in the VendorStmt is in the AP History table, then the lookup shows the Voucher number in VendorStmt, indicating that the invoice has been paid. I search for records with blank VoucherNum fields. Those are the invoice numbers that I need to get copies from the vendors.
I really need this work, because I can't reconcile the statement manually. Way too many invoices. I need to do this reconciliation weekly.
If there is more info needed to answer this question, please let me know. Ultimately this will be a runtime to use on XP.
ver10/11 Developer on Win7 and Mac (snow leopard)