Discussions

 View Only
Expand all | Collapse all

Combining multiple Pages to One for a Single Report

  • 1.  Combining multiple Pages to One for a Single Report

    Posted 12-05-2019 16:43
    I have a table in which 100+ projects reside. I have another table of phases that are connected to the projects (sometimes multiple phases to a single project). I also have a table of support tickets that are connected to a particular project. Right now I have someone running a report on all of the projects, a report on all of the phases, and a report on all of the support tickets. They then download these to Excel and do a line by line comparison to check for "open" and "closed" statuses between the three. Very cumbersome. Would it be possible to create a separate table that pulls in all of the projects, all of the phases, and all of the support tickets and then run one report on the "open" and "closed" statuses from that table? IF so, how would that look? Thank you in advance!

    ------------------------------
    Jaime
    ------------------------------


  • 2.  RE: Combining multiple Pages to One for a Single Report

    Posted 12-05-2019 19:43
    You should be able to do this with your existing relationships.

    So you have 

    Project < Phases
    Project < Support Tickets

    Can you say in words what the report is that you want?

    I want a report of all ......... where ...... is true

    ------------------------------
    Mark Shnier (YQC)
    Quick Base Solution Provider
    Your Quick Base Coach
    http://QuickBaseCoach.com
    markshnier2@gmail.com
    ------------------------------



  • 3.  RE: Combining multiple Pages to One for a Single Report

    Posted 12-06-2019 09:08
    Hi Mark,

    Thanks for your help. I do have relationships as you have listed. Basically each project will have a status of open or closed. The phases are linked to the project name and have statuses of open or closed. The support tickets are linked to the project name and also have statuses of open or closed. A report is being pulled from each table, project, phase, and support ticket based on the open and closed status to audit continuity. My thinking was that I add all of the tables into another table and pull just one report from the new table. Are you saying I can create a report based on the three separate tables?

    ------------------------------
    Jaime Jaime
    ------------------------------



  • 4.  RE: Combining multiple Pages to One for a Single Report

    Posted 12-06-2019 09:10
    To be more clear, I need a report that shows all of the projects, all of the phases, and all of the support tickets and what the status is of each one.

    ------------------------------
    Jaime Jaime
    ------------------------------



  • 5.  RE: Combining multiple Pages to One for a Single Report

    Posted 12-06-2019 10:02
    What about if we can easily do a report of all the support tickets, listing columns for the Project and its status as well as each Phase and it's status.

    ------------------------------
    Mark Shnier (YQC)
    Quick Base Solution Provider
    Your Quick Base Coach
    http://QuickBaseCoach.com
    mark.shnier@gmail.com
    ------------------------------



  • 6.  RE: Combining multiple Pages to One for a Single Report

    Posted 12-06-2019 12:01
    That sounds like just what I need.

    ------------------------------
    Jaime Jaime
    ------------------------------



  • 7.  RE: Combining multiple Pages to One for a Single Report

    Posted 12-06-2019 12:18
    OK, so you can easily look up any Project information down to the support tickets and make a report of all support tickets along with their project name and Project Status, but you are also wanting to have a field on that  Tickets report which shows the Phases.  That is the only tricky part. 

    We can float up text information from the Phases up to the Projects.  make a field on the Phase record with the information you want to float up, called [Phase Summary] such as

    List("-", [Phase name], [Phase Status])

    Now got to the relationship where One Project has many Phases and make a Combined Text Summary field of the field [Phase Summary].

    It will appear in "pill" format. 
    If you don't like that format, you can make a formula text field
    SerachAndReplace(ToText([Combined Text Summary Phase Summary]), " ; "\n")

    That should display the Phase info in a vertical format.  Then just look that up down to SuPport Tickets and add it to our report. 


    ------------------------------
    Mark Shnier (YQC)
    Quick Base Solution Provider
    Your Quick Base Coach
    http://QuickBaseCoach.com
    mark.shnier@gmail.com
    ------------------------------



  • 8.  RE: Combining multiple Pages to One for a Single Report

    Posted 12-06-2019 12:38
    Okay, thank you. I think I understand the flow. I'll get cracking but may have other questions.

    ------------------------------
    Jaime Jaime
    ------------------------------



  • 9.  RE: Combining multiple Pages to One for a Single Report

    Posted 12-06-2019 13:40
    Sorry Mark, I'm off to a rocky start already...the [Phase Summary] field will be a formula text field with List("-",[Implementation Phase Name],[Status]) as the formula? Each project I enter returns ??? in that field.


    ------------------------------
    Jaime Jaime
    ------------------------------



  • 10.  RE: Combining multiple Pages to One for a Single Report

    Posted 12-06-2019 14:02
    Let's go step by step.
    On the Phase record make a formula field 

    List("-",[Implementation Phase Name],[Status]) 

    Have you done that and when you do that does it show up correctly on the Phase record.

    ------------------------------
    Mark Shnier (YQC)
    Quick Base Solution Provider
    Your Quick Base Coach
    http://QuickBaseCoach.com
    mark.shnier@gmail.com
    ------------------------------



  • 11.  RE: Combining multiple Pages to One for a Single Report

    Posted 12-06-2019 15:40
    Ah, I see where I was confused. I was looking on the form, populating the project name and getting the ??? Yes, I created the formula field with the exact formula you have. It appears on my Phase Report and lists the Phase names. 


    ------------------------------
    Jaime Jaime
    ------------------------------



  • 12.  RE: Combining multiple Pages to One for a Single Report

    Posted 12-06-2019 15:42
    OK, great, so step 2 is to float that up to the Project.  So make a combined text summary field on the relationship where Oner Project has many Phases.

    You should now see that field on a Project record in a "pill" appearance format.

    ------------------------------
    Mark Shnier (YQC)
    Quick Base Solution Provider
    Your Quick Base Coach
    http://QuickBaseCoach.com
    mark.shnier@gmail.com
    ------------------------------



  • 13.  RE: Combining multiple Pages to One for a Single Report

    Posted 12-06-2019 16:02
    Yes, I have the phases showing up in the "pill" on the project record

    ------------------------------
    Jaime Jaime
    ------------------------------



  • 14.  RE: Combining multiple Pages to One for a Single Report

    Posted 12-06-2019 16:04
    So, you can just look that up down to the Support Tickets, but my suggestion is to convert it to a vertical list.

    You can make a formula text field I made a syntax correction in bold.

    SerachAndReplace(ToText([Combined Text Summary Phase Summary]), " ; ", "\n")

    ------------------------------
    Mark Shnier (YQC)
    Quick Base Solution Provider
    Your Quick Base Coach
    http://QuickBaseCoach.com
    mark.shnier@gmail.com
    ------------------------------



  • 15.  RE: Combining multiple Pages to One for a Single Report

    Posted 12-11-2019 13:37
    Hi Mark, sorry I have not been able to get back to the project. I'll keep working through it and let you know how it goes. Thanks!

    ------------------------------
    Jaime Jaime
    ------------------------------



  • 16.  RE: Combining multiple Pages to One for a Single Report

    Posted 12-27-2019 13:50
    Hi Mark,

    I've been pretty busy lately but just back to working on this. Everything works well as far as returning information into a single report. I just need help with the formatting a bit please. For example, I have a project with 6 Phases and thanks to your formula those show up in a vertical format. However, the Phase Status only shows the three statuses, Active, Completed, and Inactive. Is there a way to line up the correct status with the correct Phase? Thanks!

    ------------------------------
    Jaime Jaime
    ------------------------------



  • 17.  RE: Combining multiple Pages to One for a Single Report

    Posted 12-27-2019 14:33
    np,
    easy solution.

    make a field down at the child level which combines the Phase and the Status.

    List(" - ", [Phase], [Status]) and change the target field for your Combined text summary field to look at that field instead.   That way the two fields will stay together as they get rolled up.

    ------------------------------
    Mark Shnier (YQC)
    Quick Base Solution Provider
    Your Quick Base Coach
    http://QuickBaseCoach.com
    mark.shnier@gmail.com
    ------------------------------



  • 18.  RE: Combining multiple Pages to One for a Single Report

    Posted 12-27-2019 15:37
    Excellent. Works like a charm. Thank you so much for all of your help.

    ------------------------------
    Jaime Jaime
    ------------------------------



  • 19.  RE: Combining multiple Pages to One for a Single Report

    Posted 12-27-2019 16:37
    Jaime,

    You said "Pill."  Do you have a Tableau background?   

    Does moving the data to BI/BA make more sense for some grander reporting needs or does Mark have this 99% solved?  If you are almost done, then stay the course.  Otherwise we can discuss getting to SQL and BI.

    ------------------------------
    Don Larson
    Paasporter
    Westlake OH
    ------------------------------



  • 20.  RE: Combining multiple Pages to One for a Single Report

    Posted 12-06-2019 12:16
    Edited by Adam Keever 12-06-2019 12:37
    Jaime,

    Take a look at his:

    Table to Table to Table relationships with Lookups and Summary fields

    Following this architecture will allow you to view all of your records within the support tickets table. You would want to lookup the status of your project to your phase table and then lookup the statuses of project and phase to your support tickets table. Then you could create a formula field to return "open" if all three statuses are not "closed" otherwise return "closed".

    In this manner, your delegate would not need to run a report, but only navigate to the support tickets table and apply the dynamic filter. Likewise, you could easily create a report with the same filter applied and add that to a dashboard or as a link in an email notification.

    Here is what it looks like:
    Here is the formula used for the overall status:

    If([ID - Status]= "closed" and [ID - Status2]= "closed" and [Status] = "closed", "closed", "open")

    ------------------------------
    Adam Keever
    ------------------------------



  • 21.  RE: Combining multiple Pages to One for a Single Report

    Posted 12-06-2019 12:43
    Thanks Adam. I appreciate your time. Looks pretty straightforward. I'll keep you posted.

    ------------------------------
    Jaime Jaime
    ------------------------------