3 Replies Latest reply on Sep 24, 2010 10:05 AM by philmodjunk

    Doing Lookups between two tables



      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.

      Thomas Hill

      ver10/11 Developer on Win7 and Mac (snow leopard)

        • 1. Re: Doing Lookups between two tables

          What I'm not seeing in your post is exactly how your setup fails. Where does this setup go wrong? Voucher numbers don't show up for any records? Can't perform a find that locates all records where VoucherNum is blank? or...?

          • 2. Re: Doing Lookups between two tables

            Even if I do a re-lookup on the invoice number in VendorStmt, no vouchers appear.  I have verified that there are some that are paid.  But even those do not show the voucher number indicating a paid invoice.  I am stumped.  Phil I do appreciate your quick response.

            • 3. Re: Doing Lookups between two tables

              I wouldn't use a looked up value setting just to see the voucher numbers--that works, but it's needless complication and puts two copies of your data, the voucher number, in two different tables.

              You'll need to check your relationships and the data used to match records.

              1. Check data types, make sure the fields on both sides of the relationship are the same type.
              2. If you have text fields involved, check for minor invisibile differences to see if something like a space character in one table's match field is keeping the values from being an exact match to the data in the related table.


              To see the voucher number, I'd just add the voucher number field from the related table occurrence to your layout. If there's a match, you'll see the number. If there isn't, you won't and no relookup's will be required. To find records that don't have a voucher number in the related field, enter find mode, enter a lone * in the field, select the omit option and perform the find.