I have a simple application to manage a small sailing club. There is a ‘members’ table containing basic membership information and a ‘lockers’ table containing information regarding a locker. There are more members than available lockers.
A locker is assigned to a member using the locker table as a base and the membership information presented as a picklist. This works as expected.
Now I would like to display a screen of members and the locker that they have been assigned to. The display I’d like to see is either a locker number or a ‘blank’ displayed in the list of members.
I was trying to set up a formula in the locker cell using FINDROW(Lockers, “Lockers[FullName]=Members[FullName]” )Lockers[LockerName]. This function call returns a ‘#ERROR!’ in the fields where I’d expect a LockerName to be returned and a ‘#N/A in the fields I’d like to display a ‘blank’.
Any suggestions on how I should approach this?
Thanks in advance
Roy Mac
Hi @Roym-9890 Awesome to hear that you are building an application for a sailing club! I believe creating a ‘Column List’ or ‘Stacked List’ will allow you to display what you are looking for.
In my example table I created a lockers table and created a rowlink to a sailing members table
If I create a ‘Column List’ that has my Sailing_Lockers table as its source, this is what will show in my app
As a result I can see the first two lockers are assigned, but the third isn’t. If you’d like to take this one step further and add something like “This locker is free” you can create that via conditional visibility . With this feature you can make your list look something like this
Here is the visibility setting I input for my example
Hope this helps! Let us know if you have any other questions!
Thanks for the information. Regarding the 'locker' table, that's exactly what I'm doing. However, I want a screen to show membership information with a column showing the locker (if any) is assigned to the member.
How do I reference the locker information in a membership screen?
Thanks for clarifying @Roym-9890. Try updating your syntax to this
=FINDROW(Lockers, “Lockers[FullName]=Members[FullName]”)[LockerName]
You don't need to refer to the Locker table again since the FINDROW is already pulling a row from the Locker table. Let me know if that doesn't work.
For a next step after that, if you'd like to display a certain text for members who are not assigned a locker you can use the IF function that may look something like this depending on your set up
=IF(ISERROR(FINDROW(Lockers, “Lockers[FullName]=Members[FullName]”))=FALSE,FINDROW(Lockers, “Lockers[FullName]=Members[FullName]”)[LockerName],"No Assigned Locker")
Let me know if this helps!
Hey @Matt_N,
Thanks for sharing the formula last night I was trying to solve @Roym-9890 issue and used the following formula:
=IF(COUNT(FILTER(Locker,"Locker[Member]=%",THISROW()))>0,FINDROW(Locker,"Locker[Member]=%",THISROW()),"")
however I changed it to use the formula in your example and this seems to be a much better approach thanks!!
=IF(ISERROR(FINDROW(Locker,"Locker[Member]=THISROW()"))=FALSE,FINDROW(Locker,"Locker[Member]=THISROW()"),"")
It's always great to learn new things!
Best Regards,
Nancy
Glad you found the post helpful!Thanks for your assistance. My root problem was I was trying to perform the lookup within the scope of the application which doesn't work. However, if I include a column in my 'members' table referencing the 'lockers' table things worked like a charm. I just needed to then reference the column from the 'members' table in the application scope.
I found one thing peculiar though. If I copied your formula and copied it into the code it wouldn't work. However, if I typed in the formula it worked perfectly. The difference I found was that part of the formula you gave contained 'angled' quotes; something that I don't have on my keyboard and Honeycode doesn't parse correctly.
Your example...
=FINDROW(Lockers, “Lockers[FullName]=Members[FullName]”)[LockerName]
My typing.
=FINDROW(lockers,"lockers[FullName]=Members[FullName]")[LockerName]
Notice that I don't use the angled quotes surrounding the 'condition'.
I'm now off to the races. Thanks, everyone for your help.
RoyMac
Great catch on the quotes @Roym-9890 !
Glad to hear your app is working as expected now 
Let us know if anything else comes up