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

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

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}));

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted

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

View solution in original post

8 REPLIES 8
Highlighted
Community Support
Community Support

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

Highlighted

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.

Highlighted

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

View solution in original post

Highlighted

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"
));

 

Highlighted

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

Highlighted

Hi Sik,

I had checked in the collection list,there is no extra record. Extra is created in SP list.

Highlighted

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.

Highlighted

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

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

News & Announcements

Community Blog

Stay up tp date on the latest blogs and activities in the community News & Announcements.

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Community Highlights

Community Highlights

Check out the Power Platform Community Highlights

Top Solution Authors
Top Kudoed Authors
Users online (9,075)