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

[Anonymous]

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?

[timl]

Hi @Anonymous 

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.

[Anonymous]

@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?

[timl · Accepted solution]

Hi @Anonymous 

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!

[Anonymous]

@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

[timl]

Hi @Anonymous 

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.