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: