ServiceNow.List object Lookup

Magic Suite ReportMagic
1

Hi

Upon working with the ServiceNow macro’s and I seem to be faced with a common SN issue about querying the incident table and asking for the state of the incident. This returns the Value and not the Label.

I have temporarily got a workaround by using the Switch macro, but I hoping to be a bit more dynamic where I don’t have to adjust the template when a new state is created etc. So I was looking into just providing a list of all the applicable states and then look up the label from the value.

I have the objects with the properties after using this:
[ServiceNow.List:
query=“element=state^name=Incident”,
table=sys_choice,
properties=label;value,
=>States
]

The objects are returned like this:
[
{
“label”: “New”,
“value”: “1”
},
{
“label”: “In Progress”,
“value”: “2”
},

So my question is, if I have a table using RepeatRow and have the value appearing in the field, if I was to storeAsHidden, that I can then use that variable as a lookup of the value to return the applicable label.

Looking for the most efficient suggestion without having to over complicate it, and sure I am just missing something fundamental.

2

Hi @drazzopardi,

I would move as much as possibe from the RepeatRow by making a new object with exactly what you want in the table.

You can do what you want in a number of calculate macros or 1 nested calculate macro.
I will take you through the seperate calulate statements and also give you the nested statement.

The Incidents states are in a variable called States, and the current row’s state value is in RowValue.

First get the matching State with a where statement
If we were to use List.Where the statement would be:

[List.Where:
	values={=States},
	condition=`jPath(value,'value') == '{RowValue}'`,
	=>MatchedState
]

We can do this in a calculate macro

[=: `where(
		States,
		'S',
		'jPath(S,\'value\') == \'{RowValue}\''
	)`,
	=>MatchedState
]

Note:

  • We need to add \ before any embedded quotes
  • S is the string to be evaluated

Then we can use select to get just the label

[=: `select(
        MatchedState,
		'M',
		'jPath(M,\'label\')'
	)`,
	=>LabelList
]

Note:

  • M is the string to be evaluated

The output from the select is a jArray, so to get just the label text we can use itemAtIndex

[=: `itemAtIndex(LabelList,0)`, =>Label]

Note: index starts at zero

We can combine it all together in one calculate macro

[=: `itemAtIndex(
		select(
			where(
				States,
				'S',
				'jPath(S,\'value\') == \'{RowValue}\''
			),
			'M',
			'jPath(M,\'label\')'
		),
		0
	)`,
	=>Label
]

Are you aware of nCalc101? there are some examples here: https://ncalc101.magicsuite.net/examples

3

Thanks Denise…I will look into it more.

Whilst I will admit I didn’t understand all of it as its been a bit of a brain frying day, I managed to get it to work:

image
image2148×950 165 KB

4

Hi David,
thats great.

Here a better definition of the nCalc macros

select()

Purpose

Converts an IEnumerable using a lambda.

Has 3 Parameters

  • the original list
  • a string to represent the value to be evaluated
  • the value(s) to select for each item in the list (the expression) - in your case return only the label

where()

Purpose

Filters an IEnumerable to bring back only those items that match a condition.
Replaces a ForEach loop over the list (and is quicker)

Has 3 Parameters

  • the original list
  • a string to represent the value to be evaluated
  • the string to evaluate (the condition to match) - in your case match where the value equals the value of the table row

each expression / condition can to tested in its own calculate macro:
e.g.
[ForEach: values={=States}, =>StateRow]
[=: jPath(StateRow,'value'), =>Value]
[EndForEach:]

[=: jPath(StateRow,'value'), =>Value]
when used in a foreach loop the above can be shortened to
[.: StateRow.value, =>Value]

and
[=: jPath(StateRow,'label'), =>Label]
can be shortened to
[.: StateRow.label, =>Label]