Showing data from three levels of tables

[sales_bikefix]

I have these tables - Customers>Bikes>Jobs>Job Details>Items.

Is there a view based on jobs which would also show customers? ie 2 levels up.

[LeventK]

@sales_bikefix
You want this Customer data as a read_only or do you want the flexibility of adding/editing customer data within that inline view?

[sales_bikefix]

No we can edit the customer in another view if needed

[LeventK]

In your Jobs table, create a Virtual Column with below expression, which will create an inline view in your Job Details view for the related customers

SELECT(Customers[KeyColumnName],[JobID]=[_THISROW].[JobID])

You can alter the SELECT formula as per your table and column names and relationship.

[sales_bikefix]

Its just one customer. Can I acheve the same thing by making it “owned by”?

[LeventK]

@sales_bikefix
What do you mean with “owned by”?

[sales_bikefix]

Sorry, I meant
IsAPartOf

[LeventK]

Depending on what you want to achieve, yes you can. But isPartOf gives you the flexibility to create multiple customers for one job actually. So it will create many-to-one relationship between jobs and customers tables. Is that what you want?

[sales_bikefix]

All I need is for the job sheet to look like this screenshot, but with the bike description in it, which will have to be a table between customers and jobs.

[sales_bikefix]

Still struggling with this…

[sales_bikefix]

At the moment it is showing all the customers. I am using this

SELECT(Customers[Customer_ID],[Job_ID]=[_THISROW].[Job_ID])

[Steve]

Assuming the Jobs table has a column named Bike_ID that references the job’s parent bike, and the Bikes table has a column named Customer_ID to reference the bike’s parent customer, create a virtual column in the Jobs table with the app formula of [Bike_ID].[Customer_ID].

[sales_bikefix]

That works thanks. Its currently showing the Customer_ID, how do I make it show the name?

[Steve · Accepted solution]

Make sure the virtual column’s type is Ref and that the referenced table is Customers. Once so configured, AppSheet will display the content of the referenced row’s label column.

[sales_bikefix]

Thanks. Its gonna be great!