Hello Cujos and Derek,
I had a look at Derek's submission and, as always, it's spot on and without a single line of code !! Nice job
Everyone on this forum knows that I'm a big fan of complicated solutions and lines of code are like poetry to me.
The code bellow was a saturday morning coffee challenge, and might interest you if you want to know what happens in MVD but, again, Derek's solution is THE solution.
Oh, by the way old friends, I don't post often but read the forum every day
What the Tablergrid does not show but understands from what Derek as set up is :
SELECT DISTINCT
"document"."record_count" as "document.record_count",
"document"."docdate" as "document.docdate",
"doctype"."doctype" as "doctype.doctype",
"document".id,
"document"."id_doctype" as "document.id_doctype"
FROM
"document"
LEFT OUTER JOIN "doctype" ON "document"."id_doctype"="doctype".id
ORDER BY
"document"."record_count" ASC
What does the "hidden search" button do when you select an item in the combobox ? It's filtering the SQL query that gets the complete list of document from the database (the query above).
To filter this, when you change your selection in the combobox, MVD injects a line in the above query :
When the combobox says "Invoice", the filtering line looks like :
WHERE document.id_doctype = 1
and is located just before the "ORDER BY" line. (1 is the id of the Invoice doc type in the database).
When the combobox says "Receipt", the filtering line looks like :
WHERE document.id_doctype = 2
And when the combobox is empty, there are many possibilities.
The simplest one is just to remove the "WHERE" line of course,
but this is equivalent to :
WHERE document.id_doctype = 1 OR document.id_doctype = 2
--the filter has to be repeated, document.id_doctype = 1 OR 2 does not work
and also equivalent to
WHERE document.id_doctype IN (1,2)
And finally, if you disconnect the "hidden search" button from combobox by removing the incremental search parameter in the combobox, you could replace all this, easy and efficient, by this, heavy and unpractical code
procedure Form1_ComboBox1_OnCloseUp (Sender: TObject); //fires each time the combobox is closed
begin
if Form1.ComboBox1.ItemIndex = 0 then //if first item of combobox is selected
begin
Form1.TableGrid1.dbFilter := '1 = 1';
//Form1.TableGrid1.dbFilter := 'id_doctype = 1 OR id_doctype = 2'; //EQUIVALENT
//Form1.TableGrid1.dbFilter := 'id_doctype IN (1,2)'; //EQUIVALENT
Form1.TableGrid1.dbUpdate; //this executes the filtering
end
else if Form1.ComboBox1.ItemIndex = 1 then //else if second item of the combobox is selected
begin
Form1.TableGrid1.dbFilter := 'id_doctype = 1';
Form1.TableGrid1.dbUpdate;
end
else if Form1.ComboBox1.ItemIndex = 2 then //else if third item of the combobox is selected
begin
Form1.TableGrid1.dbFilter := 'id_doctype = 2';
Form1.TableGrid1.dbUpdate;
end;
end;
I've made you a little package, just if you are curious
Again, as I already said, Derek's solution is perfect and simple.
Mine is complicated and won't even work anymore if you had another document type to you collection. It will have to be adapted in order to work, where Derek's will still work.
This was just for "academical interest"... well, at least it interests me
Cheers to all and wish you a good week-end
Math
I'm a very good housekeeper !
Each time I get a divorce, I keep the house
Zaza Gabor