cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
jdhough55
Regular Visitor

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 III
Super User III

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.

View solution in original post

Helpful resources

Announcements
PA User Group

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

secondImage

Demo Extravaganza is Back!

We are excited to announce that Demo Extravaganza for 2021 has started!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

Power Apps June 2021

June Power Apps Community Call

Did you miss the call? Check out the recording here!

Top Solution Authors
Top Kudoed Authors
Users online (41,710)