2 Replies Latest reply on Mar 31, 2016 12:05 PM by erolst

    Fossilized trying to store summary fields from a layout in a table

    PieterStaes

      Hi all,

       

      I'm extremely new to filemaker and am completely stuck and yes, this is not even urgent anymore, it is beyond the end of times :-(

       

      I have a layout of products a-z sold in various branches 1-8 on different dates (this is a portal based on a join table branches & products).

       

      I use a subsummary part (branch ID) which breaks my list up into branches, so I get a list of products a-z if they have been sold

      I then use another subsummary part (product ID) which breaks each branch up into products, so I get a list of all the instances (with different dates) the same product is sold .

       

      Placing summary fields for quantity sold and value sold (qty * price) in the subsummary parts allows me to neatly see the total quantity and total value of all instances products a-z are sold per branch.

       

      And here comes da pain.... Now I need to find a way to store the results of the summary fields in the subsummary parts in a table (can even be the same table), since I want to use them as stock out for my stock table (which is a different one). If this was excell, I could simply make a sum of each subsummary part and have the data stored, but in filemaker I am this stuck in the mud I'm  about to fossilize....

       

      Anybody any ideas? Off to bed now, 1.42am. Many thanks already!.   

       

      Pieter

        • 1. Re: Fossilized trying to store summary fields from a layout in a table
          coherentkris

          you could always write a scripted process to do the calculations and store them as records in a table.

          • 2. Re: Fossilized trying to store summary fields from a layout in a table
            erolst

            PieterStaes wrote:

            And here comes da pain.... Now I need to find a way to store the results of the summary fields in the subsummary parts in a table (can even be the same table), since I want to use them as stock out for my stock table (which is a different one). If this was excell, I could simply make a sum of each subsummary part and have the data stored, but in filemaker I am this stuck in the mud I'm  about to fossilize....

             

            Since this is FileMaker , you can get the value per sub-summary part using the calculation

             

            GetSummary ( summaryField ; sortFieldOfSubSummaryPart )

             

            Create another summary field sCountAll (= count of primary key), and write a script like this:

             

            # find your records

            # sort by product(ID)

            Go to Record ( First )

            Loop

              Set Variable [ $countThisGroup ; GetSummary ( sCountAll ; sortField ) ]

              Set Variable [ $sumThisGroup ; GetSummary ( summaryField ; sortField ) ]

              Set Field [ Table::fieldWhereToStoreTheResult ; $sumThisGroup ]

              Exit Loop If [ Get ( RecordNumber ) + $countThisGroup > Get ( FoundCount ) ]

            End Loop