Summarising filtered data in New Table

[jowan_qupty]

Hi,

I’m attempting to automate the process of an app I created on a Business Process Management software (KissFlow), summarise all the data in Airtable, and then create an invoice from this summarised data in Quickbooks Online once a month.

To clarify things, here is how the process works:

1. My client completes a form on Kissflow ALL GOOD
2. Data is transferred to Airtable (Each record belongs to 1 of 4 categories) ALL GOOD
3. I would like to create a process that filters by “category” AND “within last month” (there will be multiple records here), and then send this summarised data to an invoice in Quickbooks online. STUCK

I am not quite sure if what i’m trying to do is possible, but so far what I have been able to do is create a new table with the 4 categories as the Main field, and then roll up & link the items in order to get a summary. Problem is I am not able to filter by “within last month” because I have linked records.

Would greatly appreciate any help.
Thank you

[Andrew_Johnson1]

Not sure as to why you are wanting to create a new table to send the summarized data to Quickbooks
because you can use the Airtable API or some third party service like Zapier to automate the third step from within your existing tables.

[jowan_qupty]

Hi Andrew,

I apologise, I forgot to mention but I am using Zapier for the automation (don’t know how to use the API).

Initially I tried to create invoices/bills in Quickbooks with multiple line items (1 for each completed form in kissflow), but I didn’t know how to do that/don’t know if it’s possible with Zapier to create an invoice with multiple line items, so I figured that a single summarised line for each category would suffice (each category will have it’s own invoice)

For example, this is the new table i created with linked records from another table.

Main Field | Count of linked records | Currency | Formula | Linked records
Category 1: | QTY: 17 (count) | Unit Price: $100 | Total: $1,700 | 1,2,3,4,5,6,7…
Category 2: | QTY: 12 (count) | Unit Price: $100 | Total: $1,200 | 18,19,20,21…
Category 3: | QTY: 10 (count) | Unit Price: $200 | Total: $2,000 | 30,31,32,33…
Category 4: | QTY: 10 (count) | Unit Price: $300 | Total: $3,000 | 40,41,42,43,…

But now i’m stuck at filtering the new table by date “within last month” & “Category”.
I am positive that there is a much simpler way of doing this but i simply cannot figure it out.