Hi Everybody, I’m new to this community and Airtable. So far, I’m loving the product. Just figuring out how to do things in Airtable. I’m not a computer programmer, but I’ve managed to hack together some fairly complex databases using VBA in MS Access over the years. Trying to get our company’s processes automated and cloud based after getting multiple software companies to unsuccessfully bid to make us custom software (unacceptable in both cost and development time).
First challenge, I wanted to make an automatic invoice generator. Pretty simple idea right? Just about every company has some sort of auto-invoice with basic info such as customer, PO#, and an itemized bill of materials. Since I’m used to Access, I thought it would be as simple as making a form and subform, then spit it out in a custom report. Well after twiddling with Airtable and combing the Airtable community posts for a couple weeks, I realized that it wasn’t going to be as straightforward as I naively thought.
There are a few topics touched upon from the “Ask the community” section, and I didn’t know exactly where to post this, so I’m just going to post it here, and hopefully those that have questions in other posts will find it here.
Some features that I wanted in the base:
- Order form with order details sub-form, with automatic invoice generator in page designer.
- “Smart” auto index number generator with no gaps (no ghost records resulting in gaps in autoindex number field when records are accidentally added and deleted.
- Automatic itemized numbering of BOM.
- Multi-record calculations to reference previous records, a la “like Excel” :winking_face: .
I was stumped for quite a while, then found W_Vann_Hall’s “Multi-record calculations” post, and used that as a basis to make my invoice generator. Many kudos @W_Vann_Hall for the brilliant methods you utilized in your demo base and sharing it.
This demo base has a gap-less “smart” auto-number field, which then acts as the index for the multi-record calculation to check the previous 10 items to make an automatic item # for the BOM, which is then all automatically populated in the “Invoice” page designer block. There are likely bugs and limitations in this demo base, feel free to comment or suggest any improvements. It’s not exactly fully automatic, as you still must manually link every “Order” and “Order Details” record to the “Calc” link, but its close enough for my use. Note, that the “IndexNoGaps” calculates the next number in the same order that you link the records, and also renumbers the records to fill in the gaps if you delete a record. The BOM “Item” number checks the previous 10 index numbers, so you can only have a maximum of 11 items on the BOM, but this could easily be expanded by making more PREV# calcs and adding to the “Item” nested IF formula. Hopefully this helps anybody out there for what I think is a fairly common task for a business.
Orders and Invoice Template:
<iframe class="airtable-embed" src="https://airtable.com/embed/shrwogPO0juk2oyc6?backgroundColor=teal" frameborder="0" onmousewheel="" width="100%" height="533" style="background: transparent; border: 1px solid #ccc;"></iframe>
