Pulling Data up from several tables deep to report

Question

Ok, I know this should be possible...I have an App with 9 tables in it.&nbsp; This app is functioning as a Master Staff List, and also HR Database with Staff Assignments, pay, etc in it.&nbsp;Brief Description of the image below:1) The Employees table is the "Main" table - on that will be exported into another app for useage.&nbsp;2) The Staff Assignments table contains active and inactive staff records, pay increases, job code changes, terminations, etc.&nbsp;3) The Teams table contains a list of the 30 teams within the building.4) The Supv-Team Join Table, is a junction table, with data from Supervisors and Teams feeding into it; and allowing individual Supervisors to be linked with an Individual team, while maintaining historical data of past supervisors (inactive, etc).&nbsp;

I need to be able to pull the Team Name (from the Supv-Team Join Table) into the Employees table for reporting.&nbsp; We need to have a list of staff that includes the team names.&nbsp; I have tried every which way that I can think of - and I'm friggin stumped!&nbsp; Not sure if it's something simple and I'm just blind to it, after more than a week of trying to get this to work, or what.Glad to provide more info if needed - this is my first official app, so bear with me!Thanks,&nbsp;Allissa------------------------------Allissa Weber------------------------------

markshnier__you · Answer

I Prefer to see the traditional Relationship diagram for clarity.&nbsp; In future you might look for that link in the advanced Properties for the app.&nbsp; That way you can also drag and drop the boxes to have the relationship makes sense visually.I think that diagram shows&nbsp;Employees &lt; Supervisor Team Joins &gt; TeamsSo just lookup the Team Name down to the middle join table.Then make a Combined Text Summary field of that Team name up to Employees.If you don't like the "bubble" appearance of the Team Names then also make a new text formula field like this.SearchAndReplace(ToText([Combined Text Team Name]), " ; ", "
")That will give you a nice vertical list of the Team Names.------------------------------Mark Shnier (YQC)mark.shnier@gmail.com------------------------------

allissaweber1 · Answer

Thanks Mark, that did work.&nbsp;Now, I have another question.&nbsp; Is there any way to only pull the ACTIVE record in the Staff Assignments table?&nbsp; The combined summary is pulling all instances of Team Names that are active in the employees past records (in Staff Assignment Table).&nbsp; Example below.I also learned where the original version of the Relationship Diagram is!&nbsp; ;o)------------------------------Allissa Weber------------------------------

markshnier__you · Answer

np,
Like any Summary field, the Combined Text Summary field properties can be configured with a filter. So just add the filter where the [Assignment Status is Active].

------------------------------
Mark Shnier (YQC)
mark.shnier@gmail.com
------------------------------

Forum Discussion

Pulling Data up from several tables deep to report