How to concatenate the output of a task within a loop into a variable - VisualCron - Forum

Community forum

alp
  •  alp
  • Paid support Topic Starter
2018-07-26T08:17:14Z
Hi,

I have a loop with a single task that copy files with a pattern based on loop value of course.
I want , after the loop, to send an email with the list of all copied files.
for this, I was expecting to use "set job variable" in the loop with String(Concat) function to concat a the variable itself / the standard output of the copy task.
This seems not working :(

I see a plan B to create a temp file to handle this but don't like this option !

Any idea ?

Txs
Alain.
Jon Tofte-Hansen
2018-07-26T13:29:37Z
One solution:

Create a job with a job variable (in this example {JOB(Active|Variable|file_list)}) and the following tasks:

  1. "Set job variable" that resests the variable so it is ready for a fresh run (nothing in "Value")
  2. "List file" with your search path
  3. A "For each x in y" loop ("For each row x in" is set to {TASK(PrevTask|StdOut)} or the task id directly - set a realistic max iterations) with two tasks:

    1. "Copy files"

      1. folder: {PATH(GetDirectoryName|{LOOP(CurrentValueXLine)})}
      2. Include file mask: {PATH(GetFileName|{LOOP(CurrentValueXLine)})}
      3. Destination folder: your destination

    2. "Set job variable" with "Value" set to:
      Quote:

      {JOB(Active|Variable|file_list)}
      {TASK(PrevTask|StdOut)}


      and remember to tick "Translate value to constant in Variable when running"

  4. Finally the "Email" task with "Text" set to {JOB(Active|Variable|file_list)}.


Final picture:
loop_with_job_variable.png


If you want the before location you can add {LOOP(CurrentValueXLine)} to the "Set Job Variable" task like:
Quote:

{JOB(Active|Variable|file_list)}
From {LOOP(CurrentValueXLine)} to {TASK(PrevTask|StdOut)}

jrtwynam
2018-09-20T18:38:35Z
Hi,

I'm having trouble getting this method to work. I'm trying to create a job that queries one database, generates an XML string from the results, and inserts that string in another database. I've got it to the point where it generates the XML header part, but the XML details aren't working. Basically, each header record can have any number of details, so what I've done is created 2 jobs:

Job 1

  1. Get a list of header records.
  2. Loop through the records. For each record:
  3. -- Generate the XML Header.
  4. -- Call Job 2 to generate the XML Details.
  5. -- Retrieve the XML Details from a job variable from Job 2.
  6. -- Generate the XML Footer.
  7. -- Combine the XML parts into a single XML string.



Job 2

  1. Nullify the XML Details job variable.
  2. Get a list of detail records for the current header record (primary key value passed from Job 1).
  3. Loop through the records. For each record:
  4. -- Generate the XML details for the current record and concatenate them to the XML Details job variable.


Step 4 of Job 2 is a "Set Variable" task, with the variable being "XML Detail", and the value being this:

{JOB(Active|Variable|XML Detail)}<TaskDetail><itemIdPart1>{JOB(Active|Variable|STYLE)}</itemIdPart1><locnIdPart1>{JOB(Active|Variable|LOCN)}</locnIdPart1><locnIdPart2>R</locnIdPart2><quantity>{JOB(Active|Variable|QTY)}</quantity><caseId>{JOB(Active|Variable|CARTON_ID)}</caseId><skuCaseQty>{JOB(Active|Variable|CASE_QTY)}</skuCaseQty><weight>{JOB(Active|Variable|WEIGHT)}</weight><volume>{JOB(Active|Variable|CUBE)}</volume><hndlingUOM>{JOB(Active|Variable|LABEL_TYPE)}</hndlingUOM><season></season><seasonYr>{JOB(Active|Variable|CARTON_TYPE)}</seasonYr><color>{JOB(Active|Variable|CASE_NBR)}</color><secondDim>{JOB(Active|Variable|PLT_ID)}</secondDim><sizeRange></sizeRange><sizePos>1</sizePos><dim1>0</dim1></TaskDetail>


As you can see, I'm trying to set the XML Detail variable to itself, concatenated with a bunch more XML tags. I do have "Translate value to constant in Variable when running" checked, but it's giving me an infinite loop error:

TranslateVariables->Infinite loop error on string: {JOB(Active|Variable|XML Detail)}<TaskDetail><itemIdPart1>{JOB(Active|Variable|STYLE)}</itemIdPart1><locnIdPart1>{JOB(Active|Variable|LOCN)}</locnIdPart1><locnIdPart2>R</locnIdPart2><quantity>{JOB(Active|Variable|QTY)}</quantity><caseId>{JOB(Active|Variable|CARTON_ID)}</caseId><skuCaseQty>{JOB(Active|Variable|CASE_QTY)}</skuCaseQty><weight>{JOB(Active|Variable|WEIGHT)}</weight><volume>{JOB(Active|Variable|CUBE)}</volume><hndlingUOM>{JOB(Active|Variable|LABEL_TYPE)}</hndlingUOM><season></season><seasonYr>{JOB(Active|Variable|CARTON_TYPE)}</seasonYr><color>{JOB(Active|Variable|CASE_NBR)}</color><secondDim>{JOB(Active|Variable|PLT_ID)}</secondDim><sizeRange></sizeRange><sizePos>1</sizePos><dim1>0</dim1></TaskDetail><TaskDetail><itemIdPart1>{JOB(Active|Variable|STYLE)}</itemIdPart1><locnIdPart1>{JOB(Active|Variable|LOCN)}</locnIdPart1><locnIdPart2>R</locnIdPart2><quantity>{JOB(Active|Variable|QTY)}</quantity><caseId>{JOB(Active|Variable|CARTON_ID)}</caseId><skuCaseQty>{JOB(Active|Variable|CASE_QTY)}</skuCaseQty><weight>{JOB(Active|Variable|WEIGHT)}</weight><volume>{JOB(Active|Variable|CUBE)}</volume><hndlingUOM>{JOB(Active|Variable|LABEL_TYPE)}</hndlingUOM><season></season><seasonYr>{JOB(Active|Variable|CARTON_TYPE)}</seasonYr><color>{JOB(Active|Variable|CASE_NBR)}</color><secondDim>{JOB(Active|Variable|PLT_ID)}</secondDim>

<snip>


On another note, if anyone has an idea of an easier way to generate an XML string, I'd love to hear it.

Thanks.
thomas
2018-09-21T07:49:02Z
Hi

Regarding your last question, about an easier way of creating xml. If you are using Sql server you can create xml easily with pure sql. Alternatively .net using Linq is relatively simple.

Run this as an example in Sql Server. If you don't have access to sys.all_columns, substitute with any other table

SELECT TOP(10) *
FROM sys.all_columns
FOR XML PATH('SomeNode'), ELEMENTS, TYPE, ROOT('SomeRootNode')
Scroll to Top