The question is not if you can create a partly automated invoicing logic with Coda. It is mainly why you should want to do this and if so, how.
If your ambition is to link incoming and outgoing payments to your Coda account, you better look for specialised software. The same if you want your bookkeeping done in Coda and by this I mean applying all kinds of fiscal rules.
However if you want to create invoices based on your products & services you already manage in Coda and if you want to follow up on the payments, Coda is a good choice to automate these basics. Coda is practical because you can connect data you already manage and you can enrich with payment related information. I’ll focus on a few aspects to get the job done:
- Main Table
In this article Maria explains how to make use of a controller. Below a screenshot to show how easy it should be.
If you are new to Coda it might appear to you that what is presented as self evident is not so easy at all. If you know Coda a bit you might notice that the yellow color mentioning ‘Bill To Address’ links to something. This something is the name of the controller and a controller is a
Filter() living on your canvas, thus the part you write your texts outside a table. The Controller does something any filter does. It puts all the information from one column in a list whereby each item is linked to a row. If you have 10 rows, you have 10 items in the list. If one of the rows is empty, one of the items is empty. An item can be a number, a text, anything that lives in a cell. Once you create a controller you get a list of items you can select from. The trick is to use a smart value you select in your controller (like the address in the example of Maria) as a filter on other data on your canvas or in a table, most likely on the same page.
In short, once you apply a controller you can have related data on your page because a controller is a filter.
In my case I have a table with clients (companies), one with contacts (multiple per company) , one with contact details (like email, phone etc) and last but not least one with billable products and services. So 4 related tables I need to bring together on the invoicing page.
The main question is how to select the controller wisely, thus what piece of information will be the trigger to show other relevant data? In my first set-up I used the company I had to invoice and pulled in related data. However I noticed that I have people who represent several companies and I do business with people first and second with the companies they represent. So I changed it into contacts and as you can imagine, the issue remains, now I have several companies linked to one contact. I needed a more sophisticated approach.
Happily the solution is rather easy. We take the table that contains the work done as a starting point. We add the information via lookups to the view of that table and we use the name of our contact as controller, thus as a filter. On the invoicing page I select after the ‘dear ….’ via the controller the contact and everything fills out automatically.
The Main Table as reference for the controller
As indicated, we use the table work done, create a view and add the information we need for the invoice. In our case the contact person, the related company and the invoicing company (if you have more than one).
This also implies we start filling out our tables in a specific order:
- We create the contacts per client first. Their names and salutation. We often cannot yet connect them to a company. For this we have to come back.
- we create the companies and link them to contact. Since Coda does not automatically work bi-directionally, we go back and link the contact to the now existing company.
- We continue with adding contact details to the contact person like email and phone number;
- The last part is filling out the table billable work. We select a contact, a company, the amount, the VAT, the description and the invoicing entity.
Now we have all the info we need to fill out the invoicing page based on the contact. We have a filter in the view of the table work that does two things. It filters out the contact we selected via the controller and takes the Max() number of the invoice numbers related to this contact, that should be the latest invoice.
Once we are done, we create a PDF of the page. I have the PDF linked to a view of the billable work. Once a client has paid I delete the row and add the information to another table. Paid is paid and then only for bookkeeping and some basic stats I like to keep the data. Stats like the average time between the invoicing date and the payment date. The follow-up table can contain automations that check if a payment is due and propose a follow-up. These are the typical features of any invoicing program. I did not set yet a page that will be emailed once someone is late. I’d like to do this manually.
Taken all together it is a bit of work and having used some invoicing programs really helped me. In my concept I did not push products from a database in the invoice, which is by the way not so difficult. This would require an extra table in which we select products, the quantity and maybe an attributed promotion over individual products or over the sum (taken with or without VAT).
Extra — VAT
I have clients in Belgium and outside Belgium. That is important for the VAT administration. I need to tell my clients in their language what the reason is there is no VAT on their invoice. A reason is a text and thus I created a formula that does not add VAT in case I have text.
If(thisRow.[VAT Type].IsNumber(),product(thisRow.Amount,thisRow.[VAT Type]),"0")
This logic permits me to write in Dutch, French and English something and this will result in zero (“0”). If it is a number, then we apply the
product() function. It is a simple formula that makes things easier in a multilingual environment.
Extra — Salutation
Writing a salutation goes often as follows : Dear Mr. LeJeune or Dear Mr. O. LeJeune. The latter is a combination of the lastname and the first letter of the first name. Both have a Mr. or Ms. for a starter and the combi like Mr./Ms. for those who feel this is the way to go.
Concatenate(thisRow.Salutation, " ",Slice(thisRow.Firstname,1,1),"."," ",thisRow.Lastname)
The result is something like:
Extra — Sending a SMS
Sending a SMS became easier for me as of the moment I could type the message via my laptop. An SMS is sent via your mobile or via a solution connected to your mobile (like the web version of whatsapp).
I figured out how to set up something that permits me to send text messages fast. I am not going into detail over here, but this is how it looks like.
The starting point is that you need the mobile number. I also select the country code based on the country. There is no need for trimming the white spaces away, this is automatically happening when you inject the number into the application of your preference.
The logic above enabled me to create a text message. Next I go to the Coda app on my smartphone, I look for the page I need, I click on the URL and my text app opens and I can send my message. I guess you can also send direct messages via your computer if you have the right software.
I hope you enjoyed this article. If you have additional questions, feel free to reach out. Though this article is for free, my work (including advice) won’t be.
My name is Christiaan Huizer and I am the owner of Huizer Automation. A company specialised in serving SME in harvesting data and keeping it aligned. I mainly rely on AirTable, Coda, Mailjet, Zapier & Paperform to get the job done.