[Solved] Form Based Filter leads to error [solved in V3.2.1]

[MrLintux]

Hello together,

hopefully you can find an easy solution for my (little) problem:
In Base I generated a TODO List.
This TODO List has some information (Indeed it is part of a little CRM, which doe'snt care).
Part of the TODO Tasks (the data sets) is a DUE Flag which is connected to a boolean Field in the data Base - works fine at (nearly) )all.
If I apply a Form based filter to this form to show just the DUE tasks - it works fine:
clicking the form based filter button -> clicking the De Flag -> back to the data form -> I view just the DUE=TRUE data sets - fine.
But - when I unmark the DUE flag (for example if I got my job done) and try to commit =>
the result is an error.

I checked that and think the problem is based on the conflict that I'm in a data set, which is not corresponding to the filter criterium (which is DUE=TRUE, and after unmarking DUE, it is not for the actual data).
I would like to avoid programming a formbased filter by hand, just to execute the update of the data by generating an applyfilter task at the right place in the code.
Is there a way to achieve this with the conventional form based filter?
Thanks for your help.
Rup

One remark: I'm am very impressed by the work of you all specialists here in the forum. I took a lot of ideas and input from a lot of good guys here to fulfil my 'Little CRM'-task. Special thank for Drew Jensen, so far, whose explanations in the forum and documentation are an excellent source of information.

[Villeroy]

the result is an error.

Error messages do have a meaning.
I can apply a form based filter on a boolean field without problem.

[MrLintux]

Hello Villeroy,
it's incredible good what you guys are doing.
Many thanks for your effort in designing a test DB which of course I would had to do better to make you guys understand my problem.

Unfortunately I explained in a misleading way.
The problem is not in applying the filter to a boolean field - it is in changing the field value when the filter is active.
To answer your remark, the corresponding error message is:
SQL-Status: SOO10
Fehler-Code: -62
Invalid argument in JDBC call: parameter index out of range: 2

Of course, your test DB shows the problem in a much better way, so lets go.
If I load it and do the Filter on the B-field the result is as shown here:
(Pls. remark that I unset the B-filed value of data set No 9 - this is actually NOT committed by DB!)

DB with unmarked field

If I try to commit this field change by pushing the Triangle (next data set) the result is error :

Error Message

Bildschirmfoto-OpenOffice.org Base-1.png (8.77 KiB) Viewed 11190 times

By the way, there is no way to commit the change even by typing to a different data set in the table view of the form, for example by doing so it resets the B-field value in data set 9 back to 'TRUE'.
The desired solution would be to find a solution to change the B-field and reload the filter automatically to get this work.
Thank you
Rup

[Villeroy]

I can not reproduce the error, but it reminds me of another one which is the reason why I stick with v3.1.1
http://www.openoffice.org/issues/show_bug.cgi?id=108390

I posted a new issue http://www.openoffice.org/issues/show_bug.cgi?id=109549

[MrLintux]

Hello Villeroy,
this is a very interesting information.
Do I understand wright, that you can:
a) apply the form based filter in your test.odb to B set TRUE
b) unmark one of B field data to value FALSE (=unmarked)
c) go to next data set (which should result in a 'commit' signal for the changed data set)
d) no error occures and the changed data set is NOT part of the data sets displayed further on?
(Rem: Apply filter is still set to B=TRUE and active)

I tried this on Ubuntu 9,10 and ooo 3.1.1, Ubuntu 9,10 and ooo 3.2.0 und Windows XP and ooo 3.2.0 -> always with the described (mis)behavior and/or error.
Thanks again for a short reply
Rup

[Villeroy]

I tried a lot of things with filters on the boolean field and editing the boolean field but never get "no data availlable".

[MrLintux]

Inredible,
thanks for your help and feedback - hmm lets see how we can go further on.
I looked in the net and found a similar problem in
http://de.openoffice.info/viewtopic.php?f=8&t=36151
I gonna check whether someone can find a solution.
cu
Rup

[gWolf]

I tried a lot of things with filters on the boolean field and editing the boolean field but never get "no data availlable".

Hi Villeroy,

