Prompt user for filter?

[David Salomon]

Hello group.  Is it possible to prompt the user for a value to be used in the WHERE part of a SQL Explorer query?  If so, how?

I have created a query that pulls data from 4 libraries, and I want to be able to use it interactively, with the user entering a value to limit the data returned.

Best regards - David

[David Salomon]

A little extra info.  Here's the query:

SELECT DISTINCT s.Freq, s.Network as "Network (SIGINT)", s."Signal Type" as "Signal Type (SIGINT)",
e.Station as "Station (EiBi)", h.Broadcaster as "Broadcaster (HFCC)",
i.Station as "Station (ILG)", u."Signal Type" as "Signal Type (KlingU)",
k.Station as "Station (KlingB)", n.Details as "Details (N & O)"
FROM "SIGINT" s
LEFT JOIN "EiBi" e ON s.Freq = e.Freq
LEFT JOIN "HFCC" h ON s.Freq = h.Freq
LEFT JOIN "ILG" i on s.Freq = i.Freq
LEFT JOIN "KUtility" u ON s.Freq = u.Freq
LEFT JOIN "KBroadcast" k ON s.Freq = k.Freq
LEFT JOIN "N and O" n ON s.Freq = n.Freq
WHERE s."Freq" = 4805
AND s."removed" = 0
LIMIT 500

I want to be able to prompt the user for the value of s."Freq".  I know the user can show the SQL Explorer window and change the value before executing, but I would prefer to have a way to click a button and be prompted for it.  I've tried using an Action at the library level, and I can prompt the user there.  What I don't know is how to pass the result to this query.  I am guessing that it's possible by using js in the Action, but I don't know how and can't find a reference or example to follow.

Best - David

[David Gilmore]

If I was doing something like this, then I would NOT provide the user with a text box to type in parameters to the selection. The user would not know the format needed, and a naughty user might try to cause trouble by deliberatly entering harmful text. So instead I would create a selection list that the user would pick an item for his search.

In Javascript the SQL string can be created with dynamic text, and using string concatenation. Something lise

[code]
var sName = "George"; // Where the name is actually fetched from a selection list.
var sqlstring = "SELECT * FROM DBNAME WHERE NAME='" + sName + "'";

[/code]

This would produce the string "SELECT * FROM DBNAME WHERE NAME='George'", which can then be used in the SQL request.

In the industry it is actually a big error to allow users to type in SQL stuff ad hoc. There could be destructive things like SQL insertion, or other huge security risks.

(My apologies, but I do not know if the code shown above will display properly)_

[Mmm]

Кроме того, не следует забывать, что результат функции sql() не может быть отображен так, как отображается запрос SQL. В том числе, с измененными именами столбцов.

Если набор основных частот не большой, можно сформировать пресеты для каждого значения.

Выбор пресета для Пользователя не составит труда.

понедельник, 30 марта 2026 г. в 18:25:55 UTC+3, aa6...@gmail.com:

[David Salomon]

Thanks for the input.  I am aware of the security issues with how parameters are passed to js.  That's not a problem for me, as I don't have anything exposed for external use.  I am also aware of not being able to display the results of a sql() function vs a sql statement.  The frequency tables are VERY BIG (the biggest is over 80,000 rows), so using presets isn't a solution, besides, I really want to do the join of the 7 separate tables.  I can accomplish what I want by using SQL Explorer and just changing the value of the WHERE clause.  It's just a bit of a hassle.  I read through the WIKI and other documentation many times today to try to determine if there is something I have missed, but I cannot find a way to do what I want.  BTW, despite joining a huge number of tables, several of which are very big, the performance is still very good, on btoh the desktop and the phone (Android - Samsung Galaxy Fold 5).  There has to be a way to do this.  However, I'm not familiar enough with js to figure it out...at the moment.  I will keep reading and playing with it.  I have figured out how to prompt the user, as well as include the results in the SQL statement.  What I am missing is how to take the results and display them to the user (in the joined query).  Thanks, again, to all for the advice.

[David Salomon]

FYI, the attached pic shows the SQL in SQL Explorer and the result.  As you can see, it joins all 7 tables, with selected columns from each.  Each table represents a data source of frequency information.  What I'm doing is allowing the user to enter a frequency, and displaying the selected data from any of the tables that contain records with that frequency.

[David Gilmore]

I just tried the following on two of my tables. I would assume your joins will work as well:

var r = sql(SELECT ToDo.Task, CALID FROM ToDo, ToDoCal WHERE ToDo.Task = 'Car%').asObjects();

The objects returned were in JSON format (this was the answer).

So you could parse the returned string as a JSON string and pull your results out.

Now you are probably wondering how to display those results. A long time ago, before Dialogs were implemented in Memento, a technique was developed to display a message to the user, and have it remain on screen until dismissed by the user. The message could be in HTML format. This technique was superseded by dialogs when dialogs where implemented in Memento, but that technique still has its uses today. You could write a Javascript that parses out the returned SQL JSON string, convert it into readable HTML text, and use this "Toast" method to display the HTML text to the user. I use it for special "ad hoc" displays like this.

This Toast technique can be found at:

https://groups.google.com/g/mementodatabase/c/MgdqN3hhYKk/m/e881bYtqAAAJ

[Er Mo]

Hallo

Memento hat einige Möglichkeiten etwas anzuzeigen , es kommt auf die Mennge an . message() ist für kurze machrichten die auf den Handy nach ein paar Sekunten wider verschwinden . dialog() muss vom User bestätigt werden . Auch kann man Infos im Widgets anzeigen . Wenn du aber mehrere Datensätze anzeigen willst würte ich einen Filter nehmen  . Ein Feld hinzufügen ( Suche ) und den Wert in Feld von Skript ändern ( 1 ) . Ein Eingerichteter Filter wird dann nur die Einträge ( 1 ) anzeigen . Man muss aber auch das Suchefeld ( Suche ) wider löschen ( 0 )

Hello
Memento offers several ways to display information, depending on the quantity. `message()` is for short messages that disappear from the phone after a few seconds. `dialog()` requires user confirmation. Information can also be displayed in widgets. However, if you want to display multiple records, I would recommend using a filter. Add a field (`Search`) and change the value in the script's field (`1`). A configured filter will then only display the entries (`1`). You must also clear the search field (`Search`) afterward (`0`).

Ernst