Validating field based on user, then prompting login if wrong user

[Joe_Schmo]

I have a Personnel table with a "Last" field. On a a related table's layout, there's a field that displays all "Last" names from the "Personnel" table in a drop down (Safety Clearance:Worker 1). I would like to set it up so that the following happens when choosing a name in the "worker 1" field:

(for user who's Personnel::Last = "Smith", and Account Name = "jsmith", 2nd user- "Doe", "jdoe" respectively )

Initially, the "Worker 1" field with the Personnel::Last dropdown is empty.

Initially, jdoe is logged in instead of jsmith (or anyone besides jsmith)

IF

Worker 1 = Personnel::Last for current user

THEN commit record, go to next field

ELSE

Revert record to blank

Prompt user to log in

I'm not sure how to write a validation calculation or script that can 'Get (AccountName)' then validate the entry based on that users Personnel:Last. Also, I want to make sure that the only two outcomes of the script or calculation are having the currently logged in user's last name entered in the Worker 1 field, or having it reverted to blank, no matter what the user does. I've played around with it a little and it seems like the user can click cancel on the login prompt and/or on the script.

If I just use validation only, then there won't be a prompt to log in. I want jdoe to get a pop up saying "You must be logged in to add your name as a worker", then be presented with the login screen.

If I try it with a script then the user can click cancel when the log in screen comes up and then cancel on the script. I just want to make it secure so that no one can add someone else name, effectively making the name in the block a digital signature. Once it's there, and someone else is logged in, it should remain with the previous users name and not be editable.

[Joe_Schmo]

Anyone know how to at least reference the right fields in a calculation so I can play around with it? For the account name, "Get (AccountName)" works. But I don't know how to compare that to the Personnel::Last value in Safety Clearance:Worker1.

The Personnel table has a field for UserName, in which I am entering each persons FMP account name. But FMP doesn't know that the name given to the user account and the value in a field called Personnel::UserName are the same. I'm just choosing to keep those the same as I enter each user.

[Joe_Schmo]

Maybe a "Let" function?

Let ( Get (AccountName) = AccName ; Let (here's where I get lost. Something that means, take the last name in the Worker1 Field, go to that personnel record and Get (Personnel:UserName) ) = UserName;

Then with the variables established, the validation is

AccName = UserName, with the failexpression "" to leave it blank

Could someone fill in the gaps here if this is a possible approach?

[doughemi]

You've got nothing to relate an account to a record in your personnel table, so there is no way to determine the correct personnel record to extract the Last field from. Add an acctname field to the Personnel table. Then you can search for acctname = Get(AccountName) and continue the script to see if Worker1=Personnel::last. I think I would do it in a script triggered by OnObjectExit rather than a validation.

[Joe_Schmo]

That's the direction I was trying to go with it. I've already set that up like I said in the 1st post. I have a Personnel:UserName field already. Everyone who will ever have access to the database will have a personnel record. When I give them access I will enter the Account Name they are given in the Personnel:UserName field. So the two aren't "related" in the traditional table relation sense, but the text values will always be the same.

But the problem is, the worker1 field displays the last name (Personnel:Last) field value , not the Personnel::UserName field value. I could have two people with the same last name but they will have different UserNames.

So the validation or script has to compare

Get(AccountName)

with the result of

Get (AccountName), copy, go to Personnel layout, select Personnel:Last field, paste, find, copy value

since there is no GTRR.

By the way, this would be a lot easier if there were a feature to link a user account to a personnel record. Any change there's a FMP feature or script for that? There's nothing extra in the account settings other than the description and I don't think that is a field you can reference in a calculation.

[Fitch]

I think you're doing this backwards. Why not have the user login, and as part of the login script, search the Personnel:UserName for Get(AccountName), and use the result to fill in the last name?

[Joe_Schmo]

I think you're doing this backwards. Why not have the user login, and as part of the login script, search the Personnel:UserName for Get(AccountName), and use the result to fill in the last name?

I think I get what you're saying but I'd prefer to keep the whole sequence of events as described in the first post. The reason being, this database will have a lot of people constantly logging in and out, and a lot of them are not very computer savvy. Some will fill out the majority of the form while others will only edit one block, as a digital signature that only they can edit.

If the person logged it has permission to edit a given field, then they can continue to do so without interruption. But if it requires another user's access then they will be prompted to switch users and have a supervisor or correct user for that field finish filling out the form- effectively 'bottom lining' it for approval after a lower level worker with lower access has filled out as much as they can.

I don't want them to have to remember what they can and can't do and when they have to switch users. I'd rather have my job of setting up the database be more complicated if I can keep it simple for the user.

[Vaughan]

All of this seems to assume that no two people have the same last name. Not a good idea.

[Joe_Schmo]

All of this seems to assume that no two people have the same last name. Not a good idea.

Vaughan,

That's already been taken into consideration. The "Worker1" field only displays the last name, but each user will have a unique Personnel:UserName similar to the user name we are all assigned at work for our work email. This way it is descriptive of the person, easy to remember but unique. If there are two "Smiths, one will have their first initial added, and if there are two J. Smiths, one will get the middle initial added. Everyone already has this assigned for their work email and they already know what it is so there's nothing extra to remember and it's already unique.

[Joe_Schmo]

Maybe something like this would work, but I think I need more before the IF statement to say which record the Personnel::UserName value should come from.

Set Variable [ $AccName; Value: Get ( AccountName ) ]

If [ $AccName = Personnel::UserName ]

Commit Records/Requests[ No dialog ]

Else

Revert Record/Request [ No dialog ]

Show Custom Dialog [ Title: "Invalid Entry"; Message: "You must be logged in to add yourself as a worker."; Buttons: “OK” ]

Re-Login [ ]

End If

Exit Script [ ]

[Fitch]

OK then, as part of your startup/login script, load the Personnel::UserName into a global field or global variable.

[Joe_Schmo]

OK then, as part of your startup/login script, load the Personnel::UserName into a global field or global variable.

How would a script determine which record to take the Personnel::UserName value from before it loads it into the global field though? That's the part I'm struggling with.

I myself know that the personnel record for John Smith with-

Personnel:UserName = jsmith

and

FMP login AccountName = jsmith

are suppose to be related, but how do I fetch the right Personnel:Last based on the "Get (AccountName)" command output? It would definitely be helpful to get that current user's Personnel:UserName into a global field like you said, but I don't know how to relate the current user to the correct record.

[Fitch]

Sample piece of login script:

Go to Layout( Personnel )

Enter Find Mode

Set Field( Personnel:UserName ; Get (AccountName) )

Perform Find

Set Variable( $$CurrentUserLastName; Personnel:Last )  // or set a global field

Go to Layout( data entry or whatever )

 

[Vaughan]

OK then, as part of your startup/login script, load the Personnel::UserName into a global field or global variable.

Watch out with global variables, they do not fall under the security umbrella. It's possible for a user to set the value in the global variable using, say, the data viewer.

[Joe_Schmo]

Watch out with global variables, they do not fall under the security umbrella. It's possible for a user to set the value in the global variable using, say, the data viewer.

Thanks for the tip! Are there other ways it could be altered as well that I should look out for? I'm not sure the users will ever have access to or need the data viewer but it's good to know.

[Joe_Schmo]

Sample piece of login script:

Go to Layout( Personnel )

Enter Find Mode

Set Field( Personnel:UserName ; Get (AccountName) )

Perform Find

Set Variable( $$CurrentUserLastName; Personnel:Last )  // or set a global field

Go to Layout( data entry or whatever )

 

Fitch-

This worked beautifully! Thanks for the advice. I went with a global field. One more question though. The second part of the script is to validate the current users selected value from the dropdown. I have to make sure they are putting their own name in the field, and if not, prompt them to log in with the right user. The idea is, that either the last guy didn't log out, and the right user is there but just needs to log in, or to let the wrong user know to go get the right guy and have him log in.

I have all this working now as well, but the script refers to the field name being validated, which means I will have to copy and slightly alter this script for several fields in the table. What can I replace the "Safety Clearance:Worker1" with so that the same script can be used on all fields? I tried "self" but got an error.

[Vaughan]

The second part of the script is to validate the current users selected value from the dropdown. I have to make sure they are putting their own name in the field, and if not, prompt them to log in with the right user.

Why do this?

FMP *already knows* who the current user is because they have logged-in and authenticated. Get( AccountName) is your friend. The only job left is to select the correct staff record (or create the staff record if none exists) and this should be completely automated, no user interaction.

Each user does have their own FMP account, right?

This post could be relevant...

http://fmforums.com/...-login-account/

I see you are already there...

[Joe_Schmo]

Vaughan,

This database is replacing a paper system that is currently trusted by management to track maintenance because they assume nobody would attempt to forge someone else's signature. However, some of the management has expressed concerns that by having a dropdown field with everyone's name in it that anyone can select is not secure enough to trust that the person put their own name there, and not someone else signing for them.

Having it verify on each field modification that contains a name seemed to be the solution. Being able to go back later and verify what account edited the field after the fact wasn't good enough for them. They wanted it to be set up so that if someone forgets to logout, no one could edit the wrong field.