issue using an action to change the ending time in a form

[Ruxandra]

Hello!

I have a form view with 2 fields, the start time, the time when the user starts filling the form, and the second field, the time the form is finished and saved in the DB.

For the ending hour, I created an action where I set the "end time" column to now(). In the DB (postgresql) the end time is saved correctly, but in the .pdf file which is sent using the automation I created, the time is the same as the start time, if I set the initial value of the end time field as now () and if I do not set anything as initial value, there is no end time at all on my .pdf .

I repeat, in the DB the time is correct.

Could anyone tell me what is the cause?

As a remark, after validation of the manager, the next automation used sent a .pdf file with the good ending hour.

Thank you for your help!

Regards!

Ruxandra

[Joseph_Seddik · Accepted solution]

No problem, let's take it step by step:

1. First, please have a *new* physical column, let's call it "Counter", type Number. Set its Initial Value to 0. 
2. In your "OraFinalizareControl" action, click on "Add", select your "Counter" column, and put the expression in the field on the right to: [Counter] + 1.
   
3. Set your "OraFinalizareControl" action, as the Form Served action of your form view. 
4. Set your Bot's event to "Updates" not "Adds", and put the following expression as a Condition: 
   NOT([_ThisRow_Before].[Counter] = [_ThisRow_After].[Counter])

[Joseph_Seddik]

Use a Form-Saved action to set the "end time" column upon saving the form. 

[Ruxandra]

Hello!

I'm afraid I don't understand. I already use an action applied when the Form is saved, but, as I said, in the DB the saved time is ok, but on the .pdf file sent using the automation the time is not correct, as I described above.

Could you explain me what do you mean?

Thank you!

Regards!

Ruxandra

[Joseph_Seddik]

And how is your automation triggered please?

[Ruxandra]

By an "Adds" event. I mean, for every new row added in the table, a .pdf file is created and is sent by email.

Thank you!

[Joseph_Seddik]

I see thanks. What happens is that your automation is triggered at the same time your action is launched and you can't be sure which will be going first, so you'd be better off controlling the order of the two events. 

1. First make your Form Saved action change the end-time.
2. Then change another column in the same row, like incrementing a counter.
3. Then trigger your automation to create the file on the change to this counter. 

In this way, you make sure your pdf creation comes after the row is updated. 

[Ruxandra]

I have a counter column, the number of the inspection, but I don't how to use a column change to trigger the automation, the options from the Automation are only linked to the table. Thank you!

Regards!

[Joseph_Seddik]

In the bot, set the event to "Update only":

And to trigger it only upon update of this particular column, not any column, you should put the following expression in the bot's "condition":

NOT([_ThisRow_Before].[counterColumn] = [_ThisRow_After].[counterColumn])

 This would also allow you to make an action button (incrementing the counter), so users can generate the PDF at will, not only upon the addition of the row.

[Ruxandra]

Hello!

I tested your solution, but it doesn't work, there is no email sent anymore. I've also made other tests, using Add instead Updates, using  ..._after >...._before, I tested using the time field with initial value = now() , and also with no initial value, if I have now(), the time is the same as the start time, if I delete the now() function, there is no time written on my pdf file. I don't understand which is the problem.

Thank you for your help!

Regards!

Ruxandra

[Joseph_Seddik]

Please show screenshots of your action and your bot. Thanks.

[Ruxandra]

Hello!

Please find below the screen-shots; I am not sure it is what you would like to see. The app is for my work company, I am not allowed to share data. Thank you! Regards!

[Joseph_Seddik]

• In your action, after setting the "orafctir" column, add another setting to increment your counter column ("nrpv"?).
• Make this action your Form Saved Action.
• In your bot, set the event to "Updates", not "Adds".

[Ruxandra]

I'm afraid I don't understand. I already have a formula to increment the counter column; I have one physical column which has as Initial value a virtual column where I have this formula (Max(Thetable[nrpv])+1); it work very well the incrementation.

Thank you! Regards!

Ruxandra

I have a grouped action, because I also need to write in another table, so, first action is the end time, the second is the action which writes in another table.

[Joseph_Seddik · Accepted solution]

No problem, let's take it step by step:

1. First, please have a *new* physical column, let's call it "Counter", type Number. Set its Initial Value to 0. 
2. In your "OraFinalizareControl" action, click on "Add", select your "Counter" column, and put the expression in the field on the right to: [Counter] + 1.
   
