Ref field to 2 different data sources

Not sure how to title this, but here is what we are trying to do: We have a ref column to our Employee list. When an employee leaves, they are no longer in the ref column, but they are in the Sheet that is being used to create the records (ie AllEmps is a read-only table where we are pulling employee data from, Terminations is a table where we are storing the data in the App). When the employee is removed from the AllEmps table, the App shows the yellow triangle with exclamation point on any record where the employee has been removed from AllEmps.

We are thinking that we want to make an if statement on the ref, that if the record status is complete, look to the Terminations Sheet not the AllEmps.

Thanks in advance…hope it makes sense.

Not directly possible.

Why are you removing the departed staff from the AllEmps table?

That file gets updated weekly and we have no control over the data. It is updated with Active employees only. We are thinking we want to be able to view a record from deleted data, but the data is in the Sheet…is there a way to do it other than what we are trying?

P.S. Thanks for your quick response - as always!

1 Like

Hmm… Ideally, you’d want all employees, both current and terminated, in a single table. Perhaps importing the AllEmps data into a combined table whenever AllEmps is updated. Ideally, your app would then use the combined table for everything and the AllEmps table would only be used to import data. What are your thoughts on that idea?

1 Like

Hmmmmm…we would end up, very quickly, with thousands of records - we are almost 4k now, if we included all terminated, it would be over 10k…have you worked with an App that looks to a sheet with that much information?

We have come up with a solution. We have created a virtual column with a lookup to the recordID in the Sheet (not AllEmps). We then show/hide the 2 different columns depending on the status of the record. Thanks for your input.