1 Reply Latest reply on Apr 14, 2010 5:08 PM by comment_1

    Setting up relationships

    TKnTexas

      Title

      Setting up relationships

      Post

      I would like someone to poke holes in this (please be nice about it).  :smileyhappy:

       

      I am working around the old accounting system we have ... from an inquiry perspective.  Query capabilities are minimal.  I will using FMPro Advanced to build a runtime to use on my XP machine.

       

      On June 1 we will be upgrading to a newer version of the same product... with no enhancements to the query capabilities.

       

      I cannot do any queries by check number.  I can query by invoice, but not see the check related to it that paid it.  Hence my work around.

       

       

      I have a table of all invoices posted for the day.  It has the core information regarding the invoice:

       

       

      Initial Import

      Text-Field         Vendor Number

      Text-Field         Invoice Number

      Date-Field         Invoice Date

      Number-Field       Invoice Amount

      Text-Field         Voucher Number

       

      Other fields of information not related to check printing are added also.. daily import

       

      Date-Field         Check Date  [to be updated at a later date]

      CALC-FIELD         (key)VendorNumber & mmddyyyy (created using text calcs from check date field) Each vendor number is only used once per check run.  It is as unique as the check number. 

       

       

       

      I have a table of checks per check run.  The core information I am capturing for each check is:

      Text-Field         Check Number

      Date-Field         Check Date

      Text-Field         Vendor Number      

      Number-Field       Check Total

      CALC-FIELD         (key)VendorNumber & mmddyyyy (created using text calcs from check date field) Each vendor number is only used once per check run.  It is as unique as the check number. 

       

       

      The data for both of these tables is derived from unparsed print-text files that I parse by text calculations.  All works well in this.  I need to relate the check table to the invoice table. 

       

       

      Since the invoice table does not know the check date until the actual date it is paid, I need to do a second import, from the precheck report.  This a list of invoices being paid on that date.  I want to have a temporary table to import the precheck report.  Then using a relationship from voucher number to connect precheck and invoice table, update invoice table for the check date.

       

       

      Once the invoice file has been updated for the check date, I should be able to see the invoices associated with each check. 

       

      I hope this makes sense.  It something I am going to try tonight but if it doesn't work I would like to hope it is just my scripting, not the premise.

        • 1. Re: Setting up relationships
          comment_1

           


          TKnTexas wrote:

           

          I need to relate the check table to the invoice table.


           

           

          I don't see how you can do this, based on what you said you have. The only thing they have in common is the vendor number. What if there are two (or more) invoices to the same vendor, and two (or more) checks?

           

          You said something about a "precheck report" (or table?), but with no details - same thing about "voucher number".