Inline Scripting in Workflow Studio

[neil_b]

Hi,

I am in the workflow studio, and I have a Look Up Records step. I want to retrieve the data from the Look Up Records step and pass it into my flow variable (set up as type: String) as a comma separated list. Can someone help me with my inline script and let me know why my script isn't working? I want the comma separated list because I am trying to get a list of serial numbers separated by commas to include on a notification that gets triggered using an event to a user group for machines with expired licenses. Once I am able to populate my flow variable with a comma separated list, I intend to pass it to my event as parm 2. 

var gr = fd_data._10__look_up_records.records;
var count = fd_data._10__look_up_records.count;
var serialNumberList = [];
if(count > 0 & !gr.hasNext()){
    gr = new GlideRecord(gr.getTableName());
	gr.addEncodedQuery(gr.getEncodedQuery());
	gr.query();
}
  while(gr.next()){
	serialNumberList.push(gr.getValue('u_serial_number'));
 }
  var serialNumberList_array = serialNumberList.join();

return "serialNumberListIN"+serialNumberList_array+"";

Here is my flow:

[Ankur Bawiskar]

@neil_b 

yes it seems it's not picking up the correct encoded query and hence count is not valid and array is not coming fine

Please try to use some flow variable etc as workaround.

I will check this tomorrow IST

If my response helped please mark it correct and close the thread so that it benefits future readers.

[neil_b]

@Ankur Bawiskar I've included my flow for context. Do you think you can help me revise my script? I believe the issue is that my Lookup returns 0 records.

I am first doing a Look Up in the cmn_location table to find the correct location. After finding the correct location, I'm doing a For Each to grab every single Serial Number in that location. Then I do another Look Up in our requests table to see if a Serial Number has been reported on a request for license renewal. If it doesn't have any requests submitted within the last 30 days, then set flow variables. This is where my script starts, to assign a flow variable a list of Serial Numbers that have not been reported on recently. 

I don't know if I need to do the Lookup from step 8, or step 10 within the script. I think my challenge is that the Lookup from step 10 returns 0 records, so I need to be able to somehow get the script to do a Lookup in step 8, compare if there is a matching record in the Lookup in step 10, and if not, add the Serial Number to the variable.

[John Gilmore]

@neil_b

I think you have over complicated this by trying to perform a lookup in your Set Flow Variables action.

I think if you switch to a script at step 10 similar to the following one then you could use a scratchpad variable to track your list of serial numbers and then perform a single Set Flow Variable action afterwards.

This would mean step 10 would become scripted, step 11 would be a Set Flow Variable outside of the step 9 forEach loop. If you accidently put it in the forEach loop the end result would still be the same it would just be less efficient since you only need to do it once after the loop completes.

The following example assumes your flow variable is of type string since thats what you are asking for. Under this setup you flow variable would be a comma separated string of serial numbers after the Set Flow Variable action.

Step 10 script example:

var currentSN = current.serialnumber; //update this to represent the correct serialnumber input from step 8.

var gr = new GlideRecord('asset_reports_table');
gr.addQuery('serial_number', currentSN);
gr.addQuery('sys_created_on', '>=', gs.daysAgoStart(30)); // Add any additional conditional parameters you need to the query.
gr.query();

if(!gr.hasNext()) {
     if (workflow.scratchpad.snList) {
          workflow.scratchpad.snList += ',' + currentSN;
     } else {
          workflow.scratchpad.snList = currentSN;
     }
}

Step 11 scripted input for the Set Flow Variable action:

workflow.scratchpad.snList

If your Flow Variable is of type array.string then the script changes just a bit:

var currentSN = current.serialnumber; //update this to represent the correct serialnumber input from step 8.

if (!Array.isArray(workflow.scratchpad.snList)) {
     workflow.scratchpad.snList = [];
}

var gr = new GlideRecord('asset_reports_table');
gr.addQuery('serial_number', currentSN);
gr.addQuery('sys_created_on', '>=', gs.daysAgoStart(30)); // Add any additional conditional parameters you need to the query.
gr.query();

if(!gr.hasNext()) {
     workflow.scratchpad.snList.push(currentSN);
     }
}

[neil_b]

Hi @John Gilmore I don't think the syntax is working properly for me for the step 10 script. My flow errors and says "Cannot read property "u_asset_serial_number" from null" on line 1.

I think the proper syntax would be something along the lines of

var currentSN = fd_data._9__for_each.item.u_asset_serial_number;

After updating the syntax, I ran the flow, but I experienced a second error saying "Error: "workflow" is not defined. I'm not too familiar with scratchpad variables in workflows.

Do you think you can help me out with this one? Here's the code 

