[Tutorial] Cascading Listboxes with macros

[RPG]

Hello

A regular question here is How can I make a macro for a set of listboxes so that the listboxes limits the number of choices. Villeroy, DACM and Arineckaig did have given nice examples without using macros. In the forum for the tutorials and examples you can find explanation and examples how to do it.
http://user.services.openoffice.org/en/ ... m.php?f=83
http://user.services.openoffice.org/en/ ... .php?f=100

Even with all the explanations and examples it is real difficult for most starters. I have not the idea to make it more easy. For good explanations I will point to them but I think some person like maybe the macro I will add in this post. I have the idea that the macro does have no more bugs and can be used.

Using the macro in this post expect several things

• Don't change the macros
• Use this macro for listboxes in a mainform for selecting data in a subform
• The fieldnames for the listboxes in must be the same for mainform and subform

Don't change the macros
I hope this macro makes it a little more easy for other people. Writing macros is always difficult and I hope when you use this macro you have not to change the code. I have test this macro for three listboxes but I hope you can use it for more listboxes but I have not test it.


Use this macro for listboxes in a mainform for selecting data in a subform
I have test this macro in three listboxes where each listbox limits the listboxes futher in the row. When the last listbox did get a value then the themainform does relod the current record and this does also reload the subforms.

Fieldnames
The fieldnames for the listboxes must be the same for mainform and subform
It maybe clear to the readers that there is a realtion between the data in the mainform table and also in the subform table. The only method I can get working this is the field who make this relation must have the same name. When they have not the same name then it can not work. When the table you want use do not have the same name the SQL of OOo-base is powrfull enough that you can use the alias for to take care of this.


Explanation more details
As you can see there are several limits to avoid difficult macros. When you want use this idea then you have design your form real careful.

• The listboxes you use must have all the same name
• The taborder in this listboxes is also real important. The taborder is also the order in which way they are processed in the macro. I must say: I believe this, about the taborder, and it works so for me.
• The designer of the form is responsible for a good query in the listbox. This query is modified each time when the listboxe earlier in the row does get an other value. But the user does not see the modified query there after using the new query who does make a new resultset the old query is stored again.
• I do use some of the builtin possibillities of OOo.

What are the builtins I use

• I use the group of the data-form object. For this reason the listboxes must have the same name. The group is complete different what most people knew as grouping controls in designing a form.
• I do not make the filter with BASIC but use the SingleSelectQueryComposer interface/service. I do use here a little trick and this works in the tests I did. The trick I use is using the same name for fields of different tables. I let compose a filter for the listbox while I use the table of the form.

I hope you enjoy it.

Romke

Code: Select all

Sub forlistboxchange (oEvent as Object)
oEvent.source.model.commit
call  forlistbox oEvent.source.model
end sub

'++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

Sub forlistbox(oListboxModel as Object)
' Do not bind this sub to an event but use an other sub to call this sub.
' The listbox what is the parameter for this sub
dim oSearchGroup()
dim oFormModel,oFomControl,oFormOperations

oFormModel=oListboxModel.parent
' Init the oSearchGroup there are all the control with the same name.
oFormModel.getgroupbyname(oListboxModel.name,oSearchGroup())
oFomControl=thiscomponent.currentcontroller.getFormController(oFormModel) ' This is the view of the form
oFormOperations=oFomControl.FormOperations

' This can only work when the is a group.
if  UBOUND(oSearchGroup()) then
' There are several textboxes for searching
	dim oComposerForm
	oComposerForm=oFormModel.SingleSelectQueryComposer	

	dim x
	for x=0 to ubound(oSearchGroup)
	' We go through the tabcontroller
		if x=ubound(oSearchGroup) then
	   		'print " for last control we do nothing more"
	   			if oFormOperations.commitCurrentRecord(void) then
					oFormModel.absolute(oFormModel.row) ' This does a reload of the current row. And so also a refresh of the 
				end if
	   			'This sub is made for thre listboxes in a form.
	   			'When the last listbox is loaded then the subform must be reload.
	   			'Then we can exit the sub
	   			exit sub
	   	else ' We are not the last
			if EqualUnoObjects(oSearchGroup(x),oListboxModel) then
				'Now we knew the place of the listbox in the array
				exit for
			end if
		end if
	next
