cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper III
Helper III

Collect formula not writing to SharePoint list

Hi Community,

 

I want to collect all data entered into a form to be populated onto a SharePoint List, which is the datasource. The formula is built into the "Create" button. However when I click on the button, there is no update to my SharePoint list, i.e. nothing is written to SharePoint. 

 

This is the formula I used.

Collect(ExpenseReport_2,{Title: DataCardValue1.Text, Name: DataCardValue3.Text, 'Email Address': DataCardValue4.Text, Department: DataCardValue5.Text, Purpose: DataCardValue6.Text, 'Claim Submission Date': DataCardValue7.SelectedDate, Status: "Open"});

Set(SelectedReport2, Last(ExpenseReport_2));

UpdateContext({Defaults: false, DefaultDate: Today(), DefaultTextValue: "1"});
UpdateContext({Defaults: true, DefaultDate: Today(), DefaultTextValue: ""});
ClearCollect(DefaultRecord, "");
UpdateContext({clearTest_ExpenseHeaderPage:true});UpdateContext({clearTest_ExpenseHeaderPage:false}); Navigate(Test_CreateNewLineItem, None);

 

This is my SharePoint list columns.

SP columns.PNG

 

Appreciate any help and advice. Thank you in advance!

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Dual Super User III
Dual Super User III

Re: Collect formula not writing to SharePoint list

Hey @elleeyl 

 

To submit the data into the SP list, you need to use Patch function. Collect is used for local collections only. 
 
You may need to update the formula as:
 
Patch(DataSourceName, Defaults(DataSourceName), {'Expense Type': DataCardValue12.Text ,'Expense Amount': DataCardValue13.Text ,Title: DataCardValue1.Text, Name: DataCardValue3.Text, 'Email Address': DataCardValue4.Text, Department: DataCardValue5.Text, Purpose: DataCardValue6.Text, 'Claim Submission Date': DataCardValue7.SelectedDate, Status: "Open"})
 
This is used to create a New record. DataCardValue12 is the control for Expense Type and DataCardValue13 is for the Expense Amount.
 
If you want to update an existing record:
Patch(DataSourceName, LookUp(DataSourceName, Col1 = "Value1"), {'Expense Type': DataCardValue12.Text ,'Expense Amount': DataCardValue13.Text ,Title: DataCardValue1.Text, Name: DataCardValue3.Text, 'Email Address': DataCardValue4.Text, Department: DataCardValue5.Text, Purpose: DataCardValue6.Text, 'Claim Submission Date': DataCardValue7.SelectedDate, Status: "Open"})
 
Col1 and Value1 needs to be replaced with the column and the value for this column to uniquely identify the record for updation.
 
Hope this Helps!

If this reply has answered your question or solved your issue, please mark this question as answered. Answered questions helps users in the future who may have the same issue or question quickly find a resolution via search. If you liked my response, please consider giving it a thumbs up. THANKS!

View solution in original post

8 REPLIES 8
Highlighted
Dual Super User II
Dual Super User II

Re: Collect formula not writing to SharePoint list

@elleeyl 

 

Collect will only store data locally

 

to post data to PowerApps use the Patch command

https://docs.microsoft.com/en-us/powerapps/maker/canvas-apps/functions/function-patch

 

--------------------------------------------------------------------------------
If this post helps answer your question, please click on “Accept as Solution” to help other members find it more quickly. If you thought this post was helpful, please give it a Thumbs Up.

 

Highlighted
Community Support
Community Support

Re: Collect formula not writing to SharePoint list

Hi @elleeyl,

 

Is there any error info shown in the formula bar?

Collect function is supported to save data to data source.

According your post, I find that two required fields(Expense Type & Expense Amount) are not collected to SPlist. This is why there is no update to my SharePoint list .

Please refer to the following formula, and replace <> with appropriate values.

Collect(ExpenseReport_2,{'Expense Type': <TheExpenseType> ,'Expense Amount': <TheExpenseAmount> ,Title: DataCardValue1.Text, Name: DataCardValue3.Text, 'Email Address': DataCardValue4.Text, Department: DataCardValue5.Text, Purpose: DataCardValue6.Text, 'Claim Submission Date': DataCardValue7.SelectedDate, Status: "Open"});

Hope this helps.

Best regards,

Sik

Highlighted
Helper III
Helper III

Re: Collect formula not writing to SharePoint list

Hi @v-siky-msft ,

 

Regarding Expense Type & Expense Amount, I have them in another screen. My idea is that the screen mentioned here would be an expense report header, and when we click "Create", it will bring me to the next screen where I can enter expense details, i.e. Expense Type and Expense Amount. When those are entered, I will click on Submit.

 

Is this the right way to do this? When I filled up everything on both screens and click on Submit, there is nothing on SP.

 

Thanks!

Highlighted
Dual Super User III
Dual Super User III

Re: Collect formula not writing to SharePoint list

Hey @elleeyl 

 

To submit the data into the SP list, you need to use Patch function. Collect is used for local collections only. 
 
You may need to update the formula as:
 
