10 Replies Latest reply on Mar 17, 2014 2:57 PM by philmodjunk

    Total of Table lookup fields in multi record portal.

    DavidTory

      Title

      Total of Table lookup fields in multi record portal.

      Post

           Brand new home user licensee of FM13. I have gone round in mental circles on this. The answer must be so simple!!

           An individual's contact layout contains a portal that lists number of classes booked by that individual on a given day. The cost for each class in the portal comes from a Class Table look up. I want to sum the total of the class costs for that day and display it on the individual's contact layout. I've tried every option I can think of  but am missing something very  basic.

            

        • 1. Re: Total of Table lookup fields in multi record portal.
          philmodjunk

               If this is an unfiltered portal, then a calculation field in the contact table can be written with this type of expression:

               Sum ( Classes::Cost ) // use your portal table and field name in place of Classes::Cost

               With a filtered portal, you can use a summary field defined in the portal's table to show the total.

          • 2. Re: Total of Table lookup fields in multi record portal.
            DavidTory

                 Many thanks for your quick response. Unhappily, I can't get either of your suggestions to work.

                  

                 I had already  tried the Sum option. The Result field was blank. I have tried your Filtered Portal option, the records all appear in the Portal with the "looked up" Class cost, but the Summary field I placed in the Portal is blank.

                 I have another numeric field in the Portal Records which I am able to summarize in the Filtered Portal successfully. So, I must be doing something wrong with the field that contains the Table Lookup, even though each record contains the correct result of that Table lookup.

            • 3. Re: Total of Table lookup fields in multi record portal.
              philmodjunk

                   I did not suggest that you use a filtered portal. I asked if YOU were using a filtered portal as the sum function will return a total of all the related records. That will be all records shown in an unfiltered portal but not just the sub set of those records shown in a filtered portal.

                   Is the field of type number or text? For sum to work, this field must be defined in Manage | Database | Fields as a field of type number.

              • 4. Re: Total of Table lookup fields in multi record portal.
                DavidTory

                     The Class Rate Field is numeric both in the Table being looked up and in the Portal. Total field is defined as Summary, obviously.

                      

                     Thanks for the note on Filtered and Unfiltered Tables. I had not noted the difference in use.

                • 5. Re: Total of Table lookup fields in multi record portal.
                  philmodjunk

                       There is no summary field in use if you are using the sum function option.

                       It should be set up with this format and defined in the contact table:

                       Sum ( PortalTable::Class Rate )

                       Put the name of the table occurrence for your portal in place of "portalTable" in this calculation.

                  • 6. Re: Total of Table lookup fields in multi record portal.
                    DavidTory

                         A) I have a Numeric Field "Age" in the Portal which I have produced a Summary Total correctly

                         B) I have used the same Field to produce a Calculated Total using Sum (PortalTable::Age) correctly

                         I have tried to duplicate A) and B) using the Table Lookup field containing the Class Rate. Totals are blank.

                         I believe I must be missing something that is very straight forward.

                         It seems as if the Class Rate field contains a link or formula to the data but not the data itself.

                         Sorry to be such a bother.

                    • 7. Re: Total of Table lookup fields in multi record portal.
                      DavidTory

                           To clarify my previous:  The Summary Total was within the Portal Table area and the Sum total was in the Contact Table

                      • 8. Re: Total of Table lookup fields in multi record portal.
                        philmodjunk

                             I can make no further suggestions without a much more detailed understanding of what you have currently set up. There is more than one way to set up a "look up field" but as long as the result is a number in either a calculation field with number specified as the result type or a number field that uses an auto-enter option, Sum should work to compute the needed total from the set of related records.

                        • 9. Re: Total of Table lookup fields in multi record portal.
                          DavidTory

                               I will do some experimenting with different Lookup options to see if I can pinpoint what I am doing wrong. Failing that I will come up with another way of getting what I need without using Table Lookup.

                               Thanks for all your help. You have pointed me in a number of key directions.

                                

                          • 10. Re: Total of Table lookup fields in multi record portal.
                            philmodjunk

                                 Take a look at the invoicing starter solution that comes with FileMaker 11, 12 or 13. All three versions of this starter solution use looked up value settings to get the price from products into the line items or Invoice Data records used in the portal to list items purchased on a given invoice. Then a field shows the total cost for all records shown in that unfiltered portal.