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

[TLueders]

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.

[v-micsh-msft]

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

[TLueders]

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

[TLueders]

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? 

[v-micsh-msft · Accepted solution]

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

[stumiles86]

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.