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.
@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?
No we can edit the customer in another view if needed
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.
Its just one customer. Can I acheve the same thing by making it “owned by”?
@sales_bikefix
What do you mean with “owned by”?
Sorry, I meant
IsAPartOf
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?
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.
Still struggling with this…
At the moment it is showing all the customers. I am using this
SELECT(Customers[Customer_ID],[Job_ID]=[_THISROW].[Job_ID])
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].
That works thanks. Its currently showing the Customer_ID, how do I make it show the name?
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.
Thanks. Its gonna be great!
1 Like