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");