[SOLVED] Automatically close the last task when creating a new one

[Caio_Barros]

I have an app for task Management where you can log tasks, start time, end time, comments, status, etc. I want to add a Bot that automatically closes the last “In progress” task (meaning, endTime is blank) when a new one is created.

Here is a simplified version of the app: TaskManager - AppSheet

So far I have only been able to create an Action “End Task” that works and I’m trying to make another one “End Last Task” that will close the last created one. I’m having 2 difficulties:

1. The bot doesn’t seem to run when a new task is created
2. I can’t figure out how to select only the last created task (suppose in this case that are multiple users, so something like ([_THISROW].[_ROWNUBER] -1) doesn’t work).

[Marc_Dillon]

Caio_Barros:

The bot doesn’t seem to run when a new task is created

What does the Audit History, or Automation Monitor say? Does it work if you deploy the app?

---

---

Caio_Barros:

I can’t figure out how to select only the last created task (suppose in this case that are multiple users, so something like ([_THISROW].[_ROWNUBER] -1) doesn’t work).

In here:

I would use something like this to select the last task:

LIST( MAXROW(
  "tasks" ,
  "_RowNumber" ,
  AND(
    [status] = "In Progress" ,
    [_RowNumber] < [_THISROW].[_RowNumber] ,
    [user] = "xxx"
  )
) )

help.appsheet.com

MAXROW()

Row with greatest value in column

[WillowMobileSys · Accepted solution]

Caio_Barros:

I can’t figure out how to select only the last created task (suppose in this case that are multiple users, so something like ([_THISROW].[_ROWNUBER] -1) doesn’t work).

I personally do not like to use _RowNumber. I feel there is no guarantee that the rows will be in a known order - especially if the data comes from a database where a db view or indexing has modified the order.

This is what I would do.

In your use case you have just created a new Task row and want to “complete” the previous one. If we can assume that all previous task rows for this User have already been completed then there should only be two rows where ISBLANK([endTime]).

Then there are two ways to differentiate those rows:

1. [startTime] - one is less than the other.
2. [id] - assuming this is the key, is obviously different.

So I would use an expression like this:

SELECT(Tasks[id], AND(USEREMAIL() = [_THISROW].[User],
                      ISBLANK([endTime]),
                      [id] <> [_THISROW].[id]))
                                 

[Caio_Barros]

Thanks for your help.

WillowMobileSystems:

SELECT(Tasks[id], AND(USEREMAIL() = [_THISROW].[User],
                      ISBLANK([endTime]),
                      [id] <> [_THISROW].[id]))

This one works! I think it will close all open tasks except the last one, but I’m totally fine with that.

Marc_Dillon:

What does the Audit History, or Automation Monitor say? Does it work if you deploy the app?

It showed no errors and the bot ran fine. I think the problem was the Action itself.

[WillowMobileSys]

Caio_Barros:

This one works! I think it will close all open tasks except the last one, but I’m totally fine with that.

Yes, I almost pointed this out as a GOOD side-effect. If the process fails for any reason, the next process run will correct it. Data may not be accurate but at least complete. If accuracy becomes important then you can add some way to alert you of the problem so you can fix it.

[Caio_Barros]

One thing that I noticed (in the real app) is that using virtual columns as references doesn’t work. In the App I have a column for username (which is not the email) and a virtual column for the user email itself. I can’t reference this virtual email column in the SELECT() formula, so I had to rebuild in the formula the process I use to generate the username and compare with that (real) column. In my case it was quite simple, but I thought worth mentioning, since AppSheets didn’t throw me any errors/warnings when I tried to do that.

[WillowMobileSys]

Caio_Barros:

App I have a column for username

There is a USERNAME() function in AppSheet.

I HIGHLY recommend NOT using username in the app for user validation. To reference username in the app you would need to record it with the User information. But…usernames in Cloud accounts can be changed without affecting the login credentials. If this happens, you will have mis-matching username information from the Cloud login and what is recorded in the app. Logic based on it after any changes to the username will cease to work.