You are viewing limited content. For full access, please sign in.

Question

Question

Modify a multi token variable at specific index location

Workflow
Updated March 4, 2019
asked on March 1, 2019

Seeking a bit of direction as I feel I'm going about this the wrong way.  I have a case management repository with thousands of cases assigned to about 100 investigators.  It is my goal to create a scheduled workflow run report that does all the math on a monthly basis and sends an Itemized report of how many cases were completed on a by each. Simple counting and automation of a routine report.

Ultimately I'm looking to populate an SQL table with the data so I can mail merge it into a report form template.  In order to do this, I need to create a pool of multi variable tokens that are all properly aligned and counted that I can push up into the Sql table. 

 

Example of what I'm doing

 

So what I am trying to do is parse through the search results and populate my group of multi tokens.  If its a new item in the list, I add it.  If it is existing I need to find where in the list it exists and change its count up 1.  Problem is I can't think of a way to target in on a multi tokens specific index location in order to change it.  Seems as though this should be relatively simple thing to do.  This would allow me to loop through the search results only once instead of doing multiple loops or a for each of a for each.

I know that I can create a unique list of investigators, then loop through the entire list counting by investigator.  A for each of a for each. But my original direction raises the question.

 

I have set a variable with a value from an index location or iteration.  How do I change a value of a multi token at a specific indexed location?  Without manipulating the group as a whole.  

 

I compare this to having to update phone number at say index 4  in a group of 10 phone numbers.

 

Any information/direction would be greatly appreciated.

 

 

 

 

 

 

 

 

 

0 0

Replies

replied on March 4, 2019 • Show version history

I don't think that there's a way to replace one single value. It might be easier to tear down the multi-value token and rebuild it each time through the loop. This is an old post, but @████████'s answer seems like it still applies.

Is there a reason you need to do this in Workflow? It would be much more efficient to run the query directly against the database. This query will do the aggregation that you describe up above.

DECLARE @TemplateId int = 1;
DECLARE @FruitFieldId int = 2;
DECLARE @StoreFieldId int = 3;

WITH Fields(EntryId, Fruit, Store)
AS
(
	SELECT toc.tocid AS EntryId, FruitField.str_val AS Fruit, StoreField.str_val AS Store
	FROM toc 
	INNER JOIN propval AS FruitField ON FruitField.tocid = toc.tocid
	INNER JOIN propval AS StoreField ON StoreField.tocid = toc.tocid
	WHERE toc.pset_id = @TemplateId AND FruitField.prop_id = @FruitFieldId AND StoreField.prop_id = @StoreFieldId
)

SELECT Fruit, Store, COUNT(*) AS Total
FROM Fields
GROUP BY Fruit, Store
1 0
You are not allowed to follow up in this post.

Sign in to reply to this post.

Details
Related Posts
Loading ...