3 Replies Latest reply on May 19, 2015 6:47 AM by philmodjunk

    Calculation help please



      Calculation help please


      Hi all,

      Not quite sure why this won't work... I have 2 layouts. I want to take a field on layout 1 and multiply it by a field on layout 2. I have another field on layout which has a calculation on in =(layout1field * layout2field).  Nothing happening!

      I can multiply a field on layout 2 by another field on layout 2 and the calculation field shows the answer but not when I try to pull a value from layout 1.  I've managed it before but for some reason it won't work this time.  Having a brain freeze and need a little advice to point me to the 'stupid door' so I can walk out of it.

      Using FM14 (although have done the same on FM13 with the same result). All fields are either Number or the result of calculation is displayed as a number.

      Help please...

        • 1. Re: Calculation help please

          The issue is not whether the fields are on layout 1 or layout 2, but rather what Tables they are defined in. Your two layouts might refer to the same table or different tables or even two different occurrences of the same table. This will make a difference in what is happening when you multiply the values from these two fields.

          So what is your set up? are the two layouts based on the same table occurrence or different ones? (Check and see if the exact same text is selected in the "show records from" drop down found in layout setup for each layout.)

          • 2. Re: Calculation help please

            Oops, apologies Phil.. I should have said both layouts relate to their own table. Layout1 is say Table 1, layout2 is table 2. I put a relationship between both tables as a clientID field although this field doesn't relate to the calculations in any way at all.

            • 3. Re: Calculation help please

              But it does related and relate is exactly the right term.

              In order for a calculation to take a value from table 1 and multiply it by a value from table 2, the client ID field values in the two records must be identical. The "context" you select from the context drop down in the top of Specify Calculation can make a difference also if there is more than one table occurrence box in the relationship graph for the field in which you define the calculation.

              Then there is the detail that if you have a record in table1 with clientID = 1 and 3 records in table 2 with ClientID = 1, a calculation field defined in table 1 will multiply the valuein Table1::FieldA with the Table2::FieldB value from the first related record from Table2 and ignore the values from the other two related records. In an unsorted relationship, which is the default relationship unless you specify otherwise, the oldest record in Table 2 where ClientID = 1 would supply a value to this calculation for the record in Table 1 where ClientID = 1.

              So you can see that there are a number of possible reasons why your calculation might not work as expected.