AnsweredAssumed Answered

How to convert text to number for FMP's ExecuteSQL?

Question asked by fmpdude on Apr 27, 2017
Latest reply on Apr 28, 2017 by beverly

This may be simple and I've missed it, but  how do you treat a text field as a number in FMP's SQL so it doesn't give you an error?

 

---

 

Assuming col1 and col2 are TEXT, the following SQL gives me an error in FileMaker that the input type for the first parameter (as in "AVG") is the wrong type:

 

SELECT AVG(COL1-COL2) from TableName

 

FMP ERROR BELOW:

(of course in FMP, this is just a "?" Good luck figuring that one out from the "?")

 

---

 

Now, the exact same query in MySQL with varchar field types works perfectly with no errors AND no conversion needed, either:

 

Data:

 

 

Query:

Outcomes