OpenOffice.org Forum at OOoForum.orgThe OpenOffice.org Forum
 
 [Home]   [FAQ]   [Search]   [Memberlist]   [Usergroups]   [Register
 [Profile]   [Log in to check your private messages]   [Log in

[SOLVED] Filter

 
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Base
View previous topic :: View next topic  
Author Message
erneichan
General User
General User


Joined: 09 Aug 2010
Posts: 13

PostPosted: Tue Aug 17, 2010 3:21 pm    Post subject: [SOLVED] Filter Reply with quote

I have the following query and it works fine. It is used to find alll the OPEN appointments for a particular day of the week for a doctor for a specific time.
i.e. Any Tuesday, 3:00PM for Dr. Anderson that is OPEN

It works fine. The system prompt for all the ? inputs in the following query. And, it goes to edit mode for change.

Just wonder if that is possible to have three dropdown list so that they do not have to key it in? I did read and see the suggestion on having one table record with the three fields. I tried on it and it will not refresh the data.

I hope there are other ways.

Thank you in advance for all your assistance.

Ernie

SELECT "AppointmentSchedule"."Appointment ID" AS "Appointment ID", "AppointmentSchedule"."Medical Doctor ID" AS "Medical Doctor ID", "AppointmentSchedule"."Appointment Date" AS "Appointment Date", "AppointmentSchedule"."Appointment Time" AS "Appointment Time", "AppointmentSchedule"."AppChartNum" AS "AppChartNum", "AppointmentSchedule"."Notes" AS "Notes" FROM "AppointmentSchedule" AS "AppointmentSchedule" WHERE DAYNAME( "AppointmentSchedule"."Appointment Date" ) = ? AND "AppointmentSchedule"."Medical Doctor ID" = ? AND "AppointmentSchedule"."Appointment Time" = ? AND "AppointmentSchedule"."AppChartNum" = 'OPEN' and "AppointmentSchedule"."Appointment Date" >= current_date ORDER BY "Appointment Date" ASC, "Appointment Time" ASC, "AppointmentSchedule"."Medical Doctor ID" ASC


Last edited by erneichan on Fri Aug 27, 2010 8:53 am; edited 1 time in total
Back to top
View user's profile Send private message
Arineckaig
OOo Advocate
OOo Advocate


Joined: 01 Mar 2004
Posts: 358

PostPosted: Wed Aug 25, 2010 4:02 am    Post subject: Reply with quote

Quote:
Just wonder if that is possible to have three dropdown list so that they do not have to key it in? I did read and see the suggestion on having one table record with the three fields. I tried on it and it will not refresh the data.

As you have had no replies so far, I dare to offer some suggestions. You do not say what version of OOo Base you are using, but I should warn you that currently there is a bug in versions 3.2.? which sometimes misleads by throwing an error when adding or editing a record in a filtered sub-form. I understand this error will be corrected in 3.3, but meanwhile there is a clunky work around.

For this reason there is merit in considering use of the comprehensive filtering facilities already provided by the OOo Base Navigation Bar form control. It is well suited to filtering records where the fields are largely from the same data source table, as would appear to be the case that you describe. The Base documentation tends to obscure the power of the Navigation Bar filtering icons.

As as example is better than description, you will find a sample Base file that I have prepared for downloading from:
http://dl.dropbox.com/u/10552709/FilterExamples.zip
LATER EDIT: This file will be updated from time to time - see my later post, dated 4 Sep, in this thread.

There are no macros, but the example makes use of related tables and thus frequent use of list boxes. If, however, the data source for your data form is a single table normal text boxes would suit. I have also included two methods that suffer from the 3.2 bug so that you may see how they are constructed and work in read only use.

I suggest there is no one best method for filtering but each has merits and weaknesses.

If you are further interested, please come back and I can post some more descriptive details about each method.

I should mention that the Form Navigation form control only permits ANDing of multiple filtering criteria. The form navigation TOOLBAR, however, permits more complex filters (including OR) but is not so user friendly. Again if you are interested, I will try to offer some guidance on how it can be used together with its powerful search tool - accessed through the binocular icon available only on the Toolbar and not the form control.

Edited 28 Aug 2010 to change location of download example
_________________
When this issue has been resolved, it helps other users of the forum if you add the word [Solved] to the Subject line of your 1st post (edit button top right).
OOo 3.4.1 and MySQL on MS Windows XP and Ubuntu


Last edited by Arineckaig on Sat Sep 04, 2010 1:25 am; edited 4 times in total
Back to top
View user's profile Send private message
erneichan
General User
General User


Joined: 09 Aug 2010
Posts: 13

PostPosted: Wed Aug 25, 2010 7:35 pm    Post subject: FILTER Reply with quote

Thank you so much for your reply.

I am sorry for missing out the critical information. I am using open office 3.2.1.

I did some search while I was waiting for a reply and discover the following document.

http://documentation.openoffice.org/files/documents/75/4770/file_4770.dat/Base%20Tutorial%20OOo.pdf

It gave me some insight into the FORM control. I was able to work on additional subforms and that help a bit.

I downloaded your odb and it is what I want. I do not understand how you can link three tables into the last one, the subform. Is that the three dropdowns are main forms too? Your solution is what I am looking for. Please help me out with some insight as to how it was done.

In addition, if you do not have a problem, please kindly share with me your experience with the utilization of the form navigation TOOLBAR. I like to know about the OR function too.

The three dropdown lists in my situation are 1) doctor, 2) day name and 3) time.
The 2nd and 3rd list are dependent on the selected entry of the first drop down list. i.e. Each doctor has their own work schedule and a session for each patient. They work different days of the week. Some of them, a session is 30 minutes and the other is 40 minutes. Each have their own table to build the dropdown list. The three are foreign keys to the table of appointments.

