7 Replies Latest reply on Apr 20, 2017 8:04 AM by philmodjunk

    Show summary of field value on related table's layout

    BKamp

      Hi guys!

       

      I have two tables that matter:

      Expectations

      Results

       

      I have made a layout based on the Results table. In this table I would like to show summaries of fields from the Expectations table such as Expectations::gross_income

      I have done the following:

      1. Related the two tables with the fund field that is present and populated in both tables. Expectations::fund = Results::fund
      2. Created the calc field Expectations::gross_income_total with the following calculations field: sum ( gross_income )
      3. Added the field to the layout based on the Results table, now the field displays <index missing>

       

      What am I doing wrong?

      The layout is to be filtered by the fund field to only show results for a specific result, when this is done, I would like the expectations summary field to be about that fund only as well.

       

      What approach would I use?

        • 1. Re: Show summary of field value on related table's layout
          philmodjunk

          You should look up the sum function in Help.

           

          Sum ( FIeldA )

           

          will just return the value of FIeldA from the same record.

           

          Sum ( Field A ; Field B ; Field C ) will return the sum of those three fields from the same record as the record in which you defined this calculation field.

           

          Sum ( Repeating Field ) will sum the repetitions of the repeating field

           

          Sum ( Related Table::Field A )  will sum the value of Field A across all the records in the related table that are related to the current record of the table in which you defined this sum calculation.

           

          Either define the Sum Calculation in a field in the Results table instead of expectations or use a summary field in Expectations in place of this calculation field.

          • 2. Re: Show summary of field value on related table's layout
            BKamp

            Hi Phil,

             

            That's what I thought, None of them work though. I have done the following:

            1. created a calc field in the results table with : Sum ( expectations::gross_income ) (this stays empty when put on the layout).
            2. put a summary field of the expectations table summary -> gross_income (this stays empty when put on the layout).

             

            Portals are not working either so something is wrong with my relationships but I have no clue what it is.

            creating a single condition relationship trough a text field should work right?

            Se my first post for the description.

            • 3. Re: Show summary of field value on related table's layout
              beverly

              can you take a screenshot of the relationship between these two tables and perhaps a screenshot of the results (in layout mode)?

              beverly

              1 of 1 people found this helpful
              • 4. Re: Show summary of field value on related table's layout
                BKamp

                Sure!

                the relationship:

                https://www.dropbox.com/s/3qwp8n484z62mun/Screenshot%202017-04-19%2021.54.50.png?dl=0

                the results table:

                Dropbox - Screenshot 2017-04-19 21.48.58.png

                the expectations table:

                Dropbox - Screenshot 2017-04-19 21.49.04.png

                the layout based on the results table: (the gray field left is the 'expect:income' field, the the results table. As you can see nothing is displayed, just the placeholder text)

                Dropbox - Screenshot 2017-04-19 21.51.38.png

                 

                That's it, let me know if you need more info.

                Thanks!

                • 5. Re: Show summary of field value on related table's layout
                  philmodjunk

                  From the connector line, the fund field in expectations is a global or otherwise un-indexed field. This is what is keeping this calculation from working. The relationship from expectations to fund_results is a "one way" relationship  from expectations to Fund_results, it will not work from Fund_Results to expectations.

                  1 of 1 people found this helpful
                  • 6. Re: Show summary of field value on related table's layout
                    BKamp

                    Hi Phil,

                     

                    This is it! I made them indexable and now they work.

                    does this mean:

                    1. I cannot use a field that has a calculation which gets the fund from the related table's record?
                    2. I can use an auto enter text field with this calc, but when somebody changes the fund in one record, the related record does not get updated with the new fund assignment?
                    3. I need to write a script to avoid this?
                    • 7. Re: Show summary of field value on related table's layout
                      philmodjunk

                      Well it depends. Note that a relationship where one ( not both ) match field is unindexed does work, but only in "one direction". So as in the case of all FileMaker relationships, context is key (pun intentional).

                       

                      Two methods are commonly used to get around this limitation where you need to calculate a value from related data, but need it to be indexed:

                       

                      Make it an auto-enter calculation

                      Use a script to update the value everytime a value in the related table is changed/added/deleted.

                       

                      An auto-enter calculation does not re-evaluate when data from a related or global source is modified so you have to script the updates. Thus, both methods are often combined. Script triggers and custom menus are usually needed to implement those needed scripted updates.

                       

                      OnObjectSave on a field in the child record, for example, could update a parent record value whenever the value in that field changes. A custom menu can replace the standard New Record and Delete Record (and Delete Found Set) options with scripts that both update the needed values and then do the needed record deletes.