The syntax for that is incorrect as it depicts two different calculations with no operator to link them.
Perhaps the person helping you wanted you to use:
TextFormatRemove ( Self )
As the auto-enter calculation for a field named xyz. If so, you'll need to enter it in Field Options and also clear the "do not replace existing value..." check box.
Then perhaps the second line was suggested as a calculation for a separate field that takes the data from the field named xyz and inserts the underscore character into two specific locations.
The calculation was put "_" between every 3 numbers ie (123123123 into 123_123_123) or (12312 into 123_12_ ) I dont think there will ever be more than 9 digits !
I need this format to add to an url to retrieve pictures! Mybe I should create another field and populate with the result instead of replacing?
any ideas -
That would appear to be what your suggested calculation was supposed to do though the let function as posted here, is incomplete. It isn't necessarily your only option. Your example only shows numeric digits. If that's the case, we can borrow a page from the trick many of us use to auto-format phone numbers:
Let ( T = Filter ( TextFormatRemove ( Self ) ; 9876543210 );
left( T ; 3) & "_" & middle( T ; 4 ; 3 ) & "-" & middle( T ; 7 ; 3 )
You'd find your field in Manage | Database | Fields and double click it. Then select the calculation option on the auto-enter tab to open up a dialog box where you can enter the above expression.
Then you'd also need to clear the "do not replace existing value..." check box.
If the allowable input includes other characters, you'd need to include them in the filter expression. (The filter expression has to filter out the underscores so that if you edit the field to change a previous value, you don't get two sets of underscore characters and a screwed up result.)
Thats great many thanks yet again :)
BTW is it possible to run the calculation in fields already populated?
Also because i also will be displaying the number without the "_" should i have 2 fields one for the original and one for the result?
because i also will be displaying the number without the "_" should i have 2 fields one for the original and one for the result?
That's the only way that will work and in that case, I'd just define the new field as the calculation field that inserts the underscores instead of as an auto-enter expression.
If you do that you won't need to update existing records, but here's a thread on how to do that: Updating values in auto-enter calc fields without using Replace Field Contents
Hi just discoverd a problem with the calculation
seems to be only working with 3 4 5 and 6 digit numbers! 123123123 does not calculate at all
ignore last message! it works when i change the field from number to text!
When I paste this into the FileMaker Advanced Dataviewer:
Let ( T = Filter ( TextFormatRemove ( 123123123 ) ; 9876543210 );
left( T ; 3) & "_" & middle( T ; 4 ; 3 ) & "_" & middle( T ; 7 ; 3 )
which would appear to be correct and certainly is not "Does not calculate at all"...
It should work the same in your auto-enter calculation. A field of type calculation would need to replace "Self" with the name of the field where the data is typed in.