Hi
I have a table that has two columns of date and requester:
Coda begins with a blinking cursor and grows as big as your team’s ambition. We’ve seen Coda docs do everything from run weekly meetings, to launch products.
I created a form with this table and I put the link below. How can I write a formula in this form so that if there are more than 4 from the same date, no one can complete the form on that date??
can anyone help on this?
Hi Amine
I do not think that you are going to be able to do this.
(I assume that you want the form to check in the table that it updates in your doc how many requests have been made for the day?)
Forms were designed to be free standing, so that people can fill them in, without having access to your doc.
Regards
Piet
@Piet_Strydom
What if I want to use the automation feature? I mean, if there are more than 4 of a date in the table, warn or message that the day’s requests are full.
You can do this through validation - it requires a bit of setup, a lookup table with copies of the existing requests (which can be generated automatically), but it is doable.
(There is a small bug in Coda where the validation error message is not cleared, but I am sure that can be solved). Bug has disappeared.
Happy coding,
Greetings, Joost
@joost_mineur
I did all the instructions you gave me, but I had trouble writing a formula that could count dates that are more than four. Can you help?
Hello @amin_jebeli ,
Did you share the document in which you made your setup? I am happy to help when you are trying to figure something out, but I can’t start building your app or complete samples.
The validation formula that works is thisRow.lookup<5, but I have used that in one of my private docs that I can’t share with you.
There are different ways of accomplishing what you want, I use a bit different setup in one of my planner docs: I have ‘events’ with a specific date and a given capacity - say 4 or 5 or 6 available slots on a given day. Om my form, all dates that are not completely booked are available to choose - once capacity is reached, the date is not available anymore. This way I prevent bookings on dated that I can’t accept new bookings (like on holidays).
Needless to say that building something like that is a lot of work and requires testing and planning. You can do this in Coda; I think it is easier in Coda than in just about any other package, but it doesn’t come easy. You can build it yourself or hire a consultant to help you.
But you need to do the ground work first - build a sample doc so that we can see what you are trying to accomplish and get a feel for the sophistication you need. When you get stuck on a formula there is probably someone willing to help.
Greetings,
Joost
Hello @amin_jebeli ,
I created a simple working solution - you still need to add some automation to fill the 2nd table to account for new rows.
For the future: the idea of this community is to share (publish) docs so everyone can learn, and then add a share to some people if you want them to be able to make adjustments to your doc (like you did for me).
The solution I presented is only to show it can be done and I am sure there are more elegant ways to do this. I would not use it this way, but it is a start.
Happy coding,
Greetings,
Joost
@joost_mineur
Is there a way to do this without creating a lookup table? Because a row is added to the main table when the form is completed. Suppose a date is selected and submitted 500 times a day, in which case there are 500 of that date in the main table. Until I enter 500 dates into the lookup table. This method is practically unusable. What should be done in the face of such cases?
Hello Amin,
This is going to be my last answer in this thread.
you have to use an automation to fill the lookup table upon a new record being added through the form. That works for 10 dates, for 100 dates and for 1000 dates. Just tested in my own doc and it works, no matter how many dates are added.
the solution is not perfect, because automations run a little bit behind and the form needs a refresh before allowing a correct test. So you need to turn ‘resubmit’ off and accept that if there are going to be a lot of requests only seconds apart from one each other, you will get some situations where 4 becomes 5.
I indicated that I would not use this solution myself, but that I only provided this information as an answer to your question and to show that it can be done.
I think that these are not the easiest of documents to build for a new coda user and you have to persist in finding (and testing) solutions for these questions.
One last tip: the automation can be as simple as this (tested and working):
Greetings,
Joost
