3 Replies Latest reply on Jul 17, 2015 2:33 PM by philmodjunk

    Return value from specific layout/location



      Return value from specific layout/location


      First a little background:

      I'm having an issue with a function. Throughout our database we use the field "Investor Amount Total" which just totals the field "Investor Amount". We also have a field "Investor Total Percent" which provides each investors divides Investor Amount/Investor Amount Total.  The problem is that "Investor Amount Total" and "Investor Amount" are used in many different layouts and sum up different groups of numbers. This leaves the "Investor Total Percent" calculation confused. In some locations "Investor Total Percent" will retrieve the "Investor Amount Total" from one location, and "Investor Amount" from a different location, leading to incorrect percentages. 

      Now here comes my question:

      There is one layout where all of the data is being pulled from the correct location and everything is perfect. I want to create a new field that will draw the field "Investor Total Percent" but ONLY from the layout the provides all of the correct information. Is this something that's possible to do?

        • 1. Re: Return value from specific layout/location

          In Theory, a script could be used in several different ways to capture such subtotals, but it would be better to work through the "context" issues in your database design to figure out why you don't get the correct figures in some layouts. Even if you then decide to change how you use your summary fields and calculations, you do so from a point of understanding that let's you know what will work and what will not.

          Here's some info on the subject that I wrote up a while back that might provide you with a useful "starting point" in doing such analysis:

          A "non Running" Summary field produces an aggregate value (a value from more than one field in one record). The value returned is determined by the context in which it is used/displayed:

          Summary field is referenced on a layout based on the table in which it was defined:

          A group within a FoundSet

          If you place the summary field in a subsummary part that specifies the "break" field that grouped the records when the found set was sorted, you get a subtotal--the total for that group.

          In a calculation, you can use the getSummary function to access the same group based sub total.

          All the records in a FoundSet

          If you put that summary field in a layout part other than the sub summary part, you get the total for all the records in the current found set.

          If you refer to a summary field in a calculation field defined in the same table as the summary field, it will also return a total for the current found set. (Which is why we have the GetSummary function to get sub totals in calculations.)

          Summary field is referenced on a layout based on a table related to the table in which it was defined:

          Not in a Filtered Portal

          If you place the summary field on a layout based on a related table or refer to it in a calculation defined in a related table, the relationship controls the value that is computed. It will be based on all the records in the summary field's table that are related to the current record in this table.

          Think of it this way, if you put a portal on this layout to the summary field's table, you'd see all the records in this portal that are used to compute the summary field's value in this context.

          In a Filtered Portal (FileMaker 11 and newer only)

          If you place that summary field inside a portal with a filter, you no longer get a value based on all the related records. Instead, you see a value based on all related records for which the filter expression evaluates as True.

          This is a special case use of a summary field that is often implemented by putting a single row copy of a filtered portal below it with the summary field inside so that the user sees a value based on just the records visible in the larger portal.

          This is a "Display Only" trick as you cannot refer to the value of this field in a calculation and get the same value shown on the layout--you get the result described in "Not in a Filtered Portal" above.

          Note that this does not just apply to "total" summary fields, Average, Count, Maximum, standard deviation, etc all follow these same rules.

          • 2. Re: Return value from specific layout/location

            Great that definitely helps. I do not know very much about how FMP works. I have been working with it for a few months now and am just learning what I need to know as I go along. I understand that the best way to go about this would be to fix the context issues, but, at this time, I am not someone to do this because I don't know enough about FMP and about how this specific database was created. 

            From what you posted it looks like a Filtered Portal would be able to do what I'm looking for. Do you have any advice on creating this filter? I do not really know coding well, but I am quick to learn and have been able to solve a few problems already. Some help with this would be really appreciated. 


            • 3. Re: Return value from specific layout/location

              You'd need to describe your database, the tables, relationships, the layout design so that I has something to work with in order to provide an answer.