I believe you tried a lot of things but not what MrLintux and myself did.
Probably you are working with the native table which you can reach by clicking on "data source from table" or F4.
I also created a Form and put the table-widget (object) into it to avoid working with the F4-reachable table.
If I set a Filter wherever and I change exactly the value in the field to the filter-criterium ("job done" or "100%" Examples!) and safe the data -> I get the error.

If I do it in the native table, it works fine. Only in the Form with the Form-owned objects the error occurs.

But I can do following.
I change the value in the field what influences the filter-criteria ("job done" for example) and hit the "SAVE" Button. The error occurs. I confirm it with OK. Then I hit the Button "Refresh Data" the next error occurs like "no data found" and I quit it wit "NO". Then the data disappear and they are stored (after reviewing) correct in the data base.

Wolf

[DrewJensen]

Hi,

Villorey did open an issue on itis and the issue has been confirmed.

Work around wise - I tired a few ideas (short of coding up a macro to do the update against a STATEMENT) - move back to 3.1.1. Wish I could say something else, but given not just this but some other issues, I think that is exactly what I would do. (don't tell anyone but that is exactly what I actually do when using Base for real work, I use 3.1.1 out of he Ubuntu repos.. )

[MrLintux]

Hi,
after checking a few things I was surprised to find the following results:

*-> A form with an applied filter (it does not care whether it is applied by the filter funcs or eg. basic -> form.filter = true)
*-> and a view of an updated data set (ANY field!)
*-> which is in conflict with the filter criteria
*-> leads to the described error by moving to the netxt data set

Although from a programmes view logically, it is not for the average user.
I think this behavior is resonsible for a complete class of 'errors' in user's oo-base programming.

Perhaps the great guys who spend so much time for programming this great piece of software find their time to fix this.

At the moment I can't see no other a work around, than create a temp table as a subset
(INSERT INTO table [( column [,...] )] SelectStatement};-> WHERE 'put the filter conditions here') of the original table, attach the form to this temp table, change the values in the form (it is without filter!) and write it back to the original table from the forms record set by iterating through the changes in a basic macro.

This seems which is a pretty annoying task and not very elegant).
Has anyone another good idea?

So my wishes for oo are: Fix this
(and secondly: Implement a tabbed view control for forms. ) )
Best Regards
Rup

[Villeroy]

So my wishes for oo are: Fix this
(and secondly: Implement a tabbed view control for forms. ) )

The issue has been fixed today for target 3.2.1.
You can use Calc as carrier of input forms with tabs (sheets).

[MrLintux]

Hello Villeroy,
At the moment I'm sitting here wondering what great performance is possible in this community!
Thanks to you all for supporting me with my 4 little posts when finding this little issue.
I am really supprised what is happening here and I'm looking foreward to 3.2.1.
Thanks in special to you Villeroy for support and - at last - for the tip with the tabbed view thing.
cu
Rup

[Villeroy]

Thank you for the compliment.

A quick and ungly draft of an invoicing system attached to a spreadsheet:
http://user.services.openoffice.org/en/ ... 92#p119592
One sheet to enter new invoice (suffers from the same bug), one for print output and a last sheet to search invoices by criteria.

[MrLintux]

Hi,
thanks!
I'll take a look at it later when I'm out of this presentation.
Rup

[MrLintux]

Hello together,

unfortunately I have to work with my little CRM I was looking for a work around the actual problem.
So I was looking for a work around which is implementable without much effort and handy enough to work with.

As I sayed, the problem seems to be the above mentioned error(s) are resulting in a conflict between an applied filter and a changed field, which is in conflict with the applied filter condition after the change.

Altough this effect applies to every field (e.g. apply 'New Yourk' Filter - wrong written, but there is Data! Change the Adress Data to 'New York' - > Error) my main problem is handling my boolean 'Due' flag.

I give you a short work description:
In the morning when I start my little CRM .
Firstly, the table "Tasks" is scanned where actual Data is same or greater than the Date Field in that Table (In german we call this with the nice word "Wiedervorlage").
So by starting the form I supply the following SQL Statement through a basic macro (wich is connected to the forms "On Load" event):

Code: Select all

