Calculation IF based on a Pick List Value

Tap Forms – Organizer Database App for Mac, iPhone, and iPad Forums Using Tap Forms Calculation IF based on a Pick List Value

Viewing 8 reply threads
  • Author
    Posts
  • December 25, 2019 at 9:22 PM #38909

    Wayne Powell
    Participant

    (I’m just beginning to use Tap Forms in a more sophisticated way than I have before).

    I am trying to replicate a Calculation Form, such that you enter a Value, and a Unit, and Then have a calculation (or calculations) output conversion values.

    So I am thinking something like this:

    Value = 12
    Unit (from pick list) = “inches”
    Calculation Result (in Feet) = 1 Foot

    SO I am thinking something like this (but not suer if it can be done or I have the syntax incorrect or?

    IF([Unit] = “inches”, [Value]/12, “N/A”)

    Also, can I cascade the IFs such that I more or less replicate a complex If/Else calculation:

    IF([Unit] = “inches”, [Value]/12, IF([Unit] = “yard”, [Value]*3))

    Ultimately it would great to be able to utilize TWO pick lists so I can pick both the Input Units and the Output Units.

    I’m not great at programming scripts in JavaScript or how to implement them in Tap Forms (yet, If I was going to do that I’d probably switch platforms and learn to create an App in Swift.)

    Any pointers would be greatly appreciated.

    December 26, 2019 at 12:53 AM #38920

    Brendan
    Keymaster

    Hi Wayne,

    The IF() function takes 3 parameters. The first parameter MUST be a numeric comparison, not a textual comparison.

    For textual comparisons, use the IFEQUAL(X; Y; A; B), where X and Y must be textual and A and B can be anything that matches the Result Type specified on the formula editor. It means if X = Y, then return A, otherwise return B.

    So you would do:

    IFEQUAL([Unit]; "inches"; FORMAT([Value] / 12; "0.00"); "N/A")

    The reason for the FORMAT() function in there is because you’re returning "N/A" and that’s text, so your whole function needs to return Text. The FORMAT function will convert a numeric value into a textual representation. Plus it lets you format the resulting value. In this case in a decimal format with 2 decimal places. E.g. 1.20

    Yes, you can nest IF functions.

    IFEQUAL([Unit]; "inches"; FORMAT([Value] / 12; "0.00"); IFEQUAL([Unit]; "yard"; FORMAT([Value] * 3; "0.00"); "N/A"))

    But JavaScript would be much simpler to read:

    var value_field_id = "fld-.....";
    var unit_field_id = "fld-.....";
    var value = record.getFieldValue(value_field_id);
    var unit = record.getFieldValue(unit_field_id);
    var result = "";
    
    if (unit == "inches") {
       result = value / 12;
    } else if (unit == "yard") {
       result = value * 3
    } else {
       result = "N/A";
    }
    
    return result;
    

    I haven’t run the above code. It’s just off the top of my head, but that’s the gist of it.

    Thanks,

    Brendan

    December 26, 2019 at 6:31 AM #38936

    Wayne Powell
    Participant

    Thank you! Extremely useful. I guess I really am going to need to learn JavaScript to some extent.

    Is there a library of pre-made script examples that I have missed located in a Github or some other place?

    December 26, 2019 at 9:15 AM #38938

    Wayne Powell
    Participant

    I also looked but can’t seem to find a reference as to how to determine the Field ID address when using Tap Forms iPad app to programs script. Is there a built in way, or a Form script to run to print out a list to screen?

    December 26, 2019 at 2:42 PM #38941

    Brendan
    Keymaster

    If you tap on the Fields button at the top of the keyboard on the Script Editor, you’ll see a list of fields in your form. There’s a segmented control button that lets you have Tap Forms generate a getFieldValue() command or to generate a variable with the field ID. Tapping on the field will insert the code into your script.

    There’s the Script API in the docs and also there’s the Script Talk forum where you can ask questions about scripting plus see the scripts others have posted.

    https://www.tapforms.com/forums/forum/script-talk/

    https://www.tapforms.com/help-mac/5.3/en/topic/javascript-api

    December 26, 2019 at 3:48 PM #38946

    Wayne Powell
    Participant

    OK, got it! Thank you. I wasn’t seeing the forest for the trees there and forgot about the Pop Up Keyboard (I had looked at an example from the desktop version, so I was focussed on seeing a sidebar).

    January 1, 2020 at 9:36 AM #39049

    Wayne Powell
    Participant

    OK, getting back to this one now that I have some time. I’ll search for an answer (as I am still again at very basic level of learning JavaScript), but my initial attempt returns an error (Return statements are only valid in Functions):

    var length = record.getFieldValue('fld-0f39aaf844314fde8b5af7adb5d3bd5f');
    var value_unit = record.getFieldValue('fld-616fa42329f347148fa7813db6a3289b');
    var result_unit = record.getFieldValue('fld-3148ad66cbb14d5b875ee8b26429cdec');

    var result = "";

    if (result_unit == "inches") {

    if (value_unit == "feet") {
    result = length * 12;
    } else if (value_unit == "yard") {
    result = length * 36;
    } else if (value_unit == "cm") {
    result = length * 2.54;
    } else if (value_unit == "metre") {
    result = length * 0.0254;
    } else {
    result = length;
    } }

    if (result_unit == "feet") {

    if (value_unit == "inches") {
    result = length / 12;
    } else if (value_unit == "yard") {
    result = length / 3;
    } else if (value_unit == "cm") {
    result = length * 30.48;
    } else if (value_unit == "metre") {
    result = length * 0.3048;
    } else {
    result = length;
    }
    }

    if (result_unit == "yard") {

    if (value_unit == "inches") {
    result = length / 36;
    } else if (value_unit == "feet") {
    result = length / 3;
    } else if (value_unit == "cm") {
    result = length * 0.0109361;
    } else if (value_unit == "metre") {
    result = length * 1.09361;
    } else {
    result = length;
    }
    }

    if (result_unit == "cm") {

    if (value_unit == "inches") {
    result = length * 2.54;
    } else if (value_unit == "feet") {
    result = length * 30.48;
    } else if (value_unit == "yard") {
    result = length * 91.44;
    } else if (value_unit == "metre") {
    result = length * 100;
    } else {
    result = length;
    }
    }

    if (result_unit == "metre") {

    if (value_unit == "inches") {
    result = length / 39.37;
    } else if (value_unit == "feet") {
    result = length * 0.3048;
    } else if (value_unit == "cm") {
    result = length / 100;
    } else if (value_unit == "yard") {
    result = length * 0.9144;
    } else {
    result = length;
    }
    }

    return result;

    So you can see I am missing the very rudimentary knowledge of how to output the result to a field. A simple example to guide me would likely suffice (which I will go looking for).

    A repository of complete example scripts and snippets would be great to be able to review (and for users to contribute to), but maybe I have missed such a thing (or is the forum intended that way).

    Thank you!

    January 1, 2020 at 10:22 AM #39051

    Wayne Powell
    Participant

    (I know what is wrong above, but can’t delete or edit my Post using Forum tools, so please ignore.). I’ve encountered a different scripting error which I’ve posted in the correct forum.

    January 1, 2020 at 3:32 PM #39055

    Brendan
    Keymaster

    Right, sorry. My example wasn’t good because I had the return statement outside of a function. Just type result; instead of return result; for the last line.

Viewing 8 reply threads

You must be logged in to reply to this topic.