[Solved] How to reset a Boolean column in table
-
- Posts: 881
- Joined: Mon May 30, 2011 4:02 am
[Solved] How to reset a Boolean column in table
Hi All,
I want to manually mark/flag records that need to be skipped when using a TRIGGER, but also a way to reset all to default automatically.
One of the thoughts I have is using a Boolean.
I am trying to figure out a way to reset all Boolean records in a table via a Query.
I know how to set all Boolean records in a table to 'TRUE' or 'FALSE' via the Parser, but is there a way to do that via a Query?
I cannot find anything about something like that.
Dream
I want to manually mark/flag records that need to be skipped when using a TRIGGER, but also a way to reset all to default automatically.
One of the thoughts I have is using a Boolean.
I am trying to figure out a way to reset all Boolean records in a table via a Query.
I know how to set all Boolean records in a table to 'TRUE' or 'FALSE' via the Parser, but is there a way to do that via a Query?
I cannot find anything about something like that.
Dream
Last edited by dreamquartz on Wed Jun 28, 2017 10:28 pm, edited 1 time in total.
LO 7.x, HSQLDB 2.7.x & Ubuntu 22.04 LTS.
Re: How to reset a Boolean column in table
UPDATE "Table" SET "Bool_Field"=False
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Re: How to reset a Boolean column in table
I think that your intention is to play a trick with SELECT-query. Villeroy just gave you the example of a UPDATE-query.
Please be specific in your questions as possible.
But to answer my interpretation of your question, the answer is NO.
But here is an idea that might work.
In the table that has the boolean field add a dummy record with a special name. E.g.
T1: ( ID, Description, Flag), special record: (-1, "RESET ALL FLAGGED RECORDS", FALSE)
Create an update trigger on that table that checks on ID=-1 AND "Flag"=TRUE. When that happens it updates all records by clearing the flag.
The trigger can be fired when you open the special designed form "fResetFlags", that loads only data from T1 wth ID=-1. In the form you can update Flag, so
the trigger gets fired.
Other forms on T1 just read ID>=0 data. This saves on writing a macro. But a macro is not that difficult for this action either.
Please be specific in your questions as possible.
But to answer my interpretation of your question, the answer is NO.
But here is an idea that might work.
In the table that has the boolean field add a dummy record with a special name. E.g.
T1: ( ID, Description, Flag), special record: (-1, "RESET ALL FLAGGED RECORDS", FALSE)
Create an update trigger on that table that checks on ID=-1 AND "Flag"=TRUE. When that happens it updates all records by clearing the flag.
The trigger can be fired when you open the special designed form "fResetFlags", that loads only data from T1 wth ID=-1. In the form you can update Flag, so
the trigger gets fired.
Other forms on T1 just read ID>=0 data. This saves on writing a macro. But a macro is not that difficult for this action either.
It's Microsoft marketing that tells you computers are qualified for non-technicians
W11 22H2 (build 22621), LO 7.4.2.3(x64)
W11 22H2 (build 22621), LO 7.4.2.3(x64)
Re: How to reset a Boolean column in table
Simple Basic macro to be fired from a form button.
It operates on the record set of the button's form.
It operates on the record set of the button's form.
Code: Select all
Sub ButtonClick(e)
Const cReset = "UPDATE ""Table"" SET ""Bool""=False"
oActiveConnection = e.Source.Model.Parent.ActiveConnection
oPrep = oActiveConnection.prepareStatement(cReset)
n = oPrep.executeUpdate()
'print n
End Sub
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
-
- Posts: 881
- Joined: Mon May 30, 2011 4:02 am
Re: How to reset a Boolean column in table
This line of thought is related to viewtopic.php?f=13&t=88238.
The need to suspend the TRIGGER once a while is based on:
1. the need to check existing entries on which the TRIGGER was fired, and if needed add/modify existing data
2. the ability to add a duplicate Surname-GivenName combination, where i.e. the DOB is different
The use of a TRIGGER for the "Surname-GivenName combination" is yet an other tool to try to assure UNIQUE entries in the table "tPerson".
Entries/modifications can be years apart, and therefore no one will be able to remember an entry.
There are 2 issues at play:
a. historical incomplete entries into the DataBase (which initially started this quest)
b. it must remain be possible to enter a Surname-GivenName combination (that might exist already, but is actually a different person) and no DOB
Dream
The need to suspend the TRIGGER once a while is based on:
1. the need to check existing entries on which the TRIGGER was fired, and if needed add/modify existing data
2. the ability to add a duplicate Surname-GivenName combination, where i.e. the DOB is different
The use of a TRIGGER for the "Surname-GivenName combination" is yet an other tool to try to assure UNIQUE entries in the table "tPerson".
Entries/modifications can be years apart, and therefore no one will be able to remember an entry.
There are 2 issues at play:
a. historical incomplete entries into the DataBase (which initially started this quest)
b. it must remain be possible to enter a Surname-GivenName combination (that might exist already, but is actually a different person) and no DOB
Dream
LO 7.x, HSQLDB 2.7.x & Ubuntu 22.04 LTS.
-
- Posts: 881
- Joined: Mon May 30, 2011 4:02 am
Re: How to reset a Boolean column in table
Can you elaborate?eremmel wrote:I think that your intention is to play a trick with SELECT-query. Villeroy just gave you the example of a UPDATE-query.
Please be specific in your questions as possible.
But to answer my interpretation of your question, the answer is NO.
But here is an idea that might work.
In the table that has the boolean field add a dummy record with a special name. E.g.
T1: ( ID, Description, Flag), special record: (-1, "RESET ALL FLAGGED RECORDS", FALSE)
Create an update trigger on that table that checks on ID=-1 AND "Flag"=TRUE. When that happens it updates all records by clearing the flag.
The trigger can be fired when you open the special designed form "fResetFlags", that loads only data from T1 wth ID=-1. In the form you can update Flag, so
the trigger gets fired.
Other forms on T1 just read ID>=0 data. This saves on writing a macro. But a macro is not that difficult for this action either.
Is there possibility to create a RESET functionality with a TRIGGER as you describe? I do would like to avoid a Macro, if possible.
The User is to be considered a person who could forget to open the form "fResetFlags",
It should be an automated process.
1. the User gets a Message,
2. the User must respond accordingly,
3. and to avoid receiving the Message, the User must set 'Boolean' or "Flag' manually.
4. the process must allow to continue, based on set 'Boolean'
5. at one point all 'Booleans' must be RESET
I can imagine step 5. should take place the moment the database re-opens, with other words at the beginning of a 'new' session.
The reason for that is, is that after entries/modifications have taken place, there might be a need to continue with (new) entries/modifications during the same session.
Dream
LO 7.x, HSQLDB 2.7.x & Ubuntu 22.04 LTS.
-
- Posts: 881
- Joined: Mon May 30, 2011 4:02 am
Re: How to reset a Boolean column in table
Hi Villeroy,Villeroy wrote:UPDATE "Table" SET "Bool_Field"=False
Can an UPDATE be run during initialization of the SCRIPT or @ shutdown?
Dream
LO 7.x, HSQLDB 2.7.x & Ubuntu 22.04 LTS.
-
- Posts: 881
- Joined: Mon May 30, 2011 4:02 am
Re: How to reset a Boolean column in table
K,Villeroy wrote:Simple Basic macro to be fired from a form button.
It operates on the record set of the button's form.Code: Select all
Sub ButtonClick(e) Const cReset = "UPDATE ""Table"" SET ""Bool""=False" oActiveConnection = e.Source.Model.Parent.ActiveConnection oPrep = oActiveConnection.prepareStatement(cReset) n = oPrep.executeUpdate() 'print n End Sub
You're giving me an idea.
I have a button "NEXT" on a "fPerson" Form with the Action "New record".
I would like to use it to "Execute Action" or "Key released" for the Macro you mentioned, but the Macro does not do anything.
I know I am missing something, but do not see it.
Dream
LO 7.x, HSQLDB 2.7.x & Ubuntu 22.04 LTS.
Re: How to reset a Boolean column in table
Replace "Table" with the name of your actual table and "Bool" with the name of your actual field.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
-
- Posts: 881
- Joined: Mon May 30, 2011 4:02 am
Re: How to reset a Boolean column in table
That's what I did.Villeroy wrote:Replace "Table" with the name of your actual table and "Bool" with the name of your actual field.
Tried it with "Execute Action" and "Key released", but no success.
It should be working, looking at the code.
Dream
LO 7.x, HSQLDB 2.7.x & Ubuntu 22.04 LTS.
Re: How to reset a Boolean column in table
You've got to refresh any view on the table. Contrary to spreadsheets, no Base object updates automatically on change. Exception: calculated values recalculate when you change values in the same row.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
-
- Posts: 881
- Joined: Mon May 30, 2011 4:02 am
Re: How to reset a Boolean column in table
Have a look at the sample.Villeroy wrote:You've got to refresh any view on the table. Contrary to spreadsheets, no Base object updates automatically on change. Exception: calculated values recalculate when you change values in the same row.
Don't see what's wrong, but it does not work.
Dream
- Attachments
-
- SingleBase.odb
- (28.95 KiB) Downloaded 233 times
LO 7.x, HSQLDB 2.7.x & Ubuntu 22.04 LTS.
Re: How to reset a Boolean column in table
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
-
- Posts: 881
- Joined: Mon May 30, 2011 4:02 am
Re: How to reset a Boolean column in table
Am I to understand that you removed the Action "New record" to be able to run the Macro?Villeroy wrote:
For the User it would be beneficial if the Macro is triggered automatically, to prevent the User from forgetting to push the Reset button.
I am surprised that the Action "New record" cannot be combined with the Macro.
Dream
LO 7.x, HSQLDB 2.7.x & Ubuntu 22.04 LTS.
Re: How to reset a Boolean column in table
The macro can do anything you want.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
-
- Posts: 881
- Joined: Mon May 30, 2011 4:02 am
Re: How to reset a Boolean column in table
Indeed.Villeroy wrote:The macro can do anything you want.
Was able to come up with a solution for both problems based on your input.
Code: Select all
REM Remove all "tPerson"."Check"
Sub Main(e)
Const cReset = "UPDATE ""tPerson"" SET ""Check""=False"
oActiveConnection = e.Source.Model.Parent.ActiveConnection
oPrep = oActiveConnection.prepareStatement(cReset)
n = oPrep.executeUpdate()
REM CALL Sub NextRecord
NextRecord
End Sub
REM NextRecord
Sub NextRecord
Dim document As Object
Dim dispatcher As Object
document = ThisComponent.CurrentController.Frame
dispatcher = createUnoService("com.sun.star.frame.DispatchHelper")
dispatcher.executeDispatch(document, ".uno:NewRecord", "", 0, Array())
End sub
LO 7.x, HSQLDB 2.7.x & Ubuntu 22.04 LTS.