Sub DatenUpdate_Due_from_TasksDue ' the routine we call to cpoy NORDWEST.Due <- Tasks.Due 
   sURL = "BM_Call" ' find the correct DB Connection
  ' Two Statements:
  '  sSQL1 looks in the Tasks-Table for the Due-Date Field and sets the Due Flag:
  ' sSQL2 copies the Date field "Due" into the Entry Fielt for Documentation and clears the "Tasks"."Due" Field
   sSQL1 = "UPDATE ""NORDWEST"" SET ""NORDWEST"".""Due""=-1 WHERE ""NORDWEST"".""ID"" in (SELECT ""NW_ID"" FROM ""Tasks"" WHERE ""Tasks"".""Due"" IS NOT NULL AND DATEDIFF( 'dd', ""Tasks"".""Due"", CURDATE( ) ) >= 0);"
   sSQL2 = "UPDATE ""Tasks"" SET ""Tasks"".""Entry""=CONCAT(CONCAT('OLD DUE: ', ""Tasks"".""Due""),CONCAT(': ',""Tasks"".""Entry"")),""Tasks"".""Due""=NULL WHERE ""Tasks"".""Due"" IS NOT NULL AND DATEDIFF( 'dd', ""Tasks"".""Due"", CURDATE( ) ) >= 0;"
   DatenUpdate(sURL,sSQL1,sSQL2)
End Sub

The Sub 'DatenUpdate' already called, just takes up to two SQL Statements and executes them against a Table. Here is the code to be completely:

Code: Select all

Sub DatenUpdate(sURL,sSQL1,sSQL2)
   Dim DatabaseContext as Object
   Dim oDatenquelle as Object, oHandler as Object
   Dim oDatVerb as Object, oStatement as Object, oErgSet as Object
   
   if (sUrl <>"") then
     DatabaseContext = createUnoService("com.sun.star.sdb.DatabaseContext")
     oDatenquelle = DatabaseContext.getByName(sURL)
     If (not (oDatenquelle.isPasswordRequired)) then
        oDatVerb = oDatenquelle.getConnection("","")
     Else
        oHandler= createUnoService("com.sun.star.sdb.InteractionHandler")
        oDatVerb = oDatenquelle.connectWithCompletion(oHandler)
     End if
     if (sSQL1<>"") then
       oStatement = oDatVerb.createStatement()
       oErgSet = oStatement.executeQuery(sSQL1)
     end if
     if (sSQL1<>"") then
       oStatement = oDatVerb.createStatement()
       oErgSet = oStatement.executeQuery(sSQL2)
      end if
   end if
End Sub

So here we are - "Due" Flag is set by this in relation to the "Due" Date field in my "Tasks" Table .
So, now we can filter the "Due" boolean flags in the Main Table NORDWEST.
Now think of the situation I'm calling a guy which is "Due" and get the job done. It would be nice to unset the Due Flag, but -> Error!.