Patch(DataSourceName, Defaults(DataSourceName), {'Expense Type': DataCardValue12.Text ,'Expense Amount': DataCardValue13.Text ,Title: DataCardValue1.Text, Name: DataCardValue3.Text, 'Email Address': DataCardValue4.Text, Department: DataCardValue5.Text, Purpose: DataCardValue6.Text, 'Claim Submission Date': DataCardValue7.SelectedDate, Status: "Open"})
 
This is used to create a New record. DataCardValue12 is the control for Expense Type and DataCardValue13 is for the Expense Amount.
 
If you want to update an existing record:
Patch(DataSourceName, LookUp(DataSourceName, Col1 = "Value1"), {'Expense Type': DataCardValue12.Text ,'Expense Amount': DataCardValue13.Text ,Title: DataCardValue1.Text, Name: DataCardValue3.Text, 'Email Address': DataCardValue4.Text, Department: DataCardValue5.Text, Purpose: DataCardValue6.Text, 'Claim Submission Date': DataCardValue7.SelectedDate, Status: "Open"})
 
Col1 and Value1 needs to be replaced with the column and the value for this column to uniquely identify the record for updation.
 
Hope this Helps!

If this reply has answered your question or solved your issue, please mark this question as answered. Answered questions helps users in the future who may have the same issue or question quickly find a resolution via search. If you liked my response, please consider giving it a thumbs up. THANKS!

View solution in original post

Highlighted
Community Support
Community Support

Re: Collect formula not writing to SharePoint list

Hi @elleeyl ,

 

At least, I can't find any error on your collect function.

Did you try to put all required field to formual but still no data? can you share the formulas?

Annotation 2019-10-10 181503.png

You can also use patch function to check if the issue is casued by function or call values, just as Reza posted.

 

Best regards,

Sik

Highlighted
Helper III
Helper III

Re: Collect formula not writing to SharePoint list

Hi @yashag2255 ,

 

I have now used Patch to collect data. Formula on the expense header screen "Create" button OnSelect is

 

Patch(ExpenseReport_2,Defaults(ExpenseReport_2),{'Expense Type': DataCardValue2.Selected ,'Expense Amount': Value(DataCardValue30.Text), Title: DataCardValue1.Text, Name: DataCardValue3.Text, 'Email Address': DataCardValue4.Text, Department: DataCardValue5.Text, Purpose: DataCardValue6.Text, 'Claim Submission Date': DataCardValue7.SelectedDate,Status: "Open"});Set(SelectedReport2, Last(ExpenseReport_2));UpdateContext({Defaults: false, DefaultDate: Today(), DefaultTextValue: "1"});
UpdateContext({Defaults: true, DefaultDate: Today(), DefaultTextValue: ""});
ClearCollect(DefaultRecord, "");
UpdateContext({clearTest_ExpenseHeaderPage:true});UpdateContext({clearTest_ExpenseHeaderPage:false}); Navigate(Test_CreateNewLineItem, None);

 

Expense Type is a dropdown list, and Expense Amount is a currency value.

newexpensereport.PNG

 

When I click on "Create" button, it would bring me to the Expense Detail screen, which has this formula on both "Save and Add New Expense" button and "Save" button. 

newlineitem.PNG

Patch(ExpenseReport_2,Defaults(ExpenseReport_2), {'Expense Amount': Value(DataCardValue30.Text), 'Expense Type': DataCardValue2.Selected});UpdateContext({Defaults: false, DefaultDate: Today(), DefaultTextValue: "1"});
UpdateContext({Defaults: true, DefaultDate: Today(), DefaultTextValue: ""});
ClearCollect(DefaultRecord, "");
UpdateContext({clearTest_CreateNewLineItem:true});UpdateContext({clearTest_CreateNewLineItem:false});

 

When I click on Save, nothing is written to SharePoint. When I go back to the Edit mode, I see at the header screen against the Create button that error "ExpenseType: Field Required".  At the Details screen, I see against the Save buttons "Title: Field Required".  All details have been filled out beforehand though.

 

reporterror.png

All Fields are set as Required in SharePoint and on Powerapps.

 

(There was twice during my repeated testing that it got written to SharePoint, though the Expense Amount value is not captured. However now, it went back to not being able to write to SharePoint.)

 

Appreciate your advice. Thank you.

Highlighted
New Member

Re: Collect formula not writing to SharePoint list

ForAll(CollectionName, Patch(SPListName, Defaults(SPListName), { SPColomn:ThisRecord.CollectionColumn}))

Highlighted
New Member

Re: Collect formula not writing to SharePoint list

ForAll(CollectionName, Patch(SPListName, Defaults(SPListName), { SPColumnName:ThisRecord.CollectionColumnName}))

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Find your favorite faces from the community presenting at the Power Platform Community Conference!

Watch Now

Experience what’s next for Power Apps

See the latest Power Apps innovations, updates, and demos from the Microsoft Business Applications Launch Event.

Power Platform ISV Studio

Power Platform ISV Studio

ISV Studio is designed to become the go-to Power Platform destination for ISV’s to monitor & manage published applications.

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Top Solution Authors
Top Kudoed Authors
Users online (5,086)