Creating reports from multiple tables

[apinrise]

Hi Everyone, this is my first post here, but it seems this community is really dedicated to providing helpful feedback. I thought you might be able to help me with my problem.

 

I am helping my employer move from his old database (FoxBase) to a new filemaker system. I managed to extract all of the data and structure of the old database, normalize it (to some extent) and have re-created this in FileMaker and successfully imported all the data.

 

That was the easy part. I have a bit of experience with php/mysql so perhaps my understanding of retrieving data is a bit different from how filemaker treats it, but I cannot for the life of me figure this one out.

 

I am trying to create a "consignors report" which pulls data from a lot of the tables in the system. Normally, I would do a SELECT * FROM to get this information, or at least that is how I conceptualize the process of obtaining data. More specifically, in order to get a "sales to date" value, I need to get the sum of all consigned items from a consignor that appear on an invoice...For "sales for which I have not been paid" I would think that I could find those items on unpaid invoices. It isn't obvious how to do this sort of thing with FileMaker, and I'm a bit stuck in my thought process and can't get past thinking of reports in terms of SELECT and FROM. How can I pull (and calculate) data from various tables into one report? All of my efforts have come up short.

 

I have a screenshot of what I want to achieve in the end, and there is also a screenshot of my ER diagram.

 

Thanks in advance for any suggestions, and I hope I have explained well enough.

 

[Matthew F]

I'm assuming that you have a table that lists monetary transactions, (+) values for credits, and (-) values for debits.  You'll want to filter these by client, either by performing a search on the primary table, or through a relationship to a related (invoice) table.

 

A lot of what you're after can be done with calculated fields residing directly in the transactions table, using the sum( ) function.  Your report values are a little unusual, in that you are summing up amounts from bills not paid, rather than the net balance for a client. (What happens if someone over or under paid on an invoice?) You can do it your way, of course, but you'll need to flag line items as to whether they are paid, and then use this as part of your calculations. 

 

In the screenshot that you've shown, you're propably going to want to put these calculations in a layout footer (not on the body of a record).  You could also use a sub-summary part, if your records are sorted in a particular way. 

[apinrise]

Hey Matthew, thanks for the advice, but I'm perplexed.

 

The ARCUST01 table tracks payments, but not both incoming and outgoing though. There is a DISBURSE table that handles payments to consignors, while the former tracks whether or not (and how much) a customer has paid towards their invoice of purchased items. So, you're suggesting the issue is that they are not together in one table?

 

I tried creating a summary field, with a running total in LINEITEMS to calculate when I sort by CUSTID, but could not get it to work. The sub-summary section would only show the last transaction, not a summary of them all. (Do I really need to apply payments to the line items directly, or can it just go to the invoice as a whole? That is how the old database was handling it.)

 

I'm concerned, since this is pretty much a direct copy of the old database, that there is something more significant creating these problems. Such as, a relationship error, or something like that.

[Matthew F]

The ARCUST01 table tracks payments, but not both incoming and outgoing though. There is a DISBURSE table that handles payments to consignors, while the former tracks whether or not (and how much) a customer has paid towards their invoice of purchased items. So, you're suggesting the issue is that they are not together in one table?

To take advantage of Filemaker's built-in functionality its nice if you have a table that lists both credits and debits, with + and - values, respectively.  That's not to say that you can't have separate tables of invoices and payments and then combine them in a common transactions table.  As an example, see this prior post.

 

 

 

The sub-summary section would only show the last transaction, not a summary of them all.

For a sub-summary to appear, then you will need to sort your data by the relevant field (as defined when you setup the sub-summary on your layout).  If you want to be able to sort the same layout in multiple different ways, either individually or concurrently, you can add multiple sub-summaries to a layout. 

 

Usually you will want to populate the sub-summary part with either Summary fields or with Calculation fields that perform an aggregate-type function (average, sum, count, etc.)