Advanced Coda Filtering in a LookUp
work with Remainder() to get your filtered sub set
More than 20 years ago, I stepped in Brussels into an employment agency and they asked me if I was a clerk or a worker. I was surprised. In the Netherlands this distinction did not exist, but at that time it did in Belgium (and still does but a bit different formulated today) and each type comes with certain (dis) advantages. In my use case clerks are better off, they have more freedom, for example when it comes to a ask for permission to get paid leave hours / days.
In this blog I show you two approaches how to filter your lookup like a pro. This one took me a bit more time to figure out than expected and it has to do with the formatting of the numbers. Working with integers like 1,2, 3 etc. is rather straightforward, but working with time and thus duration requires you to keep the formatting in place and comparing apples with apples. Once you see the solution, it is as often rather simple.
The concrete use case
We have three type of people working with us, the workers, the clerks and the consultants. The latter we leave out of the equation because they are not subject to labour laws, they have specific contracts with the employer.
The starting time each day is rather flex, the end time is more or less fixed, but not relevant for this use case. The same about the break. We focus on the length of a working day and the increments applicable to each type when it comes to paid leave (vacation or sick leave). Clercks take or 4h or 8h, while workers can take day parts of least 30 min.
The outcome of our filtered look-up should be for clerks 4 hours vacation (8 hours is a day), while for workers it ranges from 30 min to 7h30.
Since I did not see the solution directly, partly due to the duration logic I created an intermediate solution permitting me to move forward with my project. Time is money in my case and I don’t want to slow down an implementation because I do not see the better solution directly.
The intermediate solution to have something
I clicked together the solution as you see below by adding an extra column and selecting the right option. In this case this is possible, but once you run into a more complex set up, it no longer holds.
The lookup column in my vacation table has this formula in the option part. It works for now and could continue with other — related — work.
Although this rather easy solution works, it requires an extra column we have to fill out manually and as said before this is okay in an simple, but not in a complex context.
I decided to focus on a better way, one I can use in various circumstances like complex resource plannings whereby not every person or contract has an equal day length and works with different intervals.
I have to admit that my intuition on how to tackle this solution was almost there immediate: the Remainder function. But I did not directly see was how solve this puzzle.
Step 01
Before I could test the remainder function I had to define the list of items. Below how that goes. We limit the list by the length of the working day (in our case 8 hours, but if you live in an other country it can be 9 hours or 7.6 hours or 7.2 hours) and what I did next is in the light of what follows not really necessary, I also limited the list by the min take off value. I assumed this would be important, but this is wrong.
Step 02
We evaluate this list using the Remainder() function. Below how I evaluated each item in the list, using a virtual sequence logic to run over every item in step 01 bringing ForEach() into play.´Although the min time off is formatted as Duration, I still had to apply ToHours() to make it work. I only got this by testing and trying.
The outcome is what I wanted a list with items and in this list I only want items with value zero, so the remaining part is equal to zero.
Step 03
This preparation was necessary to see the Remainder logic in action and only functioning with the ToHours() at the end. Step 03 is to add a filter that only accepts values that have zero as remaining value:
Please notice that this outcome lives ‘outside’ the lookup. We filtered values and print them in the column, but this does not permit the user to only select preferred and fitting values.
Step 04
In previous blogs on lookups I wrote already:
Filters are all about true. Once the ‘inside’ of a filter is true, you can ask for an outcome.
What you see below is actually a simplification of the work shown in the previous steps. We first filter the list to get only values that fit a working day and second we filter the same values when the remainder is zero.
As I wrote in the beginning, this is so easy , but honestly I did not see this directly.
One maybe wonders why you see ‘Value’ and not CurrentValue. Well, there is a CurrentValue available, but it seems you don’t have to mention it. That was new for me, it is possible to directly take an other value than the CurrentValue (which is the value living in the display column).
The column with the name value you find below. I create this column always in any table to have values that are exactly the same as the values in the Name (display column), but differently formatted. In this context I also defined the column type as duration, but I still need the ToHours() to my surprise.
Anyway the solution is rather simple as you can see, two lines of code inside the options of the lookup.
Based on the above you can work with complex working schemes and permit the user to only select fitting values without the need for an extra column. The examples you find in this doc below ⤵️
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 and I support SMB with calculations (budgets and planning) and I prefer using Coda to get the job done.
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.
If you enjoyed this read and would like to get more Coda related content, please consider a Medium membership. It is it only $5 a month, and you’ll have access to every article ever published on Medium. If you sign up using my referral link, I’ll earn a small commission.
