Struggle to find a way to filter gallery from 3 tables

[spiderkzn]

Hi, 

I am battled to return Person name in gallery called galleryMeetings. So we have 3 table which it already include in data source) that contain have (table Name): ID

Meetings: RoleID (foreign key)  

Role:RoleID (primary key), PersonID (foreign key)

Persons: PersonID (Primary Key), PersonName

Basically, I am looking to display PersonName on Gallery. 

So the Meetings:RoleID equal to Role:RoleID and Role:PersonID is equal to Person:PersonID. 

So I'm trying to use formula to filter this in Items on galleryMeetings

[Drrickryp]

@spiderkzn 

It will be difficult to disambiguate your relationships in a formula if you use the exact same name as both the primary and foreign key in your tables.  INHO, it would be best practice have the Primary key of a table simply be ID and the foreign keys to be the name of the ParentTableID.  That being said, are you planning on using cascading dropdowns to obtain your result? 

[spiderkzn]

Thank you forreply. 

Yes that right I'm planning to use dropdown. if it possible? 

dropdown is ddlPerson so it should contain PersonName but the PersonID should link to PersonID in Role, and in Role the RoleID should link to RoleID in Meeting. 

If it possible? 

[Drrickryp]

@spiderkzn 

So, if I am understanding, One meeting can have many roles and one role can have many persons?

[spiderkzn]

Yes each meeting will have many Role but each Role will have one Person. (see table below)

Meeting Table	Role Table	Person
RoleID (have many)	RoleID (primary key)
	PersonID (foreign key)	PersonID (primary key)
		PersonName

Therefore, I'm looking to display PersonName on dropdown on Meeting Table. 

[Drrickryp]

If you have a gallery with the meeting table as its Items property and it contains a dropdown, Dropdown1 say, with its item property:

Filter('Role Table',RoleID = ThisItem.RoleID) and it shows the Role name in the window, you can add a label and set its Text property to Lookup(Person, PersonID = Dropdown1.Selected.PersonID).PersonName.