This is definitely possible. Depending a lot on your coding skills with jsl and how complicated the system will be (if it will be just where filtering with simple where clauses, it could be quite simple to do).
Couple of things to think about:
- Use template string and then Eval Insert to add values there (or just concatenate strings together).
- How do you want to handle like/=? Display Boxes (radio box, check box, combo box) or maybe some specific character in the Text Edit Box which would indicate how to parse the text?
- How to handle in and multiple like conditions (filter1 like something or filter2 like something)?
- How to handle not?
- Modal window gives you easy way to handle the flow of script
- Think about how the system might be expanded later
- Scripting Guide and Scripting Index will be a great help. Also JMP Help will help with different Display Boxes.
Below is very simple example on using template string and then using eval insert to build the final SQL.
Names Default To Here(1);
sqlTemplate = "\[
select *
from *
where 1=1
^filter1Str^
^filter2Str^
]\";
filter1Str = "";
filter2Str = "";
rbSel = {"like", "in", "=", "not in", "not like"};
ex = New Window("Dialog() example",
<<Modal,
<<Return Result,
V List Box(
H List box(TextBox("filter1: "), filter1txt = Text Edit Box(), filter1rb = Radio Box(rbSel)),
H List box(TextBox("filter2: "), filter2txt = Text Edit Box(), filter2rb = Radio Box(rbSel)),
H List Box(Button Box("OK"), Button Box("Cancel"))
)
);
If(ex["filter1txt"] != "",
filter1Str = "and filter1 " || rbSel[ex["filter1rb"]] || " " || ex["filter1txt"];
);
If(ex["filter2txt"] != "",
filter2Str = "and filter2 " || rbSel[ex["filter2rb"]] || " " || ex["filter2txt"];
);
finalSql = Trim Whitespace(Eval Insert(sqlTemplate));
Write(finalSQl);
I have built one quite similar system, which builds complete SQL-queries depending on user inputs and choices in an user interface (and I'm currently doing full rewrite on that when I have time, because expanding it got too difficult).
-Jarmo