Ref field to 2 different data sources

PJMatelli
Participant V

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.

0 5 192
5 REPLIES 5

Steve
Participant V

Not directly possible.

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

PJMatelli
Participant V

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!

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?

PJMatelli
Participant V

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?

PJMatelli
Participant V

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.

Top Labels in this Space