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
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
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
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
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".
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
Thanks Caroline*, I'm so use to seeing names listed Last,First 
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 ?
Hi Caroline,
Just in my solution when summing the hours up it only combined 2 of the values rather than adding the new values that were added in as well.
Yea , good news is though is that the process won't change again. Here is some background on it. The values that you see belong to one choice field that is queried in through SQL (tied to external connections in our InfoPath form). We don't have Nintex forms so we are having to use InfoPath.
Once a value is populated into a certain field, then the query runs and pulls all values that tie to that value. What it pulls in is resourcing data for certain projects: Solutions (in this case the color), COMP (specialty needed), hours sold, and the Role that it is assigned to that COMP/Solution. If it is the same COMP and same Role then it will be the same associate working those hours and those different Solutions. This is why we are trying to sum the hours and join the solutions together. It will allow for us to track each resource, by project and the hours they have to complete the Solutions.
Hope that helps.
-Greg
ok, it's more clear now.
Do you have the possibility to store those value as XML in a text field ?
It will be easier to implement a solution with an XML.
Through the query? Bare with me I'm pretty new to this so I do not fully understand everything .
I've been trying to use a combo of the workflow actions: Collection Operation / Regex / For Each
-Greg
Sorry it's a bit confused now.
what is this choice field : a SharePoint column or a workflow variable or something that comes from an external system ?
Do you execute a SQL query in the workflow ? If so, which information to get ?
Sorry about that. The choice field is a SharePoint column that queries data from SQL once another field has been entered in.
Before Value Added:
After Value Added:
Once the values are selected and the form is completed we are wanting to send this data to our "contacts list", the current list is the "projects list".
So based on this query we would want to submit to the contacts list as follows:
COMP Role Hours
ADVARCH Secondary Architect 500
EM Controller 177
OTHER Leader 2000
CTD Initial Architect 114
CTD Engineer 660
ok I understand.
Unfortunately, it's complicated to do what you'd like to do.
The only way that I can imagine is to create a temporary list where you would add all the selected choices with one column for COMP, one for role and one for hours and do what I described in my previous answers...it would be lots of actions in your workflow and a temporary list but I don't know how you could do it differently.
Hey Caroline,
So I was able to do this through a bunch of separate collection operations, however, there is more . The form that I have, which queries all the data in, has multiple fields that it populates. For example there are 8 other fields that pull in data besides "Consulting". These fields are filtered in InfoPath so the values that pull in represent each organization correctly.
These fields can have like values as well and I'm trying to combine those like values. I have successfully combined all like values in each individual field, but am struggling to do so across the different fields.
In the example above I combined the CTD:Engineer values together to get 660 hours and now I have another field called "Additional Consutling" that has CTD:Engineer. and 120 hours.
I keep trying to do a comparison to search through the different collection then combine the values, but the workflow never makes it to that step and errors out. I've tried drilling into the workflow error, but it times out and never opens, assume this has to do with the size of the workflow (close to 200 steps).
Is there a way to combine two fields that have the same value in an easier manner? Sorry if this is confusing.
-Greg
Hi Greg,
You can maybe separate the treatment in several workflows (one workflow per field treatment) and call a workflow after another has terminated.
You can also take a look at Manfred's answer to this post, in can also help you :
Re: Nintex repeating section
Hope this helps
Hi Caroline,
Unfortunately my company did not purchase all of the different workflow tasks so I am unable to use Query XML.
-Greg
It should be available in all versions of Nintex Workflow. Maybe the administrator hasn't enabled the use of this action.
Well, splitting your workflow in several workflows should help you
Yeah, I don't think it was turned on, and unfortunately don't have the power to make that decision. I'll go ahead and keep trying with splitting the workflows, thanks again.
-Greg
no problem
Good luck
Hi Ajay - yes I was able to modify my workflow to combine all collections from the beginning and align them in one variable per grouping.
-Greg
I can share the steps
1- Created separate Reg expressions to get the different string values.
2 - Created a For Each for each field and stored the results in a string
A) Collection Operation below each For Each to add the previous strings to a Combined Collection
3 - Created another For each to store result in a separate string combined element, with a number combined index
A) Reg Expression to Parse the string variable on the : and use the string combined element and input text with a new variable (string parsing having the results stored in it
Collection Operation (3 of them - 1 for each, excluding hours) to pull apart the string based on the :. Created an Index with default values (programming looks at this as 0 being first occurrence, 1 being second, 2 being third and so on. Stored the result in a new variable for each
C) Concatenated my two strings where I was looking for common values and stored result in a Combined String COMP_Role
D) Collection operation to get hours (similar as above)
a) Reg Expression (d+(?= Hours)) to extract hours out and store results in collection
b) Collection operation to store hours into a string
E) Col Op to see if my target collection Exists within string, store answer in Yes/No variable
F) Condition to see if Yes/No value is yes or no. If No, Collection operation to add all previous collections from step . If Yes, created another yes/no variable and set to no. Created loop counter which is then set to 0
G) Still within the Yes side I created another For Each that is storing the Collection into another variable (with the appropriate index selected). Have it set to stop processing when my original yes/no in step F) is true.
H) Set Condition - If any value from workflow data from new looping string equals original string. No, moves on. Yes, set loop values again.
I) Set loop counter variables to 1
J) 3 Collection Ops to Get all values from original collection (3 separate collections), with an index (indexes are key to ensure the right value is pulled in) and store result in string.
K) Add hours from new string created in J) to original hours value - store in another num collection
L) Remove previous entry from hours collection, with index setup
M) Put sum of hours back in hours collection - Original Collection for hours Add to num collection from K) - Have index setup accordingly
N) Concatenate the Solutions, between two collections created earlier and store result in concat solution string.
O) Put Concat Solution back into solution counter (same concept as row M))
4) Set another variable "Create item counter" to 0
5) Create another For Each
A) For each Collection store result in a string variable
Col Opt to get a piece of that string by using an index (Create Item Counter - step 4) variable and store in a Create variable
C) Since I have everything separated besides the COMP_ROLE I need to create a Regex to split at the : and store in a new variable
D) Create 3 more COL Operations for each individual piece of the string and index according and store in new create variables
E) For any field that requires the items to be combined rather than joined you'll need to Replace the text. Mine were separated by a comma initially and SharePoint would treat that as one giant string and I want multiple strings (so it would appear in a choice field as being different selections). To do this use replace text and put in ;# into the replacement text and store result in another create item
F) Create Item in new list.
Hope that helps!
-Greg
