Import the data into a text field. But then use calculations to "split" the data into a text field for the # if it is present and a number field for the decimal formatting. These can be two calculation fields or a text and number field with auto-enter calculations. If you use auto-enter calculations, be careful to enable auto-enter options during import.
Looks like I'm nearly there.
The challenge I seem to have now, is that when I look at the "Number" field it looks like it is displayed correctly, eg 100.00
However, the source value is 100, and so when I run a calculation on the field to display it in a separate text field it brings in 100 without the decimals. But when I retype the 100 as 100.00 then the calculation works and brings it in as 100.00. So it looks like the number field stores the source, and and only displays as a decimal field.
What do you think I'm missing
100 and 100.00 are the same value. The difference is a matter of formatting. Why is the difference between 100 and 100.00 significant?
The reason is because of the format we need to supply the client. I agree same value, but we do need the zero decimal places
Yes but what you describe IS supplying the needed decimal places. I'm trying to figure out specifically what problem is created by the fact that when you click in the field, you see the original value and you see the formatted value when you exit. This is expected behavior for FileMaker data formatting so I'm trying to understand the nature of the problem that this creates for your client.
Thanks for this.
The challenge I have is that (as an example below ) is that when I export I end up with the same value as I import if that makes sense?
Source data (Field1) can contain 100, # or Null The end result I'm trying to achieve is in a different text field should be the result 100.00 or # or Null
I can split Field1 into 2 fields the first a decimal/number field to convert 100 to 100.00 and the second to house the #.
Then using your earlier tip was to bring them back into a separate text fields to get 100.00 or # or Null
I understand there's no difference between 100 or 100.00 but unfortunately I do need to have the decimal places
Hope that makes sense.
That you need this for export, is the detail not given in your original posts. Text calculations can be setup that supply the needed trailing zeroes after the decimal, but I'd first try to use the "apply current layout's data formatting to exported data" option as that's much simpler.
Here's a calc that could be used in cases where this fails:
Let ([ x = YourNumberFieldHere ;
I = Int ( x ) ;
d = Mod ( x ; 1 )
I & If ( d ; Left ( d & "0" ; 3 ) ; ".00" )
Happy New Year
I will take a look shortly.
Thanks for your help