Populate based on Current User

  • 1
  • 1
  • Question
  • Updated 2 years ago
  • Answered

I have multiple tables in my application. Listing the ones that are related to my query.
Team, Owners, Calendar Entries and Staff table.

The Staff table is nothing but a list of all staff members. The Owner table is related to the Staff table and the Team Table. Each Team can have more than one Owner. The Owner table has a user field called 'Staff-user' and this is populated based as a look up field.

The Calendar Entry table is also related to the team table.


What am I trying to achieve?
Every time I log into the Calendar Entry table,it should show me records only where the Calendar Team selected is the same as the current user's team. In order to do it I need to fetch Team related to the current user from the Owner table.

IMO, One simple way to achieve this is use Report Links, but unfortunately I cannot convert that into a field to be able to compare it.


Any suggestions? I am really stuck and could use some help here..


Thanks in advance





Photo of Angel

Angel

  • 724 Points 500 badge 2x thumb

Posted 2 years ago

  • 1
  • 1
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 51,166 Points 50k badge 2x thumb
Can you describe all the relationships between these 4 tables. It is not clear which are the parents and which are the children.
Photo of Angel

Angel

  • 724 Points 500 badge 2x thumb

Team is the parent for Owners and Calendar Entries.

Staff is the parent to the Owners table.

Owners is also the parent to Calendar Entry.


(Edited)
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 51,166 Points 50k badge 2x thumb
I think that this one is easy, but tell me first where is the userid for the user that you are trying to match on is stored.  Is it on the owners table or the staff table.
Photo of Angel

Angel

  • 724 Points 500 badge 2x thumb
So the Staff table originally has all the users loaded which is the parent to the Owners table.
The Owner table has the users too as a part of the lookup fields trickling down from Staff table.
Photo of Angel

Angel

  • 724 Points 500 badge 2x thumb

Posting a part of the relationship diagram. Hope this helps you to understand the table relations better

(Edited)
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 51,166 Points 50k badge 2x thumb
Ok, here goes. It's not that  difficult.

Make a  formula numeric field on the Owners table called [I am the current user (=1)]
Make the formula to be 

IF([userid]=user(),1,0)

It will calculate to 1 if true.

Make a summary field for the maximum of that field up to the Team table.  Call it

[I am the on this Team (=1)]

It will be 1 io I (the current user) is on this Team.

Lookup the field 

[I am the on this Team (=1)]

down the Calendar entries table.

You can now filter your Calendar report where that field =1, or use it as a Dynamic filter if you make a new  formula text field  yes no type field  called [My Team?] with an IF formula.
Photo of Angel

Angel

  • 724 Points 500 badge 2x thumb

Works like a charm.

Thank you for your inputs. Really helpful

Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 51,166 Points 50k badge 2x thumb
I call that my "shine the light" process.  Using that technique you can snake your way down lookup fields (easy and obvious) or up though summary fields (still easy but not obvious).  You can get though ridiculously complex relationships that way.  I think of it like shine a blue light into one end of a tunnel and watching it come out the other end.
Photo of Angel

Angel

  • 724 Points 500 badge 2x thumb
Like you said, easy but not obvious. Going forward, will definitely keep this in mind. Learnt something new today :)
Really appreciate your help