AnsweredAssumed Answered

Setting up relationships

Question asked by TKnTexas on Apr 14, 2010
Latest reply on Apr 14, 2010 by comment_1

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.

Outcomes