How to fill listbox only with non previously selected items

[xhayoz]

Hello everyone

My objective: I have a property table and a property tax table. For each property several property taxes can be added.

My idea:
I created a link table containing the following columns fk_property_id, fk_property_tax_id, and percentage.

I’m using a subform with a table control. Within the table control I’m using a list control column which allows me to select the property_tax and define a percentage (e.g 3.5% of ownership tax). As several types of taxes can exists a user can just add more by adding a row in the table control.

My question: How can I implement that the listcontrol only will show non previously selected tax types? (It doesn’t make sense to add twice the ownership tax…)

Which is the best way to achieve this? By manipulating the ListSource with a macro when the user clicks the list control it?

The Select-Statement would be

SELECT name, id FROM property_tax INNER JOIN property_property_tax ON property_tax.id = property_property_tax.property_tax_id AND property_property_tax.property_id = :property_id 

I’m using Firebird as a DB.

And as always - Thanks for your help

Edit 1: I created the statement and run it through the libreOffice SQL-Window. The result set is correct:

[RobertG]

It won’t work with the tablecontrol, because the listbox in the tablecontrol is defined for all rows. If you try it by macro and scroll through the tablecontrol no values would be shown, because you define the listbox not to show existing values.

I do it this way:
Create a separate Listbox, choose values in this listbox and when choosen values this entries will be shown in the tablecontrol directly beneath (or under) the listbox. In tablecontrol the field will be only readable, not writable.

Here an example from a current database - much sql-code, but you could ignore it. It is linked to the form event “After row change”:

SUB ListenfeldfilterRechnungsreferenz(oEvent AS OBJECT)
	DIM oListField AS OBJECT, oForm AS OBJECT
	DIM stID AS STRING, stSql(0) AS STRING
	oForm = oEvent.Source
	IF hasUnoInterfaces(oForm, "com.sun.star.form.XForm" ) THEN	
		IF NOT oForm.Parent.isBeforeFirst() AND NOT oForm.Parent.isAfterLast() THEN
			IF stRecID <> "" THEN		
				oListField = oForm.getByname("lboStart")
				stDatum = oForm.Parent.getString(oForm.Parent.findColumn("Datum"))
				stKundeID = oForm.Parent.getString(oForm.Parent.findColumn("Kunde_ID"))
				stIDCurrent = oListField.BoundField.getString
				IF stIDCurrent = "" THEN stIDCurrent = "-1"
				stSource = "SELECT ""RechnungsdatumD""||' → '||""RechnungsnummerMitZusatz""||' → '|| COALESCE ( ""Rechnung_Anmerkung"" || ' → ', '' )|| REPLACE(ROUND(""Brutto"", 2),'.',',') ||' €' AS ""Field"", "
				stSource = stSource & """Rechnung_ID"", ""Rechnungsdatum"" AS ""Sort"" FROM ""viw_Rechnung"" "
				stSource = stSource & "WHERE ""Rechnungsdatum"" <= '"+stDatum+"' AND ""Kunde_ID"" = '"+stKundeID+"' "
				stSource = stSource & "AND (""Rechnung_ID"" NOT IN (SELECT ""Rechnung_ID"" FROM ""tbl_rel_Rechnung"" ) "
				stSource = stSource & "OR ""Rechnung_ID"" = '"+stIDCurrent+"') ORDER BY ""Sort"" ASC"
				stSql(0) = stSource
				oListField.ListSource = stSql
				oListField.refresh
			END IF
		END IF
	END IF
END SUB

[xhayoz]

Hey Robert

Thanks a lot. I faced the problem you described as the ListSource is used for all the list boxes. I came up with another solution.

By clicking on the listbox I’m running a query which returns my a list of already used property taxes and I’m removing these from the SelectedItems array. I’m not changing the ListSource as this remains standard for all my listboxes in my column.

I would like to avoid to have to implement a list box outside the table control. But if my previous explained idea doesn’t work I’ll have to implement it like you did.

Thanks a lot for sharing your solution. Have a great day.