cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
FelipeCaru
Level 8

2 Excel files as datasource, one saving changes and another a "log"

Hello all,

I have a question regarding the connections as datasource/database one can have in PowerApps. I have not worked yet with more than one at the same time, so am pretty much in the dark here. The question I have is: is it possible to have two Excel tables, from different files, as datasource at the same time? If so, then I have the following situation:

 

I have a form which works with the datasource one, "Consolidado_Piloto", which is the one the user access and can edit/modify some dates and add some comments. Each line of that file/table has also an unique ID. What I want to know is if it is possible that when an user edits and saves a form, asides from the form saving the changes in the datasource one, in the "table 2 / datasource 2" called "log", it would save the ID of the item that was saved plus the edited fields.

 

For example, form has:

  • Project name (non editable [NE])
  • Task name (NE)
  • Start date planned (NE)
  • End date planned (NE)
  • Real start date (edited by user)/Input (I)
  • Real end date (edited by user)/I
  • Comment (edited by user)/I
  • Finished (edited by user)/I

When user changes anything on it and saves it, the item, of course would be edited and saved, however, in  the "table 2" (log), it would save the ID of the item that was edited (not sure if is needed to be in the form, but invisible), plus the edited fields. What is important is that each edit that is saved would be a new line in the table 2, not replacing a previous one.

 

Is this possible?

1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

Re: 2 Excel files as datasource, one saving changes and another a "log"

Hi @FelipeCaru 

With Patch, the second table doesn't need to have the same column names as the one from the form in table1.

The Patch function takes 3 arguments and it looks like this:

Patch(Log,
      Defaults(Log),
      {LogComment: EditForm1.LastSubmit.Comment, 
LogFinished: EditForm1.LastSubmit.Finished} )

The first argument specifies the data source.

The second argument specifies the record that we want to add/update. Defaults(Log) specifies that we want to add a new record.

The third argument specifies the field values that we want to add. To demonstrate the situation where the column names in table2 are different to those from table1, let's assume that in the log table, the column names are called LogComment and LogFinished whereas in table1, the column names are called Comment and Finished.

To set the value of LogComment, we can use the syntax {LogComment: EditForm1.LastSubmit.Comment}. EditForm1.LastSubmit provides a way for us to quickly retrieve the values that we just saved in the form. You would obviously replace EditForm1 with the name of your actual form. Hope that's of some use to you!

View solution in original post

5 REPLIES 5
Super User
Super User

Re: 2 Excel files as datasource, one saving changes and another a "log"

Hi @FelipeCaru 

The short answer to this is yes, it's possible to include 2 Excel tables from different files in the same app.

In the OnSuccess property of your form, you can use the Patch function to write a new log record into your log table. Hopefully, that answers your immediate question. Feel free to post back if there's anything you want to know in more detail.

FelipeCaru
Level 8

Re: 2 Excel files as datasource, one saving changes and another a "log"

@timl 

Thanks for the answer. I do need however further explanation, if it isn't a bother. How would I use the function patch? Have read on it but have never used, so not sure there. And, for it to work, would I need that the second table have the same columns than the one that has saved the form in table1?

Super User
Super User

Re: 2 Excel files as datasource, one saving changes and another a "log"

Hi @FelipeCaru 

With Patch, the second table doesn't need to have the same column names as the one from the form in table1.

The Patch function takes 3 arguments and it looks like this:

Patch(Log,
      Defaults(Log),
      {LogComment: EditForm1.LastSubmit.Comment, 
LogFinished: EditForm1.LastSubmit.Finished} )

The first argument specifies the data source.

The second argument specifies the record that we want to add/update. Defaults(Log) specifies that we want to add a new record.

The third argument specifies the field values that we want to add. To demonstrate the situation where the column names in table2 are different to those from table1, let's assume that in the log table, the column names are called LogComment and LogFinished whereas in table1, the column names are called Comment and Finished.

To set the value of LogComment, we can use the syntax {LogComment: EditForm1.LastSubmit.Comment}. EditForm1.LastSubmit provides a way for us to quickly retrieve the values that we just saved in the form. You would obviously replace EditForm1 with the name of your actual form. Hope that's of some use to you!

View solution in original post

FelipeCaru
Level 8

Re: 2 Excel files as datasource, one saving changes and another a "log"

@timl 

That worked perfectly, I was not sure how it worked the Patch() function. Thank you!
A brief question, however, if it is possible: the column name in the Path inside te { x }, can it have spaces in between? Or would it need to add the "_x0020_" if it has one?

Example: ID Orden vs ID_x0020_Orden

Super User
Super User

Re: 2 Excel files as datasource, one saving changes and another a "log"

Hi @FelipeCaru 

I think for Excel, you would need to use the "_x0020_" syntax. My understanding is that at present, the 'friendly' syntax is for SharePoint only.

Helpful resources

Announcements
thirdimage

Coming Soon: T-Shirt Design Contest

Keep your eyes open for our upcoming T-shirt design contest!

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

Top Solution Authors
Top Kudoed Authors
Users online (6,506)