Forum Discussion

MarkBird's avatar
MarkBird
Contributor III
2 years ago

Create a dependent dropdown list in SQL Table Editor

Hi, 

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

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:

 

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

Dashboards
Parameters
  • JackLacava's avatar
    JackLacava
    2 years ago

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

  • ckattookaran's avatar
    ckattookaran
    VIP
    2 years ago

    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.

    • MarkBird's avatar
      MarkBird
      Contributor III
      2 years ago

      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's avatar
    JackLacava
    Honored Contributor
    2 years ago

    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.

    • MarkBird's avatar
      MarkBird
      Contributor III
      2 years ago

      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?

       

       

      • JackLacava's avatar
        JackLacava
        Honored Contributor
        2 years ago

        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.

  • ckattookaran's avatar
    ckattookaran
    VIP
    2 years ago
    • 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. 
    • Cosimo's avatar
      Cosimo
      Contributor II
      2 years ago

      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!!

      • MarkBird's avatar
        MarkBird
        Contributor III
        2 years ago

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