Thank you for all your help! It is highly appreciated.
Back to top
View user's profile Send private message
Arineckaig
OOo Advocate
OOo Advocate


Joined: 01 Mar 2004
Posts: 358

PostPosted: Thu Aug 26, 2010 12:40 am    Post subject: Reply with quote

You have justifiably put me on the spot and I must ask that you forgive me if I need time to give anything like coherent answers. Meanwhile let me me play for time by asking for more information.
Quote:
I downloaded your odb and it is what I want. I do not understand how you can link three tables into the last one, the subform. Is that the three dropdowns are main forms too? Your solution is what I am looking for. Please help me out with some insight as to how it was done.

To which of the forms in the example .odb are you seeking an insight into how it is done? They all differ in their techniques and purpose.

While I am drafting an explanation for that particular form document, and at the risk of offering grandfilial advice, I suggest much will be revealed by opening a form document in edit mode and selecting the "Form Navigator" icon from the "Form Design". This toolbar can be found from the Toolbars sub-menu of the View menu. The "Form Navigator" icon is the 5th from the top/left of that toolbar: it appears to have what looks like a compass needle in its bottom right corner.

The "Form Navigator" dialog will open to display the structure (forms and sub-forms) and contents/components of those data forms together with the relationship between the data forms. Right clicking on any of the items in the list and selecting "properties" will reveal the all important Properties dialog for any data form or its form component.

I would refer you to the better description of the "Form Navigator" in the Tutorial mentioned in your post starting around page 105.

Quote:
In addition, if you do not have a problem, please kindly share with me your experience with the utilization of the form navigation TOOLBAR. I like to know about the OR function too.

Again I call for time-out, as I will need to update and edit my notes. These were essentially prepared for my own use, and on re-reading I find my explanation utterly confusing. Due to the essential inclusion of images, it will be a case of uploading a PDF file that you can down load. Hopefully not before too long I will post here a link to that file once it is ready.

Sincere apologies for the frustration caused by my procrastinating answers.
Back to top
View user's profile Send private message
Arineckaig
OOo Advocate
OOo Advocate


Joined: 01 Mar 2004
Posts: 358

PostPosted: Thu Aug 26, 2010 9:51 am    Post subject: Reply with quote

Quote:
Please kindly share with me your experience with the utilization of the form navigation TOOLBAR. I like to know about the OR function too.
Due to the essential inclusion of images, it will be a case of uploading a PDF file that you can down load.

FWIW an initial rough draft should now be available from:
http://dl.dropbox.com/u/10552709/FilterExamples.zip
LATER EDIT: This file may be updated from time to time - see my later post, dated 4 Sep, in this thread.


Last edited by Arineckaig on Tue Sep 07, 2010 4:33 am; edited 3 times in total
Back to top
View user's profile Send private message
erneichan
General User
General User


Joined: 09 Aug 2010
Posts: 13

PostPosted: Thu Aug 26, 2010 6:24 pm    Post subject: Filter Reply with quote

The third one of your examples is the one that is best suit my need, the triple dropdown.When I tried to open that, it does not provide the EDIT option. That is the reason I have no way of getting to check out the code. As a newbie, I do not know how that option is turn on or turn off so that others cannot edit it. Let me know when you have a chance.
Thank you for your help.
Back to top
View user's profile Send private message
Arineckaig
OOo Advocate
OOo Advocate


Joined: 01 Mar 2004
Posts: 358

PostPosted: Fri Aug 27, 2010 12:15 am    Post subject: Reply with quote

Quote:
When I tried to open that, it does not provide the EDIT option.

You have defeated me. As a test I have just downloaded again the .odb from Mediafire and after right clicking on the "3. Triple filter" form and selecting "Edit" from the drop down list the form opens in edit mode without problem.

I can only suggest you try downloading the .odb file again. If that again fails come back and I will give you an alternate URL from which to download the file. I do agree that an demo file which you cannot examine is utterly frustrating - my apologies.
Back to top
View user's profile Send private message
erneichan
General User
General User


Joined: 09 Aug 2010
Posts: 13

PostPosted: Fri Aug 27, 2010 8:27 am    Post subject: [Solved] Filter Reply with quote

I downloaded it and it works. Now, I can see the EDIT and the example. The reason why it did not work last time was that I did not save the file. During download, they ask if I want to open it with openoffice.org and I did. And, the EDIT did not appears. When it is saved, the EDIT appears. Thank you for all your effort in helping me with the problem. Have a nice weekend.
Back to top
View user's profile Send private message
Arineckaig
OOo Advocate
OOo Advocate


Joined: 01 Mar 2004
Posts: 358

PostPosted: Sat Sep 04, 2010 1:21 am    Post subject: Reply with quote

I fear people may discover, or be referred to, the two download files that I posted earlier in this thread. These are essentially work-in-progress and I suspect they may be the cause more of confusion than help in that the URL references will be to files that will be updated from time to time. Currently I have added/changed some example form documents in the zipped Base .odb file.

As a result of questions already raised I have posted a premature draft of the explanation PDF file for the 'permanent filter' examples.

EDIT 7 Sep I have updated the explanation and it can now be downloaded now as a (750K) file zipped together with the ExampleDatabase file from:
http://dl.dropbox.com/u/10552709/FilterExamples.zip
Back to top
View user's profile Send private message
Display posts from previous:   
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Base All times are GMT - 8 Hours
Page 1 of 1

 
Jump to:  
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group