Photo by Wengang Zhai on Unsplash

Validate responses in a Coda form

show specific error messages

Although the Coda forms lack features dedicated form builders offer, it is a great tool to harvest data and to get an automation running to follow up on the responses. It also is a great tool to tell exactly what goes wrong when somebody fills out a field incorrectly. Since all your data lives already in Coda you don’t have te replicate data sets in dedicated form builder. You can based your rules on other parts of the table and of the doc you are working in.

My use case is a notification on sick leave and / or time off. As process owner you want to avoid unnecessary interactions and thus work. One way to avoid problems is filtering out date ranges that do not fit. A few examples:

• End dates (or until) are always or greater or equal to the start (from) date.
• When you don’t work on a Friday, Saturday & Sunday and you select these 3 days to take time off, the form should block. When you add a Monday, it should run.
• You cannot take time off (or sick leave) on only holiday dates like Sunday 24, Monday 25 and Tuesday 26 December 2023.
• You cannot take time off / sick leave when the end of your probation period ends in this date range. You have to make two requests: one ending on the last day of your probation period and one starting the day after. This due to vacation budgets.

It is possible to add additional criteria like always one employee per department should be present on Tuesday, Wednesday & Thursday. However to keep the example understandable, we work with the 4 business rules we defined.

The validation logic

Coda requires you to write rules that have to be true in order to pass. A good starter is that the end date should be greater or equal than the start date. That said, it would be great may one day Coda propose something AI based on the names of the columns and data types (date fields), but until then we have to set it up ourselves.

In my previous blog on setting up multiple forms I used the screenshot below and it shows the two essential ingredients: the currentvalue as the value representing the data filled out in the specific field and second the search for truth via logical operators and or filters.

CurrentValue >= thisRow.event.dateFrom and CurrentValue <= thisRow.event.dateUntil

example

The use cases we check

Based on what we described above, we check various use cases. You see already that per error we can generate a specific message. This blog is about how to get the code that permits you to set up a logic as you see below.

the date order

non working day

check on the end of the prohibition period

not working on Sunday and not on holidays

Below a case which is okay. Monday May 29 is a national holikday in the USA, but Tuesday 30 is not. You also see the red ‘OKAY’. This is only for testing purposes. We leave the fall back part of the SwitchIf() blank to avoid this outcome in red.

Error Code

This one annoyed me for a while. It was shown because in my error message I missed a comma between arguments in the SwitchIf().

Error code meaning check your validation logic

The required Code

When you have an input field and you set on validation is required, at least the field cannot be blank, but it still can be anything else. There are two more fields to work with:

• The formula
• The error message

In what follows we discuss both fields and we create formulas for each field.

The validation logic

What you have to understand about validation are two basics:

• you need the currentvalue (in our example the date entered in the until field) and the function applied must have as outcome that is not blocked by the currentvalue. When the currentvalue is checked, the outcome needs to be false in order to proceed.
• you can use the logical operators AND / OR to combine formulas.

We start with rule that the currentvalue (until date) always should be greater or equal to the From value. When this is not the case we write an error message. That message is not written here, it comes in the second part. There you will see that the below function gets reversed.

CurrentValue >= thisRow.from and // date validation

CurrentValue <=  thisRow.checkDatePP and thisRow.from > thisRow.checkDatePP 

// avoid the PP date in the range

OR

thisRow.holidays.Split(",").ListCombine().Sort().ForEach(CurrentValue.ToDate()).WithName(theHolidays,
  Sequence(thisRow.from,currentValue).ForEach(CurrentValue.ToDate()).WithName(theDates, 
    thisRow.nonWorkingdays.Split(",").WithName(theNonworkingdays,
    
    theDates.Filter(
      CurrentValue.Weekday().Contains(theNonworkingdays)).WithName(theWeekdays, 
        
            theDates.Filter(
      CurrentValue.Contains(theHolidays) and
      CurrentValue.Weekday().Contains(theNonworkingdays).Not()).WithName(theVacation, 


theDates.Count() != Sum(theVacation.Count(),theWeekdays.Count())
            
  
   )))))
     
     // check on non working days (weekdays & holidays)

