Issues with Calculation Fields

[Mike Schwartz]

Brendan,

I found some time to play around with some of the new features in version 5.1.1, and came across a couple of peculiarities when experimenting with one of my sandbox files.

1) I have a text calculation field that concatenates some other text fields along with static text. The formula uses the “+” operator for concatenation. It used to work, but no longer does. Do I now need to revise the formula to use the CONCAT(X,Y,…) function? It looks like you updated the online documentation to include the CONCAT function, but you also still have the section describing text concatenation using the “+” operator.

2) I created a new text calculation field that uses the CONCAT operator to merge a text field with some static text. The Result Type is set to “Text”, but the field returns the value “0” instead of the expected text string.

3) I created a new calculation field to test the IFEMPTY function. When I drag a field into the formula, it pastes in the field ID instead of the nice blue tag with the field name: IFEMPTY(fld-d2b21dc7b560489a98701201a02fee25) ,”Not Married”,”Married”). It appears this way in both the formula box in the Form/Fields sidebar, and in the drop-down Edit Formula sheet. Other calculation fields continue to show the blue tags for fields used in the calculation formula.

Please advise.

Thanks,
Mike

[Brendan]

Hi Mike,

1. You can still use the + operator to concatenate values. Check to make sure you have them everywhere you need in your formula. Even one missing one will cause a failure to evaluate the formula.

2. Can you email me your form template so I can examine it?

3. I see that. That’s a bug. To work around it, just put your cursor where you wan the field to go and then double click on the field or select it and click the [ > ] button.

[Mike Schwartz]

Brendan,

Thanks (as usual) for the quick reply. I think that items (1) and (2) from the original post were “pilot errors”. I did scrutinize my text formula and found one missing “+” sign. Adding it back to the formula fixed things. And the text calculation field that was returning a “0” value is now working correctly. If I change the Result Type for that field from Text to Number, then the field will display a zero value, which was the symptom that I was describing. I don’t know, maybe I was checking the wrong field formula when I said that the Result Type was properly set to Text. Overall, the calculation fields seem to be working as expected now.

Some additional observations:

1) It appears that you cannot test a checkbox field in an ISEMPTY function. I was expecting that the unchecked state would result in ISEMPTY testing TRUE, but ISEMPTY tests FALSE regardless of the state of the checkbox. I believe that is because a checkbox field is internally represented by a value of either 0 or 1, and therefore is never actually “empty”. Does that sound right?

2) Instead, I can test a checkbox field using an IF function for my calculation field: IF( [checkbox field]=1, result_if_checked, result_if_not_checked). I do notice that checking and unchecking the checkbox field will not cause the referencing calculation field to update immediately. I need to click into some other field first; I guess that finally tells Tap Forms that I’m done “editing” the checkbox field.

Thanks again for the advice.

— Mike

[Brendan]

1. Yes you’re correct. 0 or 1 is how internally a checkmark field is represented. So instead of IFEMPTY, use the IF statement.

IF([Checkmark Field] = 1, "checked", "unchecked")

You can even multiply values by a checkmark field and that can save you from having to use an IF statement in some situations. Like if you multiply by 1, the result is returned. If you multiply by 0, obviously 0 is returned.

For example:

[Total Amount] + [Tax Amount] * [Should Apply Tax]

If the Checkmark field “Should Apply Tax” is checked, then the Tax Amount is added to the Total Amount. If it’s off, then no tax is added.

[Jean-Christophe RICAU]

Hi,

Using text concatenation with IF(checkmark1,”a”,””)+IF(checkmark2,”c”,””) when I uncheck one the all concatenation disappear like the syntax was false. And if I check it again it comes back !
Amazing but too difficult to follow an evolutive situation !

Is it possible to put hours in text concatenation with the FORMAT or DATE function ?

Thanks for all

Jean Christophe

[Brendan]

I’m not quite sure what the first part of your reply means, but you can put hours in text concatenation if you like. You don’t need to use FORMAT or DATE for that. Just have a field that has hours in it and insert that into your formula.

E.g.

[Number of hours] + " hrs"

Where “Number of hours” is a field that contains the number of hours.

[Mike Schwartz]

Jean Christophe,

I replicated your checkmark formula and it seems to work correctly, yielding the four results (“”, “a”, “c”, and “ac”) depending on the state of two checkmark fields. But here’s something to watch out for:

==> Calculation fields with formulas that refer to checkmark fields do not update immediately when you check or uncheck the check-box. Think of it like this: Suppose you have a calculation field that concatenates a first_name and last_name. Now think of what happens while you’re typing in the first_name field. You wouldn’t expect the calculation field to update “live” while you’re typing. Instead, you finish your typing and then commit the change by hitting return or exiting the field (by tabbing or clicking out of it). THEN the calculation field updates correspondingly. The same thing happens with checkmark fields. You can keep toggling them all day long, but the calculation field won’t update until you click in some other field that doesn’t currently have focus, or hit the refresh button.

