Dataset for Create/Update User Request Form

[Goodness]

Hello,

Does anyone have a sample query of how to call values in a dataset (e.g. Country codes) so it can be listed in a drop down menu in a user request form? I have looked through the user management form documentation and I didn't really find any.

Thanks

[sk]

Can you provide the sample data for you dataset that you want display in user form?

Choose attribute type of the respective field on user form and SQL ENUM and In general you can use below query, If you have multiple columns then you may have to modify the query accordingly

select distinct attribute1 as ID from dataset_values where dataset_values.datasetname ='<dataset_name>'

[Goodness]

Thanks this was helpful!

I have two columns in the dataset; Country and Codes. In another attribute I want to display the code when a user selects the country. For example, when a user selects United States in the Country attribute (in the form) I want to display the corresponding Code in the country Code attribute. keeping in mind that I am pulling the data from the dataset, do you have any idea how that can be achieved?

Thanks for your help

[rushikeshvartak]

Example Dataset 

Attribute 1 

select distinct attribute1 as ID from dataset_values where dataset_values.datasetname ='Country'

Attribute 2

select distinct attribute2 as ID from dataset_values where dataset_values.datasetname ='Country' and attribute1 = '${Country Name}'

[Goodness]

I have followed this step, however when I select a country in the country attribute the code doesn't show in the code attribute in the user form. when I clicked directly on the country code attribute I got the message "error occured".

I tried placing the single quotes inside the country name like this ${'Country Name'}, and didn't receive the error, however, the country codes still weren't showing. When I do click directly on the drop down menu in the country code attributes it says no matches found

[rushikeshvartak]

Change below things

• Attribute1
  • Change Attribute name from Country Name to DA1
• Attribute2
  • Change Attribute name to DA2
  • Change query to 
    • select distinct attribute2 as ID from dataset_values where dataset_values.datasetname ='Country' and attribute1 = '${DA1}'
• Change Parent Attribute to DA1

[Goodness]

I have the made these changes but it's still not working. Please see screenshots below. 

I tried the single quotes both inside and out. '${DA1}' and ${'DA1'} and it's the same result

[sk · Accepted solution]

Use query as below for DA2

select distinct attribute2 as ID from dataset_values where dataset_values.datasetname ='Countrycode' and attribute1 = ${DA1}

[Goodness]

Thank you for your help!

[sk]

Can you share the screenshot of both dynamic attributes configuration?

Below is the sample configuration we did which of similar use case. You don't have use single quotes with in variable can you try variable as ${Country Name}

[Goodness]

Attribute 1

select distinct attribute1 as ID from dataset_values where dataset_values.datasetname ='Countrycode'

Attribute 2

select distinct attribute2 as ID from dataset_values where dataset_values.datasetname ='Countrycode' and attribute1 = ${'DA1'}

[rushikeshvartak · Accepted solution]

select distinct attribute2 as ID from dataset_values where dataset_values.datasetname ='Countrycode' and attribute1 = ${DA1}

[Goodness]

Thank you so much for your help! This worked 

[sk]

Yes, Let say you data set name is Country Codes and in column1 you have country and cloumn2 you have codes. Also on user form field name for country is Country then queries goes like this.

select distinct attribute1 as ID from dataset_values where dataset_values.datasetname ='Country Codes'

select distinct attribute2 as ID from dataset_values where dataset_values.datasetname ='Country Codes' and attribute1='${Country}'

[rushikeshvartak]

select distinct attribute1 as ID from dataset_values where dataset_values.datasetname ='Country Codes'

[sk]

Great. Can you please mark the solution that worked to close the thread. so that others who are on same boat it will be helpful