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...

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

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?

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

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...

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

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

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.

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

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...