I’ve spent three days trying to figure this one out, so I think it’s time to lean on the awesome Glide community for some guidance!
The goal:
To create a page that allows businesses to create and manage their ‘Products’ and 'Product Categories’. This page should display the created Products and Categories that are relevant to the business, it should not show categories or products that other businesses have created.
My progress:
I have made the logic to display only the Products and Categories a business has created by simply using a 'value from user profile’ field to add a Business Name to every submission a user of a business makes.
Current flow:
User clicks Add on the Categories collection
User fills in the Category name and submits
A new row is automatically created with the Category name. A business name is also added through a ‘value from user profile’ (to tie this category to the business).
The same process is used when creating products.
My issue:
When filtering the collection by 'Business is User Profile’s Business Name’, it correctly shows the user’s relevant categories, but it also shows duplicates if another user has created a category with the same name. I only need it to show the category once, not each time it appears within the data table. Example:
User 1 (Nike) creates a category called ’Shoes’ and one called ’Socks’
User 2 (Adidas) creates a category called ‘Pants’ and one called ’Shoes’
When both users look at their categories list, ‘Shoes’ will appear twice for them (example below). How can I filter it so the duplicate category only shows once for them and not twice?
For reference, the second half of that screenshot is what the sheet looks like when businesses create Categories and Products. Note the 5th line doesn’t have a Product because it was the user adding a new category ‘Hats’. The blank Product cell it creates is simply filtered out.
Just throwing this out there for consideration. Would it make more sense to have a separate Category table and Production Table? Seems like you are trying to dual purpose this one table for use for both Categories and Products.
I can see the potential problem here, but you example is confusing. You show Nike and Adidas and say that they will both see duplicates. How would that be the case if you are filtering by business? I think your example should be showing the fact that you have two rows for Nike Shoes, and that’s most likely why you see duplicates.
Couldn’t you just use this as your filter. Only show Categories where there isn’t a Product filled in?
Ultimately I would probably set things up differently. But to stick with your current setup, what you can do is create a template column that joins Business and Category together. Using that template, create a single relation back to itself. Then create a Lookup column that returns the Row ID from the relation. Finally create an IF column that checks if the Row ID matches the Lookup Row ID. If it’s a match, then return true, else false. Or you could probably just have the IF column return the Category name. This will give you a boolean value of true (or Category name) only in the rows that have that Business/Category combination the first time it appears in the data.
I hadn’t considered breaking Categories and Products into separate tables, sounds like that could potentially make things a little easier.
Apologies if I confused you. Here’s how the example was meant to work:
A user creates product categories (eg. Shoes, Hats, Socks) which they can then use to categorise a list of products.
When the user wants to add products, they first select which category the product belongs to (from the category list made in step 1), and then add their products. The ‘Shoes’ category may have 20 different products added to it as an example. This is why you see multiple rows for Nike Shoes - Nike (the user) should be able to create as many products assigned to the Shoes category as they’d like.
It’s a great idea, but I found the issue still hangs around that if two different users create a category that has the same name, it won’t simply show the category once when displaying the categories in a collection list, it will show the category as many times as it has been created. The below screenshot is data filtered for one user/business who created a ‘Pants’ and a ‘Shoes’ category, but because another business also created a ‘Shoes’ category for their purposes, it pulls through every instance of the category which duplicates it. I’m trying to stop this appearing like this.
I’d be interested to see more of the filter you are showing it in your screenshot above. If you click on it to expand it, I’m curious how you have it set up. Seems like you shouldn’t have duplicates categories coming from different businesses. However, I could understand duplicates within the same business because of a row for each product. I just want to see if the filter is comparing the business name to the business name in the user profile.
I think the filter is technically working in that it is showing a user only the category names they have created. However, if another user has also created a category of the same name, the filter isn’t smart enough to filter out that business’ instance, so it pulls in two instances of the same category (one for this user and one for the other user who created it) which causes the duplicate category in the earlier screenshots. It’s almost like the filter is set up correctly but not functioning as expected.
Assuming you are viewing as a Nike user, then it’s showing Shoes twice because you have two entries for shoes under Nike. I think you are assuming that it’s also picking up the Adidas Shoes category, but that’s not the case. It’s duplicated because of the two Nike records. Adidas is still being filtered out. With that, I feel the filter is working correctly as set up. But that’s neither here nor there. You would ultimately still have the issue with the category list growing bigger and bigger with more duplicates once you add more and more product records for Nike. For that reason, I recommend using the steps I provided using the template, relation, lookup, IF. Then you wouldn’t necessarily need a user to create a row with a category and no product. They could just enter products and the categories would work themselves out, because my method would only select unique categories.
