[Solved] Form control filter by date

[hcon]

Hi , I've been struggling for several hours trying to use a date form control as a filter . I'm using the following macro to apply the filter.

Code: Select all

Sub searchDate
dim oFilter as object
dim oFormCtl as object

oFormCtl = ThisComponent.Drawpage.Forms.getByName("MainForm")
oFilter = oFormCtl.getByName("DateBox")
if oFilter.Text <> "" then
strFilter="'Date Acquired' > {D '" & oFilter.Text & "'}"
oFormCtl.Filter=strFilter
rem MsgBox "Running filter " & oFormCtl.Filter
oFormCtl.ApplyFilter = True
else
oFormCtl.ApplyFilter = False
end if

oFormCtl.Reload 
End Sub

All table rows are returned by this code - regardless of the date value set in the form control.

If I change the relational operator in the filter to "<" then no table rows are returned regardless of date.
i.e.

Code: Select all

strFilter="'Date Acquired' < {D '" & oFilter.Text & "'}"

Really grateful for any pointers

[Villeroy]

Stop this macro madness! Nother example base attached.

[hcon]

Thanks very much for the example Villeroy ( and noteworthy that you uploaded it at 2.30 AM! ) . Your example uses a dedicated table "DateFilter" to store the filter params. I don't understand why this approach seems to be needed for dates only - whereas filters matching strings can be made to work fine using "macro madness". I wish I understood why this was, but in the mean time I guess I should concentrate on being grateful that there is a work around! It seems a big increase in complexity just to handle date filtering - i.e. dedicated table just to pass parameters as well as a number of query definitions seem to be required.

As you can probably tell, I have not worked much with databases before hence my confusion about how your example is working ( I started out offering to create some macros [mad ones] for scalc for my partners office admin job... and one thing has led to another..hmpff ) .

The form seems to issue the SQL command [ SELECT * FROM "DateFilter" AS "DateFilter" WHERE "ID" = 0 ] which seems to be extracting the control dates from the DateFilter table , but I am not sure what they are being assigned to in this case . Also the qFilterReadOnly query seems to cover similiar ground.

You have a number of queries defined.
qFilterReadOnly seems to be capable of reading and filtering the main table, but I am not sure what event causes this to be run. qFilterByParams is a bit more of a mystery to me - I can't see what it is needed for , and also how it gets it's parameters :pFrom and :pUntil ( I thought this would cause a user input box to appear - but I haven't seen any appear). Similiarly with qSameMonth I am not sure why it is needed or when it is run.

This is not the end of my ignorance. I am also at a loss to understand how the "Date From" and "Date To" controls are configured to write the DateFilter table ( which I assume they are).

I clearly need to spend some more time studying your example and reading the docs.
Many thanks for your help.

[Villeroy]

You got the table trick. This is necessary because forms can be used to filter subforms, but only if both forms are bound to some record sets. Since an input control always selects some input value into a field, the filter table serves as a data pit to take the bound values at a defined position (the row where ID=0). This way the criteria values do not mess with your actual user data.

There are 3 queries:
1)"qSameMonth" is a residue of a former version. Ignore that one.
2)"qFilterReadOnly" uses a conventional way to get the filtered record set using both tables, the list and the filter where Filter.ID=0. In Base all record sets from more than 1 table are read-only. This query can serve several purposes. For instance, you may create a report from this query which reflects the filtered row sets only.

3)"qFilterByParams" selects the filtered record set from the one and only data table "List" by means of input parameters. This way you get a writable record set loaded into the subform.
Parameters can be substituted by parent forms.
The subform is bound to the parameter query substituting parameter "pFrom" with parent form's "Date" and substituting parameter "pUntil" with parent form's "Date2". Have a look at the subform's data properties.

[hcon]

Thanks Villeroy. It's getting clearer, and I can now make progress thanks to your help.
Best regards

[ttreker]

