report with duplicate rows

  • 0
  • 1
  • Question
  • Updated 7 months ago
  • In Progress
I have Invoice headers and Invoice lines coming in from QuickBooks.  I am reporting off of Invoice lines, and using some related tables to match the invoice lines with the team members whose time is being invoiced on them.

There can be more than one line per invoice, and more than one team member per invoice (although there is always only one team member per invoice line).

What I want is a report that shows, for all open invoices, ONE report line for each combination of Client, Invoice number, and Team member.    I don't need all the line items that might relate to all three of these things, because they are duplicates (for my purposes, they only differ by line number).

The end goal is to show all the open invoices (by client) per team member - and their dollar total, by both client and team member.

If I use a group on a repot that does include all the invoice lines (not de-duped), it wants to add all the dollar totals together at the group level, which is incorrect because each line contains the dollar total for the entire invoice, not the line.

What to do?
Photo of Charlotte

Charlotte

  • 450 Points 250 badge 2x thumb

Posted 7 months ago

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

QuickBaseCoach App Dev./Training, Champion

  • 52,928 Points 50k badge 2x thumb
I suggest using a summary report type and for the invoice total use Averages as opposed to totals.
Photo of Charlotte

Charlotte

  • 450 Points 250 badge 2x thumb
thanks, that works!  But now..... I'd like subtotals.... so that people can see the total of those averages, by client, and then by all the clients for a given team member.  So it's like I want to take the output of the report you just had me do, and use that as a table to get subtotals.  Possible?
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 52,928 Points 50k badge 2x thumb
You could create a new table and use the More Button on the summary report to copy the records into that table. But this would need to be done from time to time to update that summary table after clearing out the existing data.
Photo of Charlotte

Charlotte

  • 450 Points 250 badge 2x thumb
yes, that works.  A good technique to know.

But it still doesn't really do what I want.
To back up -
I'm trying to replace a report that we currently run out of another system then slice apart and send out by email to team members - so that everyone sees only the data applicable to them.  Note that not all recipients are QuickBase users.

Currently this is done by manipulating the report output in Excel (with formulas and macros) so that every line of the report (even blanks) has an email address, then using that as input to a mail merge Add-in for Word that actually does the mailing. 

This is tedious, and I'd like to automate it in QuickBase if possible since QuickBase already knows which line items belong to which email address.

If I summarize the data (1st report), then paste into a new table and group/sum (2nd report) when I try to paste the resulting report into Excel, I only get the detail data - I need to be able to take the summary lines with totals out of QuickBase so I can manipulate the whole thing in Excel.
Is there another strategy that would work better?
           
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 52,928 Points 50k badge 2x thumb
Are you using a Summary report type ie not a Table report with summary totals enabled but a different report type. Summary report type


There are probably ways to automate that new table in terms of its maintenance but let’s get this low tech method working first.
(Edited)
Photo of Charlotte

Charlotte

  • 450 Points 250 badge 2x thumb
The first report is type Summary (that generates records for the helper table).
The second report is a table report with summary totals, because I need actual lines that say "Total", not just a rollup as the Summary report does.  But I can't get those lines out, into Excel.
Of course I can use subtotals in Excel to do it, but I'm trying to minimize the Excel steps, especially if I have to do more QuickBase steps.
(Edited)
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 52,928 Points 50k badge 2x thumb
I guess what I'm not understanding is what line aggregation you need exported to excel.  

For example if you just export the first Summary Report directly to excel instead of a helper table, what is is missing that you need?
Photo of Charlotte

Charlotte

  • 450 Points 250 badge 2x thumb
The first report (Summary) has all the detail lines for the final report - looks like:
Team-Member | Email | Customer | Invoice | Invoice $
The second report (Table), using the output of report 1 in a new (helper) table, is sorted by Team-Member by Customer and adds subtotal lines  that show

total Invoice $ per Team-Membertotal Invoice $ per Customer
So now, if I'm looking on the screen, I have all the subtotals I need, and the correct email addresses on the correct detail lines.
[Eventually I have to add the email addresses to the subtotal lines as well, because that's how the Mail Merge program knows how to bundle all the applicable lines together for one email address, but that's another step.]
The immediate problem I'm having is that if I run the second report and then "save as spreadsheet" what is in the spreadsheet does not include the subtotal lines - although they appear on the screen in QuickBase.
I could do the subtotals in Excel with the info I have; but there are a lot more Excel steps and I'm trying to minimize them.
Make sense?
(Edited)
Photo of Charlotte

Charlotte

  • 450 Points 250 badge 2x thumb
Perhaps the right question really is -
I have report I need to split apart and mail to different people - some not QuickBase users. Can I do that?
Or maybe -
from a single table, how can I create a series of reports that run one after other, generating reports which each filter based on the different values of a field? ie, Jane's report with only "her" records and summaries goes in one email sent to me (if not Jane), Tom's report goes to me in a separate email (or to him), etc.
(Edited)
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 52,928 Points 50k badge 2x thumb
If you have a report where the dynamic filter is persin you should be able to email the filtered report using the email button at the top.
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 52,928 Points 50k badge 2x thumb
Subtotal lines on a table report do not export.

But lines in a summary report do export

Can you make a summary report type to get what you need?