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 Description of the image below:
1) The Employees table is the "Main" table - on that will be exported into another app for useage. 
2) The Staff Assignments table contains active and inactive staff records, pay increases, job code changes, terminations, etc. 
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). 


I need to be able to pull the Team Name (from the Supv-Team Join Table) into the Employees table for reporting.  We need to have a list of staff that includes the team names.  I have tried every which way that I can think of - and I'm friggin stumped!  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, 
Allissa


------------------------------
Allissa Weber
------------------------------

3 Replies

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