This is an eloquent solution and I do not wish to take anything away from it and Villeroy's efforts. He has demonstrated some powerful techniques and understanding how this solution works provides valuable insight. However, I disagree that it has solved the problem in any general way, and I would suggest that the implication that somehow their is something inherently wrong with using macros over SQL and queries is not appropriate in this context. What approach to use should be dictated by the nature of the problem which needs to be solved. The problem as originally stated was inherently a valid Basic coding question. I would expand the question for this post, since my estimation is that a lot of people dealing with UI filtering in general are landing on this page (as I did) and being mislead by the stamp of [Solved] and the misdirection away from a macro solution. A macro solution *should* work. Why it didn't is a valid question. How to get it to work is a valid question.

Here is the problem as I see it more broadly stated:

How do I programatically configure the filter on a form for filtering dates and have it work consistently as expected?

Even more broadly:

How do I programatically configure the filter on a form for filtering in general on multiple fields and field types and have it work consistently as expected?

The reason I expanded this problem statement is because the reality is that it is almost worthless to not be able to construct a filter for dates without being able to combine it with the already existing filter selected by the user. In fact, date filter should be part of the filtering dialog UI (regarding which I noticed that OOo Base development team is looking for a developer to redesign and implement.) The relatively trivial question of getting a single date filter to work consistently, should be a no brainer. However, if Base is to be of any value to anyone aside from those willing to spend endless hours mucking around with it, this broad problem needs a solution.

BTW, not to beat up on Villeroy's solution, but it is unreasonable not to point out that it requires a lot of extraneous structure that clutters the tables and queries with unnecessary support tables. This structure multiplies for every new form being created that needs independent date filtering. In any moderately complex situation, this approach would be rather cumbersome.

Further, Villeroy's solution only works on queries and won't work for filtering tables. Again, the real issue is that there is a filter on a form, and it is important to know how to manage it in basic for any meaningful, non-kludgy UI experience.

Seamless filtering is fundamental to a clean UI for Base. it really needs clean date filtering at the core C++/UNO Model-View end of things, but that is for the developers forums.

