2 Replies Latest reply on Oct 14, 2011 2:50 PM by d.vanlooij

    help with calculating within a selection

    d.vanlooij

      Title

      help with calculating within a selection

      Post

      Hi,

      I have a problem with my datbase which I am building. 

      Background info: 1 table, name:production, is filled with production data, time, date, target, realisation, workplace, etc.
       1 table, name: settings, is filled with global fields, one of them is total_goal_workplace.

      My problem: I have made a script which says:
       go to table production,
       omit records not workplace 2
       set variable $goal1 with value: sum(production::target)
       go to table settings
       replace field contents [settings::total_goal_workplace; $goal1]

      but my result is not the total of al the values in the records of target.

      please advise me

        • 1. Re: help with calculating within a selection
          philmodjunk

          If you insert a pause script just before the set variable step or use FMP Advanced's script debugger and data viewer can you confirm that the correct records have been found? A "total of" summary field defined in production should report the correct total at this point.

          Is this layout run from a layout based on Settings or Production? This is an important detail here.

          Either way, the sum function won't work here. If this script is run from Production, you'll get just the value of target for the current record. If you run it from Settings, you'll get the sum of all target for all records related to settings and this set of related records is not affected by the steps you started with to pull up a found set of all recorsd except "workplace 2".

          Run the script from production. Define the Summary field, call it sTotalTarget and then change the last part of your script to:

          Set Variable [$gloal1 ; Value: Production::sTotalTarget]
          Go to Layout [Settings]
          Set field [ Settings::total_Goal_workplace; $goal1]

          This just fixes your script. With the right relationship, you can actually pull up this target total in settings without using any script at all.

          • 2. Re: help with calculating within a selection
            d.vanlooij

            Phil,

             

            thanx a lot! it works great!

             

            with kind regards,

             

            dirk