4 Replies Latest reply on Feb 28, 2017 8:09 AM by lolli_group

    Subtotals under request



      I have a common list of items in a quote for customer layout (attached).

      Sometimes user need to have a subtotal of some of these items (usually not all, just any of them).


      I can use a check box for each item to determinate which one will go to do a subtotal, but how can I do this ?

      Do I need to re-open a different layout with a resume based upon checked items ?

      And what about other items, items that don't need a subtotal ?


      Thanks !

        • 1. Re: Subtotals under request

          Your screen shot may show records in list view or it could be a portal. Which did you use?


          There are several ways to get your report, none are terribly complicated as long as you only need one subtotal. Often, you can find working examples of this in invoicing solutions where some items listed are or are not "taxable" and you need a subtotal of taxable items in order to calculate the tax amount to add into the invoice total. (If you need multiple sub totals, it's still possible, just not as simple.)


          But the details can vary depending on whether we have a list of records or a portal listing related records here.


          Skipping over those details for now, here are three general approaches that can be made to work. The last option does not work if you are using a portal.


          1. ExecuteSQL could be used to pull up a sum of just those records with specific values such ans an InvoiceID and a check box field set to a specific value.
          2. A relationship could match on InvoiceID and this specified value. Then the Sum ( ) function can return this sub total.
          3. Sorting records to group all selected items in a group with unselected in a second group can allow a summary field and a sub summary part to show the total.
          • 2. Re: Subtotals under request

            Thanks for reply, the layout is on a print format, not in a portal, even if a portal is present in the layout of Quote.

            But now I need to work on printable format.


            I've read all options, but in every one do I need to build a different layout than actual one ?

            And if I make a new layout with a summary field for example based on the check box of each item, how can I add a part of items listed with subtotal and another without ?


            Or maybe Filemaker group only checked items if I order for check box ?


            I don't know function ExecuteSQL enough as I'd like. Can you please explain better what I need to add after ExecuteSQL, or send a link for that ?

            • 3. Re: Subtotals under request

              ExecuteSQL requires learning at least the basics of SQL--this is not something that I can do in a post here.


              But if this is a list view of your data for printing, then #3 seems the best option. You would not need a new layout, but you would need to modify the existing layout.


              You can add a summary field that totals the field from which you want that sub total.


              You can then add that summary field "when sorted by" the check box field. Place the summary field into this sub summary part.


              You then sort your records by this check box field and you will get two groups of records, those selected and those not selected with a sub total for each.

              • 4. Re: Subtotals under request

                Ok, I'll try immediately, than reply, thanks