Bank statement-style report

[emotionuk]

Hello,

 

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,

 

Christian.

[Matthew F]

Perhaps you want to have a "transaction" table that consists of records for both debits or credits?  Records could be generated by scripts or by relationships to your invoice and payment tables.  Simply sum up the transactions at the end of the reporting period to generate the balance on your supplier statements. 

[emotionuk]

Matthew, thanks for the reply.

 

Could you give me an example of how you would populate the transaction table using the data from the other two tables?

 

For example, if I make a relationship between the "Suppliers" table (which contains the supplier's details but no credit or debit information) and the "Invoices" table (which contains the debits) and the "Payments" table (which contains the credits), then the "Suppliers" table contains 100 records (our suppliers), the "Invoices" table contains about 1000 records (our debits) and the "Payments" table contains about 250 records (our credits).  How can I pull them all together in one report in Date order?  I don't think that using the supplier's ID as a relationship is enough...

 

Do I need to script some sort of automatic table populating so that when you press a "report" button it pulls all that info together in a separate table?  If so, what might that look like, and how do I stop loads of erroneous populated tables from hanging around?

 

Thanks again everyone!

[Matthew F]

I'd think that you'd want to have some scripting involved.  The attached file shows transactions generated by scripts either from your Invoice or Payments tables.  Also the Transaction table has calculations that shows the balance forward for items prior to your report start date. 

BankStyle.fmp12.zip

[rwflick]

Thank you, Matthew, for posting the sample Bank Style database.  I have been tinkering with FM now for a few years and this problem continues to cause problems for me and it comes up in some way, shape or form in almost every database I have tried to create.  I have come across three ways to solve this problem:

 

1.  As you have done here, create the Transactions table and use a script triggered by the form that populates it when the user creates a new Invoice or Payment.  The problem that I see with this method is that if you change the form or something breaks in the script, the transactions table may not get populated and it might take a while before it is noticed.  Also, there may be a problem with orphaned transactions if you are not careful to make sure that if the user deletes the Invoice or Payment, that there is another script to delete the related transaction.  These problems are all solvable, of course, through careful scripting and, I supposed, additional levels of validation scripting.  The benefit, though, is that I really like having a separate table for Invoices and one for Payments.  Everytime I have done it this way, I have been generally happy to have the data separated but end up pulling my hair out for reports that require a combination of the transactions which are stored in other tables.  

 

2.  Which leads me to the second choice which is temporary reporting tables.  If the combined transaction data (e.g., for a statement) is only needed for certain reporting, a temporary table with all the fields needed for a report can be created and a script written that goes through and pulls all the Invoices and Payments and creates a record for each, produces the report and then dumps the data (or keeps it until the next report is run).  

 

3.  There is a term for the third version but I don't remember what it is.  It's named after someone who came up with the idea but the idea is basically to take one or more common items from the Invoices and Payments and move them to the Transactions table.  The most common candidate for this would be the date.  Since all transactions have a date, rather than store a date in the Invoice table and in the Payments table, the Invoices and Payments table each have a foreign key link to the Transactions table (as your sample does) and when the user enters the date on the layout, a transaction record is automatically created with the date and a link to the Invoice or the Payment.  When you run a report on the Transactions table, it lists all transactions regardless of whether they come from the Invoices or the Payments table.  Scripting is necessary, however, to populate the debit and credit for the transaction but those could even be calculated fields in the Transactions table which go and get the amount of the invoice for the debit field from the Invoices table if it is an invoice transaction and the credit for the credit field from the Payments table if it is a payment transaction.  The problem with this approach, in my opinion, is that now the date of the invoice is in another table and every time you have a layout on which you want to display the invoice date, you have to add a relationship and pull the date from another table.  

 

My question really is, from a database design perspective, which is the better choice.  I understand that it may be a matter of opinion and, if that's the answer, that's fine.  However, I know from experience that changing a structure later can be a nightmare and I'd rather have a good, solid entity relationship diagram up-front before I start to set-up my tables and relationships in FM.  And, if a certain approach results in more calculation fields or more table occurrences then performance will be a problem as the data grows and as you add users.

 

I am sorry for the long post but this problem has been driving me crazy and I need to settle on a theory and use it consistently. 

 

Any input would be greatly appreciated.

 

Thanks!

[comment]

My question really is, from a database design perspective, which is the better choice.

 

Speaking purely from a database design perspective, if you have a need to report on transactions as such, then you clearly have a Transactions entity and therefore should have a Transactions table. So your third alternative - for which the proper name is supertype/subtype - is the correct choice.

 

Speaking from a utility perspective, however, the strictly correct choice is not always the best one to take. Implementing the supertype/subtype model in Filemaker is not trivial (although the difficulties you mention are not necessarily the difficulties I would choose to point out). OTOH, producing an ad-hoc union report of two (or more) tables is fairly easy, using any one of several possible methods (of which you mention only one).