cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
elleeyl
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
yashag2255
Dual Super User II
Dual Super User II

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

9 REPLIES 9
RezaDorrani
Dual Super User II
Dual Super User II

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

 

v-siky-msft
Community Support
Community Support

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

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!

yashag2255
Dual Super User II
Dual Super User II

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

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

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.

hjoed
New Member

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

hjoed
New Member

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

maxtarneberg
Advocate II
Advocate II

Collect can definitely be used for SP lists. 

I have successfully been filling SP lists with a range of apps using the Collect function.

 

I normally collect to a local collection first, then collect to sharepoint, which gives an extra stage for flexibility, for example to implement offline functionality:

E.g.

Collect (colLocal, {Title: Textbox1.text, Name: Textbox2.Text})
;
SaveData(colLocal, "OfflineData")
;
Collect(SPList, colLocal)

;

Clear (colLocal)

 

- Column names should be exactly correct (as visible at the end of the URL in List Settings/Edit column - Changing a column name in SP is only superficial - name well from the start and avoid spaces/symbols)

- You must provide a value for required columns as @v-siky-msft pointed out. "Title" is automatically required on a new list, which caught me out this time and hence found myself on this thread 😊

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.

Top Solution Authors
Top Kudoed Authors
Users online (1,296)