5 Replies Latest reply on Jun 4, 2014 1:24 PM by philmodjunk

    conditional calculations



      conditional calculations



           I have a table where users can currently enter a two masses and then a calculation field will display the percentage of the larger mass that the smaller mass is.  For instance, if you entered that you prepared 100 grams of dough and 10 g chocolate chips, it would give back to you 10% in the calculation field ("percent by mass").  However, some users want to be able to go the other direction, entering the mass of dough and the desired percent by mass of chocolate chips, and having it return the mass to add.

           Is there a way to define this calculation such that people can do it in either direction?  I'd rather not create another set of fields because this information needs to be searchable and useable for later calculations.  I'm thinking this will require some if(Notempty()) type stuff, but I'm not sure how to approach it.



        • 1. Re: conditional calculations

               See this demo file: https://dl.dropboxusercontent.com/u/78737945/EditableCalcsDemo.fmp12

               Script triggers are used to clear a mass field when the user enters a percentage and entering data in that same mass field clears the data in the percentage field in order to keep the auto-entered values properly in synch with each other.

          • 2. Re: conditional calculations

                 I actually ended up doing the following, and it works with my test fields

                 A= entered by user, mass of original material

                 B= % mass desired, defined field calculation as:  case(not(isempty(C);C/A)

                 C= additive amount, defined field calculation as:  case(not(isempty(B);A*B)

                 This seems to work now.  Is there any potential difficultly with doing it this way?

            • 3. Re: conditional calculations

                   C/A does not match your original description:


                        then a calculation field will display the percentage of the larger mass that the smaller mass is

                   That may just be a lack of clarity in your original post (it doesn't specify which field gets the larger mass), but it's why the demo file uses max and min functions instead of just using a C/A type division calculation.

                   I'm also assuming that these are auto-enter calculations.

                   Other than that, test this carefully. In particular, enter one set of values and then try out editing one field to modify the data in it and watch to see if the other auto-enter calc field correctly updates or not. That's where I ended up using script triggers to clear the other field to keep things consistent with such editing of the values. But I used slightly different expressions so you may or may not encounter the same issue.

              • 4. Re: conditional calculations

                     Ok, it sounds like I should definitely include those emptying scripts in order to make this more robust.  I am trying to recreate the SetField[editablecalcsdemo::PctEdit; " "]  script.  How do I get it to accept the (;" ") part?  I have never used SetField before, and when I specify the step and select PctEdit, it doesn't let me then add the desired value (; " ") to the end of the script step.  I must be doing something simple wrong, but I'm not sure what.  I tried using the window to just manually enter [editablecalcsdemo::pctedit; " "] as the script spec, but it won't accept this, I get a dialog saying that I need an operator after the semicolon.  

                • 5. Re: conditional calculations

                       When Setting up Set Field, there are two Specify buttons that must be clicked. To get Set Field [Table::Field ; Expression], add set field to your script and click the first button (specify target field). Select Table::Field from the list of fields. Do not click the specify button next to the repetition box. Click OK to close this dialog box. Now click the lower specify button (calculated result) and create the expression to the right of the semicolon (;). Do not try to type in the semicolon.