Forum Discussion

AllissaWeber1's avatar
AllissaWeber1
Qrew Member
3 years ago

Pulling Data up from several tables deep to report

Ok, I know this should be possible...I have an App with 9 tables in it.  This app is functioning as a Master Staff List, and also HR Database with Staff Assignments, pay, etc in it.  Brief Descript...
MarkShnier__You's avatar
MarkShnier__You
Qrew Champion
I Prefer to see the traditional Relationship diagram for clarity.  In future you might look for that link in the advanced Properties for the app.  That way you can also drag and drop the boxes to have the relationship makes sense visually.

I think that diagram shows 

Employees < Supervisor Team Joins > Teams

So 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]), " ; ", "\n")


That will give you a nice vertical list of the Team Names.



------------------------------
Mark Shnier (YQC)
mark.shnier@gmail.com
------------------------------
AllissaWeber1's avatar
AllissaWeber1
Qrew Member
3 years ago
Thanks Mark, that did work. 

Now, I have another question.  Is there any way to only pull the ACTIVE record in the Staff Assignments table?  The combined summary is pulling all instances of Team Names that are active in the employees past records (in Staff Assignment Table).  Example below.

I also learned where the original version of the Relationship Diagram is!  ;o)


------------------------------
Allissa Weber
------------------------------
  • MarkShnier__You's avatar
    MarkShnier__You
    Qrew Champion
    3 years ago
    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
    ------------------------------