If-then in calculations with strings?

[Rob Griffiths]

Hello, I’m a newbie evaluating Tap Forms, and so far, it looks like it will almost perfectly meet my needs. However, I’m having trouble doing something that’s simple in pretty much any other spreadsheet or database I’ve used…

I have a form that tracks subscription costs, which can be per month, quarter, semi-annual, or annual. I want to show the annual cost, so I tried this calculation field:

[PER_TERM_COST]*IF([TERM]=”Annual”;12;IF([TERM]=”Semi-Annual”;2;IF([TERM]=”Quarter”;4;12)))

It failed miserably. After some testing, it looks like Tap Forms doesn’t support using string tests in if-then statements at all—is that correct? The only workaround I found, which is ugly but does work, was to do it on string length:

[PER_TERM_COST]*IF(LENGTH[TERM]=5;12;IF[LENGTH(TERM)=6;1;IFLENGTH(TERM)=7;4;2)))

This means I can’t ever have two terms with the same length :). Am I missing something, or is there really no string comparison support in Tap Forms?

Obviously, I can change my term field to be a numeric value, but I like the appearance of text instead. And I realize I could have a calculation to display the text-based term, but I’d still need the numeric field visible for input. String-based comparisons would add a lot of power to Tap Forms, assuming it’s not there and I’m just missing something.

thanks;
-rob.

[Rob Griffiths]

Related: Why does changing from No Style to Decimal Style change the numbers?

thanks;
-rob.

Attachments:

You must be logged in to view attached files.

[Brendan]

The IF(condition; then; else) function requires the condition part to be numeric.

If you want to do a string search, then use the IFEQUAL(A; B; C; D) function instead.

So in your case:

[PER_TERM_COST] * IFEQUAL([TERM]; "Annual"; 12 ; IFEQUAL([TERM]; "Semi-Annual"; 2 ; IFEQUAL([TERM] "Quarter" ; 4 ; 12)))

That should do it.

[Rob Griffiths]

Thanks – that worked. It also seems to avoid the oddity in setting the format to decimal, which changed the other result. Related again: Is there any way to format a number to appear with just the currency symbol, without the country? I don’t want “US$9.00,” just “$ 9.00,” ideally.

thanks;
-rob.

[Brendan]

If you’re in the US and you just set the Currency to Unspecified then it won’t include the country. But if you explicitly choose a currency then it puts the country name in there. It’s to distinguish between US$, CAD$, AUD$, and so on.

[Rob Griffiths]

I had tried that, but even with it set to Unspecified, I’m still seeing US$. Maybe once it’s been set, it can’t be unset?

Update: I got it … somehow. I toggled to another currency, then to unspecified, then to US, then back to unspecified, and now I have $ without US.

thanks!
-rob.

• This reply was modified 3 years, 4 months ago by Rob Griffiths.

[Brendan]

Glad it’s working now.

[Author]

Posts