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

Re: Add data to table in excel

FYR

GrantAstley
Level: Powered On

Re: Add data to table in excel

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

Community Support Team
Community Support Team

Re: Add data to table in excel

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

 

 

Community Support Team _ Kris Dai
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
GrantAstley
Level: Powered On

Re: Add data to table in excel

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?

Super User
Super User

Re: Add data to table in excel

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

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 (4,922)