How to Coda with VAT — Part V
How to define your promotion per product
Coding Coda is great, I love it, but in the end the design of the doc makes the real difference. Does the user feel what to do? In this blog we explore a method to apply promotions to individual products in user friendly way. We assume you have a table that brings in products with attributes like price, VAT, name etc.
When you apply a promotion on a product, the price changes and if you change your mind, you should have the option to apply a different or undo the promotion and thus change the price again of the selected product and not of the others. This is only possible if you print the values as text in your column. We use a button with the
ModifyRow() function to adapt prices . The formula lives in the button, not in the price column. This is the concept.
Is is tricky. Not because of the complexity of the the function
ModifyRows(). It is due to the order in which you process your data. I had to rework a part of my code to ensure a proper outcome. Part of the work was reordering code , second less relying on the outcomes of other calculations in other columns and last but not least add some locks.
How it works
The first part of the logic is maybe disappointingly simple. We add a button behind the promotions that presses the button on each row in the table.This button has two functions. First we bring in the standard values (thus the base price) for the unchecked rows and secondly we overwrite only the checked rows with promotional values.
We copied the formulas from the columns to the buttons, the columns became formula free and thus ready to receive the values printed by the button. We added a filter
thisRow.Promo.Not() to ensure that only not promoted products get these values.
The elegance I see is two fold:
IfBlank()in the first part ensures a product name, but avoids that it is overwritten.
- The red underlined part. Apparently Coda dislikes the notation to filter on checked and unchecked.
Without too much hassle we have an elegant solution:
The above was in practical terms the easiest part. Now the tricky part. How can we create a phrase telling us how the promotion works in each scenario? I first create each of the 4 scenarios in separate columns as you see below.
Next I bring them together in the column Scenarios. While it is copy paste, you have to be careful. Mistakes are easy made. Once this is up and running we can copy — paste the code you see partly below into the button if you wish.
Since these columns do no harm, l hide them and reference them in my button and this was a good choice. I had to rewrite some bits and this approach saved me some time.
I noticed that the order of execution in the button matter. I first look for the promo rows, next I reference the 4 scenarios and the last part is the code for the rows that do not contain promotions.
NOTE: It is not wise to mix on product level promotions VAT incl & VAT excl for obvious reasons.
The result is what you see below.
We also added a button to remove the promotion that also puts the base prices back to have a new fresh start.
Once we can apply product specific promotions, we also want to give an intelligent promotion over the total as well. This is the main subject of Part VI.
I hope you enjoyed this article. If you have questions feel free to reach out. Though this article is for free, my work (including advice) won’t be, but there is always room for a chat to see what can be done. Besides you find my (for free) contributions to the Coda Community and on Twitter
My name is Christiaan Huizer and I am the owner of Huizer Automation. A company specialized in serving SME in harvesting data and keeping it aligned. I am a Coda Consultant & Expert and rely mainly on Coda, Mailjet, Zapier & Paperform to get the job done.