Calculation IF based on a Pick List Value

[Wayne Powell]

(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.

[Brendan]

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

[Wayne Powell]

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?

[Wayne Powell]

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?

[Brendan]

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.

Script Talk

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

[Wayne Powell]

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).

[Wayne Powell]

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!

[Wayne Powell]

(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.

[Brendan]

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.

[Author]

Posts