Update last record to calulate duration

[JWPowder]

Hello, I have a sharepoint list with a Date/Time column (Column 4) and an empty column (Column 5) named "End Time". I have another column next to that named "Status Duration" (Column 5-Column 4). 

I am trying to update the last record entered into the list that matches the title in column 1, and enter only the current date and time into the "End Time" column for that record. 

On my status dashboard the textbox displaying the status will trigger the entry of the end time OnChange. I created a form, Form1 that contains just a Date/Time field and linked it to the correct datasource. 

The original form submitting the data is Form4.

I am trying to use patch but I cannot get the formula correct as you can see below:

Patch('StatusBoardData_HILLIARD_Rev2.0', Form4.LastSubmit.Title, 'StatusBoardData_HILLIARD_Rev2.0'.'End Time'= Text(Now(),DateTimeFormat.ShortDateTime24))

[JWPowder]

Hello, I have updated my patch formula and still am not having success. 

Patch(

       'StatusBoardData_HILLIARD_Rev2.0',

      LookUp(Sort('StatusBoardData_HILLIARD_Rev2.0', Created, Descending), Title = Form4.LastSubmit.Text, 'End Time')

           {'End Time': Text(Now(),DateTimeFormat.ShortTime24)}

  )

Again, I want the form to find the last record entered into the sharepoint list for the machine that the update was just entered for and then enter the current time in the empty row named "End Time" in the ShortTime24 format for the last record entered.  

[RandyHayes]

@JWPowder 

What are the names of your columns and what type are they?  Specifically I am referring to the use of .Text on the LastSubmit...is there a column named "Text" in your list?

Also, you mentioned that your column called "End Time" is an empty column...but what type of column is it?  Date or Text?

[JWPowder]

Hello @RandyHayes , My columns are named "Title", "Status", "Operator", "Time", "End Time", & "Status Duration" in that order. 

"Status Duration" is the calculated column based off the "Time" and "End Time" columns. 

Title is where the machine # is ex. "EXT 24" or "ACM 40-6"

I have tried the formula below on in my submit button "OnSelect" 

