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 Ⲇanom the ultimate (Dan Diebolt)

Ⲇanom the ultimate (Dan Diebolt), Champion

  • 30,074 Points 20k badge 2x thumb
I think this E/R Diagram documents your table relationships:

http://imgur.com/zhYsfQg


I assume you want to generate a custom report for a particular Program identified by a given [Record ID#] - not a comprehensive report for all Programs. You have to use script and the API to do this. The general approach will be to perform a series of queries using the method API_DoQuery and build up a a data object consisting of properties to hold field values and child properties to hold an array of related field values. After the data object is constructed it is merged into a template using Mustache to produce rendered HTML which you insert into your page. Simple examples are shown on this page:

http://coenraets.org/blog/2011/12/tutorial-html-templates-with-mustache-js/


Knowing the [Record ID#] of the specific Program you query the Program Statuses table where the [Related Program] is equal to the given [Record ID#]. Next you query the Project Program Join table for the [Related Project] field selecting those records where [Related Program] field is equal to the given [Record ID#]. Finally, you query the Project Status table and include parent records from the Projects table where the [Record ID#] of the Projects table is equal to the [Related Project] values obtained from the previous step. If you accumulate your all your fields in an object you can then template out the report using Mustache as shown in the URL listed above. It sounds complicated but it isn't.
Photo of Krishna

Krishna

  • 10 Points
Thank you, I appreciate the quick response! What is the approach for a comprehensive report for all programs?
Photo of Ⲇanom the ultimate (Dan Diebolt)

Ⲇanom the ultimate (Dan Diebolt), Champion

  • 30,074 Points 20k badge 2x thumb
You would use the same approach but you data structure will just have more complexity. Look at Sample 11 on that URL - you are beyond the complexity of that example but the same method would be used. You would simply have a more complicated and longer data structure but the approach would be exactly the same.
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 67,278 Points 50k badge 2x thumb
If I understand the question, this can be done natively in that you have asked how to view "a program". ie anything you need can just be sitting on the Program record.

I think that the key point here is the use of report link fields. Most users see them as only existing as part of a relationship, but that is just because they are automatically created whenever you make a relationship. But they can be created without a relationship.

So, you are sitting on a Program record.

You have a relationship to Program Statuses, so use that Report Link field to have an embedded report of program Statuses on the record, Create a report off the Program Status table for that Purpose and have your Form use it.


You have a relationship to "Program Join", so you have a report link field on that relationship. Use that on your form and create a report off that Program Join table to use on your embedded report Link field which lists the Programs.

Now, you also want to have an embedded report of Project Statuses. Here is where it gets tricky as how do you know which Projects are involved in the Program the user is viewing?

I suggest that you make a formula URL field to either edit or add a record to a new table called Users. The Key field of the table will be a User field. The button will check to see if the user has been created n that table, and if not it will create it and flag a field with the active Program for that user. If the user exists, it will just edit the record to not the Active program for that user.

There will be a relationship where 1 user has many Programs and the reference field on the right side of the relationship will be a formula field called [Current user] on the Programs table with a formula of User() - ie the Current User.


Here is an example from one of my apps.


var text CreateUser =
URLRoot() & "db/" & [_DBID_USERS]
& "?act=api_addrecord&_fid_6=" & ToText([Current User]);

var text UpdateUserRecord =
URLRoot() & "db/" & [_DBID_USERS]
& "?act=api_editrecord&key=" & ToText([Current User])
& "&_fid_10=" & [Record ID#];

var text DisplayProgramRecord =
urlroot() & "db/" & dbid() & "?a=dr&rid=" & [record id#];

If([User Record record exists for current user],
$UpdateUserRecord & "&rdr='" & urlencode($DisplayProgramRecord),

$CreateUser
& "&rdr=" & URLEncode($UpdateUserRecord)
& Urlencode("&rdr=" & URLEncode($DisplayProgramRecord)
))

The field If([User Record exists for current user] was a lookup of a checkbox field with a formula of true from the user record back down to in your case the program record. Note that fid 10 is being populated with the record ID of the Active program for that user.

OK, so the user has pushed the button, and they now have a User record populated with the [Record ID#] of the Active program. Do a lookup of that [Record ID#] back own to the Programs table and have a field which calculates to 1 if the program is active. ie if([Record ID#] of program record = [record ID# of active program for that user],1,0). Lets call this checkbox field [Program is Active = 1] (ie its a 1 if the program is active for the current user.

Now, do a lookup of that [Program is Active=1] down to the Project / Program Join table. Now you have managed to "light up" the Project /Program Join records which are active because they are all now flagged with a 1.

Next do a Summary field on the relationship between projects and Project/program Join Programs of the maximum of the [Program is Active=1] field.

Great, now we have managed to "light up" all the Projects which are involved in Programs for the Active Program for the current user. Now we do a lookup down to the Project Statuses of that [Program is active = 1] field.

Great, now we have managed to 'light up" the Project Status for the Projects which are related to programs which are active for the Current user.

So, now we make a report of Project Statues, (I suggest grouped by Project) and filtered for just the one which are "lit up" ie where [Program is Active=1] = 1. Lets call the report "Project Status grouped by Project for the Active Program".

Lastly, we need to coax this report onto the program record. Make a formula-numeric field on the Program record with a formula value of 1. There is no magic to the 1, it could really be anything. call it [Link to Project Status records]. Do the same way over on the Project Status records. Call it [Link to Programs records] Now make a report link field on the Programs record (not as part of any relationship, just a new field). Make the fields they have in common, those [link] fields be the connector. Put that field on the Program form and use your report of "Project Status grouped by Project for the Active Program" as the report which the Report Link field should use.

:)
Photo of Ⲇanom the ultimate (Dan Diebolt)

Ⲇanom the ultimate (Dan Diebolt), Champion

  • 30,074 Points 20k badge 2x thumb
I don't have a canned example that matches your table schema but this approach is perfectly general. To give you an idea of how this works for more complex examples here is an indented org chart

http://jsfiddle.net/evildonald/Hy6yn/embedded/html,js,result/

On the HTML tab you will see two Mustache template identified by id=test6 and id=test6-person. These templates contains token delimited by {{ and }} and indicate where data should be substituted. On the JavaScript tab you will see the data variable which describes the name, title of each employee and who they manage (if anyone). The report is shown on the Results tab as an indented org chart. All you have to do is generate the data object using a series of API_DoQuery calls and supply an appropriate template.
Photo of Krishna

Krishna

  • 10 Points
Thank you! Need to clarify your instruction: "Now, do a lookup of that [Program is Active=1] down to the Project / Program Join table. Now you have managed to "light up" the Project /Program Join records which are active because they are all now flagged with a 1." How do i flag my project/program join records with a 1 through a lookup of Program is Active = 1?
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 67,278 Points 50k badge 2x thumb
Once you do a lookup of that field down to the program Projects join table it will be 'lit up" (flagged) because that field will have a value of 1 in it.  I use the word "lit up" just to mean that its flagged with a "1".  I visually / mentally see this as if you turn on a light switch on that User record and the light flows back trough the whole set of relationships and out the far end, finally "lighting up" the Project Status records for the Active (flagged)  Projects related to the Active Program (lit up) which has its record ID matching the [record ID of the Active or Focus program for that user.

The elegant thing about this solution is that you can multiple simultaneous users and User A called Rudy Red might light up one program with their Red light and user 2 called Bonnie Blue lights of their own program with their Blue light and out the other end comes the red or blue light on certain Project Status records based on who the user is.
Photo of Krishna

Krishna

  • 10 Points
I am still trying to wrap my head around this solution and thanks for your patience: Please clarify:
"The field If([User Record exists for current user] was a lookup of a checkbox field with a formula of true from the user record back down to in your case the program record. Note that fid 10 is being populated with the record ID of the Active program for that user."  Is this check box field in the user table? What is the condition for checking this check box?
Photo of Krishna

Krishna

  • 10 Points
"Now, do a lookup of that [Program is Active=1] down to the Project / Program Join table. Now you have managed to "light up" the Project /Program Join records which are active because they are all now flagged with a 1." How does a lookup of the Program is active = 1 flag the child project/program join records?
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 67,278 Points 50k badge 2x thumb
The formula for that checkbox field on the user table for the field [User Record exists for current user] is just a formula with the formula of true.  I'm just trying to detect if the User record already exists on the user table.  if its there, then the lookup field of a formula with formula of true will be true.  If the User record does not already exists, then the lookup will be unchecked so i know i need to create a new record. note that the first time the user clicks that URL button it will create a User record for them.  After that, it will just update that existing record with the record ID# of the Program they were sitting on when they created.

However, I see in my post I was not clear where this URL button should be created. It should go on the Program Record and have a label like "Make this program active".

As for your second question just above, I believe I answered that.  The lookup field form the program down to the join table will end up flagging all records for the Active or "Focus" program which the user just clicked on to make it active.  maybe "Focus" is a better word.
Photo of Krishna

Krishna

  • 10 Points
Hello, I get the overall solution but struggling with the formula syntax, can you help with this?  "The formula for that checkbox field on the user table for the field [User Record exists for current user] is just a formula with the formula of true." What is a formula with the formula of true?
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 67,278 Points 50k badge 2x thumb
no problem

just type

true

in the formula box area.  no quotes, just    

true

That field will then always calculate to be true.
Photo of Krishna

Krishna

  • 10 Points
Thanks! I get this error message: "Expected a valid expression after the "&" for the following code: &   '&rdr=" & URLEncode($UpdateUserRecord) & URLEncode("&rdr=" & URLEncode($DisplayProgramRecord))
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 67,278 Points 50k badge 2x thumb
I am seeing a single quote 'as the very first character in your post.  That should be a double quote "

If that does not fix the problem, please post the entire formula.
Photo of Krishna

Krishna

  • 10 Points
i fixed the single quote but there is a problem with mismatched set of parentheses or quotes. Here is the entire formula:

var text CreateUser =
URLRoot() & "db/" & [_DBID_USERS]
& "?act=api_addrecord&_fid_6=" & ToText([Current User]);

var text UpdateUserRecord =
URLRoot() & "db/" & [_DBID_USERS]
& "?act=api_editrecord&key=" & ToText([Current User])
& "&_fid_10=" & [Record ID#];

var text DisplayProgramRecord =
urlroot() & "db/" & dbid() & "?a=dr&rid=" & [record id#];

If([User Record record exists for current user],
$UpdateUserRecord & "&rdr='" & urlencode($DisplayProgramRecord),

$CreateUser
& "&rdr=" & URLEncode($UpdateUserRecord)
& Urlencode("&rdr=" & URLEncode($DisplayProgramRecord)
))
Photo of Krishna

Krishna

  • 10 Points
Hello Mark,
can we talk? I would like to explore paying you to get fix to work on my app.
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 67,278 Points 50k badge 2x thumb
sure - you can contact me via the information in my profile. please look for my gmail email address on my Linkedin profile.
Photo of Krishna

Krishna

  • 10 Points
Hello Mark, How would I embed the project status table on a report for Programs as we did on the program main form?
Photo of Krishna

Krishna

  • 10 Points
I am unable to use the report link field of project statuses on a program report, like we did on the program main form. It shows up as a link and not as a table since I cant specify a report to be tied to this report link field. what am I doing wrong?
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 67,278 Points 50k badge 2x thumb
Would it work OK if we did this.  Give a button  on the Report to Show Project Statuses? This would be a different button than the one we did by phone yesterday. Under the covers the button push would make that program "Active", then pop up a new window of a mini Program form that just had en embedded report of the Project Statuses.
Photo of Krishna

Krishna

  • 10 Points
ok, but is there a non button way to accomplish this?
Photo of Krishna

Krishna

  • 10 Points
This should work if it is strictly an online report but some folks want the report in a document format. I wonder if there is a way? Also How would I look up program name on the project status record side?
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 67,278 Points 50k badge 2x thumb
The user will need to push a button to indicate which Program they are interested in seeing, right?  They are looking at say a report of 100 programs, so they need to indicate which one they want more details on, including the Project Statuses.  

When you say that some folks want the report in a document format, I'm not sure what you mean.  The button can pop up a screen of a form with anything you want on it and they can read it or print it.
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 67,278 Points 50k badge 2x thumb
You also asked "How would I look up program name on the project status record side?".  I'm not understanding that question yet either.  Where exactly do you want this "Active" Program name to be.  On a record or on a report? Which record or which report.
Photo of Krishna

Krishna

  • 10 Points
I would like this on the project status record so i can group my project statuses by related programs on the Project status report