Lookup on a list of values

Hey, so I have a list of “Patient IDs” which I get by subtracting two lists e.g (list 1 - list 2). Now the resulting list of “Patient IDs” from this expression contains IDs which I want to show in an enum list. Until here it is all fine and I am able to do this.

Now, instead of the patient IDs, I want to show the Patient Names in the Enum list because that will be more readable for the user. I am not able to find a way of doing this.

I was thinking of functionality like a Lookup function which can lookup an entire list of values and returns a list of values instead of just a single value (if that makes sense)


1 Like

Hi Mate, thanks for such a quick reply. I like the approach you shared but actually even my Patient ID is not the Key Column, I have a different Key Column. Is there any way for that?

1 Like

The only way to display one thing in a dropdown while storing something else is by using a key (a Ref value) to display its label.

Please describe your table structures a little more. This dropdown is to provide a value for a column of a row of what table? Is there a separate patients table?

1 Like

Yeah Steve, I have two tables. One is “Patients Sample Sent” and another is “Sample Return from China”. In “Patients Sample Sent” table I record the Patient’s sample sent to China row by row. Then I have a “Return from China” table. Now, in “Return from China” table, I have a enumlist dropdown for selecting patient samples as multiple patients sample can be returned in single courier from China.

Also, a very important thing. I have created a third table “Patients sample returned” for recording the patient samples row by row which have already returned from china The dropdown will only show the patient samples which have not yet returned. To achieve this I use list subtraction where I deduct Patient Sample Column of table “Patient Sample Returned” from Patient Sample Column of table “Patient Sample Sent”

Provided that multiple “patients’ sample sent” can be contained in a single “Return from China”, I would advise setting the “Sample sent” table as a child of (search : ispartof appsheet) the “Return” table.

Keeping things as enumlist can get things done too, but a parent-child relation offers far more flexibility.

From the “Sample sent” table :

  1. You can add that sample to a new “Sample return” by an action. Make a column in the ‘sent’ table that’s a yes/no column (sample marked for return?). An action will mark that column of the concerned ‘sample sent’ record to yes.

  2. You can add a sample to an existing “Sample return”, same way marking that column to yes.

  3. You can add sample sent records to sample return records from within the sample return table, by referencing the sample sent table (which would be set up already if you do the ‘ispartof’) and devise a proper valid-if to filter all those records that doesn’t have the “sample marked for return?” column as yes.

Also, since the sample sent rows that have been assigned a return shipment, will inherently contain the sample return reference, you can make use of isblank() and IN() to do away with the additional column.

  1. If you follow the above, you don’t need a third table, which unnecessarily complicates things and adds to the sync time.

Given that you are subtracting lists, I might as well would assume you are advanced enough to understand what I said above.

1 Like

Sorry friend but i am not that advanced user in App Sheet. Can you please refer me a resource where i can understand a little more about your approach. Or if you can just guide me a little more over how to set a child tble. I will try to do the rest myself