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

Sharepoint Row creation, and filter checking

Hi there, 

I have a list which looks like this : 

 

Screenshot 2020-12-12 at 17.53.31.png

whenever a automate flow changes the status to Completed, then another needs to be created with the Audit week Number incremented. 

If a new row gets attempted to get created (with the same Employee ID) and the status is 'In Progress' then I need an error up saying not possible. 

I also need to be able to create a new row if the employee ID is different than any other row (if it is in Progress or in completed) .

 

I have been using Collect (Clear collect) to get the data from SharePoint , so I can filter it all. Here is where I have got to and I am banging my head because they seem to interfere with each other. 

 

ForAll( Filter( AuditData, Status = "In Progress" && EmployeeID = Candidate_EmployeeID.Text), Collect( InProgress, ThisRecord ) );

Do not patch
ForAll( Filter( AuditData, Status = "Completed" && EmployeeID = Candidate_EmployeeID.Text), Collect( InComplete, ThisRecord ) );

Patch a new row with InProgress. 

This seems to be ok. 

But if I put the code in for if it is a new employeeID then it goes completely wrong.. Am I doing it the correct way with Patch and collections ? I just need a fresh pair of eyes on this if anyone can help would be awesome. 

 

Thanks, Jason 

 

1 ACCEPTED SOLUTION

Accepted Solutions

Hi @jdhough55 ,

This is a separate issue to the current item as it is looking for records where the user matches. Again note that the With() statement resulting record numbers need to be within your Delegation limit.

With(
      wData:
      Filter(
         AuditData,
         Status="In Progress" || Status = "Completed"
      )
   },
   ForAll(
      wData,
      If(
         CountRows(
            Filter(
               wData,
               EmployeeID = Candidate_EmployeeID.Text
            )
         )=0,
         Collect(YourNewCollection,ThisRecord)
      )
   )
)

You can also do this

With(
      wData:
      Filter(
         AuditData,
         Status="In Progress" || Status = "Completed"
      )
   },
   ForAll(
      wData,
      If(
         IsBlank(
            LookUp(
               wData,
               EmployeeID = Candidate_EmployeeID.Text
            ).EmployeeID
         ),
         Collect(YourNewCollection,ThisRecord)
      )
   )
)

Note in both cases CountRows() and IsBlank() statements are not Delegable, hence the need to filter on With()

 

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

View solution in original post

5 REPLIES 5
WarrenBelz
Super User
Super User

Hi @jdhough55 ,

I am struggling a bit here relating what you are trying to achieve in relation to the code you have supplied, but making a start on the "clashing" issue, you could combine this into one sequence with something like this

With(
      wEmp:
      Filter(
         AuditData,
         EmployeeID = Candidate_EmployeeID.Text
      )
   },
   ForAll(
      wEmp,
      Switch(
         Status,
         "In Progress",
         Collect(InProgress,ThisRecord),
         "Completed",
         Collect(InComplete,ThisRecord)
      )
   )
)

 

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

Hi, I am loving the With command.. I decided to do it - so I can create a collection with each scenario and then check the collection and patch accordingly. So for example I create a collection with the employeeid and the status (in progress) and if it is empty then you can create a new row with that employeeID. 

Hi @jdhough55 ,

I have done a blog on the With() statement for Delegation issues such as the one you have. What you are doing is essentially the structure I suggested. Is this now solved?

 

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

Yes it is working, but I need to Also do

'I also need to be able to create a new row if the employee ID is different than any other row (if it is in Progress or in completed) .' and I am attempting to do a FOR ALL if EmployeeID is NOT equal to EmloyeeID (on the form) then patch a new row, and I am now struggling with this final bit... its going to be a late night! 

Hi @jdhough55 ,

This is a separate issue to the current item as it is looking for records where the user matches. Again note that the With() statement resulting record numbers need to be within your Delegation limit.

With(
      wData:
      Filter(
         AuditData,
         Status="In Progress" || Status = "Completed"
      )
   },
   ForAll(
      wData,
      If(
         CountRows(
            Filter(
               wData,
               EmployeeID = Candidate_EmployeeID.Text
            )
         )=0,
         Collect(YourNewCollection,ThisRecord)
      )
   )
)

You can also do this

With(
      wData:
      Filter(
         AuditData,
         Status="In Progress" || Status = "Completed"
      )
   },
   ForAll(
      wData,
      If(
         IsBlank(
            LookUp(
               wData,
               EmployeeID = Candidate_EmployeeID.Text
            ).EmployeeID
         ),
         Collect(YourNewCollection,ThisRecord)
      )
   )
)

Note in both cases CountRows() and IsBlank() statements are not Delegable, hence the need to filter on With()

 

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

Helpful resources

Announcements
Power Platform Call June 2022 768x460.png

Power Platform Community Call

Join us for the next call on August 17, 2022 at 8am PDT.

Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

365 EduCon 768x460.png

Microsoft 365 EduCon

Join us for two optional days of workshops and a 3-day conference, you can choose from over 130 sessions in multiple tracks and 25 workshops.

Users online (2,619)