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
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.
Posting a part of the relationship diagram. Hope this helps you to understand the table relations better
Works like a charm.
Thank you for your inputs. Really helpful