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

Add data to table in excel

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

 

 

 

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
GrantAstley
Level: Powered On

Re: Add data to table in excel

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

 

View solution in original post

15 REPLIES 15
Pat_Wickramage
Level: Powered On

Re: Add data to table in excel

Have you tried using Patch ?

Its a good one to edit existing data.

Use 'Lookup' to locate the item & then use Patch to edit.

GrantAstley
Level: Powered On

Re: Add data to table in excel

Hi, yes I have tried Patch as well, however I did not use lookup to locate the item I just put Patch where Collect is in the above.   Once again it did nothing.

I am wondering if there is a permission issue or somthing similar?

Super User
Super User

Re: Add data to table in excel

Hi Grant,

 

You need to Identify the row that will be patched.  In your case, since you are appending to the table, use the syntax

 

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

This will create a new record in your table.

If you are editing a previously created record and want to write back the changes, you would have to identify the record being changed, for example, 

Patch(Table1, Lookup(Table1,ID=ThisItem.ID),{ Barcode: "2", Job_type: Dropdown2.Selected.Value})

 

For more information on the patch function: https://docs.microsoft.com/en-us/powerapps/maker/canvas-apps/functions/function-patch

GrantAstley
Level: Powered On

Re: Add data to table in excel

Hi thank you for your sugestion however still the same thing happens.  It looks like it should be working, however nothing changes in the spread sheet.

Super User
Super User

Re: Add data to table in excel

hmm,

 

Could you append a screenshot of your excel table please?

 

Pat_Wickramage
Level: Powered On

Re: Add data to table in excel

Hi @GrantAstley,

 

Do you get any error in red when you try to post your entry?

Make sure that you have given read/write permission to the file & its not open while you are trying to post the entry.

GrantAstley
Level: Powered On

Re: Add data to table in excel

Just found an error msg as a red cross came up above the save button that the file is locked.  This only came up when using the following

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

Now to figure out how to unlock it?

Thanx heaps for your help

 

Pat_Wickramage
Level: Powered On

Re: Add data to table in excel

Yeap. This means that powerapps cnt write to the file.

Like I said, make sure that the file is shared & its not open when you try to post the entry.

GrantAstley
Level: Powered On

Re: Add data to table in excel

hi do you know how to share the file with powerapps?  Seems I can only share with people?

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 (5,561)