Anyone have any experience using Datasets?

[RBKHC]

We are trying to use Datasets in multiple ways and would like the basic syntax and any special formatting requirements?

We have figured out the ARS Dynamic Attributes and that is just select x as ID from dataset_values where dataset = 'DatasetName' and attributex = 'value' but have run into issues where it does not work all the time. Not sure if there are any restrictions like field names, limited query depth, etc...

We can not find any examples of using Datasets with Update Account JSON like updating an AD attribute with a value from a dataset for example (in bold, customproperty11 is the ISO A2 for the country):

{
"c": "${user.customproperty11}",
"countryCode": "${select attribute2 from dataset_values where datasetname='ISO' and attribute1 = user.customproperty11}",
"co": "${user.country}"
}

Are there any other examples available for use in other parts of Saviynt?

Any help would be appreciated.

[sahajranajee]

Hi @RBKHC ,

You cannot directly use datasets in your connector jsons. The values have to be resolved as a part of your dynamic attributes which can then be fetched onto the Connector JSONs.

Example DA below :

Ensure you have an accounts column/users column (whichever applicable) assigned to the DA.

[RBKHC]

Thank you Sahaj! I will try that and let you know.

[piyushm]

@sahajranajee  Posting here as it is related to dataset ad dynamic attribute.

Can attribute1,2... etc from dataset_values table be referenced in dynamic attributes sql query where clause dynamically?

In ARS form,

APP_ID as attribute1  using sql enum

APP_NAME as attribute2 in dataset_values table. Now I want to fetch app_name based on APP_ID selected by the user in ARS form.

select distinct attribute2 as ID from dataset_values where attribute1=${APP_ID}.

I am getting error no such property as APP_ID in the logs.

[rushikeshvartak]

APP_ID is dynamic attribute name correct if yes it should work 

APP_ID select attribute2 as id from datasets_values

APP_NAME select attribute1 from datasets_values where attribute1= ${APP_ID}

[piyushm]

No, it is not working. I think it is unable to recognize the endpoint attribute object from dataset table.

[piyushm]

Error details

ERROR errors.GrailsExceptionResolver - MissingPropertyException occurred when processing request: [GET] /ECM/workflowmanagement/hierachyentitlementbyendpoint\n","stream":"stdout","time":"2022-10-28T22:11:57.181505314Z"}"
"ecm","2022-10-28T22:11:57.330+0000","{"log":"org.codehaus.groovy.grails.web.pages.exceptions.GroovyPagesException: Error processing GroovyPageView: Error executing tag \u003cg:render\u003e: No such property: APP_ID for class: SimpleTemplateScript9\n","stream":"stdout","time":"2022-10-28T22:11:57.181551043Z"}"
"ecm","2022-10-28T22:11:57.330+0000","{"log":"Caused by: org.codehaus.groovy.grails.web.taglib.exceptions.GrailsTagException: Error executing tag \u003cg:render\u003e: No such property: APP_ID for class: SimpleTemplateScript9\n","stream":"stdout","time":"2022-10-28T22:11:57.181589876Z"}"
"ecm","2022-10-28T22:11:57.330+0000","{"log":"2022-10-28 22:11:57,200 [http-nio-8080-exec-28] ERROR error.ErrorController - Exception\n","stream":"stdout","time":"2022-10-28T22:11:57.200523245Z"}"
"ecm","2022-10-28T22:11:57.330+0000","{"log":"Caused by: org.codehaus.groovy.grails.web.pages.exceptions.GroovyPagesException: Error processing GroovyPageView: Error executing tag \u003cg:render\u003e: No such property: APP_ID for class: SimpleTemplateScript9\n","stream":"stdout","time":"2022-10-28T22:11:57.200594083Z"}"
"ecm","2022-10-28T22:11:57.330+0000","{"log":"Caused by: org.codehaus.groovy.grails.web.taglib.exceptions.GrailsTagException: Error executing tag \u003cg:render\u003e: No such property: APP_ID for class: SimpleTemplateScript9\n","stream":"stdout","time":"2022-10-28T22:11:57.200602048Z"}"

[rushikeshvartak]

Please share screenshot of query / paste query 

[piyushm]

@rushikeshvartak 

APP_ID - select distinct attribute1 as ID from dataset_values

APP_NAME - select distinct attribute2 as ID from dataset_values where attribute1=${APP_ID}

[rushikeshvartak]

I hope you have added mapping and parent attributes please add screenshot 

[piyushm]

[rushikeshvartak]

It seems mapping keyword missing in APP_ID 

[piyushm]

Thanks. I  had accidently added it in the Action string instead of Action. It works now.