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

[Ajwou]

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. 

The flow I was playing with looks like this:

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]

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

[Ajwou]

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 · Accepted solution]

@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

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

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

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

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

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.

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

7. Add compose action with following expression

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

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

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]

@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.

[annajhaveri]

@Ajwou  the steps should be as following screenshot

[Ajwou]

@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).

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)

[annajhaveri]

@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]

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

[Ajwou]

@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'.  

[annajhaveri]

@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.