Anyway, I am going to try to find some time to look into this (rather than just shooting off my mouth about someone else's efforts). I am new to developing in OOo Base, so I don't know what I will find or when. In the mean time the Villeroy solution is a work around to this inherently macro-based problem.

(please forgive my criticisms, Villeroy, they really aren't aim at your efforts they are aimed at the shortcomings of OOo Base that the community needs to help address.)

[Villeroy]

You are right. My filters are not usable in a general way where the user of a form can compose his/her own set of filter criteria. <nit_picking>My filters are no filters at all since they modify the form's row set one level below form filters.</nit_picking>
This technique was my approach to tackle the most frequently asked Base related question about how to select records by means of form control values when the controls are designed to select values into records and only subforms are designed to select records by the parent form's linked criteria fields.
The usual [and still perfectly valid] answer was a macro of some kind which required a minimum of programming skills to make it work. Since all those macros are written in procedural Basic with hard coded object names, they could not provide a generic add-on solution.

You macro code seems to reinvent the form based filter where you switch into a special mode to you enter filter criteria instead of data into your form. In this mode you can use a small navigator tool to edit filter criteria and the logical AND/OR connections. You don't need any extra controls and seemingly it allows for the whole hierarchy of forms and subforms being involved.

Then there is the "magic stick" which adds another filter criterion based on the currently selected value (WHERE ... AND SelectedField=SelectedValue)
Finally there is a filter dialog similar to the one in Calc. I have no idea why this one is accessible from to grid views only and why it is limited to 3 conditions, but this is the one I use to use frequently. You can retrieve a grid view for any Base form hittin the last button on the navigation toolbar and then the filter icon in the toolbar on top of the grid view.

BTW, not to beat up on Villeroy's solution, but it is unreasonable not to point out that it requires a lot of extraneous structure that clutters the tables and queries with unnecessary support tables. This structure multiplies for every new form being created that needs independent date filtering. In any moderately complex situation, this approach would be rather cumbersome.

The criteria are stored in rows (Filter.* WHERE FID=0) so you need only one table with one row per set of filter criteria or some other table with a few rows in case of ORed conditions (WHERE =Filter.Y).

As far as I recall, there are situations where form filters fail silently (queries based on direct SQL???). I don't think that calling the same filter method through the API can cure these problems. To debug your macro code you could set your date filter manually and see if it is possible to filter your form in the way you want and if so, you can compare the resulting filter string with the filter string set by your macro code.
The MRI extension is indispensible to find your path through the API. There are other object inspectors, but this one is simply the best. See http://user.services.openoffice.org/en/ ... 10#p132810 for a link and a short demo with recorded code and screenshots.

[Villeroy]

Just saw that you made a mistake in your Basic code.
The Text property of a date control is useless. It may be any kind of date expression in the respective local context.

A date control has a pseudo-property "Value" (method getValue()) which uses a rather crude format misusing an integer number whose 8 digits represent an ISO date.
Today's "Value" of a date control is integer number 20100405
At least this value is unambiguous and for your filter string you concatenate it like
strISO = Left(v,4) &"-"& Mid(v,3,2) &"-"& Right(v,2)
in order to get strISO="2010-04-05"

[ttreker]

Hey, Villeroy, please forgive me. I am not the one that submitted the code. I believe the original post was from hcon. I am just shooting off at the mouth I have written little Basic code so far in Base, myself. You have clearly put some work into this and come across some real issues that I haven't yet had time to explore. Judging from much of my experience so far with Base, I won't be surprised to find quirks in the code-- even ones that are simply insurmountable. It is also abundantly clear that a lot is going on with the filter code as I think that you have alluded to in your last post and I don't expect easy solutions. I have actually been trying to minimize any coding efforts for my application (although I am an experienced developer and would be comfortable drilling to the depths on the C++/UNO side of things if I had the time right now). But I find the Base filtering structure extremely frustrating when it comes to dates and have decided it must be addressed for my application to be of value; actually, this is in general a real fly in the oinment for Base in my opinion in general. Date filtering is central to a point-and-click desktop database for it to of value as a tool for knocking out powerful ad hoc database apps. Date filtering has been central to every db app I have developed over the last 20 years. It can't be brushed under the carpet.

My criticisms aren't really of your fine efforts; they are of the state of affairs of Base in regard to date filtering. I am really trying to speak to the community here and say that duct tape and bailing wire solutions to the date filtering problem are simply not good enough. Date range filtering, combined with other filter criteria is simply essential for the usability of base. The app that I have been working on has a lot going on, yet so far has only needed Basic code for duplicating records. Everything else has been point and click. Ideally, I shouldn't need special code to filter on date ranges, but clearly I do. I should be able to filter on dates using the filtering dialogs you mentioned where they provide date selections with spiners and dropdowns for both ends of a date range and integrate them with other filter criteria on other fields. I don't seem to see any reasonable solutions out there in the community that provide even a Basic dialog shoehorned in to fill this void. Yours was as close as it got, but when I started applying it to my app its short comings for my app became apparent. There needs to be a clear, unambiguous way to provide date filtering as seamlessly as possible into the UI filtering mechanism.

Your comment

I have no idea why this one is accessible from to grid views only and why it is limited to 3 conditions

is addressed by the OOo Base developer community. They are looking for a developer to fix these problems. If I only had the time, I'ld take it on and throw in the date filter to boot:

http://dba.openoffice.org/development/p ... new_filter

For now I am going to try to dive into this myself and see if I can't develop a general purpose piece of code in basic that smooths some of this out. To this end, your pointer to the MRI extension looks like a really powerful tool for drilling down into the system and I have installed it. Thanks for that tip!

Also, some of the comments about hcon's code you provided in your last post will probably help steer me in the right direction. Thanks much.

[ttreker]

Villeroy,

Have you looked at this extension yet?

http://extensions.services.openoffice.o ... /BaseTools

I don't know what value it will be for the date filtering issue, but it might make coding a little more fluid.

[ttreker]

This is a post that gets you on the right track for managing date filtering of a form programatically:

http://user.services.openoffice.org/en/ ... te+control

I believe it addresses the original question of this post, and goes beyond that. The ultimate solution of that forum post is at the bottom of that thread.

Villeroy's suggestion above to use the Mri tool is indespensible. This tool is incredibly powerful.

Villeroy points to a post describing his use of the tool. This post points to a dead link for the tool itself which is installed as an extension. Here is the correct link:

http://extensions.services.openoffice.o ... roject/MRI

The Mri tool is easy to use. Every Basic developer should know how to use it as Villeroy described.

There is one caveat, but it is not with the tool itself; it is with UNO. UNO is steeped deeply in complex structure, as it simply has to be in order to address the complex problem of a remote, cross-platform office suite. However, Sun has done a pretty good job of documenting it from the development side-- at least as far the "First Steps" go, anyway. I highly recommend to all OOo Basic developers that they run through the following developers tutorial:

http://wiki.services.openoffice.org/wik ... irst_Steps

It is meant not only for introducing hardcore OOo product developers to developing in UNO, but it is also designed to provide a foundation for Basic developers. You don't need to run any of the examples, try to follow the arguments even if you are not familiar with advance issues such as IDL, Java, or the like. Sun does a great job of explaining at a level that should get any developer up to speed on the overall picture.

OOo Basic makes it easier to interact with UNO, and they point this out in First Steps.

Remember that the Mri Tool is an instance inspector. It allows dynamic runtime interaction with the object of interest. Once you have it pointed at an UNO object there is a lot you can do to the object through the Mri tool.

These posts and tools should get anyone attempting to tame date filtering on forms well on their way towards a filtering solution that works for their particular situation.

When I complete my investigation, I will give a detailed synopsis of what I have learned and what I did.

[Villeroy]

Thank you very much for this extremely valuable contribution (and fixing my broken links). Most newcomers do not investigate any further than needed to conclude that OOo is not MSOffice, therefore way too complicated to learn.

The Mri tool is easy to use. Every Basic developer should know how to use it as Villeroy described.

And Python, Beanshell, Java as well. I do not write in Java, nevertheless I have posted working Java solutions entirely recorded in MRI.

[ttreker]

Villeroy, perhaps I should also point out to the community that MRI comes with a help file which gets you up and running with it *immediately*, and shows you how *easy* it is to use it to explore the system even if you are one who finds UNO intimidating.

Further, as you pointed out in your referenced post, the code window is just fantastic. It does a good job of hiding away at first, but once a developer finds that code pane they will never loose it, because they will love it. (drag the bottom edge up or double right-mouse-button click in the lower right hand corner).

To reiterate to the community what Villeroy explained in his referenced thread, as you explore the system with MRI it generates the code you need to get to the places you want to go in whatever scripting language you choose. If you navigate to something of interest in the MRI tool you will automatically have the code to get there written for you in the code window.

You don't have to think about it you just cut and paste from the code window to your macro whatever you need! its great!!!

Stated another way, MRI allows a developer who is intimidated by the depth and complexity of UNO, to explore UNO without becoming an expert in it. MRI allows you to do all sorts of things with UNO while knowing very little about it!

[ttreker]

The solution to the problem of this original post is very simple. (Reread the original problem at the top of this thread)

The filter string in the line

Code: Select all

strFilter="'Date Acquired' > {D '" & oFilter.Text & "'}"

is incorrect.

You need to use double quotes around the field name, not single quotes. I haven't researched how to do this in Basic within the construct of double quotes but no doubt this is a straight forward language construct. The filter string itself that is being set should look something like this:

Code: Select all

( "Date Acquired" > {D '2010-04-01' } )

It is worth noting how I got to this solution. Villeroy pointed me to the tools that provided a beeline to the answer. I used the MRI tool. This tool is really powerful. It provides point-and-click access to the methods and properties of the objects of your runtime system. It allows for navigating these objects with double clicks. It allows you not only to explore the structures of a running system, but also to change them on the fly as you would in code, but without writing code. When you do so it writes and records the code for you so that you can cut-and-paste it when you work out your solution. It allows you to call simple methods, even some methods that require simple parameters. It provides popup windows with selection lists of indexes or names for collections indexed as such.

I used the MRI tool to get a hold of the Form and then used the MRI interface to experiment with various filter strings. Here is explicitly what I did:

1) Through any event call from the form (I put down a button and used its execute event) I put the following MRI debug line in:

Code: Select all

MRI ThisComponent

2) This brought up the an MRI inspector window on the ThisComponent object. From here I double clicked the getDrawPage() method (or the equivalent property DrawPage, either works).
3) From the resulting MRI inspector window which now is inspecting the drawpage, I double clicked the getForms() method.
4) From the resulting MRI inspector window which now is inspecting a Forms collection, I double clicked the getByName() method. This presents a selection list of the Forms in this collection. I double clicked the desired form name.
5) The MRI inspector is now inspecting the form, which is the target that holds the filter string of this original post.
6) I now selected the properties and using the Get and Set modes from the Mode dropdown menu on the menubar of the inspector window, I examined and set the Filter property.
7) When changing the Filter property, I went to the methods listing of the inspector and double clicked the reload() method to see the effect of the filter change.

