Field CR filtering

[Anuviel]

What would be the best way to filter carriage returns out of the field?

If I use Filter or FilterValues I would have to type in all of the chars I want to keep in the field and the ones that are not specified will be filtered out - at least that is my understanding.

How would I specify it the other way around?

[Lee Smith]

See if this Custom Function does what you want

Trim4

[LaurenKuhlman]

Substitute ( text ; "¶" ; "" )

I would recommend using a auto-enter replaces existing value.

[Anuviel]

Thank you both.

I set up filtering. I have another question - how do I auto enter the value and filter the field at the same time?

I tried this but it does not work (for example)

"12345" & Substitute(Self ; "X" ; Y)

It does not auto enter any info.

Is it possible to set it that way?

[Lee Smith]

Auto Enter is not Retroactive if you are setting this up with existing data in the fields.

If you need to learn about using this feature, take a look at my screen shot Here called Tony_D..png

Your calculation isn't complete.

It should have your fields to be filtered and/or Concatenated something like this

Filter ( Field ; "1234567890" ) & " " & Substitute ( Field2 ; "X" ; "Y" )

Hard Coded, the numbers do not need to have Quotes.

1234567 & " " & Substitute ( Field2 ; "X" ; "Y" )

and Self

123456 & " " & Substitute ( Self ; "X" ; "Y" )

Which requires and entry of either X or Y, otherwise the enter will remain unchanged.

Either make a mockup of your file, or compress a copy of it, and attach it here.

Lee

Edited June 30, 2009 by Guest
Sample calcs

[Anuviel]

Thanks, I did not know how to write the calculation properly.

Basically I am having an auto enter text field which enters 12345, the users then need to complete the sequence.

I also wanted to filter that field to get rid of carriage returns that may be entered by users during data input. The carriage return may or may not be entered by users.

Can this be achieved with one field or do I need to use two fields?

[Lee Smith]

is the 12345 a serial number being entered into a separate field?

if so, then your calc will look something like the

Field1 & " " & Field2

Use the Trim4 Custom Function I mentioned before

Trim4 (1234567 & " " & Substitute ( Field2 ; "X" ; "Y" ))

or

Not sure why you mentioned the Filter Function unless they have a combination of alpha numeric characters in the Field1

Trim4( Filter ( Field1 ; "1234567890" ) & " " & Substitute ( Field2 ; "X" ; "Y" ))

Lee

Edited July 1, 2009 by Guest

[LaRetta]

I would probably suggest two fields but can't verify until I understand more. Basically, if you want auto-enter to enter 12345 then the User enter something, you will have to protect from MANY possible contingency, such as User modifying field which has already inserted the 12345. This means that, in addition to simply ADDING 12345 to the beginning, auto-enter would have to check for existing pattern and make (possibly) many corrections.

As Lee asks, what does the 12345 represent? And what is the purpose of the field to the User? :wink2:

[LaurenKuhlman]

It looks like you are using FM 10 so, that helps. I would use a normal, ordinary auto-enter (not replaces existing value) for your "12345" part.

For the carriage return removal part:

Create a new script that has this step:

Set Field [ table::fieldName; Substitute ( table::fieldName ; "¶" ; "" ) ]

Then, find all the occurrences of your field on a layout. Perform these steps for each occurrence of the field on a layout:

Right-click > Set Script Triggers > Check OnObjectSave > Choose Script

You could use OnObjectModify also which would remove the carriage return the moment it is entered. This may be confusing to the user. Either way, there will not be carriage returns in the field once it is saved.

It is important to note that script triggers occur on each field on each layout (not on each field, at the schema level like auto-enter) so, for this to work you would need to add the trigger to every occurrence of your field.

Also, if you have a mixed user-base of pre-FM10 users and FM10 users, this will not work for FM9 (and lower) users.

[Lee Smith]

The carrage return issue was resolved prior to Anuviel last question.

[LaurenKuhlman]

Anuviel,

Please let us know what ends up working for you.

[Anuviel]

Thanks.,

The 12345 is an account number. The users will add a sequence of numbers after it. The 12345 will never change (or so I am told).

It is a kind of serial number. Basically they want to use 12345 + customer phone number for a record ID.

It has to be that way.

I am actually using an auto enter serial number for relationships and record ID's.

This will just be a field I made to appease them and as far as they know that is the Record ID. They will use it to search for records and such.

I can validate the data by selecting Numeric Value Only in field validation settings so the filter I mentioned is not an issue.

Actually that validation will validate for carriage returns as well, just noticed that!!!

[Anuviel]

I however do not know how to validate the 12345 in the beginning or to make sure they do not change it, from 12345 to 31245 or something similar.

If I use auto enter with replace checked on, I end up with a bunch of 12345's. Same thing happens when I use two fields. If I do not check replace existing value they can simply delete everything and type any number...

I could maybe set it up with three fields but I am sure there is a better way of using only one or two fields for this.

[LaRetta]

As I said - use two fields. You should not be using standard field with auto-enter to concatenate the account number and phone together. Have your Users enter the phone number into the phone number field (text). Remove the carriage returns or apply your validations there - using auto-enter.

Use a CALCULATION (result is text) with: "12345 " & PhoneNumber. Then the Account Number can never be messed with. But if the account number never changes, why search this new calculation and why not just search the phone number directly? I feel you are getting twisted in User requests; happens to the best of us.

You might also consider storing that account number in a Preference table so that, if they ever DO change it (and believe me they will), they can change it in browse mode in Preferences and you won't have to open field definitions to change your calc. The calculation would then be similar to: Preferences::AccountNumber & " " & PhoneNumber I wouldn't use script triggers NOR auto-enter for something which is simple concatenated calc.

[Anuviel]

Point Taken, thank you very much.

Thank you all for assisting with this, I believe I learned things and the preference table seems like the way to go with a regular calc field instead of auto enter.

Thanks.,