Questions about the form filter

[arfgh]

Ok, the question is the follow:
If we use a filter on the form, and we have a filter button that we can click, the form back to the first record that show by default.
What we need to change that behaviour ?
I mean, first we use the filter, then we use the filter button to set it off, and then we want to keep the last record the form showed when the filter was on.

I have tried with 'form.absoulute', ids involved etc. But that doesnt works because when we set the filter to off, we cant jump using the Ids.

help

[Nocton]

Are you using the built in from Filter controls? These are easy to use and the record stays on the one you are at.

If you use you own control then you will probably need a bit of macro code to save and then restore the current position in the table.

[arfgh]

yes, i am using an own button to enable of disable the filter.
But, are you saying that the built-in filters control keep on the current record when disable the filter ?

[Nocton]

But, are you saying that the built-in filters control keep on the current record ?

Yes, but not sure what you mean when you say "when disable the filter". Do you mean when you don't apply a filter set by a separate form control?

[arfgh]

ok, i mean that the form has a filter applied, then we are on a record, and then i click my filter button (that is on) to set it off, and the form them, jump to the first record. What i want is to keep on the record that showed the filter, and be able to move the records ahead and back from that point.

[Nocton]

Yes, I understand that.
I am suggesting that you use the built-in filters as they will keep the current record.
If you wish to use your own filter then you will need some macro code to restore the position. Something like:

Code: Select all

Sub Restore_Position
Dim oForm
Dim nRow as integer
oForm = ThisComponent.Drawpage.Forms.getByName("YourForm")
	nRow = oForm.getRow  'save the current position
... apply your filter
        oForm.reload                'reload form if necessary
	oForm.absolute(nRow)  'restore position
End Sub

[Villeroy]

You have to find out how to do this by your own. It may be possible. May be it is impossible. I found out that bookmarks do not work as they used to work for me in MS Access: store bookmark in var, do something with the set, go back to the bookmarked record. In Base the bookmark of the same record is different when the record set is filtered and the bookmark value seems to be the same as the row number minus one. I don't know if it is a bug or if I misunderstand something. I do not have the time (and nerve) for this API.

[Arineckaig]

arfgh:

I have tried with 'form.absoulute', ids involved etc. But that doesnt works because when we set the filter to off, we cant jump using the Ids.

As Nocton and Villeroy have indicated if a macro is to be used for filtering displayed records then the row numbering of the filtered recordset will seldom match that of the original recordset. Thus Nocton records the row number from the original recordset BEFORE applying the filter.

In more complex cases where switching to a series or variety of filters may be involved, it is sometimes helpful to store the original record's primary key value. The macro can then search whatever may be the new chosen recordset for that original record and if it exists therein obtain its updated row number so as to move the display to that record.

Hence it is often simpler and more reliable to make use of the GUI's filtering and sorting facilities.

[arfgh]

with filter and without it the row number will be always diferent in the same record ?
I guess that certain row data in unfiltered form, is for example the row number 8, but in a filtered form is the 4. So then if we want to keep the data from the row 4, and use the button to disable the filter, i assume the same data should be again the row number 8. Is this the exactly problem ?

Anyways, and why my button works different than the gui filter buttons ? because my button use just that, i think: .uno:RecSearch

[Arineckaig]

Anyways, and why my button works different than the gui filter buttons ? because my button use just that, i think: .uno:RecSearch

I can offer no useful comment. The detailed programming of the GUI filter buttons and of the .uno:RecSearch procedure are outwith my knowledge and way above my pay grade.

Later edit:Yet again I must apologise to Villeroy for my failing memory of his valued education: I had quite forgotten his post viewtopic.php?f=13&t=42738#p197316 where he indicated that .uno:RecSearch is similar to the command triggered by the binocular button. Hence its use in a macro will probably differ significantly from use of a filter button from either the Form Navigation Toolbar or a Navigation Form Control. Then again without seeing your actual code it must be hazardous to offer useful comments.

[Villeroy]

Store the primary key value(s) in a global variable on record change event (after moving from one record to another).
Use the after_reload event to trigger a macro that loops through all records until it reaches the one with the same primary key value(s). If you loop beyond the last one you have hidden the record with the stored key value(s).
I think, this could work. The after_reload event is triggered when you sort, filter or push the reload button. I have no idea how fast this can be with huge record sets.

[arfgh]

Sorry guys, i did a mistake, the command on the button is not '.uno:RecSearch'. It is '.uno:FormFiltered'. My apologies

villeroy, i am on it, but important question related to this, and events.
Is there a way to capture certain event, and stop it ? Some kind of 'return false' like in other languages....
Example, imagine the event 'before record change'. Can we act on that event, and stop the record change ?

I have find out very useful that button because, if we hide all the toolbars, to have a clean form, we eventually need some navigation or some special button in our form design. And by the way, when the user click on the filter button when it is applied, the form automatically jump to the first record, that is in my opinion not desirable. What we always want, is to keep on the record that we filtered, to be able to go ahead and back through the records, but from the filtered record position.

[Villeroy]

I'm not 100% sure. I think that the beforeXXX events can be implemented as a function and the action is inhibited if the function returns False.

[arfgh]

ok, that is the point now, to be able to inhibit a change record to run my macro.... but how...

[Arineckaig]

inhibit a change record to run my macro.... but how...

