Replace with calculated result and
yourFieldHere < 1 and yourFieldHere > -1; 0 & s;
I tried getasnumber(getastext("0" & ".25")) with no joy
As a slight aside a custom function formatting numbers correctly for SQL might be a way to go too
decimal storage is a valid numeric format, so stating:
These numbers then are used wholly further downstream by ExecuteSQL which otherwise would use the decimal point as a delimiter. However, if I have the zero in place first, this is no longer a problem.
would indicate to me that what you're trying to do with ExecuteSQL() downstream is invalid. FileMaker operating as an ODBC data source for external SQL, or just in the ExecuteSQL() function itself knows how to handle numeric values and decimals.
What exactly are you trying to do later that is not working?
I'm working on a sub summary report which uses say, 32 records out of 20,000 based on those held for a particular customer. These 32 records can be summarised normally using standard FM functions.
However, I need to compare these 32 records against the average and 3rd quartiles of the 20,000 data set as a whole. I think I'm correct in saying because the remaining 19,000 records are not part of the found set, they're not subject to standard summary formulas. However, ExecuteSQL is agnostic about tables and found sets and you're able to specify in the parameters exactly what's required, so I do this for the calculations across the board. This gives me the overall average and in turn, I can use a CF for the 3rd quartile.
When I select as SQL, the decimal point acts as a delimeter, so instead of say, 0.56, it calculates against 56, giving incorrect results.
Further information on this community thread here: Select full decimals using ExecuteSQL
This is where I need anything lower than 1 to be appended with "0.", and anything higher than 1 to be left as normal.
Have you tried the version of the CF from user19752?
You really only have to replace "Words" through "Values" like:
WordCount --> ValueCount
RightWords --> RightValues
MiddleWords --> MiddleValues
then the values get treated right
If this is only going into to your SQL then how about using the CAST function? something like CAST( yourField AS DOUBLE) Although you might want to try and find some documentation on CAST so you understand the full ramifications of using it. I have never used it in FileMaker but in MySQL there are a couple of little gotchas......
In that article though you state it’s returning .52, NOT 52.
Also Beverly states that ExecuteSQL() returns a result as text. You would need to parse the resulting list back to individual numeric records, or run it through a custom function to alter each value to display as you need. If you do the former you can obviously use filemaker’s built in decimal field styling.
Is the field/calculation type text instead of number?
If he’s returning a LIST of results, the result will be text.
If he is returning a single result, then he can return it as a number.