Add data to table in excel

[GrantAstley]

Hi All

I have a table (Table1) stored on onedrive.  This table has been linked as a data source to the powerapps app and I am able to retrieve data from it using the following 

Last(Filter(Table1,Barcode=Label6.Text)).Gas_Remaining

However I am at a loss how to write data back to the table.  I will always want to append data to the table.  I have tried using 

Collect( Table1, { Barcode: "2", Job_type: Dropdown2.Selected.Value})

Where Barcode and Job_Type are columns of the table to be updated, note that this is not all of the columns, I do not wish to append data in all columns.  This code is placed in on select of the save button.

Once the button is clicked the browser updates and the save button goes grey for a second then back to blue.  However upon checking the excel file nothing has been updated.

These are not part of any forms, galleries, etc just buttons, labels, dropdowns, etc placed on a new screen.  

Any guidance would be appreciated.

Cheers

Grant

[Pat_Wickramage]

FYR

[GrantAstley]

So once I have the link can I add it to powerapps anywhere so it has permission to edit the file?

[v-xida-msft]

Hi @GrantAstley,

I think there is something wrong with the formula that you provided.

The Collect function could only be used to add new recrods to a data source. If you want to update an existing record within your Excel table, I agree with @Drrickryp's thought almost, I think Patch function could achieve your needs.

If you want to use Patch function to update an existing record, you must find the record you want to update firstly. Please take a try with the following formula:

Patch(
    Table1,
    Lookup(Table1,Barcode=Label6.Text), /* <--- Find the specific record you want to update */
    {
      Barcode: "2", 
      Job_type: Dropdown2.Selected.Value
    }
)

In addition, you could also take a try to achieve your needs using UpdateIf function. Please take a try with the following formula:

UpdateIf(
     Table1,
     Barcode=Label6.Text,
     {
       Barcode: "2", 
       Job_type: Dropdown2.Selected.Value
     }
)

More details about Patch function, UpdateIf function and Collect function within PowerApps, please check the following article:

Patch function, UpdateIf function and Collect function

Based on the error message that you provided, I suppose that you or other users you share the Excel table data source with are opening (or editing) this Excel table.

If you or other users you share the Excel table data source with are opening (or editing) this Excel table, the Patch function (or other update operation) would not work. This Patch function (or other update operation) would not work until you close this Excel table or other users (you share this Excel table with) close this Excel table.

Note: If you share an app with other users within your Organization, you may need to share the data source (e.g. The Excel table data source) with them, so they could be able to access your data source (e.g. The Excel table data source). More details about sharing data source, please check here.

More details about sharing a Excel table data source, please check the following article:

https://docs.microsoft.com/en-us/powerapps/maker/canvas-apps/share-app-data

Note: You could not share the Excel table data source with other users via the PowerApps, you could only share the Excel table data source with other users within your OneDrive. Only after you share this Excel table data source with other users within your OneDrive (service),  the other users could access your Excel table data source within this shared app.

Best regards,

Kris

[GrantAstley]

Hi Kris

Thank you for your time and detailed response, as stated at the start all I need to do is append lines to the spread sheet as such collect should suffice.  

At the moment only I have access to the spread sheet, once I get it working with one user I will start sharing and making sure it works with everyone that needs it.  

I make sure that I close the workbook before trying to add data to it via the app.  I still get the error that the file is locked.  The sharing for the file is set to private and I have permission for full control, everyone else has read only permissions. 

I just shared again with myself allowing editing and now 

I have been able to edit it using Patch as below

Patch(Table1, LookUp(Table1,Barcode=Label6.Text),{ Barcode: "1", Job_type: Dropdown2.Selected.Value})

This updates the Job_type column to the selected item where the barcode column is 1, note in this test Label6.Text = 1

However using Collect does not make any changes, is this syntax correct to achieve the following?

Collect( Table1, { Barcode: "1", Job_type: Dropdown2.Selected.Value})

I would like to add a row to the table with barcode column = 1 and the Job_Type column = the selected drop down item.

Do you have any further thoughts/ideas?

[Drrickryp]

Hi @GrantAstley,

 Not sure why the Collect function doesn't update but 

Patch(Table1, Defaults(Table1),{ Barcode: "1", Job_type: Dropdown2.Selected.Value}) should work unless you have a required column that is not being patched.

[GrantAstley · Accepted solution]

I got this to work with the following code

Patch(Table1,First(Filter(Table1,Barcode = "0")),{Barcode: "1", Job_type: Job_Type_ddm.Selected.Value,Job_Description: Job_Desc_txt.Text,Weight_After: After_txt.Text, DT: DatePicker1.SelectedDate})

I filled all rows under Barcode with "0" this value then gets replaced with the actual barcode number when a new line is updated.

I re-shared with myself once again enabling updates and now all seems to be working.

Note: Collect will still not work, however this accomplishes the same thing.

Thanx all for your help.

Cheers

Grant