Ignore Deleted References in Formula

1

Hi,

I have a very simple issue that I’m having trouble finding the right way around:

I’m trying to wire up a button that will not allow someone to complete the task if its blockers are not completed.

Everything works fine, however if one of the blocker rows that is referenced is deleted rather than checked off as completed it prevents the button from being pressed.

My formula for the box that marks a task as “ready” to be completed is as follows:

if(thisRow.Blockers.Complete.ContainsOnly(true) OR thisRow.Blockers.IsBlank(),true,false)

I recognize from another helpful thread that the formula .IsBlank() is at fault here, since it does not ignore deleted references. I’m just struggling with figuring out what it needs to be replaced with.

I’ve seen some methods to en masse remove deleted reference rows, but for this application I can’t make them wait for that process to complete - it has to be immediate.

Any help is appreciated!

1 Like
2

Instead of .isblank(), can you try .isfromtable( [BLOCKER TABLE NAME] )?

1 Like
3

Hi Samuel,

Thanks for the suggestion, I think this is what I was looking for.

It wasn’t as simple as just plugging in .IsFromTable() where I had .IsBlank but I think I got there:

if(thisRow.Blockers.Filter(CurrentValue.IsFromTable(Tasks)).Complete.ContainsOnly(true) OR thisRow.Blockers.IsBlank(),true,false)

I pre-filtered the blockers to only show non-deleted rows using IsFromTable and then ran my formula. Seems to be working perfectly! Open to anyone who might want to suggest a refinement though.

1 Like
4

For some reason when I translate this formula over to my much larger much more complex doc this method doesn’t work. My example set up is really the exact same so I’m really confused why it’s failing to translate over. Unfortunately I can’t share my larger doc, but if someone has ideas on alternate ways of how to tackle this I’d be grateful - I’m hoping that one of them might actually function in my main doc.

1 Like
5

Can you try to make separate button that filters out deleted values before you run the if statement? Like modifyrow(thisrow, BlockerColumn, thisrow.BlockerColumn.filter(currentvalue.isfromtable(blocker table) ) ).

2 Likes
6

I plugged this into your example doc and it works as intended.

image

2 Likes
7

Hi Samuel,

Thanks for the suggestion. I think you’re right that a formula like this should definitely properly clear the deleted references for my bigger doc, but unfortunately in my use case its not going to work. It’s hard to explain without sharing the larger doc, but that “Ready” checkbox is very important to the functioning of the doc as it helps determine which tasks are ready next, so manually pressing a button in order to fix the behavior of it isn’t going to work.

I think I could probably hook up the action you mentioned and set it to run automated at set intervals in order to clear out the dead references on all my rows. I’m just hesitant to implement that into my main doc as its thousands of rows and an automation like that might bloat things a lot.

For now I have added a button to safely delete a task which first clears all of its dependencies before deleting the row, so no deleted reference issues can occur. In some ways this might be best practice anyway since I’ve already got most of my tables locked to prevent important things being deleted.

1 Like
8

So what you could also do is add it to the beginning of your Complete Task button. You can add multiple actions to a single button by modifying the button formula. So it would be like Runactions([filter out deleted rows], [compete task fomrula])

1 Like