Louise Mowbray posted a simple version of this question here and didn't get an answer. That was 10 months ago. My immediate interest is in automating the generation of my invoices. But this question touches on what I think is the biggest (or one of the biggest) failings of SmartSuite and Airtable: They're terrible at generating attractive, printable reports.
I want to generate an invoice each month for a given account. Today is October 5, so I want the invoice to find uninvoiced activity from prior months, which if I invoice monthly means last month (September) but I don't always invoice monthly. I charge my clients by the hour, so I need the line items in the invoice to show the date, description of what I did, the hours * my hourly rate, and the charge for that line item. But of course I need to be able to calculate the total charged, and I'd like to have a cover page with the account's name, address, and with the totals from the line items.
In FileMaker, this is easy-peasy. In Airtable and SmartSuite what I've resigned myself to doing is using Google Sheets and moving the data by hand. That is, I copy the account info from the invoice record (which pulls it from the linked account record) and paste it on the cover page of the Sheets document. Then I copy the activity and paste it into the line items pages (2+) of the Sheets document. It works but only because while I have almost more work than I can handle, I never need to generate many invoices in any given month. If I had to generate more than a handful of invoices, this system would simply be impossible.
I've tried to use the Document Designer to do this but handling an indefinite number of variable-length line items does not seem to be something the Document Designer is good at. Anyway, I can't figure it out.
I'd really like to be able to generate attractive multi-page pdfs with my company logo, and I'd really like to be able to do it with an automation that does the job from start to finish. Any suggestions?