"IF" and "Lookup" in glide

Hey all,

I hope you’re all doing fine !

I’m kinda stuck on something with Glide. I’ld like to know if a particular item appears in a relation, but can’t find a way to make it work inside Glide (and I’ld like to stay inside Glide and not use Google Sheet).

A SIMPLIFIED SITUATION

3 tables :

  • A > one with a list of users, and a relationship to C
  • B > one with a list of items to choose from,
  • C > one with a list of items taken by specific user. Each line has a specific ID, and links an item with a user

The relationship works, and let me create a lookup column, which lists all the items taken by each specific user.

What I’ld like to do, is a IF column, to check if a specific item is taken by that user. The result should be boolean i guess. This way, i could use this result to attribute visibility to certain part of my app.

Does it make sense ? Do you have a way around ? Should I use Google sheet for that kind of thing ?

Thanks a lot for the time you’ll take for my lil problem, I’m really grateful !

1 Like

Can you explain more about what it is you are trying to control visibility for?

1 Like

Hi Jeff,
Thanks for your time !

I’m trying to control the visibility of the button which gives access to the form enabling a user to select an item.

OK. I’m assuming you are then viewing the details of the items in Table B. You should be able to create a relation from Table C to Table B, and if the relation is empty, then show the button. That’s the simplified version. I guess other questions would be…can more than one user have the same item? Can the item be returned for the same user and/or another user to claim again?

1 Like

Yes, I view the details of the items in Table B, and in fact, you’re right, I’ve a relationship from C to B.
And you’re right too, I should have indicated that more than one user can have the same item.
What I’ld like to prevent is for one user to take a single item multiple time. This doesn’t work with only checking “empty/not empty” (or at least, i can’t seem to find a way around it)

Well in fact, in a not so simplified situation, I also have another part to that problem. Each line of C has a feature that says “NOT USED” or “USED”. As long as a user has a “not used” item, I would like to prevent him from chosing another item. So, i’ve a relationship from C to A, and a lookup from it which tells me something like “(USED) (USED) (USED) (NOT USED)”. I would like to check if there is a “NOT USED” item in C for that specific user, to prevent the button “TAKE ITEM” to appear in B for that specific user.

Does it make sense ? I think it’s pretty much the same as the first problem : if I solve one of them, I should solve both of them using the same trick.

I made a mistake telling you the relation should be from C to B. Instead it should be from B to C.

Well, following your simplified version, you would be able to create a template column in table B that contains the signed in user’s email, the item, and the words ‘NOT USED’. A similar template would be created in table C. There you would already have an email column, item column, and status column, so your template would combine all three. Then you can create a relation in table B that joins the template in table B to the template in table C.

To enhance that for your ‘not so simplified’ version, you could create another template in table B that only joins the signed in user’s email and the words ‘NOT USED’. You would then create a similar template in table C. Then you would create a relation that joins that template in table B to the template in table C.

So simply adding a condition that checks if the first relation is empty would tell you if that particular item has been claimed and unused by the user. The second relation could be used with a rollup column to count the number of unused items a user has (assuming that a user can have up to 4 unique items at one time.)

In the end you can then add a visibility condition on your button to check if Relation1 is empty Rollup is < 4.

Edit: If count is not important and a user can only have one item at a time, a simpler alternative to the above would be to create a relation in Table A that links the user email to the email in Table C. Then you only need a Joined List column to create a string of all the Used/NotUsed statuses. Finally you could create an IF column in table A that would check if the Joined List column ‘includes’ ‘NOT USED’ or 'NOT USED ‘is included in’ the Joined List column. That could be your boolean result by returning ‘true’ or ‘false’ for the result in the IF column.

Kind of several ways to handle it. You could also do the simple version in Table B too, by creating a template with the signed in user’s email, and create a similar relation/joined list/If combination.

2 Likes

Thanks so much ! It’s such a great idea to use template this way ! it totally enables what i wanted.

In the end, i created 3 things :

  • in C, a template joining username and status of item
  • in A, a template joining username column and “USED”
  • in A, a relationship between those two templates

It works great, as count is not needed right now.

And thanks a lot about the rollup idea, it helped me solve some other stuff. But, just to be sure, for your idea about the 4 unique items, I would need to create a number column inbetween ? Else, the rollup would only be able to count or countunique. Am i missing something ? i mean, i can’t count lookup with multiple ?! I don’t get how I would make it work !

After another analysis of your answer, the point that i dont get is that u tell me to

create template column in table B that contains the signed in user’s email and the words ‘NOT USED’

I don’t get the part about the “signed in user’s email”. Table B is about items, not users. Do you mean it can check the user browsing the app, and that this column has a different value for each user, even if they are both online at the same time ? whaaaaaat, for real ?? I need to process that…

1 Like

What I was thinking there was that the relation would only be picking up the items marked as ‘NOT USED’, so a Rollup column with Count would be sufficient and you wouldn’t need a number column in between. No need for a Lookup column in any of those cases.


That was assuming that you were recording emails in Table C. In Table B, what you can do is create a template column and one of the replacement values can directly be replaced with the signed in user’s email, like this:
image

If you are instead directly writing a username instead of email address to Table C, and you have user profiles enabled, then you can pull that username directly from the user profile sheet in table B, like this:

In either case, yes the email or username would be unique to each user, even if multiple are signed in at the same time. The thing with any glide computed column is that they are computed locally on each user’s device, so there isn’t that round trip to the server and back. The results are instant and unique to each user.

2 Likes

hehe… once you get your head around that, it opens up a whole new world of possibilities :wink:

3 Likes

You just blew my mind. Thanks a lot for the very useful help !
As Darren say, i do have to try to get my head around it. It makes me realize there are certain things i’ve been avoiding using google sheet that i could have done from within glide. But, my app is quite big now… damn, so many possibilities and so little time ! Feels great and frustrating at the same time !

1 Like

Stick with it.
I’ve been a big GSheets user for almost 15 years now - the company I used to work with was one of the early adopters in the Asian region. I’m so used to doing “stuff” in sheets with formulas and scripts that once I started working with Glide it took me a while to cotton on to the fact that most of that is no longer necessary with Glide apps. Fast forward to almost a year later and I’m fully converted - I avoid Sheet formulas like the plague now. There is the occasional scenario when I find them still necessary, but those scenarios are increasingly few and far between.

4 Likes

That’s quite inspiring…

After some thoughts, i guess i’ld be able to work around about every thing I use GSheets for, but one : I need GSheet to use autocrate to export pdf docs from the data in my app.
Damn, it would have made my app way cleaner in the backend. Well, nevermind !

1 Like