Hi guys, i am having a trouble with making 4 subcategory for my app. As you can see in this pic
On the database sheet, i have customers, ASC and unit model column while on the 2nd pic on the detail commodity sheet, i have P/N based on the unit model
The plan for my app is to set the ASC column as the main category, Customers column as the 2nd category, Unit model as 3rd category and P/N as 4th category.
So when i open the ASC tab on my app, it will show all the customers list for that ASC, then when i tap the customers, it will show all the unit model that they have and when i tap the unit model, it will show all the P/N based on that particular unit model.
I am having no trouble making the relation between the ASC and customers column but im stuck when tried to tie the customers with the unit model and so on.
The database sheet itself is a connected with Form Button on my app, so when the user fill the form, the database will updated. Can someone help me with this problem?
First I want to ask if you expect a customer to be listed twice after selecting ASC or only once? I ask because from what I understand from your description and from what I see in your screenshots, it appear that a single customer can have multiple units, so they are listed twice in the Customers column. This makes things a little more complicated but still doable if you expect that customer list to be unique.
Thanks for your respond @Jeff_Hager I expect the customers to be listed one (unique).
Ok, that will complicate things a bit. Not bad, but a few more steps. I’ll try to write something up later today. Not asking you to do it yet, but if we need it, would you be willing to have a separate sheet that lists customers only? There may be a way without it using your existing sheet structure, but I will have to double check.
That’s totally fine because that was the way i try it before, by making new sheet and using Unique function to get only one unique customers and made a relation column to get the unit model. However, when i click the customer tab, it shown all the unit model including from other customers.
The following was as far as I got before I hit a snag. Technically I think I could continue and make it work, but in one way or another, we would probably be excluding information from the Database sheet that you would want to view at some point. I’m still including it in case it helps to provide ideas and insight on how to set this up. Essentially the problem is the sheet structure. Getting from ASC to Customers is pretty easy, but the problem comes in when we want to display details for a customer with a list of Units. We have to design the screen with the Customer name and the Unit list, but also when we click on a Unit, we will still be within the same screen from the Database sheet. This causes a problem because detail layouts are tied explicitly to the sheet, so anytime you view that rows details by clicking on a list item, the layout is consistent throughout the app and throughout all list items. Since Customers and Units are listed on the same sheet, the method below would put us into a perpetual loop without being able to select a unit to view a list of parts:
So here is how I would do it. This is all assuming that the combination of Customer/ASC/Unit will always be unique:
- You already have an ASC sheet and a relation to the Database sheet. That’s perfect.
- First we will need to create a Template Column in your Database sheet that will join the Customer and ASC together as one column value.
- Next create a Relation Column that links that Template column back to itself in the same sheet. This will bring back all rows that match the Customer/ASC combo.
- Next create a Single Value Column that will get the First matching Unit from the Relation>Unit.
- Next create an If/Then column that checks to see if the Unit Model column matches the Unit returned in the Single Value column. If so, set the result to ‘true’.
- Next, go back to your ASC tab in the app and select one of the ASC items to view it’s details. Here you can delete all of the components or keep some if you want to at least show the ASC name.
- Add an inline list that will display rows from the Relation you already set up in the ASC sheet in Step 1. You can set up the list to display customer names.
- Also set a filter on that inline list to only display rows where the If/Then column value we set earlier is equal to ‘true’. This will give you a unique list of customers.
- Now click on one of the customer names to view the details of that customer. This will only be showing the first row for the matching Customer/ASC combo in the Database sheet.
- Delete any unnecessary components except for maybe Customer Name and ASC.
- Add an inline list that will use the Relation we setup earlier in Step 3. Set the list up to display the Unit Model that belongs to that Customer/ASC.
- Now back to your Database sheet in the glide data editor.
- Create a Lookup Column that will use the same relation you created earlier Step 3 to return all the Unit Models for that Customer/ASC combo as an array.
- Create a Relation Column that will take that Lookup array and link it to the Unit Model in the Detail Commodity sheet. (This is where it fell apart because to display details for each unit, we would not be able design the unit details screen without altering the layout for Customer details.)
Ultimately what I would do in a situation like this is create a separate Customer sheet that is simply a UNIQUE formula that pulls the Unique Customer Names and ASC column values into another sheet. I don’t know if you have additional customer details or if this is enough. You would then create the relation from the ASC sheet to the Customer sheet. Display the ASC details when clicked, and add an inline list that uses the relation from ASC to Customers. Within the Customers sheet, you would then create template column that joins ASC and Customer together as one column. Create a similar template in the Database sheet, then create a relation in the Customers sheet that links the template columns together. This will show a list of customers within the ASC and allow you to click to see the customer details. In the Customer details you add an inline list to show the related rows (Units) from the Database sheet. Click on a Unit item in the list to view it’s details. In the Database sheet create a relation to link the Unit to the Unit in the Detail Commodity sheet. Then add an inline list to display the list of parts related to each Unit.
Hope that’s clear as mud.
1 Like
I think i am going to try the second suggestion from you, but can you explain more the detail about making the template column?
What would you like to know about it. You should be able to create a template columns, add two placeholder values, such as ‘Cust’ ‘ASC’, the set up which columns to use to replace those placeholder values.
