How to combine multiple "like" items into one item

[gbaker88]

Hi,

I currently have a list that has the following format:

ProjectID          COMP             Hours

Test1                    VEN               44

Test1                    VEN               36

Test1                    NOM               18

I am trying to run a workflow that will look at the values within ProjectID and COMP, and if they match up loop through the list to get one line.

Based on the example above the returning result would, hypothetically, be:

ProjectID          COMP               Hours

Test1                    VEN                    80

Test1                    NOM                    18

Would someone be able to assist in designing a workflow for this? I've tried querying the list with a collection operations, but feel like I am missing something.

Thanks in advance,

Greg

[gbaker88]

Any thoughts?

Thanks,

Greg

[cju]

I think you can achieve this with a site workflow.

In this workflow you should first initialise a number workflow variable to 0 and then perform a loop.

In this loop :

• query your list with the query list action in which you sort the items by ID (ascending) and get only the first item which ID is greater than your number workflow variable.
• Set your number workflow variable with the ID of the retrieved item.
• Then perform another query to your list to get the other items which have the same ProjectID and COMP.
• Sum their hours with the hours of the first retrieved item and update the hours column of the first retrieved item with the sum that you performed.
• Delete the items that you retrieved in the second query list action.
• Perform a commit pending changes. I'm not sure if the items will be deleted at this step or later, that's why I advise you to add an action to wait for 5 minutes.

You can stop the loop when the first query list action doesn't return any result.

Hope this works

[gbaker88]

Hi Caroline,

Thank you for the response. I am fairly new to this so am trying to follow step-by-step here. On the second query "Then perform another query to your list to get the other items which have the same ProjectID and COMP.", how am I suppose to set that query? In the past I've tried setting where ProjectID is equal to ProjectID and COMP is equal to COMP, but I think I'm missing something.

Currently, I have Loop (without a condition attached) > Query List with a filter of ID is greater than FN-MIN(Workflow Variable), sorted ascending > Set WF Variable to ID

For the first Query am I suppose to store each field in a unique variable as well (hours, COMP, ProjectID)?

I think gathering that information would help with the start of this, then I'll be able to look at the following steps after.

Thanks,

Greg

[cju]

Hi Greg,

For the loop's condition, you can create a yes/no variable (for example, isItemToProcess) and set its default value to Yes. And set the loop's condition as isItemToProcess equals Yes.

As you said, in the first query, you should store each field in a unique variable (ID, hours, COMP, ProjectID).

Then you can add a "Set a condition" action to check if the variable where you've stored the ID of the first retrieved item is empty. If it's empty, set the isItemToProcess to False. If it's not empty (in the other branch of the set a condition action), add all your others actions :

• Set FN-MIN with the ID of the retrieved item
• Perform the second query (store the ID, hours in unique collection variables) : you're right : you can configure the filter of this query like this :
  • ProjectID equals the workflow variable in which you've stored the ProjectID of the first retrieved item
  • AND COMP equals the workflow variable in which you've stored the COMP of the first retrieved item
  • AND ID is not equal to the workflow variable in which you've stored the ID of the first retrieved item
• Sum the hours
• Delete the items retrieved in the second query (you can perform a foreach on the collection variable where you've stored the ID of the retrieved items from the second query and in the foreach delete the item where the ID equals the current ID)
• Perform a commit pending changes and wait for 5 minutes if the safe looping is disabled (if it's not disabled in the central administration then it will wait for 5 minutes)

Hope this helps

[gbaker88]

Thanks again! Running in to another snag - I am unable to set the Number WF variable to FN-Min ID. The ID field doesn't allow for that to occur.

-Greg

[cju]

The variable where you stored the ID of the first retrieved item is of type single line of text. You should first convert it to number to be able to set your number variable with it. There is an action to do the conversion called "Convert value" in Operations category (in the convert value action, you can directly store the result of the conversion in your number variable).

[gbaker88]

Thanks Jung - I was able to get two columns to add up, but was never able to get to the third value (probably due to my limitations on this).

It turns out the process has flipped and I am looking to do this in a different way now. If I need to create another discussion I can do so, but wanted to try here first.

I have a text String from one list that needs to have "like" values combined and created in another list. The fields you see below are part of a choice field (multi-select), if the values have the same COMP (value in center with caps) and role (value to the right of COMP), then they need to be rolled into one item and the hours need to be summed, while at the same time the Colors need to be "joined".

Example:

With this value here the end result that we are looking for on the next list would be 3 lines:

Color                                   COMP                    Hours                          Role

Blue; Yellow; Pink                COMP                      196                       Consultant

Red                                       ALPHA                      14                        Analyst

Silver                                    COMP                        10                        Analyst

I've been trying to complete this with the Regular Expression, but am unable to break apart then add back together before creating the new list item.

Would you have any ideas on how to do this?

Thanks again!

Greg

[gbaker88]

Thanks Caroline*, I'm so use to seeing names listed Last,First

[cju]

Hi Greg,

No problem

When you say that you was unable to get 3 columns, are you talking about the query list action ?

What you'd like to do is more complicated now...

The values are only in one text field or are they in list items ?

Have you also built a form where the user can populate those values or these values comes from another system ?