' We have search through the tab controller.
' we knew the place of the current listbox
' Now prepare for the follwoing things.
' We must init the next listbox with search values given in the previous control in the same tabcontroller.
	dim oPrepareThisListbox
	dim iTakevalue
	oPrepareThisListbox=oSearchGroup(x+1)
	'oComposerCon.setquery(oPrepareThisListbox.listsource(0))
	oComposerForm.setQuery(oPrepareThisListbox.listsource(0))
	for iTakevalue=x to 0 step -1
		oComposerForm.appendFilterByColumn(oSearchGroup(iTakevalue).boundfield ,createunovalue("boolean",true),1)
	next



	oPrepareThisListbox.listsource=array(oComposerForm.getQuery ) ' Place the compose query in listbox
	oPrepareThisListbox.refresh ' Load listbox with the query result
	oPrepareThisListbox.listsource=array(oComposerForm.original) ' store back the original query
	
else
	msgbox " There is no listbox group" & chr(13) & "See in your dataform "
end if

End Sub

[RPG]

Hello

This is an update of the macro earlier in this thread. I think it is much more easy then before.
The same is you have to give the same name for all the listboxes. The listboxes must display the values in a good way without filtering. Only the first listbox must not be bound to the macro.

Difference for listboxes
What is the most important difference between the first macro and the second macro? The first macro does look to all the following listboxes when the current listbox is changed.The second macro does look back to the listboxes before the current list-box and makes a filter for the current listbox. This is done when the mouse is inside the list-box. The result is the creator of the form does not have a problem for making complex filters for every listbox. The maker must only give the good query for all the list-boxes. The query must give that value to the listbox when there is no filter.

I restore the original query back after I have update the itemlist. I believe you can use tables, queries and SQL. I think you cannot use Native SQL, value lists and Table fields. I have insert an on error routine. This beeps two time for all errors including when you use a value list, Native SQL or a tableFields.

I'm not sure if all is clear but I hope this method is maybe so easy that also starters can work with it.

A last warning do not chance any thing in this macro unless you understand each character in the macro.

Romke

Code: Select all

REM  *****  BASIC  *****
option explicit

Sub CasCadeListbox (oEvent as Object)
oEvent.source.model.commit
'Use the event: mouse inside
' The first listbox does not need the event
dim oListboxControl,oListboxModel
dim oFormModel
dim oFilterGroup() ' This is the group for the listboxes
const cPropertyNames="ListSource ListSourceType" ' I use this getting and setting properties
dim mPropertyValuesOld(),mPropertyNames

' I use the error sub 
' for filtering Value list, Native SQL and table list in the listboxes.
' When one of this option for the listboxes are used then the sub produced 
' an error for the command in the querycomposer.

on local error goto PlaceBackListboxValues:

oListboxControl=oEvent.source
oListboxModel=oListboxControl.model
if oListboxModel.implementationname="com.sun.star.comp.forms.OListBoxModel" then

	' The listbox is in a form and not in a gridcontrol
	' In a gridcontrol we have no group so we can not use this idea
	oFormModel=oListboxModel.parent
	' Init the oFilterGroup there are all the control with the same name.
	oFormModel.getgroupbyname(oListboxModel.name,oFilterGroup())
	if  UBOUND(oFilterGroup())<>-1 then
		'do action
		'++++++++++++++++++++++
		' There are several listboxes for searching
		dim oComposer 
		mPropertyNames=split(cPropertyNames) 
		mPropertyValuesOld=oListboxModel.getPropertyValues(mPropertyNames)
		oComposer =oFormModel.ActiveConnection.GetComposer(_
				choose(oListboxModel.ListSourceType,0,1,2),_
				oListboxModel.listsource(0)			)
		' I use choose for changing the difference between the listboxsource type to
		' the command type. It does also filter out the zero for value list and
		' four for the native SQL
		' Both cases give an error. The error call the on error and beeps.
		dim x,oListbox
		for x=0 to ubound(oFilterGroup)
			oListbox=oFilterGroup(x)
			'appendFilterByColumn
			if EqualUnoObjects(oListbox,oListboxModel) then
				'Now we knew the place of the listbox in the array
					oListboxModel.setPropertyValues(_
					mPropertyNames,_
					array(array(oComposer.getquery),3))
				' Set the listsource query and the listsource type.
				' listsource type is 3	
				
				oListboxModel.refresh '
				oListboxModel.setPropertyValues(_
					mPropertyNames,mPropertyValuesOld)
				exit for
			else 
				oListbox.commit 'Be sure the value is commit to the field but not to record
				oComposer.appendFilterByColumn(oListbox.boundfield,createunovalue("boolean",true),1)
			end if
		next

	else
		msgbox	"When you have no group then you can not use this macro" & chr(13) &_
				"Make a group in the form navigator by giving some control the same name"
	end if
	

else print "The control must be a listbox in a form not in a grid control"
end if
on local error goto 0

exit sub


PlaceBackListboxValues:
beep
		oListboxModel.setPropertyValues(_
			mPropertyNames,mPropertyValuesOld)
on local error goto 0
beep
oListboxModel.refresh
end sub