How can I get a list of all projects and their invoices - even projects that don't have invoices yet?

  • 0
  • 1
  • Question
  • Updated 2 years ago
  • Answered
This seems like a simple thing to do, but I'm struggling and would appreciate help.

I have two tables, Projects and Invoices.  Each project has many invoices.

I'm being asked for a report of all projects and invoices.  I can create a report from the Invoices table (see attached), but if a project doesn't have an invoice yet it won't appear on the invoices report.

If I create a report on the Projects table, I can use summary fields for invoice totals, but not a list of invoice numbers.  Which they want.

Help?  I can export to Excel and do this, but they want it twice a day.  I need a report I can just push to them.

Thanks in advance.
Photo of Jen

Jen

  • 82 Points 75 badge 2x thumb

Posted 2 years ago

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

QuickBaseCoach App Dev./Training, Champion

  • 65,096 Points 50k badge 2x thumb
It is possible to natively float up invoice numbers from a limited number of Invoices up to the project level.  Say the first 5 invoice #'s or  so would be reasonable and maybe up to 10 at the limit.  You could do that and have a "... and more suffix to that list of there were more.  Is that good enough?
Photo of Ⲇanom the ultimate (Dan Diebolt)

Ⲇanom the ultimate (Dan Diebolt), Champion

  • 30,004 Points 20k badge 2x thumb
QuickBase's query engine is not expressive enough to do this. If you want to stay native you have to use two reports:

(1) Report #1 - those Invoices with Projects grouped by Project

(2) Report #2 - those Projects without Invoices - create a summary field in the Projects table to count invoices and set a criteria #invoices=0

Using script you can get exactly one report but you have to write the code and use templating to render the HTML for the custom report. Assuming your "joined" Project and Invoice data was like this in raw form:

P1   I1
P1   I2
P2
P3   I3
P3   I4
P3   I5

You can generate a tabular looking report using script that will look like this:

P1   |    I1   |   I2   |
P2   |         |        |
P3   |   I3    |   I4   |   I5

In essence what the script does the following:

(1) use the QuickBase API to  make the two queries listed above and convert the XML response to JSON

(2) merge the two JSON objects into one

(3) use Underscore's _.groupBy() method to group on Project

(4) template out the result using Mustache or Underscore's _.template()

The template you use can be as simple or as complicated as you wish in both formatting and information. For example you could include hyperlinks to the Project or Invoice so that you could drill down to a report or record for greater detail.
Photo of Jen

Jen

  • 82 Points 75 badge 2x thumb
How? Invoice summary fields can feed into Projects table, but the options are total, average, min, max, or standard deviation.
Photo of Jen

Jen

  • 82 Points 75 badge 2x thumb
Can you point me in a direction to get started?  Maybe find more detailed directions for something similar?

I've done a form for people without QB accounts to enter data using API authenticate.  It's my only experience with it.
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 65,096 Points 50k badge 2x thumb
I am happy to explain how, if you are able to answer my question.  How many invoice numbers do you need floated up?
Photo of Jen

Jen

  • 82 Points 75 badge 2x thumb
It would potentially max at at 8 per project.
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 65,096 Points 50k badge 2x thumb
One project has many invoices.

summary field of min record ID, and call it [Record ID 1 of Invoices]

make a new reverse relationship where 1 invoice has many projects based on that field being the reference field on the right side of the relationship. (deleted the auto generated fields on the left for report link and Add Project.  Lookup the invoice # to the project. call it [Invoice #1] That's 1!

Go back to the regular relationship and lookup that [Record ID 1 of Invoices] down to invoices.

Duplicate the summary field and call it [Record ID 2 of Invoices].  Conveniently it will duplicate the relationship.  Edit the filter on the summary field so that the record ID must be > [Record ID 1 of Invoices].  ie this is the min record ID of invoice, not including the one we already got.

Get rid of those extra auto generate fields on the left side of the relationship.  Edit the relationship to lookup the invoice and call, it [Invoice #2].  That's 2 done!, six more to go

Just keep doing that until you get to the number you want.


Art any point you can summarize the number of invoices to know if there were say 10 invoices, 



Then lastly on the project level you will concatenate them all together.

List(", ", [Invoice 1], [invoice 2] ... [Invoice 8])

& if([# invoices] > 8, "... and more!")



 
Photo of Jen

Jen

  • 82 Points 75 badge 2x thumb
I think they'll like this.  I did something similar with our vendor contracts table, used effective date to pull pricing for the most recent agreement.
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 65,096 Points 50k badge 2x thumb
It will certainly work. let me know if you get stuck anywhere.  QuickBase engineers know that this is awkward for users to setup and one day they will create a "Text Summary" field type, but until then, we loop-de-loop.
Photo of Ⲇanom the ultimate (Dan Diebolt)

Ⲇanom the ultimate (Dan Diebolt), Champion

  • 30,004 Points 20k badge 2x thumb
maybe next week ...
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 65,096 Points 50k badge 2x thumb
I think that Jan is going native on this one as she can get there in 8 loops.
Photo of Ⲇanom the ultimate (Dan Diebolt)

Ⲇanom the ultimate (Dan Diebolt), Champion

  • 30,004 Points 20k badge 2x thumb
I hope Jan does not become dizzy with eight loops. I will look at this later as my first impression is that it is an "outer join" not an "inner join" issue because there may be no invoice for some projects.
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 65,096 Points 50k badge 2x thumb
She is just running a list of projects, so she will get all projects.
Photo of Jen

Jen

  • 82 Points 75 badge 2x thumb
Dizzy is right, with my other tables I now have 17 relationships.
Photo of Ⲇanom the ultimate (Dan Diebolt)

Ⲇanom the ultimate (Dan Diebolt), Champion

  • 30,004 Points 20k badge 2x thumb
Just to let you know I did create a demo for this problem and although I have most of the code written it will be a short while before I publish it (I am backed up with work). The Beta and Epsilon projects have no Invoices while the Alpha, Gamma and Delta Projects have Invoices.

Projects and Invoices
https://haversineconsulting.quickbase.com/db/bmf5dp7cx

This solution does not introduce any new fields or relationships. More to come ...
Photo of Jen

Jen

  • 82 Points 75 badge 2x thumb
Looks like exactly what I'm working with.