Create a dependent dropdown list in SQL Table Editor

MarkBird
Contributor II

Hi, 

Is it possible to create a dependent dropdown list using a SQL Table Editor? Example below:

MarkBird_0-1683213553870.png

If I select Entity A in the Entity column, I would like PC1, PC2 to be options within the Profit Centre column.

If I select Entity B in the Entity column, I would like PC3, PC4 to be options within the Profit Centre column.

 

I have tried using 'Selection Changed Server Task' to update a Literal Parameter that I can use as a basis for a Bound List parameter that I am attaching to the Profit Centre column , however this only appears to work after the row has been saved:

MarkBird_1-1683213816821.png

 

I think it may be possible if the user was to save the row after choosing the Entity and then selecting a Profit Centre, but this wouldn't be an ideal user journey.

 

Thanks,

Mark

1 ACCEPTED SOLUTION

No, that's not going to work. But you can hide that button and just create yours.

View solution in original post

10 REPLIES 10

  • You can, I would suggest using a dashboard data set rule and then pass the parameter of the first drop down. Then in the rule check if the parameter is expanded. If it is then create a SQL to get the dependent ones and return the data table. On the dashboard side, you'll need those drops downs as a part of a different dashboard like a toolbar dashboard. On your anchor combobox, perform a refresh of the toolbar dashboard. There you go. 

If I read correctly, he would want the parameter list assigned to the Profit Centre field to update after making a selection in Entity field. I don't think it's possible as I'm not sure how making a selection in Entity field would invoke record level refresh of the STE component. Not sure how what you recommend would look like but now curious!!

Cosimo, that's exactly what I'm looking for

Hahaha, this is why before responding I should read the whole thing. They I've done that is to move the column from SQL editor back to a combobox and not keep it in the editor itself.

That won't work as the SQL table needs to hold multiple entities.

Another thought that I had, is to have a popup dialog when you click new add new row, which allows you to choose the entity and store the selected entity in a literal parameter (and use the literal parameter in the Profit Centre filter). But not sure if that is possible?

JackLacava
Moderator
Moderator

I always hate to be "that guy", but I fear using a STE for this sort of activity is not the best approach. What you describe (creating a record where field choices are interdependent) is closer to going through a wizard than mass-editing raw SQL rows.

It would probably be better to have a dedicated pop-up with (at least) the basic fields a user has to enter when creating records, which you can control more precisely. You can then provide a STE for reviewing or mass-updating records afterwards, if necessary.

Hi Jack

That sounds like a possible solution, however, I'm not sure how I would get a pop dialogue when creating a row? Is it achieved by setting the 'Selection Changed User Interface Action' below to Open Dialog?

MarkBird_0-1683636000512.png

 

 

Assuming we are talking about a button (which is what i had in mind, something like "Create New Entity"), yes - and note that there are many "Open Dialog" options to help you manage the relevant popup. You'll also have to set the "Dashboard to Open in Dialog" property to point to the dashboard that will be used in the popup.

I was hoping to be able to use the add new record button?

MarkBird_0-1683637954756.png

 

No, that's not going to work. But you can hide that button and just create yours.

Please sign in! MarkBird