Auto-enter unique

[LaRetta]

I want the best of both worlds and I'm not sure if it's possible. I want to pre-fill a text field (called ClientCode) with the result from two additional fields when a new record is created. Sounds easy! Formula is: [Lower(Left (LastName,4) & Left (FirstName,3))]

I can't seem to use an auto-enter calc. When I type the Last Name it pre-fills the ClientCode text field, but when I then type the First Name, it doesn't update. Okay, that's fine. I then created a calc field (c_shortcut), text, do not evaluate, store, using the same formula and then in ClientCode auto-enter calc, do not eval, store = (c_shortcut). That works! And it updates the text field if the name changes.

Here's the rub. This ClientCode must be unique. But even though I set ClientCode's validation to unique, the calc just pastes the result into it (even if it's NOT unique) . Even that would be okay if it displayed the message "must be unique" and allow the user to manually assign a new code. But it doesn't.

So, will I be required to use a script? How do I test for unique in a script? There isn't a [Case(notIsUnique ] .... bummer!

[BobWeaver]

You need to modify the auto-enter calculation so that it can't execute until both fields are filled. Use this formula:

Case(IsEmpty(LastName),"",

IsEmpty(FirstName),"",

Lower(Left (LastName,4) & Left (FirstName,3)))

[LaRetta]

Thank you for the idea and maybe I'm doing something wrong. If I use that, you are right ... it waits and updates according to both LastName and FirstName. But, it doesn't reassign if the name is changed, and it doesn't catch 'not unique' even though I have the validation set to that. I tried selecting both 'do not evaluate' and 'evaluate.' Did I misunderstand your suggestion?

[BobWeaver]

No, sorry, it was my misunderstanding. I thought you didn't want it to update after it got its initial value. In that case, you can't use auto-enter. Once an auto-enter gets a non-empty value, it won't ever change (unless it is auto-enter modification date or time). You need a calculated field. I think you should reconsider what you are trying to do. If you try to make a unique ID field based on the first few letters of a persons first and last name you will inevitably have duplicates at some point. Then, you need some scheme to handle the problem when it occurs. Best to rethink the problem and maybe come up with a different approach.

If you are trying to have a system where you can look up a person's record, I suggest something that lets you enter a few letters of the person's name and displays the matches in a portal, (just in case there is more than one). Then the user picks the correct one. You can use the same seven character ID that your calculation produces; it just isn't unique. If you need true uniqueness, then it's best to use Captkurt's unique ID field calc, or an auto-enter serial number.

[kennedy]

Yep, validation functions don't fire on auto-entered values.

So, one option is to enhance your auto-enter calc to do a check for the existence of the value. If so, continue to return "" so that the user has to fill it in.

Another option is to add an additional calc'd field that checks if its unique, then you can display the non-uniqueness as obnoxiously as you want.

The first option has the advantage that the record won't be committed with a non-unique value... the user will be stuck until he puts in an appropriate value.

P.S. This is a slippery slope... I recommend CaptKurt's unique IDs in the Sample Files section... or my shortened variation of it in my Starter Template. Relying too much on "pretty codes" for any but hand-crafted data is always problematic.

[LaRetta]

Thanks guys! I should have explaind that this is a shortcut key used for fast, massive data-entry. Data-entry enters 600-800 records a day, doesn't want to lift her hands off the key to select from dropdowns, etc.

This ClientCode is temporarily assigned (and re-assigned) and is independent from the ClientID, in which case, I use CaptKurks Unique Primary ID. When the ClientCode is entered, the ClientID is looked up and inserted in the Primary Key field. I just don't want to assign this shortcut to more than one client at any given time. It would just be nice to have it pre-fill (for consistency) and make sure that no other Clients being temporarily assigned in this group, have the same one. Can you please help me further by explaining how, once the calc inserts the result into the field, a script (?) could verify that it's unique and provide a message to the user?

[BobWeaver]

Create a self-join relationship with ClientCode relating to ClientCode. Then create a calculated field cUnique with this formula:

Case(Count(selfjoin::ClientCode)>1,"Not Unique","No problemo")

[LaRetta]

I used the following in Child Code, text, calc, do not eval and removed the validation for Unique:

Case(IsEmpty(LastName),"",

IsEmpty(FirstName),"",

Lower(Left (LastName,4) & Left (FirstName,3)))

Then created the c_unique, calc, text, do not eval, do not store, and placed this field on the layout right above the field. It works perfectly. Hmmm, I don't want users to get used to seeing it. I want it only to display when it's not unique. So I reviewed a formula Cobalt Sky gave me to flag a new record (which uses Case). Surprise! The only difference is that, in his example he left off the 'else' part (in this instance "no problemo". It disappeared when the record was unique! I LOVE THIS STUFF! BTW, I'd have left the "no problemo", I think it's cute ... but you know how users can be ... we must bop them over the heads to get their attention! Thanks you guys!