Value list that displays calculated field

[posternutbag]

Hello,

I have a database to record data entry errors.  In the "Log" table the user selects the transgressor from a value list "staff" that pulls from a field called "StaffID" in a table called "Staff".  I would like the values displayed to be from a second field- "Staff::Username".  Unfortunately, "Username" is a calculated field -

Right(Users::LoginID;Length(Users::LoginID) - Position(Users::LoginID;"";Length(Users::LoginID);-1))

 

"Users" is a external SQL database.  The values of LoginID read like

DOMAINNAMEUsername

so the calculation is to trim off the domain name and the slash.  If I try to create the desired value list, when I specify the fields for it, I select "Staff::StaffID" for the first field and "Staff::Username" for the 2nd (displayed) field.  When I do that, I get this:

 

This value list will not work because the field "Username" cannot be indexed.  Proceed anyway?

 

And the value list doesn't work.  Am I missing something?  I'm sure there is a better (working) way to do this.

 

 

[Rick Whitelaw]

Not sure if this is relevant, but the key field of a child table must be able to be indexed.