A ref that just "is"...?

Patrick_Paul · 08-31-2020 09:00 AM

I’m relatively new to Appsheet, and have only built one app. A new training app I’m working on needs to have a table that references data in other tables based on various ID numbers. For instance, a trainee may have an employee ID which should link to a table with HR info, a training ID issued by an outside training vendor that links to a table with their classes from that vendor. How can I allow a user to just enter these IDs as text or a number without them picking the number from a list of possible values as a ref normally does? I ask because usually the ID gets assigned before that data is present in the other table, and nobody wants to have to “remember” the ID for a week until the info populates. They want to put the ID in when it’s assigned and forget about it, and the ref become “active” once the data is populated in the other sheet(s). Is this possible?

Marc_Dillon

Perhaps provide a simple text input column for the user to enter the IDs into, then the actual ref-type column uses an appformula or initial value formula to read from the input column.

Patrick_Paul

OK, I can give that a try! Out of curiosity, what would be the expected result if the data isn’t “there yet” on the referenced sheet? Blank? Error? Thanks!

Steve

If a Ref column has a value that doesn’t match the key column value of a row in the referenced table, the Ref value will be displayed with a yellow triangle next to it indicating it’s a broken reference.

https://community.appsheet.com/search?q=yellow%20triangle

Patrick_Paul

OK, so once it’s “unbroken” (the data is populated in the referenced sheet) how do I get it to recognize that? For now, it’s staying with the triangle in the detail view. I’d like (ideally) the detail view to automatically update to a valid ref with no further user intervention if possible.

Steve

A Ref value must match the key column value of a row in the referenced table. As soon as that matching key column value exists in a row of the referenced table, the yellow triangle should disappear.

See also:

Patrick_Paul

Yes, I’ve realized it works on one of my tables where the label and key are the same. On another table, the label must be different from the key because there’s a possibility of duplicate IDs in the source data. This is the one that is not updating because the value of the ref is the label, not the key. How can I “fix” this without changing the key/label on the referenced table? Is there a way to “translate” the label to the key? For instance, some way to have the ID number they input in the text column (which is the label in the referenced sheet) reach out and “find” the key for that row? I’m not worried about the duplicate IDs because it should be very rare and can’t be helped since this is the data I’m receiving.

Marc_Dillon

Patrick_Paul

Thanks! This seems to work beautifully.