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,
Solved! Go to Solution.
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 :
You can stop the loop when the first query list action doesn't return any result.
Hope this works
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.
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 :
Hope this helps
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.
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).
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".
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?
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 ?