Filter with Dropdown Widget on SQL
Question
Application Type
Reactive

Hello there,

I have a table in my reactive app with results coming back from an SQL query. I would like to filter these results with a dropdown filter by a value from my database. I know how to handle the Dropdown widget with an aggregate but not with SQL. For instance, I would like to filter only the "Shanghai, CN" data. Is it the implementation that different? Can anyone demonstrate with a simple example what should I do?

Thank you in advance,
Vasilis

Screenshot 2022-04-28 at 2.17.55 PM.png

Hi Vasilis,

Please follow the below steps -

1. Create a parameter in SQL Widget.

2. Use that parameter in your SQL where condition.

See Below image -


Thanks

Vinod

Hello Vinod,

perhaps I have not made clear what I want to achieve. I have done that in SQL and I get the correct results. I would like to implement that on my Dashboard with a dropdown widget and there it isn't obvious to me what my steps would be. For the time being I have done this:
where I get the Ports list in the dropdown and then on the FilterByPort action I am refreshing the sql query. This isn't working though.

Hi Vasilis,

you need to write a query in SQL Tool to get the records  by filtering the mentioned column, by using the where clause.

If you wish to get all columns in the list use this-

Select *  From {EntityName} where {EntityName}.[EntityAttributeName] = @Query Parameter Name

you can create the query parameter to pass a attribute value by which you wish to filter the records.

you may need to create one output Structure/Entity. in this case where you want all columns in to the record, just have the output structure as you Entity.

you can test your output, if the query is running properly , by passing test input values.


If you whish to have some of the columns in the output ,you need to mention the columns name and seperate them with comma,in select ,like-

Select {EntityName}.[EntityAttribute1Name], {EntityName}.[EntityAttribute2Name] 

  From {EntityName} where {EntityName}.[EntityAttributeName] = @Query Parameter Name

rest process is same.


Hope this helps,

Thanks Mita

Thank you for your time Mita, but this is not the issue for me as you can see below. I appreciate the time and effort to reply!

Many thanks,
Vasilis

Hi @Vasilis Roumeliotis

Add one input parameter (Description) in the sql and pass value from dropdown. 

SELECT {location}.[id] , {location}.[desc]

FROM {location}

WHERE ({location}.[desc] LIKE CONCAT('%', @Description, '%'))


Thanks,

Vipin Yadav

Hello Vipin,

This is what I implement and this is the output
so that works on the SQL. In dropdown I am assigning the InPort as the local variable of the dropdown and it isn't working.

Thank you,

Vasilis

Hi Vasilis,

Can you please share your oml .

Thanks,

Vipin Yadav

Hello Vasilis,

Not sure if this is what you want to achieve but check attached oml.

Regards,

Pramod.

Demo4.oml

Hello Pramod,

I see the logic but I can't click on the dropdown in the App. But if I understand correctly, do you suggest to make an entity after my structure in the SQL output and try from there to go with the dropdown logic?

Many thanks,
Vasilis

Hi Vasilism,

I did understood your exact requirement,

So I cant say anything ......sorry :(

No worries. Thank you for taking the time anyway!
Vasilis

Hello Vasilis,

I got your issue. And it was quite interesting for me to get solution for this issue so I created a Screen(bookings) in one of my existing POC app and tested it. It worked properly(screenshot). Kindly go through the logic in Bookings screen in attached OML. 

Kindly give attention in these highlighted point:

Hope it helps!!

Sanjay

HotelBookingSQLTest.oml

Hello Sanjay,

I get the logic that you implement but my GetStatuses Entity does not exist and it also changes dynamically so I am not so sure I can use that solution.

Many thanks for your time!

Vasilis

Hello Vasilis,

You have GetPorts instead. you should just store selected port's ID(from dropdown) into your local variable and pass it to SQL query like my solution. It will work.

Let me know if any other issue.

Community GuidelinesBe kind and respectful, give credit to the original source of content, and search for duplicates before posting.