cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Anonymous
Not applicable

Patch Timestamp

Hello,

 

I am trying to create a "sign in," and "sign out," timestamp from my Powerapps to OneDrive spreadsheet.

 

I currently am trying to Patch the current time to my OneDrive spreadsheet using the following formula I found online:

 

Patch(Table2, First(Filter(Table2, SignIn = "")),{SignIn: Text( Now(),"[$-en-US]mm/dd/yyyy hh:mm:ss" )})

 

The Patch currently works, however, it doesn't perform the filter, which I would like it to filter to the correct person based on last name.

 

I have also tried to accomplish the timestamp feature using Flow, however, have not had any luck.

 

Any help would be greatly appreciated.

 

Thank you!

 

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
mr-dang
Level 10

Re: Patch Timestamp

Hi,

If I understand correctly, you want a system for signing in one record and signing out the same record. 

 

Below is your formula:

 

Patch(Table2, First(Filter(Table2, SignIn = "")),
	{SignIn: Text( Now(),"[$-en-US]mm/dd/yyyy hh:mm:ss" )
	}
)

This means, "Save a timestamp in the first record in Table2 whose SignIn field is blank."

 

 

The problem you might be facing is that two users sign in; their sign in is logged, User1 then User2. User2 logs out first, but they override User1's sign-in because it is the first blank in Table2.

 

The solution is to reconfigure your table then add more conditions to the Filter.

 

Reconfigure your table

I'm not sure what your table looks like, but adding more columns can help identify which person you want to log out. 

 

I assume you have these columns:

  • LastName: last name of the user
  • FirstName: first name of the user
  • UserID: id number of the user to distinguish users who have the same first and last name
  • SignIn: time signed in
  • SignOut: time signed out
  • __PowerAppsId__: a unique id created by PowerApps that identifies each record

I presume that you are keeping a growing list and not replacing old records for sign-in and sign-out. You could keep the first and last name in another table as a lookup--I kept them in for convenience though.

 

Add more conditions to the Filter

I have two solutions for rewriting the formula: one has fewer changes from your original, the other uses context variables.

 

Method 1: Revise the Patch formula with more conditions

 

For signing in:

Patch(Table2, Defaults(Table2),
	{LastName: [however you get the last name],
		FirstName: [however you get the first name],
		UserID: [however you get the id],
		SignIn: Text( Now(),"[$-en-US]mm/dd/yyyy hh:mm:ss" )
	}
)

This means write a new record to Table2 that includes the LastName, FirstName, UserID, and SignIn timestamp.

 

Assuming they do not sign in again or they do not forget to sign out, the following formula can pick out which record to write for signing out:

Patch(Table2, First(Filter(Table2,LastName=[reference the control/variable with the last name] && IsBlank(SignOut))),
	{SignOut: Text( Now(),"[$-en-US]mm/dd/yyyy hh:mm:ss" )
	}
)

This means, "Find the first record in Table2 that matches the user's last name and has a blank SignOut time, then write a timestamp to the SignOut field."

 

Method 2: Use a context variable

 

The first method can fail if the same user had forgotten to sign out the first time and as a result they have two records in the table with their name and blank SignOut fields. To resolve this, I use the unique PowerAppsId. I set a context variable equal to the SignIn record. When it comes time to sign out, I can write straight to the variable I had set.

 

 

UpdateContext({signinrecord:
	Patch(Table2, Defaults(Table2),
		{LastName: [however you get the last name],
			FirstName: [however you get the first name],
			UserID: [however you get the id],
			SignIn: Text( Now(),"[$-en-US]mm/dd/yyyy hh:mm:ss" )
		}
	)
})

It's the same as Method 1, but I wrapped the formula for writing a variable around it.

 

 

To sign out, recall the variable signinrecord:

 

Patch(Table2, signinrecord,
	{SignOut: Text( Now(),"[$-en-US]mm/dd/yyyy hh:mm:ss" )
	}
)

or

 

 

Patch(Table2, First(Filter(Table2,__PowerAppsId__=signinrecord.__PowerAppsId__)),
	{SignOut: Text( Now(),"[$-en-US]mm/dd/yyyy hh:mm:ss" )
	}
)

Both formulas mean locate the record that is identical to the variable signinrecord, then write the SignOut time.

 

 

Let me know if this works.

Microsoft Employee
@8bitclassroom

View solution in original post

3 REPLIES 3
mr-dang
Level 10

Re: Patch Timestamp

Hi,

If I understand correctly, you want a system for signing in one record and signing out the same record. 

 

