Auto update when other fields change

[Tobster]

I'm having trouble getting a field to automatically update when other fileds change: specifically, I need to generate username and password fields using calculations based on first name and surname fields. The problem is, when a new record is created, those fields are blank and the calculation doesn't automatically update when they're filled in...

How can I fix this? Do I have to write a script or do I need to perform some sort of lookup?

Any help, most appreciated...

[Peter Fenner]

Hi There

Your calculation field must be text NOT number. See the option to select what type of calculation it is in the calculation creation window.

Pete

[Tobster]

The calculation field is text. My current calculation is:

Upper(Substitute(Substitute(Substitute(FIRSTNAME, "'", ""), "-", ""), " ", "") & "." & Left(Substitute(Substitute(Substitute(SURNAME, "'", ""), "-", ""), " ", ""), 2)) & Right(Random, 2)

so that I end up with an all upper case user name that uses people's first name and a period then the first two letters of their surname with two random numbers. The field is also set to be unique.

When a new record is created, the first name and surname fields are blank: the script just generates the period and two numbers. When the name fields have data in them, the user name field doesn't update. How do I get it to do this automatically?

[Peter Fenner]

Hi There

Your calculation works fine. What do you mean "the field is also set to be unique"? You can't set a calculation field to be unique.

Pete

[Tobster]

I've just found the problem - thanks, you gave me the clue.

I had the field as a text field with an auto-enter calculation, and the field set to be unique.

If I make it a calculation, it automatically updates...

[Wendy T]

Hi Tobster

You mention both "calculation" and "script". Is your displayed result actually a calculation or is it generated by a script, for example using SetField? If calculation, it would update automatically when you add a firstname and/or surname. If script, you would need to re-run the script to update the field.

hope i'm on the right track....

cheers,

Wendy

[Peter Fenner]

Hi Tobster

Also, you run a risk of not having unique ids. Take this example: John Moore returns a result of: JOHN.MO24

But now you add John Morrison which could return the very same result. Random is "pulling a 2-digit number from the air" each time.

Pete

[Tobster]

Yes, I now run the risk of not having unique IDs (I did think of that), which was why I originally had it as a unique text field with an auto-enter calculation... However, what I need it for doesn't generally involve huge numers of people, so I'm hoping... The only other way would seem to involve writing scripts.

[Peter Fenner]

If you categorically know you will have less than 100 people then rather create a serial field starting at 01 and incrementing by 1. Use this instead of the random function in your calculation.

If 100 is not enough then have a 3-number ending and have a serial number starting at 001. This will allow for 999 people. A 4-digit serial number would of course allow for 9999 people.

Pete

[Tobster]

I have a better solution now. The database has about 9000 entries but only about 300 actually need this username. Every record has its own unique number, so I'll use that instead of a random number...

Thanks for everybody's help and suggestions...