Base Table Grid Control restore the rows selection after applying a filter

[Lyndhurst]

I am trying to filter a sub-form grid control based on main form table selection.

If user does not select any rows on the main form , a macro gets all the rows displayed, and uses it to filter the sub form. If the user selects on or more rows, they are used as the sub form filter.

The macros do work, but when the filter is applied to the sub-form, selection of rows is lost in the main form table grid control.

This is how I retrieve the rows selected:

oForm = ThisComponent.Drawpage.Forms.getByName("MainForm")
oTable = oForm.getByName("MainForm_Grid")
oTableView = ThisComponent.getCurrentController().getControl(oTable)
oSelectedRows = oTableView.getSelection()

iStart = LBound(oSelectedRows)
iEnd = UBound(oSelectedRows)
filterIds = ""

For n = iStart to iEnd
    oForm.absolute(oSelectedRows(n))
    t = oForm.columns.filter_id.getInt()
    filterIds = filterIds & t
next

Then I use the filterIds string to Apply a filter to the subform. I tried a few posts using oTableView.Select like this one but I was unsuccessfull getting error messages about illegal arguments and such.

Since I already retrieve the selection before applying the filter, I thought it would not be too hard to restore it…

Does anyone know how I can prevent this from happening, or restore the selection after the filter is applied ?

[Ratslinger]

Answer deleted because deleting records causes errors. Adding records can be overcome. Will need to look for another approach.

[Lyndhurst]

I guess someone answered my question before I had the time to read it.

Reading my question again, maybe I was not clear enough and should edit it.

I think at the end my problem can be summarized by how to select (highlight) rows in a table grid control from the object (an array I believe) returned by getSelection()

[Ratslinger]

@Lyndhurst The answer was mine and you could read it in your notifications. It had a problem as I stated in the first comment and therefore I deleted it. No sense in using something which has a problem. Quesion is clear enough but the answer is very elusive. What may seem simple is in fact very hard to resolve.

[Lyndhurst]

Ok, thank you for letting me know. I will not rephrase the question then.

For all the testing I have done, I can understand that the oTableView.Select() method I linked in my question takes a variant as an argument, and the GetSelection() method returns an array.

The variant part is the reason why I get some ‘illegal argument’ error messages I think. I tried declaring this variant using iStart to iEnd from the array I have, and I was not successful. The reason being I have never used arrays any other way than basically copying and adapting code snippets from the web, or the docs.

In the example I linked, the Variant is declared (o To 1) I first thought it was because all example attempt to select one row; I am now wondering if this is not a True/False value passed to .Select() as a Variant for some reason.

I will try it while looping through records, moving the form cursor to absolute position from the GetSelection() Array I have. It is pure guess work, I ll post the outcome.

[Ratslinger]

Hello,

Have re-examined the link you provided. First attempt was only able to set one line. Then tried diferent directions. A second look enabled progress. Needed to set the array at number of lines +1 for it to work. This code works in the sample previously provided here → Read values from selected rows in a table control

Option Explicit

Sub GetSelected
    Dim oForm             As Object
    Dim oSelectedRows     As Object
    Dim oTable            As Object
    Dim oTableView        As Object
    Dim iStart            As Integer
    Dim iEnd              As Integer
    Dim iTotalSelected    As Integer
    Dim myTotal           As Integer
    Dim n                 As Integer
    Dim i                 As Integer
REM Get access to the current form
    oForm = ThisComponent.Drawpage.Forms.getByName("MainForm")
REM Get Table Grid Control
    oTable = oForm.getByName("MainForm_Grid")
Rem Get View for Grid Control
    oTableView = ThisComponent.getCurrentController().getControl(oTable)
Rem Get array of selected rows
    oSelectedRows = oTableView.getSelection()
Rem Bounds for array
	iStart = LBound(oSelectedRows)
	iEnd = UBound(oSelectedRows)
Rem Compute the number of rows selected an dimension the array
	iTotalSelected = iEnd - iStart + 1
    Dim sel(iTotalSelected)
    myTotal = 0
Rem Loop through selected rows
    For n = iStart to iEnd
        oForm.absolute(oSelectedRows(n))
    Rem Add selected row to array
        sel(n) = oSelectedRows(n)
Rem Total value for each selection
        i = oForm.columns.FSVALUE2.getInt()
        myTotal = myTotal + i
    next
Rem Print the total
    Print myTotal
Rem Re-set the selected rows
    oTableView.Select(sel)
End Sub

If this answers your question please tick the (upper left area of answer). It helps others to know there was an accepted answer.

[Ratslinger]

Just a couple of notes. oTableView.Select() is looking for an array. Think of an array as letters in a word. HELLO is a 5 character array. Arrays use an index (starting with 0 is default) to access elements. So in the example, if that word is placed in the array ‘myWord’, then myWord(1) retrieves the character E (indexing starts at 0!).

A type of Variant can be many things. Should when possible set the type. In code above the type should be Integer or similar.

The part that confused me and set me astray with many errors was that the array needed to be one item larger than the data it held. Still not certain as to why.

You should look more into arrays.

[Lyndhurst]

Thank you for the extra explanation on arrays. They are important, and I have overlooked them for too long it is true. I cannot test your answer right now, but I should be able to in a few hours. I will accept your answer as soon as I have tested it.

[Lyndhurst]

I confirm that the sub works fine to re select the rows in the main table.
I have to confess that I do not understand it all, so I still need to work out the filtering. I guess I did not integrate your code correctly, because I get filtering only for one row selected now. I think it has to do with the event I use to trigger the filtering. I ll try it out when I understand better what happens.
Also, I figure FSVALUE2 is some kind of placeholder, it gives me errors. But as far as I understand the code, it is only for printing purposes. Anyway, commenting it, or replacing it with my ‘filter_id’ column produces the same results. It looks like it just sums those values.
Thank you for taking the time to solve my problem (again;).

[Ratslinger]

@Lyndhurst,

Have just confirmed that the routine as given, if used in original sample linked to in answer, does work without any errors. FSVALUE2 is a legit field there.

For your code posted in question (have not actually tested but should work) replace:

For n = iStart to iEnd
    oForm.absolute(oSelectedRows(n))
    t = oForm.columns.filter_id.getInt()
    filterIds = filterIds & t
next

with this:

Rem Compute the number of rows selected and dimension the array
    iTotalSelected = iEnd - iStart + 1
    Dim sel(iTotalSelected)
    For n = iStart to iEnd
        oForm.absolute(oSelectedRows(n))
    Rem Add selected row to array
            sel(n) = oSelectedRows(n)
        t = oForm.columns.filter_id.getInt()
        filterIds = filterIds & t
    next
Rem You may need to place your filtering code here! Then re-set the selection - next line
    oTableView.Select(sel)

Hope that is of help.

[Lyndhurst]

You beat me to the comment )) I was just going to edit mine; sorry to have posted too quickly. I had just done exactly that, and it works like a charm thanks a lot.

For the FSVALUE2, now that I read you it is obvious, I was so absorbed with my form that I did not even keep in mind that you did post your own example, just wanted to make your code work !! Sorry about that.

Thank you again for your help, I could not have done it alone, everything works fine now.