SetVarListFromCache: Create a list of variables from the SQL cache

Tag: SetVarListFromCache

The SetVarListFromCache creates lists of variables from the values in the SQL cache.

XML

<SetVarListFromCache/>

Tag Attributes

ListName	List name - Required
Type	Object type name - Required (among ‘user, ‘group’, ‘site’, ‘personalsite’, 'serviceprincipal’)
(variable names)	Any unreserved name is valid in XML. Reserved names are listed here: Variables
(Variable Values)	Valid field names. e,g, v_ID='id' If the field is not found (invalid field name), an error is raised, the action fails, the list ends up empty and the loop will do nothing.
Field	Field name to read from cache (only works with fields that can be used in an RBAC filter)
CacheFilter	SQL formatted ‘where’ condition. – Cannot be used with Where parameter ex: CacheFilter= "json_extract(min, '$.displayName') = 'Adele Vance'"
Where	Field name used as a filter. The field value in this column is tested against the value set in (where mode). – Ignored if CacheFilter parameter is set.
(Where Mode)	– Ignored if CacheFilter parameter is set. Text: Equals Begins NotBegins NotEquals Contains NotContains Ends NotEnds IsIn (colon separated list) IsNotIn (colon separated list) GreaterThan GreaterOrEquals LessThan LessOrEquals Examples: Begins="abcd" IsIn ="abcd:efgh" true if the value returned by where is "efgh" but false if "ef"
Unique	True: Only keep unique variable sets "Any variable name": Only keep sets with a unique value for this variable
Verbose	When set to TRUE, displays a console trace for each element added to the list Defaults o FALSE

Example Script

XML

<ytriaAutomation Application="sapio365">
	<SetVarListFromCache Type="user" ListName="UserList" v_Id="id" v_UPN="userPrincipalName" Where="displayName" Equals="Adele Vance"/>
	<!-- loop on the generated list -->
	<loop list="UserList">
		<ExecuteListAction/> <!-- Does a <SetVar/> with the attributes in SetVarListFromGridSelection except 'ListName' and 'Target' -->
		<echo value="User id is {%v_Id%}."/>
        <echo value="User UPN is {%v_UPN%}."/>
	</loop>
</ytriaAutomation>

Example Script 2

XML

<ytriaAutomation Application="sapio365">
	<SetVarListFromCache Type="user" ListName="UserList" v_Id="id" v_UPN="userPrincipalName" CacheFilter="json_extract(min, '$.displayName') = 'Adele Vance'"/>
	<!-- loop on the generated list -->
	<loop list="UserList">
		<ExecuteListAction/> <!-- Does a <SetVar/> with the attributes in SetVarListFromGridSelection except 'ListName' and 'Target' -->
		<echo value="User id is {%v_Id%}."/>
        <echo value="User UPN is {%v_UPN%}."/>
	</loop>
</ytriaAutomation>

Example Script 3

XML

<ytriaAutomation Application="sapio365">
	<SetVar UserEmail="adeleV@tenant.onmicrosoft.com"/>
	<SetVarListFromCache Type="user" ListName="UserList" v_Id="id" v_UPN="userPrincipalName" CacheFilter="USE_CDATA">
		<![CDATA[lower(min) LIKE lower('%"{%UserEmail%}"%') OR lower(list) LIKE lower('%"{%UserEmail%}"%') OR lower(list) LIKE lower('%:{%UserEmail%}"%')]]>
	</SetVarListFromCache>
	<!-- loop on the generated list -->
	<loop list="UserList">
		<ExecuteListAction/> <!-- Does a <SetVar/> with the attributes in SetVarListFromGridSelection except 'ListName' and 'Target' -->
		<echo value="User id is {%v_Id%}."/>
        <echo value="User UPN is {%v_UPN%}."/>
	</loop>
</ytriaAutomation>