Sometimes my Calc field works, sometimes it doesn't

[philipcaplan]

I have the following Calculation field in my database:

"<tr><td>" & Quantity & "</td>" &

If (SELL_Speed1 > "50000";"morethan50000";

"<td onclick=\"\" class=\"snipcart-add-item\"" & ¶ & "data-item-name=\"" & Product_Name & "\"" & ¶ & "data-item-id=\"" & id_SPEED1  & "\"" & ¶ & "data-item-price=\"" & SELL_Speed1 & "\"" & ¶ & "data-item-taxable=\"false\"" & ¶ & "data-item-description=\"" & "Qty: " & Quantity & " " & Product_Description & "\"" & ¶ & "data-item-url=\"https://www.reliaprint.co.uk/prices/" & id_FOLDER & "/" & id_FileName_SP1 & "/\"" & ¶ & "data-item-custom1-name=\"Delivery Cost\"" & ¶ & "data-item-custom1-value=\"Free UK Mainland Overnight (£0)\"" & ¶ & "data-item-custom1-options=\"Free UK Mainland Overnight (£0)|Scotland Highlands & Islands (£4.80)[+4.80]|Northern Ireland (£7.20)[+7.20]|Isle of Man (£7.20)[+7.20]|Isle of Wight (£9.00)[+9.00]\"" & ¶ & ">£" & SELL_Speed1 & "</td>"

& If(SELL_Speed2>"50000";"morethan50000";  "<td onclick=\"\" class=\"snipcart-add-item\"" & ¶ & "data-item-name=\"" & Product_Name & "\"" & ¶ & "data-item-id=\"" & id_SPEED2  & "\"" & ¶ & "data-item-price=\"" & SELL_Speed2 & "\"" & ¶ & "data-item-taxable=\"false\"" & ¶ & "data-item-description=\"" & "Qty: " & Quantity & " " & Product_Description & "\"" & ¶ & "data-item-url=\"https://www.reliaprint.co.uk/prices/" & id_FOLDER & "/" & id_FileName_SP2 & "/\"" & ¶ & "data-item-custom1-name=\"Delivery Cost\"" & ¶ & "data-item-custom1-value=\"Free UK Mainland Overnight (£0)\"" & ¶ & "data-item-custom1-options=\"Free UK Mainland Overnight (£0)|Scotland Highlands & Islands (£4.80)[+4.80]|Northern Ireland (£7.20)[+7.20]|Isle of Man (£7.20)[+7.20]|Isle of Wight (£9.00)[+9.00]\"" & ¶ & ">£" & SELL_Speed2 & "</td>" ))

& ¶ &If(is_last_quantity="";"";"</table>")

==========

The field SELL_Speed1 is a simple Text field.
For some values (such as 447, 171, 39) it gives the result I expect (all the data in the fields are in the result).

But for other values of SELL_Speed1 (such as 97 or 54) its result is only "<tr><td>xxxx</td>morethan50000" (where xxxx is the value of field Quantity)

?????????

==========

Could some kind person please explain why my calculation gives the result I expect with some values of SELL_Speed1, and with other values it doesn't.

 And how to fix it!!

Many thanks.     Philip Caplan

[comment]

52 minutes ago, philipcaplan said:

If (SELL_Speed1 > "50000";

...

The field SELL_Speed1 is a simple Text field.

Since both "50000" (quoted!) and SELL_Speed1 are texts, the comparison will be done alphabetically - and alphabetically, 97 comes after 50000, as does 54.

To perform the comparison numerically, make SELL_Speed1 a Number field (or at least convert it to a number for the purposes of the calculation) and remove the quotes from the numerical constant:

 

If ( SELL_Speed1 > 50000 ; "morethan50000" ; ...

 

 

[philipcaplan]

That's it. The quotes around the 50000 was where I was going wrong.

Once they are removed, everything works OK.

And it doesn't seem to matter if SELL_Speed1 is a Text or Number field.

Once again you've come to my rescue.

Thanks once again comment and FMForums!

[comment]

13 minutes ago, philipcaplan said:

it doesn't seem to matter if SELL_Speed1 is a Text or Number field.

But it does matter. If you leave it as Text, Filemaker has to compare text to a number - and it needs to decide which one of the two to convert to the other type. The rules guiding this decision are not published and can change from version to version (and I seem to remember they have been changed at least once). I don't know which version you're running, but here on v.17

"10" > 2

returns false.

[philipcaplan]

Thanks again. I mentioned this because it seemed strange to me that it didn's SEEM to matter.

So I'll make sure that SELL_Speed1 is either a Number field, or if I have to keep it as Text I'll make the comparison on a number-conversion value of it.

How do I do that? I thought there was a Text-to-Number function, but I can't seem to find it!!

[comment]

GetAsNumber ( SELL_Speed1 )

 

[philipcaplan]

Oh the old "Get" function.  Early versions of FMPro were easier to get around (pun not intended!!), cos they had fewer functions to remember and use!!!  <smile>

Untold thanks again, comment

Edited December 6, 2018 by philipcaplan

[comment]

Actually, it's not a "Get" function. Get functions have a structure of Get ( xyz ). This is a Text function. Not sure why, but they bundled all typecasting functions of the form GetAsSomething() under Text functions.