Discussions

The number of records to measure will be a filtered list of a type of record so the new record will have as a document number CURRENT YEAR + COUNT of records plus 1.  In otherwords, I'm adding a new invoice document and there are already 15 invoices for 2017 so the new document will be 2017-16. When I get to 2018, the first invoice entered will be 2018-01.  I would like to do this with formula language if possible.

Hi Scott. Here's how you can get that done. It's a convoluted multistep process, so follow along carefully.

Create Three Fields In Your Invoices Table
First, create a text field in your Invoices table. Let's call it [Invoice Year]. This field is going to hold the year based on the invoice date. If the invoice is dated 2/14/2018, it will have a value of 2018. We're going to use a Quick Base Action to write that value every time you create or update an invoice record.

Second, create a formula text field in your Invoices table called [Invoice Year Formula]. It's going to parse the year from the invoice date and will be needed for the Quick Base Action. The formula is this:

ToText(Year([Invoice Date]))

Before we create the Action, we need to create another field - a report link - that links only to the individual Invoice that gets created or updated. Create a report link called [This Invoice] and set it to show records that match Record ID# in this table (Invoices) to the values in Invoices:Record ID#. This report link for each record does nothing more than provide a link back to the record itself. We need it because Quick Base Actions rely on report links to know which records to act upon.

So we now have three fields: a text field [Invoice Year], a formula text field [Invoice Year Formula], and a report link [This Invoice]. Time to create an Action.

Create a Quick Base Action In Your Invoices Table
Still in the Invoices table, create a new Quick Base Action. Let's call it Update Invoice Year. Add a description and maybe the URL of this page so you or future developers will know where it came from. It should do this:

• When an Invoice is modified or added
• edit related records
• using report link [This Invoice]
• copy the value from [Invoice Year Formula] to [Invoice Year]

Why are we doing this? Because this solution depends on using a summary field (count) of the number of invoices created each year. We're going to have to create a Years table that will have the Invoices as child records. We want the relationship between those two tables to be created automatically based on the invoice date (no additional user input required). And Quick Base doesn't allow summary fields in relationships that use a formula field as the reference field. So we need to have the invoice year entered as a final value.

Create a Years Table and a Relationship With the Invoices Table
Now create a new table to hold a record for each year. There will be a 2017 record, a 2018 record, and so on. The purpose of this table will be to summarize the number of invoices dated in each year and pass that total back down to your invoices table for document numbering.

Create a single text field (beyond the built-in fields) called Year. In the Settings, set the Year field as the primary key for the Years table.

Now create a new 1:M relationship between Years and the Invoices table. Each Year may have many Invoices. This is important: when you create the relationship, don't use the Related Year field Quick Base will suggest by default. Instead, select the [Invoice Year] field to be the reference field. That's the one the Quick Base Action wrote to.

Finally, Create Four Fields: A Summary, a Formula, a Lookup, and a Snapshot
Now that the relationship is established, create a new summary field [# of Invoices] in the Years table that reports the number of Invoices related to that Year.

Then create a formula text field called [Next Invoice Number] as follows:

[Year] & "-" & ([# of Invoices] + 1)

This will always give us the very next invoice number for invoices dated that year. Now we need to get that down into the Invoices table as a static (snapshot) value. Go back into the relationship between Years and Invoices and create a new lookup field in the Invoices table corresponding to the [Next Invoice Number] field we just created: [Year - Next Invoice Number].

We're almost done! The last step is to create a snapshot of the [Year - Next Invoice Number] field every time an invoice is created. Go back to the Invoices table and create a new text field [Invoice Number]. Toward the bottom of the properties for that field is a setting called Snapshot: Get this field's value from a lookup field and don't allow the value to change. Check that checkbox and select [Next Invoice Number] as the lookup field.

Notes and Warnings
I haven't explained how to pad the invoice number by adding leading zeros. It's not hard to do, and I'm sure you can find an answer to that question here somewhere or call me at the number below.

This isn't a perfect solution and there are some things that could go wrong:

1. If you have a lot of concurrent users or other webhooks or Quick Base Actions configured, then you could exceed the built-in rate limits (10 per second per app, I believe). This could result in invoices that don't get invoice numbers.
2. If someone deletes invoices, this would reduce the count in the Years table and possibly result in duplicate invoice numbers.
3. If you forget to create a new Year record every year, invoice numbers won't get assigned for that year. You might want to create 50 years worth of records right off the bat.

Other Solutions
There are multiple other ways to accomplish this. A simpler way might be to create an invoice numbers table that would have a new record created for each invoice. I don't have time to detail this now but if you have trouble with the solution I've described or want to work through a simpler approach, you're welcome to call or e-mail me.

PHILLIP DENNIS, CMA
Principal | Watkyn LLC
(954) 900-6690 | www.watkyn.com