If(SubmitForm(Form4_1), Reset(Timer2_4); UpdateContext({varShowPopup1: true}) ; Patch('StatusBoardData_HILLIARD_Rev2.0',LookUp(Sort('StatusBoardData_HILLIARD_Rev2.0', Created, Descending), Title = Form4_1.LastSubmit.Title, 'End Time') {'End Time': Text(Now(),DateTimeFormat.ShortTime24)}); NewForm(Form4_1)

[RandyHayes]

@JWPowder 

So first, your Submit button should only be SubmitForm(Form4_1)

Always avoid doing anything after the submit form function in your formula as SubmitForm performs asynchronously and trying, for example, to get the LastSubmit, will not be accurate as the SubmitForm has not completed when referenced in the same formula.

Your formula should be in the OnSuccess action of the Form.  THIS is where you want to do the actions when the form submits properly.

Also, you are using an IF on a submitform function - That function does not return a value to "if".

So, in your OnSuccess action, the formula should be:

Reset(Timer2_4); 
UpdateContext({varShowPopup1: true}); 

Patch('StatusBoardData_HILLIARD_Rev2.0',
     LookUp(Sort('StatusBoardData_HILLIARD_Rev2.0', Created, Descending), Title = Self.LastSubmit.Title),
     {'End Time': Text(Now(),DateTimeFormat.ShortTime24)}
); 
NewForm(Form4_1)

The above assumes again that your End Time column is a TEXT column, not a date column.

[JWPowder]

@RandyHayes , I've only been using PowerApps for about a month now, these tricks and tips are greatly appreciated. 

I made the changes to my submit buttons and form OnSuccess actions. 

The code below is now in my OnSuccess Action. I changed the date input from a text string to an actual date becasue the column is formatted as a date. 

Reset(Timer2_4); UpdateContext({varShowPopup: true}); Patch('StatusBoardData_HILLIARD_Rev2.0',
     LookUp(Sort('StatusBoardData_HILLIARD_Rev2.0', Created, Descending), Title = Form4_1.LastSubmit.Title),
     {'End Time': Now()}); NewForm(Form4_1)

When I tested it, it worked perfectly but it entered the time as if it were 5 hours ago. I just had the admin correct the time zone and formatting in our sharepoint site to get 24 hour time. However, it is still inputting the date 5 hours in the past.

In the record below, it as entered 7 minutes after the initial input, instead of showing -5 hours, I want it to show the fractional duration between the two columns i.e. 0.1 hours. 

[RandyHayes · Accepted solution]

@JWPowder 

Your time in the list is correct.  SharePoint will display dates and times based on the regional settings of the site the list is in.  If you have had the regional settings changed, then make sure you are also refreshing the datasource in the designer.  It shouldn't make a difference, but I would verify that PowerApps has gathered all the list information properly with a refresh.

Since your duration is a calculated column, you will need to look at the formula for the calculate column in SharePoint to determine/provide what you want for the value.  Personally, I would ditch the calculated column and just have a regular numeric column and do the calculation directly in the App.

[JWPowder]

@RandyHayes , the time is still off after adjusting the sharepoint settings. 

I have modified the formula you supplied above to add 5 hours to the time that is input but now I am getting an error. 

Old:

Patch('StatusBoardData_HILLIARD_Rev2.0',
     LookUp(Sort('StatusBoardData_HILLIARD_Rev2.0', Created, Descending), Title = Form4.LastSubmit.Title),
     {'End Time': Now()}); NewForm(Form4) ; Notify("Success, "&Label8 &" Status Updated!", NotificationType.Success) 

New:

Patch('StatusBoardData_HILLIARD_Rev2.0',
     LookUp(Sort('StatusBoardData_HILLIARD_Rev2.0', Created, Descending), Title = Form4.LastSubmit.Title),
     {'End Time': DateAdd(Now(),Value(5),Hours})); NewForm(Form4) ; Notify("Success, "&Label8 &" Status Updated!", NotificationType.Success)

[RandyHayes · Accepted solution]

@JWPowder 

Well, you've got a couple of things wrong in the formula.

First, if there is an offset based on time zone, then you should be using the TimeZoneOffset function.  Also, Value(5) is not necessary...5 is already the Value.  You would use Value to convert a Text string to a numeric.  Thus, Value("5") would return 5.  So again, not needed.

Next, your Notify is referencing a control...not a property.  Label8 is a control.  You would want to reference the .Text property of the Label - i.e.  Label8.Text  This kind of thing causes issues at run time.

And finally, you are missing a closing paren in your DateAdd formula.

And as a little extra...it is best not to reference the label, but instead reference what your label derives from.  In the context of your formula, my guess is that it references one of the column values of the record you just wrote.  So, it should be stated that way in the formula.

With all the above...the formula should be (this version showing your original add to the time):

Patch('StatusBoardData_HILLIARD_Rev2.0',
     LookUp(Sort('StatusBoardData_HILLIARD_Rev2.0', Created, Descending), Title = Self.LastSubmit.Title),
     {'End Time': DateAdd(Now(),Value(5),Hours)}
); 
NewForm(Form4); 
Notify("Success, " & Self.LastSubmit.Title & " Status Updated!", NotificationType.Success)

This version showing the time zone offset):

Patch('StatusBoardData_HILLIARD_Rev2.0',
     LookUp(Sort('StatusBoardData_HILLIARD_Rev2.0', Created, Descending), Title = Self.LastSubmit.Title),
     {'End Time': DateAdd(Now(), TimeZoneOffset(), Minutes)}
); 
NewForm(Form4); 
Notify("Success, " & Self.LastSubmit.Title & " Status Updated!", NotificationType.Success)

[JWPowder]

@RandyHayes , you are an absolute wizard. Thank you Sir.