6 Replies Latest reply on Jul 18, 2015 4:27 PM by TKnTexas

    Looping through sub summary values



      Looping through sub summary values


      Here's my (simplified) example:

      I have a purchases file that contains supplier name and amount. There are multiple purchases for some suppliers.

      A simple listview report with no body part and a sub summary part for each supplier when the table is sorted by supplier gives me  the supplier's name and a total value of the purchases made from him - the amount I owe each supplier.

      How do I use the report as the source of information for a script that produces a check for each supplier? Can there be a 'found set' of sub summary values? Is there a way of looping through sub summary fields in a script?

      Many thanks for any ideas.


        • 1. Re: Looping through sub summary values

          I can think of multiple options--which works best would depend on what you mean by "producing a check on each supplier".

          You might have a table or value list of suppliers. Either could be used to produce a list of suppliers in a variable and then you could loop through records or values in a list in order to "perform a check". A relationship or eSQL query could use both the supplier ID and the same criteria used for your summary report to access aggregate data that matches your report but is specific to a given supplier.

          You can also set up a looping script with two loops nested inside each other that simply loops through your found set produced for your summary report and performs your "check" each time the supplier ID of the current record is found to be different from that of the preceding record. The outer loop performs the check, the inner loop just moves to the next record until such a change in Supplier ID is detected. GetNthRecord is a function that you can use to check the value of a field in the preceding record.

          • 2. Re: Looping through sub summary values

            Hello Phil - and thanks for your suggestions.

            Producing a check means printing a check (cheque) for the amount due to each supplier.

            I do know how to loop through the full recordset (ordered by supplier) accumulating amounts until the supplier changes and then processing the total. That is the method I used. But I wanted to be able to display the results to the user before processing so that he is aware of and can approve the checks being issued. This is easily done by a reports showing sub-summaries when sorted by supplier. It occurred to me that Filemaker was providing the information that I needed for each check and, if I could look through that sub-summary supplier names and totals, I could then just print the checks from that. But I couldn't find a way to do this and was wondering it there was  a method that could be used.

            Sub-summary parts would be far more useful if they could be access by a looping script for further processing.

            Any ideas?

            Thanks again.


            • 3. Re: Looping through sub summary values

              What do you need to do in your database to "produce a check"?

              When it comes to just printing a check, you could set up another list view layout where there is also a sub summary layout part  but were the sub summary layout part is designed to show amounts, etc in a format that prints on your check stock.

              But I suspect that you also have a table that serves as a transactions register where you would need to create one record for each such check produced. Such records would be pretty easy to produce from your existing layout if you did it this way in a script:

              Used Export Records to export this data to your temporary folder using the "group by" option to generate one row of data in the exported data for each sub summary group. Then used import records to import these new transactions into your check register table and then you could use a layout based on that table for printing your checks if such is desirable (Of course you might be exporting to a different application such as quickbooks to manage your check printing and that could also be done from this approach.)

              Note: files created in the temporary folder are not retained so you this process won't result in large numbers of files of financial data building up in your computer.

              • 4. Re: Looping through sub summary values

                Thanks for the further comments, Phil.

                My current solution works fine - where I am looping through transactions records sorted by supplier accumulating the check amount until the supplier changes and then I create the check and zero the accumulated amount. While working on this, I was looking at a a report that already had this information as sub-summary values and I just wondering if there was a way to loop through sub-summary parts and extract the data.

                Apparently not....!


                • 5. Re: Looping through sub summary values

                  I don't see how that information changes my previous suggestions.

                  I can imagine many different variations of "create the check and zero the accumulated amount"

                  And I must gently suggest that looping through the records that make up your report IS essentially the same thing as looping through the sub summary parts.

                  And it seems to me that it would be even simpler to loop through a found set of Supplier records instead of this summary report in order to generate the needed check and other database changes. A relationship from Supplier to these transactions can be so constructed as to match to the same records that makes up each sub summary group.

                  • 6. Re: Looping through sub summary values

                    My solution has been that the check table is the parent to the invoice table as the child.  The payment number is assigned to the unpaid invoices.  The check record has a calculation field that sums the records from the related file, the invoices.