The days off logic requires a double check. We first check for the non working days and second we check the holidays. In the holiday check we filter out the non working days to avoid that we count a day twice (like if you are not working on a Sunday and Sunday is a Holiday). Since the the from — until logic generates a list (even with one item) we know that when the count of the list is equal to the sum of the both filters, all dates are days off.

That observation can be translated into an error message and here we have the opposite. When the count of the list is equal to the sum of the filters, something is wrong and we block the process. More about the related error message below.

The error message

With the above in mind, you can see how easy and difficult this one is at the same time. Easy because you need the inverse from the above logic, difficult because you have to inverse it correctly.

To get the right error message per business rule, we use a SwitchIf() . In the snippet below I start with the hardest part and name it to reuse the outcome in the SwitchIf() All error messages appear in red by default. You cannot change that, which is a pity.

Once an argument is true, we show the error message. Compare this code snippet with the above and you noticed the reversed logic.

thisRow.holidays.Split(",").ListCombine().Sort().ForEach(CurrentValue.ToDate()).WithName(theHolidays,
  Sequence(thisRow.from,CurrentValue).ForEach(CurrentValue.ToDate()).WithName(theDates, 
    thisRow.nonWorkingdays.Split(",").WithName(theNonworkingdays,
    
    theDates.Filter(
      CurrentValue.Weekday().Contains(theNonworkingdays)).WithName(theWeekdays, 
        
            theDates.Filter(
      CurrentValue.Contains(theHolidays) and
      CurrentValue.Weekday().Contains(theNonworkingdays).Not()).WithName(theVacation, 


theDates.Count() = Sum(theVacation.Count(),theWeekdays.Count())
            
  
   )))))
   .WithName(rule2, 

SwitchIf(
currentValue < thisRow.from,
  Concatenate("The until date is always equal or greater than the start date")._Color("Red"),
  
rule2,
  
 Concatenate("You cannot take time off on days you don't work")._Color("Red"),
 
currentValue  >  thisRow.checkDatePP and thisRow.from < thisRow.checkDatePP,
  
Format('Please split your request in two: days before and including {1} and the second request days after {1}',thisRow.checkDatePP),
  
" "
  
))

Two validation related suggestions for Coda

I have two suggestions.

First extend and level up the validation logic. Allow multiple rules and per rule a response. Today we have to repeat the inversed validation rule in a SwitchIf().For most makers this is too difficult.

Second do not automatically print the (error) message in red, but allow the user to apply a color of choice. This would permit for giving positive directions.

There is something else I repeat in this context after I got mixed up in my settings. My region is Europe (Brussels), but often I work for USA clients. Each time I have to check settings, I cannot rely on function logic

Coda needs to improve te Weekday() logic

Coda should clean up this approach of regional weekDay() settings in your doc and replace it with a formulaic approach. I propose:

• Weekday() — Sunday as first day of the week
• isoWeekday() — Monday as first day of the week
• friWeekday() — Friday as first day of the week
• satWeekday() — Saturday as first day of the week

Likewise for week numbers. It would bring back order in world of chaos. The slogan right over familiair did not work out well in this context, I am afraid.

In the doc below you can check how the validation logic works.

Validation Logic · Template Sum Tables

The code logic is difficult. Don’t underestimate how much time it will cost you to get it right. It took me far more time than I had imagined and budgeted.

My name is Christiaan and I support SMB with calculations (budgets and — Human Resource — planning) and I prefer using Coda to get the job done. More about me below:

Coda-Expert

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. You find my (for free) contributions to the Coda Community and on Twitter.

Coda comes with a set of building blocks ー like pages for infinite depth, tables that talk to each other, and buttons that take action inside or outside your doc ーso anyone can make a doc as powerful as an app (source).

Not to forget: the Coda Community provides great insights for free once you add a sample doc.