8 Replies Latest reply on Apr 6, 2014 9:08 PM by JonathanWexler

### Post

The boss wants a new report, with some what unusual timeframes and data groupings running across the page.

Think about a sales  report based on a table containing customer #, sale amount and date, and creating a report than shows a customer on each line and then - on that line - total sales in January ... December.  Then multiply the complexity by about seven and that's what I am looking at.  I KNOW HOW TO DO THIS WITH CALCULATED FIELDS, but I am reluctant to add 70 calculated fields to a table that already has over 200 fields (about 120 of them calculated).  I am also reluctant to generate the report by cloning the key fields to a separate table which could hold the 70 fields.

Any advice on techniques to keep this maintainable?

Jonathan

• ###### 1. Re: Advice on Maintainablity

And what tables and relationships do you have? Do you have a table of customers linked to a table of invoices such that the total sales for a given month and year would be the total of a set of records in invoices with dates in that month and linked to the same customer?

• ###### 2. Re: Advice on Maintainablity

The short answer to your question is "yes", but I can reiterate that the example was hypothetical ( the real report has a host of fields like "the per unit cost of a sales order line item with a scheduled delivery date falling in the second subsequent to the run date of the report"). I can create 70 or so calculated fields and get this report done - I am just not thrilled with the prospect of adding 70 calculation/summary fields to a table that already has in excess of 200 field defined over it.

• ###### 3. Re: Advice on Maintainablity

I can't follow all of that. Sticking with this general model:

Customers::__pkCustomerID = Invoices::_fkCustomerID

and a field named InvoiceAmt in Sales recording  the total invoice amount plus an InvoiceDate field recording the invoice date.

You can add a calculation field to Invoices, cMonth, defined as InvoiceDate - Day ( InvoiceDate ) + 1 and select Date as the result type. This calculation returns the date for the first day of the same mont as that of the date in InvoiceDate so all records for a given month and year will have the same date in cMonth. You can also add a summary field, sTotalSales as the Total of InoviceAmt. This one summary field can be used to show many different subtotals in columns on your report.

In the Customers table, you can define a global date field, gMonth1 and put the date for the first month based column that you want in it. This can be edited on your layout to set up different ranges of months if you want. Define some calculation fields, cMonth2, cMonth3 for additional monthly columns.

cMonth2 would be defined as: Date ( Month ( gMonth1 ) + 1 ; 1 ; Year ( gMonth1 ) ). cmonth3 would add 2 to the month and so forth for each subsequent field.

You can then set up a list view layout based on Customers and put the customer name field plus a series of one row filtered portals to Invoices in the body.

The first one row portal would have this portal filter expression: gMonth1 = Invoices::cMonth. Put Invoices::sTotalSales inside this one row filtered portal and you will see the total amount for all the invoices for that customer for the month specified in gMonth1.

Repeat this for additional portals, but use cMonth2, cMonth3 in place of gMonth1 in the portal filter expression.

This is one of several methods that can be used to get a "Crosstab" report.

• ###### 4. Re: Advice on Maintainablity

Thanks for the portal suggestion - let me figure out what that buys me.

• ###### 5. Re: Advice on Maintainablity

The filtered portal method:

a) reduces the number of different relationships that might otherwise be needed to match to different subsets of records.

b) reduces the number of additional fields needed since the same filter field (cMonth) and summary field can be used over and over again.

The main draw back is that the totals shown on your report are "display only". You can't access those totals in a calculation or from a script to do anything more with them than display them on this layout.

Another option, if you are using FileMaker 12 or newer, is to use a series of Calculation fields defined in the customers table that use ExecuteSQL() to compute these sub totals. That's one calculation field for each column of subtotals, but you no longer need the cMonth fields in Customers to use in portal filters, no portals are needed, no relationships need be added to Manage | Database and the results are data in calculation fields so you can chart the data or otherwise use the totals in scripts and calculations.

The main draw back to this last option is that you need a decent knowledge of how to set up SELECT queries with aggregate functions in SQL and the current versions of FileMaker aren't very user friendly when it comes down to debugging issues with the SQL syntax.

• ###### 6. Re: Advice on Maintainablity

Thanks again: with your help, I think I understand this.

This is in large part a learning exercise for me, so please bear with me. I now really appreciate the filtered portal approach. We use FMP v10, so I would need to go about this somewhat differently.

I have attached a pdf that is simplified from the actual report, but presents the issues compactly. Fields in red are from the line-items table, in blue from the invoices table, and the part-red part-blue field is common to the two.

So my two candidates are 1) using repeating fields and not using portals - I see this as a moderate increase to the schema footprint, but requiring a whole bunch of tedious calculations 2) setting up a self-join for each "month" - that is "past due" "current" and "next month" I have some leanings towards repeating fields, feels like a somewhat flatter solution, and avoids some portal pitfalls

Any further advice would be appreciated.

Jonathan

• ###### 7. Re: Advice on Maintainablity

It's really important to let people know what version of FileMaker you are using. That keeps others who volunteer to answer your question from wastig their time posting a suggestion that does not work with your version of FileMaker.

But you can replace the filtered portals with portals that rely on a series of different relationsihips to different occurrences of the same table to do the needed filtering.

The gMonth1, cMonth2, cMonth3... fields become additional match fields in relationships to different Tutorial: What are Table Occurrences?.

Customers::__pkCustomerID = Invoices|Month1::_fkCustomerID AND
Customers::gMonth1 = Invoices|Month1::cMonth

Customers::__pkCustomerID = Invoices|Month2::_fkCustomerID AND
Customers::cMonth2 = Invoices|Month2::cMonth

and so forth....

• ###### 8. Re: Advice on Maintainablity

Phil -

Thanks for everything, and please do not think your efforts in explaining filtered portals was a waste ... it was what made things clear to me.

Jonathan