var currentSN = fd_data._9__for_each.item.u_serial_number; 
var gr = new GlideRecord('asset_report_table');
gr.addEncodedQuery('sys_created_onBETWEENjavascript:gs.beginningOfThisMonth()@javascript:gs.endOfThisMonth()^asset_report_table.u_serial_number='+currentSN);
gr.query();
if(!gr.hasNext()) {
     if (workflow.scratchpad.snList) {
          workflow.scratchpad.snList += ',' + currentSN;
     } else {
          workflow.scratchpad.snList = currentSN;
     }
}

I definitely think we're closer to getting this to work! I see your code basically states, if it doesn't find any records, then add the S/N to the scratchpad variable.

Once we're passed the error, I believe this will finalize everything as it should cycle through all the For Each's per location and once that's complete, I can send that list to Parm2 and email the user for that location. This should still allow me to have 3 different emails (1 per location) sent to 3 different users with their own unique list of serial numbers.

Let me paint the entire picture for context.

1. I do a Lookup in the cmn_location table to find ones that contain assets (which I've added a new field to associate a Responsible User for each location) 

2. I do a For each to grab the asset for the location found above

3. I do a Lookup in the request table to find if the asset in the For each above has been reported on

4. After all the For each assets have ran, compile the list of unreported assets into a string

5. Pass the string to Parm2 in my event and email the Responsible User with the list of assets 

6. I need it to perform steps 2-5 again for a different location and a different user

For instance, we have Location A, B, & C, each with their own designated Responsible User.

Location A has 8 out of 10 assets reported

Location B has 5 out of 15 assets reported

Location C has 2 out of 11 assets reported

A reminder email needs to be send to the Responsible User A containing a list of the 2 remaining serial numbers.

A reminder email needs to be send to the Responsible User B containing a list of the 10 remaining serial numbers.

A reminder email needs to be send to the Responsible User C containing a list of the 9 remaining serial numbers.

I think once we get the script in step 10 working, this should do the trick!

[John Gilmore · Accepted solution]

@neil_b 
Sorry the error you are getting now is because I'm crossing up traditional workflows and Workflow Designer. Because we are in workflow designer you don't have scratchpad because it has Flow Variables. We can use dot walking to access the flow variable directly, this is also why current.u_serial_number didn't work.

Try this script:

var currentSN = fd_data._9__for_each.item.u_serial_number; 

var gr = new GlideRecord('asset_report_table');
gr.addEncodedQuery('sys_created_onBETWEENjavascript:gs.beginningOfThisMonth()@javascript:gs.endOfThisMonth()^asset_report_table.u_serial_number='+currentSN);
gr.query();

if(!gr.hasNext()) {
     var snList = fd_data.flowVariables.variableName; //replace variableName with the name of your flow variable
     if (!gs.nil(snList)) {
          fd_data.flowVariables.variableName = snList +"," + currentSN;
     } else {
          fd_data.flowVariables.variableName = currentSN;
     }
}

Given that we are now setting the flow variable as part of the script your Set Flow Variable step is no longer necessary.

With regard to how you are sending emails it might be easiest to actually create a grouped array of the serial numbers. Then you could perform a for each loop on the groups and the serial numbers under the group in the array would already be aligned with the proper user. I haven't specifically used this method with a flow variable.

You could try this:
Make sure you flow variable is of type JSON then try this script:

var snListArray = {};
var currentSN = fd_data._9__for_each.item.u_serial_number;

var gr = new GlideRecord('asset_report_table');
gr.addEncodedQuery('sys_created_onBETWEENjavascript:gs.beginningOfThisMonth()@javascript:gs.endOfThisMonth()^asset_report_table.u_serial_number='+currentSN);
gr.query();

while (gr.next()) {
     var responsibleUser = fd_data._9__for_each.item.getDisplayValue('responsibleUser'); // update this to reference the correct attribute, it may be something like fd_data._9__for_each.item.location.getDisplayValue('responsibleUser') if the responsible user isn't directly associated with the record in the loop
     if (!snListArray[responsibleUser]) {
          snListArray[responsibleUser] = [];
     }
     snListArray[responsibleUser].push(currentSN);
}

fd_data.flowVariables.variableName = snListArray; // I haven't done this specifically before so you may have to use JSON.stringify(snListArray)

This would then set your flow variable to a JSON array which you could then use as a grouped array where the responsible user is the group name. Again I've never applied this method in the way you are using it I've only used it inside scripts. If you are able to figure out how to use the grouped array in a for each item I'd appreciate sharing how (might work better if you use a flow variable type of array.object but not sure if the script would change in that case).

Editing to add an additional thought: If you used the responsible users email address as the group name rather than the display value as in my example script that would allow you to reference it directly when generating the email which might save you the complication of having to look it up in that step. If you need more than one attribute of the responsible user such as using their name for a Hello Name! start to the email then using the responsible users sys_id could also simplify the process of retrieving the full user record as you could use it in a .get() or lookup record step.