Jump to content
Sign in to follow this  
philipcaplan

Sometimes my Calc field works, sometimes it doesn't

Recommended Posts

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

Share this post


Link to post
Share on other sites
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" ; ...

 

 

Share this post


Link to post
Share on other sites

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!

Share this post


Link to post
Share on other sites
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.

  • Like 1

Share this post


Link to post
Share on other sites

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!!

Share this post


Link to post
Share on other sites

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 by philipcaplan

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.
Note: Your post will require moderator approval before it will be visible.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

Sign in to follow this  

×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.