If I understand the problem correctly, interrupting a data form's Before Record Change event is relatively easy but requires care because the event is triggered twice: first by org.openoffice.comp.svx.FormController (essentially the closest service to a form's non-existing View) and secondly by com.sun.star.comp.forms.ODatabaseForm. It is therefore quite easy to upset the GUI's accurate saving of changed data.

Without knowing the intent or coding of the macro, it would be dangerous to comment further. On the other hand I have used some simple coding to permit investigation into what might be involved:

Code: Select all

Sub TEST_MRI(oEv as object) 
  REM Called by Data Form BeforeRecordChange event
  'Print "Before MRI"
  MRI oEv.Source
  'Print "After MRI" 
End sub

Calling this macro, as is, permits MRI to display the sources of the two events that are triggered. Further use of MRI to investigate how best to add interrupting code is helped by removing the REM from the two Print lines: they then pause the macro at each stage and thus permit further investigation using MRI. It can be critical to catch the correct event and to avoid problems that the event duplication might cause.

[arfgh]

the reason to stop the event happening in this case, is to wait while our macro locate the previously filtered record that, when it is disabled, it will be located in other row and the searching will require eventually some time. While that, the event just happens and show the first record or the set. If we can stop the event happening, is is of course more clean.

Other posible way to do it, i hidding all the form controls while that...

[Villeroy]

MRI oEv.Source

Some events include more than the Source object.
In order to check out an event struct, I simply assign the event to MRILib.Module1.Mri instead of writing my own little macro.

[Villeroy]

Back on topic, the attached example works (almost, somehow).
It ignores the first record. When it *should* return to the first record because that was the record before reloading, then it will return to the second last record simply because it ignores moving to the first one. I tried with many events, lock variables, call counters but this simple solution is the only comprehensible one. 3 lines of code after 1 hour of testing.

[arfgh]

seems to works fine villeroy, but because it is a writer document, i dont know how you made that document to works. See the fact that i speak all this time about BASE.
Example, where you aplied these two events you programmed in the macro ? etc.
Please, explain more about that solution.

[Villeroy]

A form document is a just a normal Writer document embedded in an odb. The hierarchy of forms is identical in Writer, Calc, Draw and Impress. All these document types can have input forms. There is one difference regarding the storage of embedded macros for embedded form. In this particular case you can move the code to a global library under "My Macros" or into the odb document. It does not contain any document specific symbols. The 2 routines are driven by 2 form events.

[arfgh]

yes but....

after_RecordChange(e) and after_Reload(e)

where you applied the subroutines to the events that happen on that table ? I know how to locate that in base forms, but no on your writer document.

[Villeroy]

Exactly the same. Get toolbar "Form Controls" or "Form Design" and switch to design mode (button #2). Then get the form properties (button #4).

[arfgh]

lol, thx for the explanation. I feel stupid.
Now i have seen how you did it, using the events 'Reload' and 'After record change'.
And in the subroutines you use an object i havent seen before called 'Bookmark' and a related function called 'MoveToBookmark()'

[arfgh]

Other important detail i want to throw, and related to the Global variables...
I have been using other global vars for my macros, but i have seen also a collateral problem on them.
Taking for example this code i run on the event 'view is created'

Code: Select all

    GlobalScope.BasicLibraries.loadLibrary("Tools")
    'pathDB = Tools.Strings.DirectoryNameoutofPath(ThisComponent.Parent.getURL(), "/")
    pathDB = Tools.Strings.DirectoryNameoutofPath(ThisComponent.getURL(), "/")
    pathDB = pathDB & "/"
    'Function FileNameoutofPath(ByVal Path as String, Optional Separator as String) as String
    fileDB = Tools.Strings.FileNameoutofPath(convertFromURL(ThisComponent.getURL()), "\")

the pathDB and fileDB vars are global, and i store there the path for the DB file, to use it on other macros.
The problem, each time i edit the basic document, or other operations, that var gets empty.
I dont know why that happen and/or if is normal.

[RoryOF]

Every time one edits a BASIC macro all the variables _in that module_ are cleared; this is clearly told in the BASIC documentation.

Edit: edited for clarification in the light of Villeroy's subsequent postings.

[Villeroy]

Every time one edits a BASIC macro all the variables are cleared; this is clearly told in the BASIC documentation.

You can avoid this by declaring your globals on a separte module.

[RoryOF]

To clarify: @Villeroy, are you saying that the variables are cleared _ONLY_ in the BASIC module being edited?

[Villeroy]

To clarify: @Villeroy, are you saying that the variables are cleared _ONLY_ in the BASIC module being edited?

Yep.

Code: Select all

Sub Main
gTEST = 23
print gTEST
End Sub

gTEST is declared on a separate module.
Comment out gTEST = 23 and run it again. It still prints 23.

[RoryOF]

Thanks, Villeroy. I've edited my earlier posting for clarity.

[arfgh]

villeroy, retaking the main question, your solution with movetobookmark function, is not valid for my case. It works fine in your writer example, because, all the data is loaded. But in a base form and filtered, that doesnt happen. So, if the bookmark is for example the value 1, when we disable the filter button, the bookmark cant be again 1.

Do you see the difference ?

I still think that the correct way to do it, is like we said, inhibit the record change before it happens. Because the searching takes time.