Universal search on Tables

Universal search on Tables

  

Hi.

I'm trying to make a universal search bar, in the form of a reusable web block, which can be dragged and dropped. Is there a way to implement an SQL query which can search through all tables, or as specified by user, and redirect to corresponding details page on click?

Hello Saurav


Will the user only be searching for tables? And will all these tables have detail pages? 


Regards,

Ângelo

Ângelo Sousa wrote:

Hello Saurav


Will the user only be searching for tables? And will all these tables have detail pages? 


Regards,

Ângelo

Hi Angelo,

The functionality will be similar to "Spotlight Search" which is available on Apple devices. The developers will select the tables that will be queried, and the response will appear in a drop down list from which users can select options. It can be customized by developers according to their needs, but I don't know if the table names can be entered as variables in an SQL query.


Hi Saurav,

You can use a SQL tool with Input Parameters with Expand Inline set to No Yes. That way you can generate dynamic sql queries. Based on some configuration elsewhere you'd build the SELECT ... FROM ... WHERE ... .

But there's a caveat, the SQL tool's Output Entities/Structures cannot be dynamic, so you need common Entities/Structures for all possible SQL queries that you generate from that single SQL tool.

Edited: Thanks Eduardo for pointing out the bug in my explanation!

Hi Jorge, did you mean Expand Inline set to YES?

Otherwise it will not be interpreted as sql but as values...

A solution would be to return always the id (as integer) and the name of entity plus some text that represents the result. Joining with an entity that stores links to pages would also make easier to navigate to correct pages depending on the entity.

Jorge Martins wrote:

Hi Saurav,

You can use a SQL tool with Input Parameters with Expand Inline set to No Yes. That way you can generate dynamic sql queries. Based on some configuration elsewhere you'd build the SELECT ... FROM ... WHERE ... .

But there's a caveat, the SQL tool's Output Entities/Structures cannot be dynamic, so you need common Entities/Structures for all possible SQL queries that you generate from that single SQL tool.

Edited: Thanks Eduardo for pointing out the bug in my explanation!


Hi Jorge,

But expand inline option is not available for mobile applications. I'm sorry I didn't mention it in the original post. Is there another solution that is applicable for mobile apps?

Hi Saurav,

For mobile apps you really can't do that. But I'm wondering why you need this, as local apps should have very limited storage typically, and I think a "universal search" is the wrong answer to the problem you're facing.

Kilian Hekhuis wrote:

Hi Saurav,

For mobile apps you really can't do that. But I'm wondering why you need this, as local apps should have very limited storage typically, and I think a "universal search" is the wrong answer to the problem you're facing.

Hi Kilian.

Most applications I'm making is using a server side database. So not much local storage. Since the server databases (like an employee DB) are quite large, and will be used for most projects, I need a way to be able to query those tables from the mobile application. 

If not this way, do you have any other suggestions as to how to achieve this goal?


Hi Saurav,

If you're doing this server-side, then you do have access to the SQL tool, on Server Actions. But I'm with Kilian, probably won't be the best experience for your users... what's the goal you're trying to achieve with your "universal search"?

Jorge Martins wrote:

Hi Saurav,

If you're doing this server-side, then you do have access to the SQL tool, on Server Actions. But I'm with Kilian, probably won't be the best experience for your users... what's the goal you're trying to achieve with your "universal search"?

It will basically be a reusable web block containing a search bar, which can be dragged and dropped into your application. The user will have to provide a list of table names (in their DB, or those they have access to) as an input to the web block. This in turn will query the tables provided by the user to find possible matches.

My issue is that SQL queries in Outsystems have the format {Table_Name}.[Column_Name], but they cannot be entered as parameters which have the format @Parameter_Name. So is there a way that the table names can be a variable parameter, that can be entered by the user during the usage of the web block?

This is cut down the work of future developers who want to include a search feature in their application, wherein they are only required to enter the tables the be searched and the keyword to be searched for, rather than having to implement a filter for their aggregates.


Solution

Hi Saurav,

I understood what you wanted to do with your block... what I'm curious about is how useful would that be to the end users of your applications, that's the usability question. And also, from a technical aspect, the resulting query will be expensive to execute.

Regarding the format... marking your SQL tool input parameters as Expand Inline would allow you to inject anything in your SQL statement, but I'm not sure if the platform would still translate from "{SomeEntity}.[SomeAttribute]" to the corresponding table and column names afterwards, you'll have to do some quick testing on that.

Solution

Jorge Martins wrote:

Hi Saurav,

I understood what you wanted to do with your block... what I'm curious about is how useful would that be to the end users of your applications, that's the usability question. And also, from a technical aspect, the resulting query will be expensive to execute.

Regarding the format... marking your SQL tool input parameters as Expand Inline would allow you to inject anything in your SQL statement, but I'm not sure if the platform would still translate from "{SomeEntity}.[SomeAttribute]" to the corresponding table and column names afterwards, you'll have to do some quick testing on that.

Hi Jorge.

Your solution worked out great. Thanks for the advice.

I will be looking into how expensive the queries will be.

Thank you. :)