[Solved] How to reset a Boolean column in table

[dreamquartz]

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

[Villeroy]

UPDATE "Table" SET "Bool_Field"=False

[eremmel]

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.

[Villeroy]

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

[dreamquartz]

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

[dreamquartz]

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.

Can you elaborate?
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

[dreamquartz]

UPDATE "Table" SET "Bool_Field"=False

Hi Villeroy,

Can an UPDATE be run during initialization of the SCRIPT or @ shutdown?

Dream

[dreamquartz]

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

K,

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

[Villeroy]

Replace "Table" with the name of your actual table and "Bool" with the name of your actual field.

[dreamquartz]

Replace "Table" with the name of your actual table and "Bool" with the name of your actual field.

That's what I did.
Tried it with "Execute Action" and "Key released", but no success.
It should be working, looking at the code.

Dream

[Villeroy]

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.

[dreamquartz]

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.

Have a look at the sample.
Don't see what's wrong, but it does not work.

Dream

[Villeroy]

SingleBase2.odb

(30.09 KiB) Downloaded 227 times

[dreamquartz]

SingleBase2.odb

Am I to understand that you removed the Action "New record" to be able to run the Macro?

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

[Villeroy]

The macro can do anything you want.

[dreamquartz]

The macro can do anything you want.

Indeed.

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

Dream