Using this tool, I was able to reproduce the original problem and find the solution. All of this was done with one line of code. Again, here is the line:

Code: Select all

MRI ThisComponent

The rest was point and click.

See above posts and replies to point you to getting MRI running. Explore all its features. It is really worth it!

[ttreker]

BTW, it is worth understanding why the original problem of this post occurred and how to think about these problems in general when tackling not only coding, but also when just running Base as a desktop point-and-click rdbms.

First thing to note is that all rdbms systems with an SQL interface handle quotes differently. This is the source of confusion, even for those who have a good handle on SQL. Moving from system to system can cause confusion regarding how quotes work.

The Analyse-SQL (ASQL) parser of Base is just another SQL parser handling quotes its own way. To test how ASQL handles a particular parsing situation, just bring up the "Create Query in SQL View" window from the Queries task list in Base. Be certain to not select the "Run SQL Directly" option (the toggle button that says SQL on it should be undepressed). This will be the default. (Incidentally, you don't have to save this query to play around in this window) The question of the appropriate string which was begged by the question raised at the top of this forum thread could have been answered in this window by making it the target of the WHERE clause in this query configuration dialog. Both the original problem and the solution can be explore in this window.

In this context, it becomes a little clearer what the actual problem was with the original filter string. Here is my explanation without getting too formal:

1) In Base ASQL tables, column names, etc. are put in double quotes. They don't always have to be, however, quotes always work and no quotes often don't. They are used for names with spaces or names that are case sensitive.
2) In Base ASQL it interprets single quoted items as literal values, and not as columns.
3) So the comparison in the original string of this forum post was comparing too literals in ASQL, i.e. it was comparing a constant to a constant rather than a constant against the column value of each record in the table. The result happened to be true so all records tested true against this predicate and therefore all records showed in the results set. If it had been false (say by changing the operator to <), no records would have shown.

