Discussions

Expand all | Collapse all

report with duplicate rows

  • 1.  report with duplicate rows

    Posted 05-24-2018 02:18
    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?


  • 2.  RE: report with duplicate rows

    Posted 05-24-2018 02:20
    I suggest using a summary report type and for the invoice total use Averages as opposed to totals.


  • 3.  RE: report with duplicate rows

    Posted 05-24-2018 03:16
    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?


  • 4.  RE: report with duplicate rows

    Posted 05-24-2018 10:37
    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.


  • 5.  RE: report with duplicate rows

    Posted 05-24-2018 19:58
    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?
               


  • 6.  RE: report with duplicate rows

    Posted 05-24-2018 20:01
    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.


  • 7.  RE: report with duplicate rows

    Posted 05-24-2018 20:14
    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.


  • 8.  RE: report with duplicate rows

    Posted 05-24-2018 21:00
    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?


  • 9.  RE: report with duplicate rows

    Posted 05-24-2018 21:37
    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?


  • 10.  RE: report with duplicate rows

    Posted 05-24-2018 21:49
    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.


  • 11.  RE: report with duplicate rows

    Posted 05-24-2018 21:54
    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.


  • 12.  RE: report with duplicate rows

    Posted 05-24-2018 21:57
    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?