4 Replies Latest reply on Mar 3, 2017 3:11 AM by saguiar RSS
    saguiar Newbie

    Use Filter Widget with a SQL database

    Hello,

     

    I am trying to use the Data Filter widget but I am a bit confused on how to set it up. I would like to filter data that comes from a SQL database, which I am getting by a SQL query, which is written as a service that I have predefined. I am displaying the data using the Grid Widget and I want to able to use the Data Filter to manipulate it (i.e filter by the options the widget itself provides). However, I don't know how to define the Query property in the Data Filter widget. Do I need to specify every single field that would be used by the Data Filter widget in a service that will be defined as the Query property or how do I tell the widget which database should be filtered?

     

    Thanks

      • Re: Use Filter Widget with a SQL database
        saguiar Newbie

        Thanks for your answer. So lets say I have a table called colors, like this:

         

        colorId || colorName

        1          || red

        2          || blue

        3          || yellow

         

        And I would like to filter by using all the possible outcomes the widget has. These are: "Starts With", "Contains", "Ends With", and "Is Exactly".

         

        Therefore, to achieve this I would have to create an SQL query that does the same thing for every single possibility I might have in my database? If that is the case, then I don't see how the widget can be useful! Since you can create different SQL Query services and assigned them to Text Inputs or Radio Buttons and perform different filters to the same data contained , for instance, in a Grid Widget.

        • Re: Use Filter Widget with a SQL database
          supandey Collaborator

          Hi Santiago, you can use that data, which you are pulling from the SQL DB, into an Infotable and then you use that in a query service something like this

           

          var params = {
            t: undefined /* INFOTABLE */,
            query: undefined /* QUERY */
          };
          
          
          // result: INFOTABLE
          var result = Resources["InfoTableFunctions"].Query(params);
          

           

           

          You don't have to manually enter each of those fields if you'd use Datashape for that. To build your query you can refer to the documentation on Query Parameter for Query Service

           

          Once you have that query service you can bind that to Query Property for the Data Filter widget

          • Re: Use Filter Widget with a SQL database
            qngo Creator

            When you get the data from the DB and bind it to the Grid Widget, you can bind the same data to the Data Filter. The Query is generated by the widget when the user defines the filter in runtime. Then you can select the data available for filter in Configure Data Filter Fields.