You want to use the Trim() function and the Substitute() function. In the substitute function replace carriage returns with nothing.
Here are three options
- Use a script trigger "On Save" to run the calculation on the field
- Go to your database manager and in the "auto enter" section turn on calculation and make the field replace itself with the result of the calculation
- Use a script to replace the field contents with the calculation, perhaps before doing an import (this is the least efficient way).
The calculation would look like
Trim( Substitute ( self; "¶"; "") )
If self doesn't work (depending on context) then just replace that with the PO field.
I agree with Carl on his options.
Personally I would tend towards an auto-enter calculation (Carl's second option).
Depending on what characters are valid in a PO#, you could use a Filter function to get rid of anything that is not valid. For example, if PO#s only contained digits as in your example, you could use an auto-entered calculation:
Filter ( Self ; "0123456789" )
Awesome gang - can you walk me through exactly where I'd go to set this up? I use it every day but not super saavy on the programming part. The only validatino we have on that field now is that it has to be unique (can't have duplicates). Our valid POs will include numbers, letters, and hypens. (not always all of them).
1233456 is valid
123456R is valid
123-1234567-1234567 is valid
Basically numbers, letters, and hyphens. That's it.
If you are rolling David's suggested approach—and I certainly would if I were you—you would place the Filter () calc as an auto-enter in the field itself. The calc David gave, Filter ( Self ; "0123456789" ), will instantly replace whatever the user enters with everything but the Filter characters removed, provided you leave unchecked the "Do not replace existing value of field" option.
As you wish to allow letters and hyphens as well you need to include these in the Filter list—Filter ( Self ; "0123456789abc…etcABC…etc-" )
Note that if both upper and lower cad letters are acceptable you need to include both in your Filter list. You might also need to consider whether you want to standardise in other ways (e.g. convert all letters to Upper case, convert dashes to hyphens, etc)
I'm revisiting this thread, and hope that someone who originally responded will help.
I asked for this help at the beginning of the year, and here I am on the last day of 2016 - I never employed this, and now I'm sitting here fixing 3-4 hours worth of bad entries from my staff who can't seem to NOT paste a leading space in our Order # (PO) field. As a result, when uploading vendor invoice data into our COGS field (and using PO as the matching field), I created thousands of NEW records because they didn't match due to the stupid spaces.
I've read and tried to employ these suggestions, but I'm just stumped and need step-by-step hand holding here.
Here's how far I've gotten. (I'm using FMP V11)
#1: File > Manage Database
#2: I choose my "PO" field and click "options"
From there, I have "Auto-Enter" tab, the "Validation" tab, and of course, Storage and Furigana.
I don't know where to go from here.
FYI, in "Validation", there is already one thing there "require unique value".
I tried checking off "Validated by calculation" and I pasted the above Filter formula in:
Filter ( Self ; "0123456789abc…etcABC…etc-" )
I saved this, and went and created a new record with a leading space and it didn't stop me or fix it.
So clearly, I just need someone to tell me exactly where to go, and exactly what to paste/where (validation) so that anytime we create a new record and manually enter PO, it can ONLY be alphanumeric ... upper/lowercase letters, hyphens, numbers, but NO SPACES.
Thanks a ton!
You are on the wrong tab it seems. What has been recommended is to be set on the auto-enter tab, not the validation tab.
And it sounds like your staff are copy/pasting these values into one file and then importing them into FileMaker where you are doing an "import matching" option and you need this correction to take place in the FileMaker table into which you are importing the corrected data. Do I understand correctly?
For importing, you'd need to enable the auto-enter option to get this correction to take place from an auto-enter calculation on the field. There's a small dialog that opens with a check box for this option just before the actual import starts. Even then, this correction won't take place in time to keep the mismatch of values from creating a new record. What you would need to do in this case is import into a temporary "Staging table" in FileMaker where your auto-enter calculation strips out the unwanted characters and then you'd do your "import matching" import from this staging table into the table where you need this info to be stored.
Undo “Validated by Calculation”. Return to “Auto-Enter”, then click the ChekBox by “Calculated Value”, or click the “Specify” button next to it. There you enter the Filter Calculations.
I suggest using: Substitute ( Self ; Char (32) ; “” ) This removes all spaces in the field when data is entered in the field.
Char (32) = Space.
Filter does a better job. You don't have to identify every undesirable character to list for substitute to remove. With filter, you just list the characters that are acceptable and filter removes all others.
The staff are manually creating FM records within the IWP access. So, for example, Amazon Order:
Our remote staff will copy that order # and paste into PO -- but using Chrome or other browsers, and depending on where they copy from, maybe they carelessly paste the PO as:
Then, a week later, when I am uploading the Amazon statement that includes sales tax, etc, I have it setup to match the PO# (and add new records if it doesn't exist). My uploads are fine - they don't have spaces and doesn't need to be validated. But when I do the upload, and the file from Amazon correctly has "102-111111-111111", it creates a new record instead of updating the existing record, because the existing record was manually created with " 102-111111-111111" (the space); thus, creating an extra record.
So, what I'm trying to accomplish is when/if my remote staff pastes in the PO# with a space, it won't let them save it, much like it won't let them save it if it's not a unique value. (we have requires unique value on validation to prevent remote staff from entering the same item twice).
So I'm going to try this "Substitute ( Self ; Char (32) ; “” ) to see if that will fix things. If I'm still missing the mark let me know.
OK, I just tried it, and still doesn't work, so obviously i'm still missing something
First screenshot - applying the auto-enter value:
Second screenshot - after hitting OK and saving this, I went into my database and created a new record, and I intentionally put a space at the front of the PO# field: " 111-111111-111111". As you can see, it accepted the value and I hit "save and new record" and went back to make sure, and the record is there WITH the space.
From your screen shot, you didn't clear the "do not replace existing value..." check box.
Note that your calculation only removes a single type of character the space character. If some other non printing character is pasted into the field, say a return, tab, just to name two of a great many other characters, they will not be removed. This is why several of us have recommended that you use Filter instead.
philmodjunk OK I will fix that. I thought I WAS using filter, since the post above said "there you enter your filter calculations". I really don't know the difference. I'm a hack.
If you can write out what the filter code should be, I'll copy and paste it in exactly as you give me. I'll even send you a virtual bottle of wine (or home brew!)
To recap, the only thing we put in our PO field is numbers, letters, and some symbols, like hyphens. We don't want spaces or returns or tabs.
That's awesome, and I see already gathering comments and being useful. But for me, I still need to know what to paste to accomplish my objective. I just don't understand all the code. (I dunno the difference between a function and a filter, etc) .. it must be painful for you guys reading these posts. You are FM pros. I run an e-commerce group and I get to answer all the newbie questions about how to list a product on eBay haha - so I get it.