Auto Populate Columns from a "Ref" Selection

[CANA-IT]

I have a gsheet data source for an App.
The gsheet has numerous tabs on it.
Main Tab - Deliverables and the tab which all the data I need to pull from is "Project_Info" and all these columns are on it with rows of data.
Key = Project Name
The columns below (need to be auto-populated) when the 'Ref' column "Project Name" is selected:
Deliverable Lead
Deliverable Lead Name
Project Lead
Project Lead Name
Program Manager
Program Manager Email

I have tried various methods including dereference and maybe I am doing something wrong, I don't know.
Do I need to create multiple virtual columns to call the data which needs to be auto-populated?
I'm sure it is something simple I am missing...(Always is, LOL)

I have another App which used a Database and everything works find in it... I select a "city" (Ref Column) and the other data populates under it such as "Zipcode" automatically. using a virtual column
Virtual Column "City Data" with a formula [City].[Zipcode]
For whatever reason this process won't work with the GSheet Datasource.
Thanks...

[WillowMobileSys · Accepted solution]

To paraphrase what I think you are attempting...you have a Projects Table and a Deliverables Table.  In the Projects table you have all the values you need including Deliverable Lead, Deliverable Lead Name, etc.   When you select a Project on the Deliverables Form View,  you want the Deliverable Lead, Deliverable Lead Name, etc to made available to the Deliverables row for viewing or decision making in the app.  Do I have that right?

There are 2 ways to go about this:

1)  Have actual table columns for each of Deliverable Lead, Deliverable Lead Name, etc columns you wish to replicate.  Then in each replicated Deliverable Table column, assign into the App Formula the dereference expression-e.g. as [Project].[Deliverable Lead] where [Project] is the column in your Deliverables table where the Project reference has been chosen.

2) Add Virtual Columns for each Deliverable Lead, Deliverable Lead Name, etc columns you wish to replicate.  In each Virtual Column,assign into the App Formula the dereference expression.

For this situation, I prefer option 2.  Should any of the Project details change in these columns, the Deliverables rows will automatically pick them up.  The dereferencing does not add much of any time to the Sync function UNLESS the dereferenced column has some slow performing expression itself.

I hope this helps!

[WillowMobileSys · Accepted solution]

To paraphrase what I think you are attempting...you have a Projects Table and a Deliverables Table.  In the Projects table you have all the values you need including Deliverable Lead, Deliverable Lead Name, etc.   When you select a Project on the Deliverables Form View,  you want the Deliverable Lead, Deliverable Lead Name, etc to made available to the Deliverables row for viewing or decision making in the app.  Do I have that right?

There are 2 ways to go about this:

1)  Have actual table columns for each of Deliverable Lead, Deliverable Lead Name, etc columns you wish to replicate.  Then in each replicated Deliverable Table column, assign into the App Formula the dereference expression-e.g. as [Project].[Deliverable Lead] where [Project] is the column in your Deliverables table where the Project reference has been chosen.

2) Add Virtual Columns for each Deliverable Lead, Deliverable Lead Name, etc columns you wish to replicate.  In each Virtual Column,assign into the App Formula the dereference expression.

For this situation, I prefer option 2.  Should any of the Project details change in these columns, the Deliverables rows will automatically pick them up.  The dereferencing does not add much of any time to the Sync function UNLESS the dereferenced column has some slow performing expression itself.

I hope this helps!

[CANA-IT]

Of course... after I posted this I went back and did all the same stuff again and it worked.. So... I did Your Option 2. and also what @AlexM suggested.

[AlexM · Accepted solution]

One virtual column for each column you want to pull the data: 
[Project_Info].[Deliverable Lead]

But ideally avoid VC and have a physical column in your table.
Better performance over time