Below is your formula:

 

Patch(Table2, First(Filter(Table2, SignIn = "")),
	{SignIn: Text( Now(),"[$-en-US]mm/dd/yyyy hh:mm:ss" )
	}
)

This means, "Save a timestamp in the first record in Table2 whose SignIn field is blank."

 

 

The problem you might be facing is that two users sign in; their sign in is logged, User1 then User2. User2 logs out first, but they override User1's sign-in because it is the first blank in Table2.

 

The solution is to reconfigure your table then add more conditions to the Filter.

 

Reconfigure your table

I'm not sure what your table looks like, but adding more columns can help identify which person you want to log out. 

 

I assume you have these columns:

  • LastName: last name of the user
  • FirstName: first name of the user
  • UserID: id number of the user to distinguish users who have the same first and last name
  • SignIn: time signed in
  • SignOut: time signed out
  • __PowerAppsId__: a unique id created by PowerApps that identifies each record

I presume that you are keeping a growing list and not replacing old records for sign-in and sign-out. You could keep the first and last name in another table as a lookup--I kept them in for convenience though.

 

Add more conditions to the Filter

I have two solutions for rewriting the formula: one has fewer changes from your original, the other uses context variables.

 

Method 1: Revise the Patch formula with more conditions

 

For signing in:

Patch(Table2, Defaults(Table2),
	{LastName: [however you get the last name],
		FirstName: [however you get the first name],
		UserID: [however you get the id],
		SignIn: Text( Now(),"[$-en-US]mm/dd/yyyy hh:mm:ss" )
	}
)

This means write a new record to Table2 that includes the LastName, FirstName, UserID, and SignIn timestamp.

 

Assuming they do not sign in again or they do not forget to sign out, the following formula can pick out which record to write for signing out:

Patch(Table2, First(Filter(Table2,LastName=[reference the control/variable with the last name] && IsBlank(SignOut))),
	{SignOut: Text( Now(),"[$-en-US]mm/dd/yyyy hh:mm:ss" )
	}
)

This means, "Find the first record in Table2 that matches the user's last name and has a blank SignOut time, then write a timestamp to the SignOut field."

 

Method 2: Use a context variable

 

The first method can fail if the same user had forgotten to sign out the first time and as a result they have two records in the table with their name and blank SignOut fields. To resolve this, I use the unique PowerAppsId. I set a context variable equal to the SignIn record. When it comes time to sign out, I can write straight to the variable I had set.

 

 

UpdateContext({signinrecord:
	Patch(Table2, Defaults(Table2),
		{LastName: [however you get the last name],
			FirstName: [however you get the first name],
			UserID: [however you get the id],
			SignIn: Text( Now(),"[$-en-US]mm/dd/yyyy hh:mm:ss" )
		}
	)
})

It's the same as Method 1, but I wrapped the formula for writing a variable around it.

 

 

To sign out, recall the variable signinrecord:

 

Patch(Table2, signinrecord,
	{SignOut: Text( Now(),"[$-en-US]mm/dd/yyyy hh:mm:ss" )
	}
)

or

 

 

Patch(Table2, First(Filter(Table2,__PowerAppsId__=signinrecord.__PowerAppsId__)),
	{SignOut: Text( Now(),"[$-en-US]mm/dd/yyyy hh:mm:ss" )
	}
)

Both formulas mean locate the record that is identical to the variable signinrecord, then write the SignOut time.

 

 

Let me know if this works.

Microsoft Employee
@8bitclassroom

View solution in original post

Anonymous
Not applicable

Re: Patch Timestamp

I can't thank you enough for this. For now, I am using Method 1 even though it might contain some possible flaws. When I take a further look at context variables I will try Method 2.

 

Thanks again

Max1
Level 8

Re: Patch Timestamp

Hello,

 

I'm sorry for digging up an old thread but I had a question on your solution. Why did you use Context Variables and not Global Variables? 

 

I'm new to powerapps and may be missing something but would the solution in method 2 only work if the sign in and sign out screen were the same?

 

I am adapting the solution below to record how long a worker spends on a specific job.

 

Thanks,

 

Max

Helpful resources

Announcements
thirdimage

Power Apps Community User Group Member Badge

Fill out a quick form to claim your user group badge now!

sixthImage

Power Platform World Tour

Find out where you can attend!

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

SecondImage

Difinity Conference

The largest Power BI, Power Platform, and Data conference in New Zealand

Top Solution Authors
Top Kudoed Authors (Last 30 Days)
Users online (4,807)