To go aroud this I divided the "Due" Flag (on which I filter) from the Due flag I work on(setting/unsetting):
I defined a new boolean flag (a new one which is added to the table and is named "SetDue".

Now I can filter on "Due" and if I need to change something I set or unset "SetDue" Field on my behave.

But now I have to sync theese fields periodically to make everything fine!
(... as long I remember NOT to change the "Due" field (I underlied it Red not to forget this) )

Here is the form:

Not very elegant, but working!

Ah, the statement for the periodical update!

To make this run you have to do two things:
Initially go to the Extras/SQL Window (use this window!) and type the following:
UPDATE "NORDWEST" SET "NORDWEST"."SetDue"="NORDWEST"."Due";
to sync from "Due->SetDue" initially.

After that you can add the following routines:

Code: Select all

sub DatenUpdate_Due_from_SetDue
   ' copy the (Set-)Due Flags, changed in Form back to the original Due Flags:
   sURL = "BM_Call"  ' find the correct DB Connection
   sSQL1 = "UPDATE ""NORDWEST"" SET ""NORDWEST"".""Due""=""NORDWEST"".""SetDue"";"
   sSQL2="" ' just one SQL Statement
   DatenUpdate(sURL,sSQL1,sSQL2)
end Sub

To make this run unlink the above mentioned "on Load" Event and link is to this routine:

Code: Select all

Sub Form_PreLoad
   DatenUpdate_Due_from_SetDue ' the new one, for sync from SetDue->Due
   DatenUpdate_Due_from_TasksDue ' the routine we called above from Tasks.Due -> NORDWEST.Due
   DatenUpdate_SetDue_from_Due ' just to get the Tasks.Due Data in SetDue
   ThisComponent.Drawpage.Forms.getByName("MainForm").reload ' don't forget the form.reload
End Sub

Code: Select all

sub DatenUpdate_SetDue_from_Due
   sURL = "BM_Call" ' find the correct DB Connection
   sSQL1 = "UPDATE ""NORDWEST"" SET ""NORDWEST"".""SetDue""=""NORDWEST"".""Due"";"
   sSQL2="" ' just one SQL Statement
   DatenUpdate(sURL,sSQL1,sSQL2)
end Sub

The result is: Everytime you start the form (again) the changes from the last telefone action and Tasks.due are synced

At last some information how to make some other things run (if you use the actual HSQLDB, which is standard):
If you like to make a TIME STAMP Default value (from Extras/SQL Window):
ALTER TABLE "Tasks" ALTER COLUMN "Created" SET DEFAULT CURRENT_TIMESTAMP;

After Import from a Table create a primary key (from Extras/SQL Window):
Generate a a new Sub-Table to test this (or could be even an Calc-import):
1: SELECT * INTO "NEWTABLE" FROM "NORDWEST" WHERE "NORDWEST"."Due" = True;
Do the primary key (be shure that is unique and not null for every field, of course):
2: ALTER TABLE "NEWTABLE" ADD CONSTRAINT "NEWTABLE_pk" PRIMARY KEY ("ID");

The Statement could change in next SQL DB Version:
from 1.9: CREATE "NEWTABLE" PRIMARY KEY (COL1) AS (SELECT * FROM "NORDWEST" WHERE "NORDWEST"."Due" = True);
in this: 1.8:
SELECT * INTO "NEWTABLE" FROM "NORDWEST" WHERE "NORDWEST"."Due" = True);
ALTER TABLE "NEWTABLE" ADD CONSTRAINT "NEWTABLE_pk" PRIMARY KEY ("ID");

[lwarranty]

Same problem here - am I to understand this is fixed in "3.2.1"??? Before I lose what sanity I have remaining... With the exception my error is "Error updating the current record Illegal operation on empty result set.". Change boolean "check box" without filter applied works fine...

My first post - please be gentle oh great OO guru's

Hello Villeroy,
it's incredible good what you guys are doing.
Many thanks for your effort in designing a test DB which of course I would had to do better to make you guys understand my problem.

Unfortunately I explained in a misleading way.
The problem is not in applying the filter to a boolean field - it is in changing the field value when the filter is active.
To answer your remark, the corresponding error message is:
SQL-Status: SOO10
Fehler-Code: -62
Invalid argument in JDBC call: parameter index out of range: 2

Of course, your test DB shows the problem in a much better way, so lets go.
If I load it and do the Filter on the B-field the result is as shown here:
(Pls. remark that I unset the B-filed value of data set No 9 - this is actually NOT committed by DB!)

Table1 Formularentwurf.png

If I try to commit this field change by pushing the Triangle (next data set) the result is error :

Bildschirmfoto-OpenOffice.org Base-1.png

By the way, there is no way to commit the change even by typing to a different data set in the table view of the form, for example by doing so it resets the B-field value in data set 9 back to 'TRUE'.
The desired solution would be to find a solution to change the B-field and reload the filter automatically to get this work.
Thank you
Rup

[Marco Bernardini]

This post is really helpful, thanks!
I was populating a table with an autoincremental ID field, filtered by ID, and while correcting the last record wrongly I removed the value from the ID field.
I was stuck until I found here the solution: to disable the filter with the Xed funnel button.
Doing this I lost the ID for the last record I was inserting (the autoincrement jumped to the next value), but this is really a minor hassle compared with the loss of a whole table.