Logical table setup for page designer - help!

[Edd_Turner]

I think I’ve bitten off more than I can chew.

I want my data in Airtable to flow into a snazzy PDF invoice.

My data is spread across several tables (!) and now I need help to unpick my knotted CRM to enable this task.

For example…

Every invoice goes to a company, right?

So, I put a lookup in my invoices table to reveal the company name. Then I lookup the company address, ZIP code and city. To make this a form-sensible address, I concatanate these together (with carriage returns!) to start.

Then comes a subject line, and the salutation. That needs a lookup to the name, gender and then a field with Dear Sir…, or Dear Mr and so on.

My customers get billed monthly, and I keep a running total of their orders. What I’d therefore like to do is print a list like that below:

`Socks     2   @$5 + tax`
`Shoes    4   @$4 + tax`
`Belts     5    @$6 + tax`
`Ties      5    @$7 + tax`

For that I need linked records, right? But these aren’t in the same table as my customers and address… currently I’m upto over 30 fields in this table and aren’t even halfway thgouth generating one simple invoice.

Basic question - how does one simply generate a table for invoices, given that most of the information I want to use in page designer is already spread across several tables, but I appear to only be able to use page designer on one?

Side question - if I’m going to have to put so much work into making page designer work for me, where does one learn these techniques (or which competing CRM might serve me better?).

Thanks!

[andywingrave]

I don’t think you’re going to be able to do what you’re wanting to do, without a little automation.

I’d recommend using Zapier/Integromat to use the initial lookup value to then search through the rest of the tables, and output you a nice neat table with the data you’re looking for.

[Cara_Van_Meter]

I’m pretty new to this, but I’ve been working on a similar goal and found these two videos to be helpful: https://www.youtube.com/watch?v=HyfE57VoLNY and https://www.youtube.com/watch?v=WrQImB96kfk – especially the second one, which goes into more detail about how you can pull multiple fields from a linked table into the Page Designer block rather than having to use lookup fields to duplicate that data in one giant table. Hope this helps!

[Edd_Turner]

I’m conflicted.

Thanks @Cara_Van_Meter for the link and @andywingrave for the integration ideas.

<rant>

On the one side I’m glad there’s a possible solution. On the other I’m frustrated that the solution requires extra effort and the use of another tool - or even just 20 billable minutes to invest in my tools and not my work… is my usage case really so bizzare that Airtable hand’t planned for it?

Most likely I built m tables wrong from the get-go. The thought of all this work makes other solutions (like HubSpot) all the more alluring… </rant>

[JonathanBowen]

Hi @Edd_Turner - This should be possible to do within Airtable, but needs a bit of table wrangling to get right. Here’s how I would set this up:

I’ve got 3 tables (to start with) - companies, invoices and invoice items. An invoice belongs to a company and an invoice item belongs to an invoice.

If we build the page designer pdf off the invoices table, then we can pull in the invoice items as required (usually I would do this in a table layout). But as you note above, not all of the data is on the invoices table directly, so we need to pull this into invoices to make it work.

My basic table layouts are:

and, building the pdf off the invoices table, I can get a basic invoice layout like this:

We want to add other info to this, of course, so we can add a company address in the companies table (I would go for a long text field, rather than concatenating multiple fields here):

Now have a lookup field on the invoices table which passes this address field through:

This can then be pulled into the page designer:

You probably want an invoice total too. Again we need this data on the invoices table, but it is going to come from the invoice items table, so a rollup on the invoice items will achieve this:

And on the page designer:

Of course, this is still pretty basic, but by following the same pattern, I think you should be able to get to what you need. Contacts/People can be added to the companies table. Or People can be its own table, linked to Companies. Quantities, unit prices and line total prices could be added to the invoice items table. Tax amounts can be added here too (again you might do a rollup of tax total onto the invoices table). You can also add whatever static text or graphics you need to the page designer too.

Hope this helps!

JB

[Edd_Turner]

Thanks @JonathanBowen - I’m gonna take a few undisturbed hour at the end of the week to take a look.

[Chuck_F]

I ended up handling this by building a jason object in a script block with all the info necessary for the invoice. I pass that via a POST request to a google apps script that uses the Airtable data to fill in a template doc and email that doc to the user. There’s a learning curve, but the effort is worth it compared to trying to force a result from the page designer.

[Edd_Turner]

Where would a beginner go about learning this in an step-by-step way?

Experience = 0. I mean, as Lisa Simpson says: „I know those words, but that sign makes no sense!“.

Edd Turner
Übersetzer, Dolmetscher, Sprachenberater und Dozent für Englisch

M: edw@rdturner.co.uk
T: +49 15202 021764

Edds Englisch
Priesterstr. 1
Hansestadt Stendal
39576
Deutschland

www.rdturner.co.uk

E-mails checked 2x/day - Thank you for your patience.
E-mails nur 2x im Tag abgerufen - danke für Ihre Geduld.