Skip to main content

Notifications

Power Automate - Building Flows
Answered

Best way to split SP list rows based on column single/duplicate values to different SP lists

Posted on by 126

Hello, 

 

I have a list of projects in an SP list. Some projects are listed multiple times as there might be several departments working on the project. 

I would like to take the projects that are occurring on the list several times and create a record of that project in another list A (even without the departments)  and take the single department projects and create items in different list B. 

Ajwou_0-1631263309282.png

 

The flow I was playing with looks like this:

Ajwou_1-1631263349368.png

 

However, I am not sure what 'condition I would have to have there to say IF the Project ID is on the list more than once create record here (list A), if only once then create record there (list B)

 

  • annajhaveri Profile Picture
    annajhaveri 8,533 on at
    Re: Best way to split SP list rows based on column single/duplicate values to different SP lists

    @Ajwou  you will need a value in the list to identify if the record already exists in the list or not? Do you already have that value in that list, if yes, then before adding a record, add Get Items action to query the list using that value (you can specify that in  filter query parameter of Get Items e.g. columnname eq 'value'), then you can check length of items returned by get items to determine if the record already exists or not.  

  • Ajwou Profile Picture
    Ajwou 126 on at
    Re: Best way to split SP list rows based on column single/duplicate values to different SP lists

    @annajhaveri  - apologies for opening this again, but I was wondering, as you know the current flow well, whether there would be an easy way to include condition in the item creation stage in the flow saying 'if the record already exists in the new lists, then don't create one'.  

  • Ajwou Profile Picture
    Ajwou 126 on at
    Re: Best way to split SP list rows based on column single/duplicate values to different SP lists

    @annajhaveri  - yes, that worked perfectly. Thank you very much for your help!

  • annajhaveri Profile Picture
    annajhaveri 8,533 on at
    Re: Best way to split SP list rows based on column single/duplicate values to different SP lists

    @Ajwou  so if your list has same data for all same project IDs then you can use below expression to get the data, you need to provide your column name in place of ProjectName, Title

    body('Filter_array')?[0]?['ProjectName']
    body('Filter_array')?[0]?['Title']

     

  • Ajwou Profile Picture
    Ajwou 126 on at
    Re: Best way to split SP list rows based on column single/duplicate values to different SP lists

    @annajhaveri - Thank you for your help. I have tried that option and that sent the data to separate lists so that's brill. However the output will be Departments only (I assume that's because only departments have been mapped).

    Ajwou_0-1631693078743.png

    Please would there be a way to create the records based on the current splitting, however include features from each column so they can be assigned to relevant columns (as in the initial list)

    See my current Condition flow below (I am looking for adding Project ID/Name/Accepted/Price/Hours to each item)

    Ajwou_1-1631693420899.png

     

  • annajhaveri Profile Picture
    annajhaveri 8,533 on at
    Re: Best way to split SP list rows based on column single/duplicate values to different SP lists

    @Ajwou  the steps should be as following screenshot

     

    annajhaveri_0-1631532216284.png

     

  • Ajwou Profile Picture
    Ajwou 126 on at
    Re: Best way to split SP list rows based on column single/duplicate values to different SP lists

    @annajhaveri Thank you for putting the details together. Just to clarify, would all the steps from Step 4 be inside the loop (step 3)?

     

    When I included all steps from 4 in the Apply to each, I get the original list items in the list A (5x) and all projects from the original list in list B.

  • Verified answer
    annajhaveri Profile Picture
    annajhaveri 8,533 on at
    Re: Best way to split SP list rows based on column single/duplicate values to different SP lists

    @Ajwou  so first you will have to get all unique projects and then loop through those unique project list, inside the loop get unique department of each project and if length of list of unique department is 1, then project id has one department and if length of list of unique department is greater tan 1, then project id has more departments.

     

    See below detailed steps:

     

    1. Add Select action after the Get Items action, select value from dynamic content of Get Items in From parameter. Map Project ID with project ID column as shown below

    annajhaveri_0-1631351846196.png

    2. Add Compose action and use following expression in compose action. This will give you list of unique project IDs.

    union(body('Select'),body('Select'))

    annajhaveri_2-1631351977441.png

     

     

    3. Now to loop through unique project ids, add apply to each action and use output of Compose action

    annajhaveri_1-1631351950186.png

     

    4. Now under Apply to each action, add compose action and configure following expression

    items('Apply_to_each_5')?['ProjectID']

    annajhaveri_3-1631352000176.png

     

    5. Now add filter array action, in from parameter select value from dynamic contents of Get Items. in filter configure it as shown below, on right side of filter use output of compose action we added in step 4.

    annajhaveri_4-1631352041164.png

     

    6. Now add Select action and in from parameter use output of filter array action from dynamic content. In map parameter configure as shown below, on right side of map parameter use item()?['Department'] expression

    annajhaveri_5-1631352184697.png

     

    7. Add compose action with following expression

    union(body('Select_2'),body('Select_2'))

     

    annajhaveri_6-1631352229251.png

     

     

    8. Now add condition action to check length of unique department list of the project

    annajhaveri_7-1631352286059.png

     

    length(outputs('Compose_9'))

     

    In the yes branch of condition, then as per your logic go to list A, in no branch go to list B

     

     

  • Ajwou Profile Picture
    Ajwou 126 on at
    Re: Best way to split SP list rows based on column single/duplicate values to different SP lists

    Hello, 

    I want to take the projects (project IDs) with only 1 department and send them to one list and then take the projects that have 2 or more departments into another list only as 1 record (the department would not be relevant at that stage). So if project ID has only 1 row then it would go to list B and if it has 2 or more rows then it would be send(created) in list A. Essentially testing if the project ID has 1 or more records and then recreating these records in different lists based on the condition result. 

  • annajhaveri Profile Picture
    annajhaveri 8,533 on at
    Re: Best way to split SP list rows based on column single/duplicate values to different SP lists

    @Ajwou  you want to get values of both Project ID and Departments and see if both of these values are repeating?

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

October 2024 Newsletter…

October 2024 Community Newsletter…

Community Update Oct 28…

Power Platform Community Update…

Tuesday Tip #4 How to Conntact Support…

Welcome to a brand new series, Tuesday Tips…

Leaderboard

#1
WarrenBelz Profile Picture

WarrenBelz 142,473

#2
RandyHayes Profile Picture

RandyHayes 76,308

#3
Pstork1 Profile Picture

Pstork1 63,659

Leaderboard