6 Replies Latest reply on Mar 18, 2013 12:08 PM by emotionuk

    Bank statement-style report from two tables



      Bank statement-style report from two tables



           I am trying to create a bank statement-style report of a supplier statement.

           Basically, the statement will contain four fields from three related tables.  The fields are: Date, Credit, Debit and Balance.

           One table is "Supplier" which contains a unique supplier ID, the next table is "Invoices", and the final is "Payments", all linked using the unique supplier ID.

           So, the data is stored like this: Date(Both Invoices and Payments table), Credit(Payments table), Debit(Invoices table) and Balance(summary field).

           But, I can't figure out how to get the information from the Invoices and Payments tables to show on my report.  I have tried creating a new table called "Supplier Statement" with the correct calculation fields, but this table contains 0 records, so I don't know if I need to perform some sort of scripted import from the other tables?

           Or maybe I am just being really stupid and missing something simple?

           Thanks all in advance,


        • 1. Re: Bank statement-style report from two tables


               Supplier::__pkSupplierID = Payments::_fkSupplierID
               Supplier::__pkSupplierID = Invoices::_fkSupplierID

               The problem here is that there is no directly link between Invoices and Payments. In order to get one row of data for a given transaction, the records in payments need to be directly linked to records in invoices.

               Is there only one payment record for a given invoice?

               Do all payments apply only to a single invoice?

               If so, you can either restructure your relationships to link payments to invoices by an InvoiceID or you can discard the payments table and move those fields from payments into the Invoices table.

               If NOT, I will wait on your description of how you need to manage payments and invoices before suggesting a different approach. (In some businesses, you have to have a join table linking payments to invoices so that on payment can pay off multiple invoices and so that one invoice can be paid off by multiple payments.)

          • 2. Re: Bank statement-style report from two tables


                 Thanks for your reply!

                 Currently the only common element between the tables is the unique supplier ID, because payments are not applied to specific invoices, payments are made independent of invoices, so for example the statement might look like this:

                 DATE     CREDIT     DEBIT     BALANCE
             13/2          $500                             $500           - THIS COMES FROM THE "PAYMENTS" TABLE
                  14/2                            $100           $400           - FROM THE "INVOICES" TABLE
                  15/2                            $50             $350           - FROM THE "INVOICES" TABLE
                  15/2                            $100           $250           - FROM THE "INVOICES" TABLE


            So the only common denominator is the supplier ID and the date...  Because the "Payments" table and the "Invoices" table are part of another section of the FM file I can't move/merge them.

            • 3. Re: Bank statement-style report from two tables

                   Yet a report layout cannot be based on records from two tables. It can only be based on records from one table and then relational links can be added, if needed, to link to data from other tables.

                   The simplest solution for this will be to define a new table with the fields you need for your report. Then, at periodic intervals, you would import the data from invoices and payments into this common table. Both imports would import the date, but your Payments table would import an amount into the Credit field and the invoices import would import an amount into the debit field.

                   You can then add a calculation field defined as:

                   Credit - Debit

                   and your summary field can be defined to compute the total of this calculation field.

                   A more radical option would be to make payments and invoices a single table. A "invoice" record in this table would leave the credit field empty. A "payment" record in this table would leave the Debit field empty. This option would then eliminate the need to import data into a third table to use for your reports.

              • 4. Re: Bank statement-style report from two tables
                 Phil, Thanks again for your help with this. I have decided to take the user to a screen where they select the date range they want for the statement and then it generates a report. Could you recommend what you consider best practice for this kind of table export/import.  What I want to avoid is lots of outdated data sitting around that I don't want/need. Do you have any suggestions for the scripting workflow as far as request report > export/import fields > exit report > destroy data? Thanks! Christian
                • 5. Re: Bank statement-style report from two tables

                       The achilles heel in importing the data into a "report" table rather than using a merged table for both payments and transactions is the risk that one of the records included in the report fails to correctly show the right data due to an update in a record in one of the two source tables after importing the data into the report table.

                       There are two options for handling that issue:

                       1) don't keep any data in the report table. When the user specifies a date range in a pair of global date fields, a script uses the resulting date range to perform a find for records of that date range in both tables, then does an import records on each table to pull them into the report table. After the report has been viewed/printed etc, the records are deleted from the table. This process can create a lot of complications in a multi-user environment as well as significant delays producing a report when the date range entered pulls up large numbers of records to import.

                       2) Import the records on a regular basis such as a script that imports the data once every night, but do not permit any records to be changed after their data has been exported without also running a script to manage the data in the report table. To correct data errors, you use a script that finds and deletes the matching record in the reports table and then either imports the revised data after the record edits are committed or set's a "flag" field so that the nightly import script successfully finds the modified record in order to import the data.

                       3) Hmm, come to think of it, you might be able to get away with enabling a "delete" option in a relationship between the two source tables and the report table plus a modification date field that will identify newly modified records and then an import script that does an import matching records type of import with the "add new records" options specified so that newly created records automatically generate a new record in the report table...

                  • 6. Re: Bank statement-style report from two tables
                    I think I'm going to go with option number 1 and keep no data in the reports table.  Seems like the neatest and safest way to me, and they will contain few records (only recent invoices) so I'm not worried about the performance hit. Thanks for your help and guidance. -Christian