cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
TLueders
Level: Powered On

Update Column in a Excel Table using a Flow and RowID/ PowerappsID

Hello,

 

wehave a problem with an app for recording working hours of our employees. Sorry if this question is too simple, but i could not find anything, that solved my problem in the community.

I have built a small app where you can select a button "Start" if your work started. On Select the Button creates a Row in a connected Excel Table and an Event in the calendar of the current user. In the Excel table there are Columns like:

User | StartTime | EndTime | Hours | Pause | PowerAppsID....

 

If work is finished the employee can select another Button "End". On select the button should update that row, where he recorded start time of work that day, with current time in the Column "EndTime".

How is it possible to build a flow or use a function using the PowerAppsID for finding the right row or is their another solution for our problem?

 

Hope someone can help us.

Kind Regards.

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team
Community Support Team

Re: Update Column in a Excel Table using a Flow and RowID/ PowerappsID

Hi @TLueders,

 

You should be able to use patch function to add new row into Excel, formula is similar to:

Patch(Datasource, Defaults(DataSource), {record})

 

Yes, you could add two action formula under the same onselect property of a button, 

Use semicolons to create a list of actions to perform. For example, you might want to update a context variable and then return to the previous screen:

  • UpdateContext( { x: 1 } ); Back()

Actions are performed in the order in which they appear in the formula. The next function won't start until the current function has completed. If an error occurs, subsequent functions might not start.

For more information, see:

understand behavior formulas in PowerApps

Regards,

Michael

Community Support Team _ Michael Shao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

5 REPLIES 5
Community Support Team
Community Support Team

Re: Update Column in a Excel Table using a Flow and RowID/ PowerappsID

Hi @TLueders,

 

Using the PowerApps ID within PowerApps is not possible.

Under Microsoft Flow, to Update the corresponding Row in Excel using the PowerAppsID is available, but that would require only one PowerApps trigger. 

The workable scenario that I could think out is the following:

1. Using collection to collect StartTime,

2. Using Patch to add a new record when the EndTime button has been pressed, together with the User and the StartTime collected.

I saw you have the pause column, would you please explain a bit for that field? 

Collection and the formula under the EndTime patch may vary based on what data stores under the pause.

 

Regards,

Michael

Community Support Team _ Michael Shao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
TLueders
Level: Powered On

Re: Update Column in a Excel Table using a Flow and RowID/ PowerappsID

Hi @v-micsh-msft,

 

thank you very much! That really helped me.

But i still have some problems with the syntax...

 

These are my two buttons for now:

 

"OnSelect" on the Start button (Just choose "K" to have any value he can look up for later):

Collect(ArbeitszeitErfassung; {Mitarbeiter:User().FullName;ArbeitsBeginn:Text(Now();DateTimeFormat.ShortTime);ArbeitsEnde:"K";Baustelle:ddwnBaustelle.Selected.Value;OrtArbeitsbeginn:Location.Latitude&Location.Longitude;OrtArbeitsende:"K";Datum:Text(Now();DateTimeFormat.LongDate)})

 

"On Select":

UpdateIf(ArbeitszeitErfassung;ArbeitsEnde=Text("K")&&OrtArbeitsende=Text("K");{ArbeitsEnde:Text(Now();ShortTime);OrtArbeitsende:Location.Latitude&Location.Longitude})

 

So these two work really good.

 

But how can i get this one record from my collection into a new row in the excel file?

Regards

 

 

Highlighted
TLueders
Level: Powered On

Re: Update Column in a Excel Table using a Flow and RowID/ PowerappsID

Hi @v-micsh-msft,

 

I solved the problem. Thank you very much. 

 

But i have a last question. I want to handle two Action by pressing a Button. How is the Syntax for it?

For Example: Button "Start" collect Starttime (and some other variables)& should send a mobile Notification via Flow
I tried it like this:

Collect(ArbeitszeitErfassung; {Mitarbeiter:User().FullName;ArbeitsBeginn:Text(Now();DateTimeFormat.ShortTime);ArbeitsEnde:"K";Baustelle:ddwnBaustelle.Selected.Value;OrtArbeitsbeginn:Location.Latitude&Location.Longitude;OrtArbeitsende:"K";Datum:Text(Now();DateTimeFormat.LongDate)}) && ZeiterfassungArbeitsbeginnNotification.Run(Text(User().FullName);Text(First(ArbeitszeitErfassung).ArbeitsBeginn))

 

How can i handle these two events by pressing one Button? 

Community Support Team
Community Support Team

Re: Update Column in a Excel Table using a Flow and RowID/ PowerappsID

Hi @TLueders,

 

You should be able to use patch function to add new row into Excel, formula is similar to:

Patch(Datasource, Defaults(DataSource), {record})

 

Yes, you could add two action formula under the same onselect property of a button, 

Use semicolons to create a list of actions to perform. For example, you might want to update a context variable and then return to the previous screen:

  • UpdateContext( { x: 1 } ); Back()

Actions are performed in the order in which they appear in the formula. The next function won't start until the current function has completed. If an error occurs, subsequent functions might not start.

For more information, see:

understand behavior formulas in PowerApps

Regards,

Michael

Community Support Team _ Michael Shao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

stumiles86
Level: Powered On

Re: Update Column in a Excel Table using a Flow and RowID/ PowerappsID

can you please share this flow? I am trying to replicate the exact same thing but am having trouble trying to get the end time working. I can get the new row and start time working its just finding the user and updating their row that is stumping me. Please help.

Helpful resources

Announcements
thirdimage

Power Automate 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

fifthimage

Microsoft Learn

Learn how to build the business apps that you need.

Top Kudoed Authors (Last 30 Days)
Users online (6,075)