Hi, I am trying to build an advanced search function where user can filter the data by multiple fields and 3 conditions (Is, Contains, Is not). My first thought was to build a dynamic SQL through user's input, but how could I do this? There are over 30 fields, and they span over 5 tables. I have read this article by OutSystems, but it seems like the dynamic part is on the input variable only. As for my case, the number of fields, condition, value, the table of the fields are all dynamic.
Does anyone have experience building similar functionality in OutSystems that can share your experience with me? I would be so grateful for your sharing. Thank you
Hi @Max Chia ,
I am not sure this is right approach or not but i have done similar thing in one of my project.
You can follow the steps.
Create a Logic for return where condition like-
companyCode selected and condition and value also selected so add like in variable - {AggregateName}.[AttributeName] <> 'Test'
and in your select statement should have like below ex
SELECT ColumnName from Aggregate
Where 1=1 AND @WhereCondition
Hope you get an idea from it.
Thanks
Deepa
Hi @Deepa Tiwari ,
Thank you for your suggestion. I just realized we can insert SQL query as a parameter by setting the Expand Inline property to true. So, I have come up with something similar too.
Cheers,
Max
Hi Max,
yes, that's the way to go, please make sure though that you are building your sql string in a safe way on server side.
Dorine