So with some system understanding, the MRI tool is unnecessary. However, exploring the system with the MRI tool gained me the system understanding to put this all together.

[ttreker]

Here is a post without noise providing just the answer to the originally posted question at the top:

Double quotes are needed around the column name, not single quotes.

'Date Acquired' needs to be "Date Acquired"

(how to construct this in OOo Basic is not provided here. I don't know off the top of my head.)

single quotes are treated as an SQL literal constant, the double quotes are treated as a column name.

[Villeroy]

Here is a post without noise providing just the answer to the originally posted question at the top:

Double quotes are needed around the column name, not single quotes.

'Date Acquired' needs to be "Date Acquired"

(how to construct this in OOo Basic is not provided here. I don't know off the top of my head.)

single quotes are treated as an SQL literal constant, the double quotes are treated as a column name.

Good catch. The quotes are wrong.

Code: Select all

s = "Nonsense"
print "Quoted "& """"& s & """"

or with " as ascii code 34:

Code: Select all

print "Quoted "& chr(34) & s & chr(34)

[evwool]

I would suggest that the implication that somehow their is something inherently wrong with using macros over SQL and queries is not appropriate in this context.

There is an enormous advantage to having a non-macro solution even if you are completely comfortable with macro coding. When using OO at work, I have less trouble persuading users to open a non-macro db (they are startled by the macro warning). No macro means less chance of another user having to update their system to match the macro creator's. Although OO isn't as infamous as MS Access for abandoning old code, SQL has been around a long time so the design can be adapted more easily across platforms.
I love this form filter and will be using it in my own db to create an instant count of records between 2 dates. Apart from the Filter Table concept the whole db has many other valuable 'How To's' within it which make it worth a close study by OO BASE learners. e.g. How to build a parameter query, how to link a form by more than one Master Child field, the existence of and how to use the Date Field control (anyone who has had to learn how to program an Access Calendar control will appreciate this feature). And that's just the start.

[ttreker]

Your points are all well-taken, evwool, and I will keep them in mind. I like the idea of creating a file that won't bark at someone to turn off security on macros and I will keep that "context" in mind for future apps for which it might apply.

I would qualify your statement by saying "there can be an enormous advantage to having a non-macro solution..." I would also point out that nothing in my quote contradicts this.

By the way, there can also be enormous disadvantages to a solution like Villeroy's and I believe I have already pointed them out in this thread. The issue is context. The fact is none of us know hcon's context so we should be careful about misleading him. The safest thing to do is to just answer the very direct and clear question he asked.

This forum thread was not worthy of being marked Solved until my recent posts. I am not trying to toot my horn here, nor am a trying to point any fingers as everyone has made a contribution. I am merely trying to raise awareness of how easy it is to send somebody off track. In this kind of product, people really have to dig and sort to get to answers. We can burn I lot of time running down the wrong path.

I am glad to have popped Villeroy's solution into my toolbox for future reference, but I also think hcon was mislead by Villeroy's strong language. His solution should have been presented as a possible macroless alternative to the approach hcon was taking. But my read is that the message was that the macro approach was wrong. It is simply not wrong to take the Macro approach.

[ki3456]

Nother example base attached.

The example gave unexpected results for me on my system when setting pFrom and pUntil to the same date and pressing ok button. Subform displays all records for the date but it appears no records can be added.

[Villeroy]

Nother example base attached.

The example gave unexpected results for me on my system when setting pFrom and pUntil to the same date and pressing ok button. Subform displays all records for the date but it appears no records can be added.

1. The filter works for me. Your version 3.2 had several problems regarding subforms.
2. I can confirm that Base forms, Java 6.18+ and dates do not play well. I can not edit nor insert nor delete records using the form. Only the naked grid view of the tables works as expected.
Recent topic: Cannot Alter or Delete records with date field

[goodvibes]

Thanks Villeroy for the info about a date control having a Value property containing an int with value YYYYMMDD.
There is a typo in this though:

A date control has a pseudo-property "Value" (method getValue()) which uses a rather crude format misusing an integer number whose 8 digits represent an ISO date.
Today's "Value" of a date control is integer number 20100405
At least this value is unambiguous and for your filter string you concatenate it like
strISO = Left(v,4) &"-"& Mid(v,3,2) &"-"& Right(v,2)
in order to get strISO="2010-04-05"

Should be:

Code: Select all

strISO = Left(v,4) &"-"& Mid(v,5,2) &"-"& Right(v,2)

Actually, now I’ve had a chance to try it and examine with mri, there is no Value property (only CurrentValue) and no method getValue(), only getCurrentValue(). CurrentValue is a struct with Day, Month, Year but I can find no way to return an integer in YYYYMMDD format….

[Villeroy]

How to filter by criteria in form controls without any macro: http://forum.openoffice.org/en/forum/do ... p?id=11663 (search this forum for "power filtering").

LibreOffice canceled the integer dates and times in favor of date and time structs.