How to limit selection in listbox

[nofue]

So far my Form - SubForm - SubSubForm stuff works pretty well. Now I have yet to solve one more issue which is beyond my knowledge. Given is this set of tables:

These (and some more) are presented in this form:

Everything works fine as it is, however, to make the self reference in tEffort table work (tEffort.effortID = tEffort.effortRef) the user has still to enter the effortRef value in the SubSubForm (which is child to the tAssignment SubForm which is child to tProspect Form). The previous Effort ID is shown at the far left, 209 in this case. Now the user has to select 209 from the listBox to properly connect the most recent effort to the chain of efforts. This works reasonably well, but with the growing number of efforts, the list gets quite long, and, even worse, wrong numbers, pointing to different prospects/efforts, may get entered involuntarily.

My intended solution was to limit the list of values in the listbox to only effortIDs which relate to the prospect shown. But somehow I can’t figure out how to limit the effortIDs to the prospect. In a query, it’s no problem to get what I want, f.e. for prospectID = 13:

SELECT "tEffort"."effortID" 
FROM "tEffort", "tAssignment" 
WHERE "tEffort"."assignmentRef" = "tAssignment"."assignmentID" 
AND tAssignment.prospectRef = 13
ORDER BY "tEffort"."effortID" DESC

But I can’t figure how to make this work in a SubSubForm (accessing tAssignment.prospectRef in the parent SubForm would be ideal, but …)

Question:
How can I limit the current list of effortIDs to the current tAssignment.prospectRef (or tProspect.prospectID) from within the listbox?

TIA

[RobertG]

Might be this helps: How to fill listbox only with non previously selected items

[nofue]

This is what I was afraid to get to hear Well, as I just installed PostgreSQL to get my feet wet with that db, too, I will first migrate my HSQLDB db to PostgreSQL. Meanwhile I hope somebody will chime in with a less intimidating solution However, can you point me to a list of all the objects and methods available in LibreOffice Base, and maybe a list of supported languages? I quit BASIC in late 1986 in favor of C and I’m not looking back JavaScript would be nice, but I’ll take most anything offering a reasonable debugger.

[RobertG]

See Base Guide 7.3. There is more stuff in German Base Handbuch, but translating to English is too complicated…

[Wanderer]

@nofue

JavaScript would be nice, but I’ll take most anything offering a reasonable debugger.

Usually LibreOffice brings BASIC, some version of JavaScript (I’ve seen they updated Rhino recently) and Python. On Linux usually the python install of the OS is used.
.
You can also send commands from external programs to LibreOffice (search for -accept switch), so using compiled languages is also possible, or you may create an extension…
.
There are also some who use a Jupyter notebook to control LibreOffice…
.
You will find most examples for BASIC, but usually you can do the same with other Languages. My recommendation would be to look into Python for internal scripting, if you start new. But for typical " read some data from form/cell and call an API-function, save result somewhere" language doesn’t matter much. IMHO one big question is, if you wish to have strong type-checking or not.

[Villeroy]

countries_cities.odb (65.1 KB)
Continents → Countries → Cities of voyages.
First form is a work-around to avoid the problem of filtered listboxes. Having a limited amount of countries, you can type a country code into the focussed listbox in order to jump to the cities of that country.
Second form makes use of subforms with table grids instead of listboxes.
Third form implements cascading listboxes by means of a filter table. The filter record with FID=0 stores the continent ID of form the logical form named “FilterContinent” (see form navigator), the one with FID=1 stores the country ID of form “FilterCountry” which is refreshed by the blue button.
The listbox goes like this: SELECT "Name", "ID" FROM "Countries" WHERE "ContID" = (SELECT "INT" FROM "Filter"WHERE "FID"=0) ORDER BY "Name" ASC
The white controls and the green refresh button belong to the form “New_Voyage”.
The listbox goes like this: SELECT "Name", "ID" FROM "Cities"WHERE "CountryID" = ( SELECT "INT" FROM "Filter" WHERE "FID" = 1 ) ORDER BY "Name" ASC

The 4th form works pretty much like the 3rd one but with an AutoRefresh macro. The buttons between the listboxes do not refresh the next form. Instead, they store the current form. **edit: (and they are hidden while in data entry mode)**Because they are set up as default buttons, hitting the Enter key stores the current form without mouse click. Storing the form triggers a macro which refreshes the next form. The form data and listbox queries are the same as in the 3rd sample.

[nofue]

This is very inspiring. As I lack the time to digest the application in great detail, I just skimmed the versions and there’s a lot lot learn there – thank you very much. Only the forth version doesn’t behave – no matter which continent I select, the countries remain African ones, Kenya and Nigeria. Same when I select a city: it only shows cities from Kenya, although I chose Nigeria. Anyway, it’s great food for thought, and I hope there’s some time soon to look deeper. Again, thanks a lot.

[Villeroy]

Tools>Options>Security>[Macro Security…]
Set the highest level of security.
Declare your documents folder or anything therein in as trusted folder. Subfolders of the selected folder(s) are trusted too.
Do NOT trust your downloads folder.

[nofue]

Works like a charm. Just for the record: on the Mac, it is “LibreOffice/Preferences”, not “Tools/Options”.

[xhayoz]

Hey

You could modify the ListSource of your ListBox Column via a macro using the sql code you wrote an setting the prospect_id dynamically when moving back/forth in your parent form. The macro would be called when loading your form with the table control.

Be aware that the ListSource is the same for each row. (If an effort_id was already used as reference it will still be available in the list)