8 Replies Latest reply on Jun 19, 2009 12:51 PM by singerdf

    sub summaries in portals

    annacaltech

      Title

      sub summaries in portals

      Post

      Hi,

       

      can you do subsummaries in a portal?

       

      What I want to do: have two databases (#1 and #2). They are in a many-to-many relationship. How can I create a portal in the layout (database #1) for database #2 showing all the records of database #2. But with a subsummary format, sorted according to database #2?

       

      thanks,

      anna 

        • 1. Re: sub summaries in portals
          philmodjunk
            

          You can't put sub-summary headings in a portal. You might be able to approximate the effect.

           

          Back before FMP 10, we'd sometimes need to make sub totals visible in a list or table layout while in Browse mode. One method, was to simply place the summary field in another column in the layout to get:

           

          Name         Category  Value  SubTotal

          Blueberry    Berry         5       15

          Strawberry  Berry         5       15

          Raspberry   Berry         5       15

          Tilton         Apricot      4        8

          Patterson   Apricot       4       8

           

          This would work equally well in a portal.

           

          If you want actual sub-headings, you'd have to generate dummy records in your related table to function as your sub heading and use a script to put the correct sub-totals in the correct dummy record. That approach can get a bit complicated, especially for records that undergo frequent updates.

          • 2. Re: sub summaries in portals
            mrvodka
               It may be possible. Could you please elaborate on this? Could you provide an example, input, and output...
            • 3. Re: sub summaries in portals
              singerdf
                

              Phil,

               

              This is sort of what I need to do.  I have a portal in which I select a material which is weighed.  I would like a summary of each material and its total weights.  I can get a running total of all weights, but do not know how to separate by material.  Your table looks handy, but I do not know how to implement it.

               

              any help would be great,

               

              Debbie

               

              • 4. Re: sub summaries in portals
                philmodjunk
                  

                The secret of this approach is a function called GetSummary(summaryField;breakField)

                 

                1. Define a summary field that computes the "total of" your number field.
                2. Define a calculation field that uses the above function: GetSummary(summaryField;breakField)
                3. Use the name of your Summary field as the first parameter, use the name of the field that groups your entries into the correct sub-categories when you sort your found set using this field as the second parameter, your "breakField".
                4. Make sure that your portal sorts the fields in an order that specifies this "breakField".
                5. Place your new calculation field into your portal.

                That should do it.

                 

                 

                • 5. Re: sub summaries in portals
                  singerdf
                    

                  You are a genius!!!  I would never have figured this out.  It worked great.  I could not even figure out how to sort the portal.  Double clicking gave me that option.

                   

                  Thank you for  your time and energy you put into the forum.

                   

                  Debbie

                   

                  • 6. Re: sub summaries in portals
                    singerdf
                      

                    One more question or so:  how do the getsummary fields populate themselves?  It almost seems like I have to tab through the portal??

                     

                    Next one:  Here is what I have:  many different types of gold, silver and other stuff.  I would like to show on a customer's receipt the total weight of gold metal and silver metal.  2 lines.  Right now I get each line with the subsummary.  It is right, but not clean looking for the customer and some what confusing for the customer.  

                     

                    Do I need to set up some sort of If loop and copy the field and then paste using scripting where it would look to see if the "material" field was gold or Silver?

                     

                    Thanks in advance,

                     

                    Debbie

                     

                    • 7. Re: sub summaries in portals
                      philmodjunk
                        

                      "Next one:  Here is what I have:  many different types of gold, silver and other stuff.  I would like to show on a customer's receipt the total weight of gold metal and silver metal.  2 lines.  Right now I get each line with the subsummary.  It is right, but not clean looking for the customer and some what confusing for the customer."

                      Well, that's how this technique works. It's not intended to group your records into a sub-summary format, just place the sub-totals in another column in the list.

                       

                      Being familiar with your invoice database from an earlier thread, I'd advise not using a portal at all for this. Instead, do the following:

                       

                      You'll need two fields in LineItems, a Total Cost Summary field and a "metal type" field that identifies each item as Gold, Silver, etc.

                       

                      Enter layout mode and select New Layout to create a new list/report style layout that specifies your line item table.

                      Select the Report with grouped data option and check both Subtotal and Grand Total check boxes.

                      Select the Line Item fields you want to appear in the body of the report. Do not add the summary or metal type fields.

                      In the next step, select your metal type field as your report category.

                      The next screen should show that the report will be sorted by your metal type field. Leave this as is and click next.

                      Now specify your summary field as the sub-total field.

                      Select this same field as your Grand Total.

                      Select the options you want on the next two screens.

                      Choose Do not create a script.

                      Choose Layout Mode and click Finish.

                      You now have three layout parts: a header, sub-summary part and a body. To finish your layout, you'll need to do some more editing.

                       

                      Add the fields you want from your Invoice table to the header part.

                      Add the summary field to your Sub-Summary part

                      Add a Trailing Grand Total layout part and add the same Summary field to use as your Invoice total

                      Add layout text as needed.

                       

                      Now you can enter browse mode, use a find request to enter an invoice number in the header of your new layout to find all the line item records for one invoice, sort the records by your metal type field and you're done.

                       

                      You can, of course, script  the find and sort operation and perform the script whenever a button on your current invoice layout is clicked.

                       

                       

                      • 8. Re: sub summaries in portals
                        singerdf
                          

                        Thank you.  I am going to try this.  Your earlier suggestion of using the lineitems portal in my invoice has worked beautifully.  And with the efftimestamp I can update the metal costs as often as I need to.  The same portal allows me to populate a police form and a receipt.

                         

                        Your suggestion yesterday on GetSummary also worked in the portal.  Today I wrote a not to elegant script to grab the totals for each metal, copy and paste them into the related fields on the receipt.  Lots of buttons for our front end people to use.  It works for me, but I have had complaints already.  They want simple like using a pencil and paper!!!

                         

                        So, I will continue to work the database.

                         

                        Debbie