Generate records from summary report?

  • 0
  • 1
  • Question
  • Updated 3 years ago
  • Answered
I'm trying to figure out if I can generate records in a table base off of a summary report in another table.

I have Projects and Assignment table (P < A). Some assignments are billable and some are not. Some of my clients want a monthly invoice with summary of each of the assignments on the invoice.

For example, we do transcription work where the client can request 5-day, 3-day or 1-day turnaround (each billed at a different rate). Over the course of a month, the client sends us 100 projects. For their monthly invoice, they don't want to see a line item for each of the 100 billable assignment, they only want to see summaries of the 3 different assignment types.
5-day, 50 Assignments3-day, 35 Assignments1-day 15 Assignments

Now I know I can create summary fields for each of those Assignment types but in actuality, I have over 500 Assignment types. I don't want to create summary fields for each of them because at the most, an invoice will probably have 1-5 of those Assignment types and the Assignment types are constantly being added to / changed.

Now I can create a summary report on the Assignment level to show me that 50-35-15 breakdown but I'm trying to figure out how I can get that information to the Invoice table. What I was thinking of doing was to use a Table-to-Table Import from the summary report to create records in the Invoice table but I'm not even sure if that's possible. 

This is a very basic breakdown of my situation but it should give a decent idea of what I'm trying to do (I hope).
Photo of Carlos

Carlos

  • 100 Points 100 badge 2x thumb

Posted 3 years ago

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

QuickBaseCoach App Dev./Training, Champion

  • 65,542 Points 50k badge 2x thumb
I will suggest this method first if you want to start with a low-tech solution.

Make a summary report on say the client record of uninvoiced assignments.

There will be a More button on that report to export to another table. Include the field for [Related Client] in the summary.

To make it easy for the copying line up the fields correctly, ensure that you have a set of target fields which are letter perfect the same as the export. You might even have duplicate field where your import target fields have one name and then the properly named fields would just be a formula field equal to the import fields.
Photo of Carlos

Carlos

  • 100 Points 100 badge 2x thumb
Thanks for the reply Mark.  I'm not seeing an option for copy/export records from the summary report.  The only options I'm seeing is: New subscriptions, Print, Save as spreadsheet.  I'm in an Admin role so I don't think that's the issue.  Just not sure if I'm missing something here or not.
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 65,542 Points 50k badge 2x thumb
Can you confirm that you have a Summary report, and also I think that your report cannot have any column grouping being used.  Just row grouping.  Let me know those two things.
Photo of Carlos

Carlos

  • 100 Points 100 badge 2x thumb
The column grouping was the issue, that worked now, so thank you.

So that is the low-tech solution which works but I appear to be limited in the amount of fields that I can copy over.  I was possibly thinking about doing this through an API call (AddRecord) but I don't know if I can reference that summary report.  Any thoughts on that?
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 65,542 Points 50k badge 2x thumb
You should be able to copy as many fields across as the Summary report will allow Group By's, so that may be about 8 I recall.

There is no simple API to copy across summary results.  If you have budget for development work contact me off line and we can discuss options to automate this.
Photo of Carlos

Carlos

  • 100 Points 100 badge 2x thumb
As always Mark, thank you for your time.  Let me see what i can do with the summary report.  If I'm not liking what I see, then I will be in touch.