Claris Studio is great for data collection, and fortunately, it provides us with a plethora of field types. So, for example, when you need to know the respondent’s phone number, you choose:
Indeed, Phone.
The same goes for a credit card number: if you need to know the respondent’s credit card number, you choose...
...hmm, there is no credit card field type in Studio. But, wait… it is a number, so you choose Number right? And then you make sure to validate it in a Connect flow using an OpenC Connector. Problem solved! While we are at it, check out some of the other OpenC Connectors.
Now, back to the basics of numbers.
Does it really count?
A number and numerals are something with which you count, measure or label. You can also use it for ordering things: a recordID that consists of “Rec” + 001 for a new record, or “001” + 1.
Until recently we indeed used Studio fields of the Number type for example for a credit card number. But credit card numbers are not things you can add or divide with.
So when it comes to math, not everything that looks like a number, is always a number.
To complicate things a little further, you cannot calculate with every numerical value in Claris Studio. So in this blog we want to share our experiences with numbers and calculations in Claris Studio.
It all adds up, right?
We have made the decision to only use the Number type field if we can do math with it, like 2 + 2 = 4. We have documented this in our OpenC guideline for field name conventions in Claris Studio. We found this article that kind of underscores the importance of why we made this decision.
For field values that look like a number but not for calculating, we use a Short Text field.
Can we summarise Calculation Fields in Claris Studio?
Yes. But no.
The Spreadsheet View we have created for this purpose, gives us a good example how the numbers play out in several field types in Studio:
- Number
- Currency
- Calculation
- Short Text.
As you can see in the screenshot below, we have created some currency amounts excluding VAT, and we want to calculate the VAT amount. Then we want to add it up, so that we get the total of the VAT amounts:
In the 'Amount (Currency)' column the user has entered some amounts. They can have multiple decimals, but on the screen the are nicely rounded to two decimals, as they should. Because they are currencies.
For demonstration purposes we have put the Studio field type between brackets. So the field 'Amount (Currency)' is of the Currency type.
Please note:
- The currency symbol is configurable in Studio. We chose the euro symbol.
- This Spreadsheet View uses a dot for the decimal point and a comma as a thousands separator (€ 1,000.00). In Europe, this should be the other way around (€ 1.000,00).
- We have given the VAT percentage a default value to .21 (21.00%). That's the way we get taxed where we come from. We mostly live happy lives, apart from the high taxation that is.
Having said this. We have calculated the VAT amount in the column 'VAT (Calculation). This one is of the type Calculation.
In the following screenshot you can see the actual calculation: VAT percentage x amount. This works perfectly, both if you create a new record and if you update the amount in an existing record. So far so good.
Zero sum game?
We finally want to add up all the calculated VAT amounts. But we run into a problem, that the following screenshot shows us at the bottom 'Summary: none'.
In Claris Studio we can beautifully and very easily summarise Number fields. And create certain summaries for Text fields and even Single Choice fields. But for Calculation fields adding it all up is not possible. That really feels counter-intuitive (pun intended).
In our vision at OpenC:
- Because calculation fields are dynamic and number fields are static, we tried hard to accept and understand that it kind of makes sense that Calculated fields do not add up.
- But we still want to know the total VAT amount of all records anyway.
So how to figure this out? The best way we have found, is the following: with a simple Claris Connect Flow, the VAT amount is calculated. You can make the Connect flow put the result in:
- Either a Number field, in our example the column 'VAT amount (Number)'.
- Or a Currency field, in our example the column 'VAT amount (Currency).
In both cases you can easily make use of the summarise-options you find at the bottom of the Spreadsheet View. You can see this in the following screenshot.
In this screenshot you can also see that the Number-column has quite a lot of decimals, in the records where this is the case. While the Currency-columns shows the rounded amount in euro-cents. But fortunately the underlying values in the Currency-column are not rounded. You can check this by looking at the bottom, at the two Sum-sections: both columns add up to 233.23356.
Minus 1
Calculation fields cannot be summarised. We can solve this with a simple Connect flow.
Connect puts the VAT amount into the Currency field, and does that very fast. So the record and the table, and the summary has been updated almost immediately.
But this solution comes at a cost (no more puns after this point): while the record is updated under the hood, you will not see this reflected on your screen. You have to refresh your browser tab, to see the updated VAT amount on your screen.
Claris Connect flow: 1 – 2 – 3
Below, we show you the screenshot with the Connect flow steps. It is easy to create it, it only has these three steps:
- Trigger is the amount field
- The calculation does the math
- The result is put in the record.
Rounding it all up
What is your experience with number crunching in Claris Studio? Please let us know, let's number crunch together!