3. Set your "OraFinalizareControl" action, as the Form Served action of your form view. 
4. Set your Bot's event to "Updates" not "Adds", and put the following expression as a Condition: 
   NOT([_ThisRow_Before].[Counter] = [_ThisRow_After].[Counter])

[Ruxandra]

It works as you said, but I have another problem with the time format. The data type is "Date" and I need to do TEXT((now),"HH:MM) to display on my pdf file, and I wrote this in the action, but I have errors now trying to write in the postgresql database because the datetime format is not what the db expected... For the start time I've created a virtual column , and it was ok, but now, for the end time, how could I do ? The time format is English style, 3 p.m, I need 15:09.

Thank you for your help!

Regards! 

[Joseph_Seddik · Accepted solution]

DateTime raw values are just a number, for example the most common current time representation of when I'm writing this post is: 1715532251. In PostgreSQL the same datetime will be stored as a different value but it will also be just a number. The conversion between the two is handled by AppSheet according to your data source.

In your app, the format each user will be seeing depends on his own Locale/Regional Settings of his device. So English users will see 3 p.m, while other Europeans will see 15:00. 

In case you want to force a certain display format, yes you can use AppSheet's TEXT() function, but you should NOT do that in your dateTime column. Instead, you should create a new Text-type virtual column, the one that will be displayed to the users, leaving your dateTime column hidden and *unchanged*. 

So in summary:

1. Do NOT change your Date or DateTime columns. Leave them as is. 
2. For display purposes, have another virtual column with the formula:
     TEXT([dateTimeColumn], "HH:MM") or TEXT(TIMENOW(), "HH:MM")

[Ruxandra]

Hello!

Yes, this time using the virtual column worked fine, it shows the end time; as a note, I saw in my postgresql db that the counter column is not incremented, I have the same value=1 in every row - however the end time is ok, it's the time when the form is saved. The counter doesn't bother me because I have my column with max() where I created an incremented number. Thank you for your help!

Can I also ask you something else in the same topic or should I open another one?

I need to use an action to link to another view, but in the Form View they are not displayed (it seems this is the way the Form View works). I need that, after filling some fields, the user goes to another table to change some status - I have already done this for another requirements, using Action - set the value of some columns...- but in this case, I should be able to change multiple status on multiple rows in another table.

Actually, the requirement is more complex: after finding some issues during the inspection, the inspector comes back after a week, or two, etc,  to verify the issues were solved for the specific department; so, he creates another record using the same type of View as last date, he could be able to automatically see the inspections which have issues in this department, to select only these issues which have been solved; their status should be changed in "Closed" for only these issues, the others should remain "WIP". 

I did in my app all these requirements, except automation of changing the status in my "Issues" table. I should search in this table in the historical records.

Could you give me an idea how could be done?

Many thanks!

Regards!

Ruxandra

[Joseph_Seddik]

OK, first which counter are we talking about? "Counter" or "nrpv"? 

What exactly are you doing expecting it increments but it does not?

[Ruxandra]

It's about the "Counter" column, I thought the action will increment the number; the condition from the automation needs to have a different value _after versus _before, for the column "Counter" (NOT([_ThisRow_Before].[Counter] = [_ThisRow_After].[Counter]) ) , and I saw in the database it was equql to 1 for every row.

So, I thought that could be the problem which stopped the automation (I mean, there is no email sent with the pdf file).

Thank you for your help!

Regards!

[Ruxandra]

Hello again!

I don't understand why the automations do not work anymore; I've made changes to my app, I didn't touch Counter column or the automation; the counter column, as I told you, has only a value =1, it is not incremented, could be the problem?

Many thanks!

Regards!

Ruxandra

[Ruxandra]

Well, I investigated the issue all evening; earlier I added a virtual column, to process another physical column with "Date" format; I used it in my pdf template file, from my automation, but I forgot this detail.

In the end, I found another solution for my need, and I deleted the virtual column. So, because the column was used in the pdf file, and the app didn't find it anymore, the automation was not working.

It is strange I receive no error message from my Automation about the lake of this virtual column (no red bullet, no nothing). I've made many tests, but there was no error message. Is that a normal behavior of Appsheet?

So, your solution is fine. Thank you very much for your help and your explanations.

Regards!

Ruxandra

[Ruxandra]

"about the lack of this virtual column" - sorry, I misspelled

[Joseph_Seddik]

Thanks Ruxandra for the update. Failed automations do generate errors, but you wouldn't see them in the Editor. They get logged in the app's Automation Monitor, which is an important tool you should use when you are running automation. Each time an automation runs you'll find it in this monitor which will also logged failed automations and give you details about the reason for which they failed.