Page 1 of 1
How do I: Filter, based on 2nd table?
Posted: 2019-12-04 13:07
by onoehring
Hi
I am writing some custom code to open the table view von table C but only show filtered items. But how do I tell AG which filter to use?
The database is set up like this:
C has a field that is lookup from table L which has a field that is lookup from table O.
I need to create a filter which shows all records in table C that have a certain (my filter criteria) setting in table L, in the lookup field that comes from table O.
(can this be understood?)
So: How do I open (GET parameters?, options=>?) table L in tableview with my filter?
PS: A custom SQL as filter is fine - but how do I tell AG to use my SQL to display the records instead of all?
Olaf
Re: How do I: Filter, based on 2nd table?
Posted: 2019-12-04 13:37
by jsetzer
Hi Olaf,
did you see the $options->QueryWhere property in TABLENAME_init()-function?
Best,
Jan
Re: How do I: Filter, based on 2nd table?
Posted: 2019-12-04 14:26
by onoehring
Hi Jan,
I hoped for a quick an easy answer. No, I did not see this option .. I looked in the documentation and there this was not listed and in -ini I must have overlooked it.
Thank you very much.
Olaf
Re: How do I: Filter, based on 2nd table?
Posted: 2019-12-04 16:05
by onoehring
Hi,
I do not find any documentation for this. So I am asking: Can I use a new SQL statement there or only a WHERE clause?
As I need some JOINS in my SQL before the WHERE I am wondering.
Olaf
Re: How do I: Filter, based on 2nd table?
Posted: 2019-12-04 16:15
by jsetzer
Hi Olaf,
docs are available online:
https://bigprof.com/appgini/help/advanc ... ename_init
https://bigprof.com/appgini/help/advanc ... ist-object
The SQL Query usually already contains all joins for each and every modeled lookup.
Jan
Re: How do I: Filter, based on 2nd table?
Posted: 2019-12-04 16:39
by onoehring
Hi Jan,
thanks, sorry for being annoying.
I found a solution which should work (will try later):
Code: Select all
WHERE
mytable.PK IN
(Select .... HERE THE QUERY THAT SELECTS ALL PKs THAT I NEED
Olaf
Re: How do I: Filter, based on 2nd table?
Posted: 2019-12-05 06:26
by onoehring
Hi Jan,
thanks for the links, I was looking at the pdf docs only. But even at the first link the $options->QueryWhere property is missing.
Bute the code on the bottom of the page helped:
Code: Select all
ob_start();
$xc=get_object_vars($options);
ksort($xc);
print_r($xc);
$c=ob_get_clean();
echo "<pre>".htmlspecialchars($c)."</pre>";
Olaf
Re: How do I: Filter, based on 2nd table?
Posted: 2019-12-05 06:31
by jsetzer
onoehring wrote: ↑2019-12-05 06:26
But even at the first link the $options->QueryWhere property is missing.
Sorry, I do not understand if there is still a problem or not:
There is the documentation for the
_init()-function and there is also the documentation for the
$options-parameter.
Some more links:
viewtopic.php?t=2090
viewtopic.php?t=3105
viewtopic.php?t=1173
If I remember right, this has also been described in the tutorial videos in Udemy courses.
Regards,
Jan
Re: How do I: Filter, based on 2nd table?
Posted: 2019-12-05 06:33
by onoehring
Hi Jan,
yes, there is the QueryWhere option, but it's not documented in the page itself. One can find it when running the code (in _init) I added to my former posting.
Olaf
Re: How do I: Filter, based on 2nd table?
Posted: 2019-12-05 07:54
by onoehring
Hi Jan,
well, my QueryWhere works .. initially.
I added a test for $_GET in the URL to the INI and according to that value I set my QueryWhere.
The problem occurs, when I want to flip through pages or sort: Then, of course, the $_GET parameters are not available anymore and my QueryWhere is lost.
How would you suggest solving this issue?
I am thinking of using a session variable - but where (and on which event) will I clear that again? Maybe there is a way to check if the former table (before reloading) was filtered / had a QueryWhere set? And even if: How do I then remove it again.
Any suggestions / ideas?
Olaf
Re: How do I: Filter, based on 2nd table?
Posted: 2019-12-05 08:30
by jsetzer
Yes, Olaf, it is not easy. I'm also using Session variables for that.
If you just prepend the table name to the session variable, it should work when opening a different table, for example you could name your session variables like this:
Code: Select all
$_SESSION["myfilter_table1_column1"] = "123"
$_SESSION["myfilter_table1_column2"] = "A"
On init(), only read filters for the current table and apply QueryWhere conditions.
Or you could even think about storing more SQL-like conditions (I did not test this) ...
Code: Select all
$_SESSION["myfilter_table1_column1"] = "=123"
$_SESSION["myfilter_table1_column2"] = "IN ('a', 'b', 'c')"
$_SESSION["myfilter_table1_column3"] = "BETWEEN 5 AND 10"
$_SESSION["myfilter_table2_column1"] = ">=27"
You can do amazing stuff with
$options->QueryWhere and Session-Variables, have a look at my "Quickfilters" implementation:
- chrome_JokKwXFfmr.png (91.19 KiB) Viewed 5110 times
Re: How do I: Filter, based on 2nd table?
Posted: 2019-12-05 09:14
by onoehring
Hi Jan,
marvelous!
I very much like the idea of prepending the session variable with the tablename. My solution works for now ... but shows my hint that the data has been prefiltered also in other parts of the application. I will probably use your suggestion to improve my solution.
Thank you very much.
Olaf