Limit Results / Rows?

[Neil_Gonzalez]

Hey all,
I am struggling to find what I’m looking for, and hoping someone might jump to the rescue. :slightly_smiling_face:

I’m making a to-do list using a Kanban view and would like to limit the amount of active tasks or “in progress” tasks to a fixed number, I would ideally like to run an automation to detect when something leaves “in progress” into the “completed” or “deferred”/“rejected” status and take a new task from the “to-do” tasks.

However, I cannot think of how to solve this using the options available in the automation. Has anyone dealt with this? I’m thinking of having a counting row in the table that the automation checks for when selecting which row to add next, although I’m not sure that’s the most elegant/smart option.

In summary: I would like to limit the number of rows that have a particular status to a fixed number, I cannot manually add more to that status unless another leaves that status, and if the number is below the fixed number; the automation will select from a prepared status to add it to that status.

[TheTimeSavingCo]

Hey Neil, I would do the following:

1. Set up a trigger for when the status field is updated
2. Do a “Find records” for all records with the status “To do”
3. Do a conditional action based on the length of the result from step 2
4. Pick the “To do” record with the oldest created time, and update it’s status to “In Progress”

You can find a working copy here with the automations set up

The most difficult part to me was figuring out how to update a single “To do” record that I won’t get into the details of here, and I feel like I’ve overcomplicated things. If anyone’s able to figure out a better way to deal with it I’d love to hear about it!

[kuovonne]

Thank you for posting the link to your working base. I like seeing how different people find solutions to puzzles like this. I hope you don’t mind me sharing a few thoughts for you and for anyone else who might come across this thread.

• I see you put the logic for the number of statuses “In Progress” in the condition for the conditional actions. That is a clever method.

• You use a “control” record in a different table that is linked to all of the other records in your main table. Using a single control record has its own complications. Making sure that new records get linked to the control record requires additional automations. Using a control record also does not scale well if you end up with thousands of records in your main table that need to be linked to the control record–the base slows down.

• When I viewed the base, there are multiple records with the same created time. This means that multiple records think that they are the oldest task. This also means that the Update Record action will fail if there happens to be multiple “oldest tasks” in the “Todo” state.

• The trigger for the automation watches for all changes to the {Status} field. Thus, the automation is triggered more often than necessary. For example, the automation is triggered when a task moves from “Todo” to “In Progress”. To limit the number of automation runs, I suggest creating a formula field whose value will change only under the conditions require to trigger the automation. Then have the automation watch that formula field.

Once again, thank you for sharing your solution. (I personally would use scripting, but I appreciate people posting non-scripting solutions for people who prefer to not use scripting.)

[Alexey_Gusev]

Funny puzzle, I created 2-way automation - to hold number of In progress = 4, no more no less, and learned how to extract single ID from ‘Find records’ results without code, using extra fields in ‘one-record table’. I can’t leave this copy long enough to share, but can explain by images.

[TheTimeSavingCo]

Oooh, yeah, excellent point regarding the scaling, created time, and automation runs. I was thinking that because the created times were down to the second that it would be fine, but you’re right, if a user ever duplicates rows or creates a bunch of rows in bulk, the automation would error out