Try experimenting some more with your calculation field: first double-check the formula, then see if it works as expected if you click the refresh button (at the bottom of the list of records) after changing the checkmark fields.

Hope that helps,
Mike

[Brendan]

Actually I tested that calculation field formula and when I toggled the checkmark fields, the Calculation field did update live as I clicked the checkmark field. So it should work live for you too.

IF(checkmark_field_1 , "a", "") + IF(checkmark_field_2 ,"c", "")

[Jean-Christophe RICAU]

Brendan & Mike,

Thanks for your helps about using IF & checkmarks, it works fine with refreshing.

Considering hours field in text concatenation I’d like to make the hour value appears in HH:MM format. When I use your suggestion I got seconds or a decimal value if I divide it by 3600.
Is there a way to apply the HH:MM format in text results ?

Thanks
Jean Christophe

[Mike Schwartz]

Brendan,

Live update of the calculation field when I toggle the referenced checkmark field definitely does not happen for me. And based on Jean Christophe’s last post, it looks like clicking Refresh was needed for him, too.

For the avoidance of doubt, I’m referring to the behaviour of Tap Forms Mac (both 5.1.1 and now 5.1.3). When testing the same form on my iPhone, the live updating of the calculation field does work.

Thanks,
Mike

[Brendan]

Ok, I think I know the issue with the checkmark / calculation field. Live updating works on the Default Layout, but not on a custom layout. I’ll have to look into that further.

Jean Christophe, as for the HH:MM question, ya, that won’t work with the DATE formatter or the new FORMAT function. I would need a new specific formatter type function to properly format durations. It works fine if the HH:MM is meant to be for a Time value, but not for a duration value because they represent different things. One being the time of day and the other being a duration of course. Does 12:05 mean 5 minutes after noon? Or does it mean 12 hours and 5 minutes? So I’d have to account for that. The Tap Forms Number Format setting on a Number field does that conversion for you, but that result wouldn’t display within a Calculation field since Tap Forms would treat it as the underlying numeric value which would be the number of seconds in that duration value.

[Jean-Christophe RICAU]

Brendan,

Thanks for your attention and solutions, for the checkmarks live updating works less or more on custom layouts but enough for me : I’m using my phone (Default Layout) when I need this update to be live.

And good news the DATE function works now well for hours (HH:mm) with text concatenation, even on hour calculated fields. Did you change something ? I was trying it before without success but maybe it’s the live updating story again !

Maybe out of thread but can I force upcase letters in the Mac text fields like I can do on the phone ?

Regards
Jean Christophe

[Brendan]

Hi Jean-Christophe,

The DATE function will format date values. I thought you wanted a duration value. But if it’s time values you wanted to format then ya, HH:mm should work.

You an force uppercase strings on the Mac version with the caps-lock key :) There’s no function to do it automatically for you per field though.

[Jean-Christophe RICAU]

Hi Brendan,

The DATE works well for my purpose, thanks,but I still have some heavy troubles with my time value calculated fields… First a strange result appears when you choose “HH:MM” in the DATE function : one minute more than the parent value!, but it’s OK with “HH:mm”(and “hh:mm” gives a twelve hours less or more result but maybe it’s PM/AM).

My biggest problem is that I have in my form a synthesis text concatenation field with a “big” formula, IF(IF+IF( and so on… and I have inside 2 time values calculated from one time value entry which is inside too.
It’s very useful for me to see on my small phone screen all the values I need in this synthesis field without going down & up & down again.

The formula works well on my Mac, works on my phone too but make the form reactivity very very slow from the moment I put the time value entry. If I change a value which is not in the formula the form works normally but become very slow if it’s a formula-inside value, even if it’s not the hour one.

I have tried to convert all my time values in text format before including them in the “big” formula, I feel it’s better on the Mac but really worse on the phone, cause the form is slower and often crashes.

And the purpose is to make my entries on the Mac and check/uncheck them on the phone…

Thanks & regards
Jean Christophe

[Brendan]

Hi Jean Christophe,

Can you please email me your form template so I can see what’s causing the performance problem? I did have a big performance problem recently with the calculation field in formulas that were concatenating text and numeric result together. But I had fixed that in a recent update.

[Jean-Christophe RICAU]

Hi Brendan,

here are they, PREOP béta2 without pre-text transformation in the formula & beta3 with time value converted before including

Thanks

[Brendan]

So I’m not seeing any performance problems on either form on the Mac or iOS versions.

Are you using the latest Tap Forms version on all devices? 5.1.3 for Mac and 5.1.1 for iOS.

Tapping on a checkmark causes the calculation result to be updated immediately. I’m using an iPhone 7 for testing, which is a pretty fast iPhone. And my MacBook Pro is the new one with Touch Bar.

[Jean-Christophe RICAU]

I’m so sorry … I didn’t check that before sending you a request and you’re right !

Thanks a lot
Jean Christophe

[Brendan]

Excellent. At least I know I didn’t make a different mistake with formulas. :)

[Author]

Posts