Item/Category filtering issue

Ask for Help
1

Hi,
I’ve got a specific issue with item relations and filtering and have described the issue in the linked video here as I thought it would be easier to show it.

Thanks

2

There’s a lot to absorb in your video, so this will be a generalization, but essentially, if you want your categories list to only show the 3 or so items selected by the user, then you can either create a template column containing the signed in user’s profile email, then create single relation from your home tab to to the user sheet where the 3 categories are selection, then create a lookup to get the selected categories, then create a relation from the lookup to the options sheet.

Another option would be to create a template column in the options sheet containing the signed in user’s profile email. Create a relation from option sheet to the user sheet to link the category to the category array, then simply filter the categories list when the relation is not empty.

Hopefully that gives you some ideas.

3

Jeff,

Thanks for the response and I eventually managed to get it to only show the correct categories however when I click on a category to look at the items it show ALL of the items against that category - Auuurrhh!
I have tried various relations/lookups/filters but can’t make it work!

Any other words of wisdom?

Thanks

4

Maybe I need some clarification on how your app functions, but I think what I’m understanding is you have several businesses. For each business is a subset of categories, and within each category is a subset of activities. What i’m understanding is that a customer can sign in and enter a pin to see only one business and their associated categories and activities. Am I close?

Going with that assumption, you may be close with your Options sheet. It looks like you are obtaining the business the user is associated and creating additional columns from that. I’m having trouble determining what everything is based on the video, but i think you are getting the signed in user’s email and placing in a template, the using a relation to get the client/business they are associated with. I think you will only need a relation that’s set to single instead of multiple, so you will only return one business. From there you can get the client/business name via a lookup column. Then I would create a template to join the category and the business name together. In the POI sheet you will have to create another set of columns in the sheet that join the category and the business name together using an arrayformula, and set it up as an array just like the categories columns. Then you can create a relation from the joined category and client/business template to the category and client/business array column in the poi sheet.

I may be way off and misinterpreting your setup, but in essence, you will probably need some sort of templates to join multiple values together, so you can create a proper relation from the category to the business that only relates to that business.

I hope that makes some sense.

5

Thanks Jeff.
My interpretation in what you are suggesting is to create a UID by joining business/category fields together in the two tabs and then relating these together to be able to then filter the items within the options tab where there’s a match.
I understand the general logic of why you are suggesting this so I will give that a go.

Many thanks.

1 Like
6

Jeff,

So after a few more hours on this I have finally cracked it so thank you - It was a real head scratch for me to resolve that one.

Phil

1 Like