How to validate this field, help!

[yaya]

question about validating.

See attachment FM9 file, contain 2 small table.

1. table User

fields:

userId Text unique auto increase

UserName text

2.Table Service

Fields:

user ID Text (value: User::UserId)

user name text (value: drop-down list from the value of User:Username

Service text (select from drop-down list) (given sample sevice name cleaning,gardening,teaching ...)

Relationship user::Username --< Service::Username

random added some sample user names from table user.

(e.g. peter, simon, jon)

Now, I'd like to add service to those users from table Service.

each record contains one user and one service.

Record 1, say, Peter, given service Cleaning.

Record 2, Peter, Gardening

Record 3, Simon Gardening

I don't want to same user has same serive, say

I want a warning from FM system that Peter has already has cleaning,to prevent we add cleaning to Peter again.

(I can not set service field unique, because other people say Jon could have cleaning)

Once Jon has cleaning, he can have any other more services but we don't allow him has cleaning again.

How can I implement it. I've been thinking so long time and didn't get it :

thank you very much for help.

validation_service_filed.zip

[comment]

See if this helps:

http://www.fmforums.com/forum/showpost.php?post/204677/

See also:

http://www.fmforums.com/forum/showpost.php?post/246743/

[yaya]

thanks, seems the 1st way is easier. Once question. from your ansewr, what operator is "|", especially the |, I didn't see it from the operator buttons there. Thank you.

1. In the Appointments table, define a text field, set it to auto-enter a calculated value =

CustID & "|" & GetAsNumber ( AppointmentDate ) & "|" & AppointmentTime

Uncheck the "Do not replace existing value.." option. Set the field's validation to 'Unique value'.

[comment]

It's not an operator. It's just a text character (note that it's surrounded by quotation marks) chosen to separate between the concatenated values. It is assumed that the values themselves will never contain the separating character.

Without the separator, or if the above assumption is not true, you might get ambiguous results, e.g. "1" and "23" combined to produce "123" will prevent the creation of otherwise legitimate record of "12" and "3".

[yaya]

thank you very much, I used the 1st method and it works perfeck, thanks again for help.