My Reporting challenge in Quickbase: The details follow.

  • 0
  • 1
  • Question
  • Updated 5 years ago
  • Answered

1. I have a many to many relationship between Projects and Programs: Tables are Project, Program and Project to Program Join

2. I have a one to many relationship between Projects and Project Statuses: Tables are Project and Project Status

3. I have a one to many relationship between Programs and Program Statuses: Tables are Program and Program Status.

Requirement: View a Program with associated projects, program status records and related project status records.

Apparently I cant do this natively in Quickbase. How would I generate this report?

thanks,

Krishna

Photo of Krishna

Krishna

  • 10 Points

Posted 5 years ago

  • 0
  • 1
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 67,348 Points 50k badge 2x thumb
Is there a practical limit to the number of Programs which could be on that Join table for a given Project?
Photo of Krishna

Krishna

  • 10 Points
No, why do you ask?
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 67,348 Points 50k badge 2x thumb
Well, say project 1 is involved on Programs A, B, C.  So you want the Project and the Project Status records to know that and print on a report as a simple field "A, B, C"  (e.g. Digital, Adobe, Shmobe) .  Then Project 2 is involved with Programs B, C, D.  So you need those names to be on the Project record.  Then maybe Project 3 is involved with programs ABCDEFGHIJKLMNOPQRSTUVWXYZ - ie 26 Programs. So you want all 26 programs to print in a field on a report.  Is that what you are asking for?  I have native a solution which can do say the first 10 Programs or maybe the first 20, but you need to do what is called a reverse relationship for each.  So there needs to be a limit for this solution as to how many it can handle before it just says "... and more"
Photo of Krishna

Krishna

  • 10 Points
I dont see the number of programs a project is associated with going beyond 10 or 20.
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 67,348 Points 50k badge 2x thumb
OK, so here we go.


Let's just do say the first thee Programs and you will then be able to continue to do another 7 or 13 more.

We will look at the relationship between the Join Table and the Projects table.

1. Make a summary field of the Minimum of the field [Related Program] and call it [Record ID# of Program 1]

2. Look that up down to the Join table and call it [Record ID# of Program 1]

3. Make a summary field of the Minimum of the field [Related Program] subject to the filter that the [related program] > [Record ID# of Program 1] and call it [Record ID# of Program 2 ] (you may find it faster to Copy that first field)


4. Look that up down to the Join table and call it [Record ID# of Program 2]

5. Make a summary field of the Minimum of the field [Related Program] subject to the filter that the [related program] > [Record ID# of Program 2] and call it [Record ID# of Program 3 ] (you may find it faster to Copy that first field)


( and you will continue that patterns as far as you like).



So now we are step closer. The Project records now know the record ID# of the first three programs.

Now we need to set up a relationship to pull in the name of the first program as us humans are not so good with a Record ID# as a descriptive identifier of a program.

Make a relationship between Programs and Projects where the reference field on the right side is [Record ID# of program #1], and then do a lookup of the Program name, and call it [Program Name 1]

Repeat the above to get down the [Program Name 2] and [Program Name 3]



Lastly we will concatenate them altogether calling the field [All programs].

List(", ",

[Program name 1],

[Program name 2],

[Program name 3])



If you decide that you also want these on your Project Status records, then that's just a lookup down from Projects to Project Status of that [All programs] field.






Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 67,348 Points 50k badge 2x thumb
Oh, one more tip.  If you make the relationship to do that lookup of the Program name to get [Program Name 1], then when you do this
Make a summary field of the Minimum of the field [Related Program]  subject to the filter that the [related program] > [Record ID# of Program 1] and call it [Record ID# of Program 2 ]  (you may find it faster to Copy that first field)

 and if you do it by a Copy, it will also copy that Relationship for you to get the Program name - saving some time.
Photo of Krishna

Krishna

  • 10 Points
Genius! Thank you.
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 67,348 Points 50k badge 2x thumb
So did it work for you?  You built it?
Photo of Krishna

Krishna

  • 10 Points
just built it, checking it as we chat
Photo of Krishna

Krishna

  • 10 Points
On a cursory test, it works. Thank you sir!
Photo of Krishna

Krishna

  • 10 Points
Reporting Requirement:

I have a one to many between Programs and Program Statuses;
I have a many to many relationship between Projects and Dependencies, implemented thru a Join table called Project Dependencies;
I have a one to many between Programs and Projects.

How would I include related project dependencies on a Program status report?