1 Reply Latest reply on Dec 15, 2012 8:38 AM by philmodjunk

    A simple problem or not?



      A simple problem or not?


           The first of two related tables is a simple expense report allowing the user to select a category from a third table (also related) via a drop down box, and then enter an amount  and date of the expenditure. Each expense is an individual record on table number one. Also on the table is a summary of the amount that correctly updates when a new record is added, changed or deleted.

           The second table(related to the first)  is designed to display summaries of each category from the first table, which it does, but not dynamically.   A found set from the first table does not pass through to the second table, rendering it useless for the purpose of looking at reporting periods. The only (non-solution) I have found is to delete all records from unwanted dates not in the found set  from Table #1 and then click on each fiedl in table Number 2. Not acceptable!

           Preview does display the categories in table number 2 with summed expenses correctly (after clicking on each field) , but that's it. Nowhere to go with the data I need. Can't export it to Excel or post on another  FM report , which is the purpose. Worst is that it does not auto -update as new records are added in table numner one

           I'm looking for a workaround, or solution or maybe insight on why the found sets do not pass through to the second table. Any help would be greatly appreciated. 

           P.S. I use FM 10. Anybody know with certainty if 11 or 12 addresses this issue?


        • 1. Re: A simple problem or not?

               You can set this up in FileMaker 10,11, or 12.

               One option is to discard table 2 completely. You can set up a summary report with one row for each expense category to show the total spending in that category. You perform a find for the records you want in your report (such as all records for spending in a particular time period), sort the found records and you have what you describe here. All fully dynamic.

               To do this, set up a summary report layout based on your expenses table. Remove the body layout part from this layout. Replace it with a sub summary report "when sorted by" your expense category. Put a field (can be from your related third table) for the expense name and a summary field that computes the total of your expense amount table in this sub summary layout part. As long as your records are sorted by the same field that you specified as the "when sorted by" field in the sub summary part, the sub summary part will be visible.

               The find and sort you use to pull up the records used in this report can be done manually (but may need to be performed on a different layout based on the same table) or via a script.

               If you still want that 2nd table, it can show category expense totals if you use a relationship that matches by more than the expense category. It can match, for example, by a pair of global date fields in addition to the category ID field.