Collect formula not writing to SharePoint list

(1) Share

Report

Posted on by elleeyl

121

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!

Categories:

Building Power Apps

All responses (9)

Answers (1)

Sort by

RezaDorrani 12,145 on at

Like
a
(1)

Report
Copy link

Link copied!

@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 Microsoft Employee on at

Like
a
(0)

Report
Copy link

Link copied!
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
elleeyl 121 on at

Like
a
(0)

Report
Copy link

Link copied!

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!
Verified answer

yashag2255 24,769 Super User 2024 Season 1 on at

Like
a
(1)

Report
Copy link

Link copied!

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!
v-siky-msft Microsoft Employee on at

Like
a
(0)

Report
Copy link

Link copied!

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?
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
elleeyl 121 on at

Like
a
(0)

Report
Copy link

Link copied!

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.

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

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.
Community Power Pla... Microsoft Employee on at

Like
a
(0)

Report
Copy link

Link copied!

ForAll(CollectionName, Patch(SPListName, Defaults(SPListName), { SPColomn:ThisRecord.CollectionColumn}))
Community Power Pla... Microsoft Employee on at

Like
a
(0)

Report
Copy link

Link copied!

ForAll(CollectionName, Patch(SPListName, Defaults(SPListName), { SPColumnName:ThisRecord.CollectionColumnName}))
maxtarneberg 116 on at

Like
a
(0)

Report
Copy link

Link copied!

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 😊