Answered

Want to use SharePointList instead of Collection,but don't know how to convert collection formula into sharepoint patching.

(0) Share

Report

Posted on by Community Power Pla... Microsoft Employee

Hi, actually i am a novice learner of powerapps.I have made an app with the help of tutorial and guidance that i found. But i am stuck between Collection and Sharepoint List. Can you help me how to convert all the formula(for collection) into formula for Sharepoint List?

For example:

I have a collection named :TicketCollect2 and

a sharepoint list named: TicketCollect1.

but all the formula like
-->CountRows(Filter(TicketCollect2,Status="In Progress")) are working only for collection list "TicketCollect2",Not working for sharepoint list "TicketCollect1".

I am also attaching how i have created my Collection and Sharepoint list for you to give me proper direction in case of my fault.

Code:

Collect(TicketCollect2,{CreatedBy:createdby1_1.Text,Subject:Subject1_1.Text,Description:Description1_1.Text,AreaName:appdrop_1.Selected.Value,Application_Name:areadrop_3.Selected.Value,ID:Text(CountRows(TicketCollect2)+1),Status:"New",DateCreated:Text(Today()),DateClosed:Text(Today())});

Patch(TicketCollect1,Defaults(TicketCollect1);{CreatedBy:createdby1_1.Text,Subject:Subject1_1.Text,Description:Description1_1.Text,AreaName:appdrop_1.Selected.Value,Application_Name:areadrop_3.Selected.Value});

ForAll(TicketCollect2,Patch(TicketCollect1,Defaults(TicketCollect1),{CreatedBy:CreatedBy,Subject:Subject,Description:Description,AreaName:AreaName,Application_Name:Application_Name}));

Categories:

Building Power Apps

All responses (8)

Answers (1)

Sort by

v-siky-msft Microsoft Employee on at

Like
a
(0)

Report
Copy link

Link copied!
Hi @MKMUMU ,

What is the column type of Status in sharepoint list?

If it is Choices type, you need to use Status.Value code to reference its column value since Choices is a complex type column in sharepoint. Please try this:

CountRows(Filter(TicketCollect2,Status.Value="In Progress"))

The Patch and ForAll function syntax looks good, I just want to mention that don't forget the Required column when patch a new record and keep the column type consistent.

Hope this helps.

Sik
Community Power Pla... Microsoft Employee on at

Like
a
(0)

Report
Copy link

Link copied!

Hi @ v-siky-msft,
The formula are working perfect for collection, but missing share point list. I don't know how to patch all these data from Collection to Sharepoint list.

1)I am unable to create an auto incremental ID column in the Share point list where ID will be auto incremented serially with the submission of Ticket Request.
2)DateCreated column will be auto filled with the submission of Ticket Request.
3)Status will be "New" when ticket request is submitted.When admin will change the ticket status from "New" to Closed, Sharepoint List will be updated with that status.
Verified answer

v-siky-msft Microsoft Employee on at

Like
a
(1)

Report
Copy link

Link copied!
Hi @MKMUMU ,

What is the column type of DateCreated and Status column? This is important since we need to keep value consistent with the column types. I assume the DateCreated column is Date type and Status column is Choice type.

The ID column is system predefined index column for sharepoint list, you are unable to modify it, so you have to create an new Number ID column, e.g. TicketID.

I found the incremental ID column work properly in collection, we just need to use ForAll/Patch function to save the collection to shareopint list.

Please try this:

ForAll(TicketCollect2,Patch(TicketCollect1,Defaults(TicketCollect1),{CreatedBy:CreatedBy,Subject:Subject,Description:Description,AreaName:AreaName,Application_Name:Application_Name, Status: {Value: Status}, DateCreated:Now(), TicketID: Value(ID)}));

Hope this helps.

Sik
Community Power Pla... Microsoft Employee on at

Like
a
(0)

Report
Copy link

Link copied!

Hi Sik,
Thank You for letting your helping hand out to my problems.Your solution is working really fine, but there is a problem. When i am entering one data, sharepoint list is generating 2 records against 1 submission. Here i am attaching the screenshot of the sharepoint list.

##Although i have not changed the status of the newly generated ticket, it is auto generating an extra record "Closed" in the SP List when i submit a New ticket.
## Here i am also attaching my code which is being used (in case of collection) for updating Ticket status

Ticket Status updating Code
If(TextBox5_25.Text<>"Closed",
UpdateIf(TicketCollect2,ID=EditRecord.ID,{Status:TextBox5_25.Text}),
UpdateIf(TicketCollect2,ID=EditRecord.ID,{Status:TextBox5_25.Text}));
If(TextBox5_25.Text<>"Closed",
Office365Outlook.SendEmailV2(
First(
Sort(
TicketsCollect1,
Modified,
Descending
)
).'Created By'.Email,
"Ticket Resolution Confirmation",
"Your issue has been solved,hence your ticket has been closed"
));
v-siky-msft Microsoft Employee on at

Like
a
(0)

Report
Copy link

Link copied!

Hi @MKMUMU ,

I can't judge the issue is from the Ticket Status updating Code.

In general, since the sharepoint list record is generated from the collection, so I would suggest you to first check the collection items to see if there is an extra record in it.

sik
Community Power Pla... Microsoft Employee on at

Like
a
(0)

Report
Copy link

Link copied!

Hi Sik,
I had checked in the collection list,there is no extra record. Extra is created in SP list.
Community Power Pla... Microsoft Employee on at

Like
a
(0)

Report
Copy link

Link copied!

Thank You Sik....Really thanks a lot. It is working fine now. I had used patch and For all both, thats why it was creating double record.
Community Power Pla... Microsoft Employee on at

Like
a
(0)

Report
Copy link

Link copied!

Hi @v-siky-msft ,
I am facing error in this code when i converted collection name into SP ist name.Below code is used for taking me into another screen where status are changed.Can you tell me what should be done to remove error?

Set(EditRecord, ThisItem);
Navigate(ticketdetailspage,ScreenTransition.Fade,{type:ThisItem.Status,subjectdisabled:true,subjectfill:RGBA(0,0,0,0),subject_visible:true,description_disabled:true,description_fill:RGBA(0,0,0,0),description_visible:true})