[Base] Display in Listbox only "active" items

[Ferineum]

Hi guys, I need your help regarding my LO Base form.

Let’s assume I have the following setup:

Table 1: Attributes are ID, Employee Name, PositionFK (Foreign Key, refers to ID of Table 2)
Table 2: Attributes are ID, Position Name, Status

Now, I have a LO form which is showing the contents of Table 1 within a grid. The Position column was created as list box in order to provide a dropdown selection based on the contents of Table 2.

What I would like to achieve is that within the dropdown, only those items of Table 2 are shown which are active (that is Status = 1) BUT in the grid, there should be also those items shown which have the status = 0.

For example:
In Table 1 are the following Employees stored:

ID = 1, John Doe, PositionFK = 1, ID = 2, Jane Doe, PositionFK = 2, ID = 3, Luke Skywalker, Position = 3

In Table 2 are the following Positions stored:

ID = 1, Director, status = 1; ID = 2, Manager, status = 1; ID = 3, Consultant, status = 0

If I’m going to create a new employee, the dropdown of the form should show Director and Manager only. But within the grid, Luke Skywalker’s position should still show Consultant.

What I’m currently able to achieve is that either all Positions are displayed in the grid as well as in the dropdown, or only active items are displayed in the dropdown and inactive positions appear empty in the grid (that means for Luke Skywalker the position column is empty).

[Ferineum]

Okay, I tested a litte bit by myself and now I’ve got the following macro in place (in on Mouse Button pressed Event):

Sub loadPositions (oEv)
dim oControl As Object
dim qry As String

oControl = oEv.Source.Model ' get ListBox
qry = "SELECT `positionName`, `positionID` FROM `position` WHERE `status` = 1;"
oControl.ListSource = array(qry)
oControl.refresh()
End Sub

[Ratslinger]

What you want to accomplish will take an added column on the form for Table1.

• Leave the existing ‘PositionFK’ column as a listbox but remove any restrictions (ie: all available from Table2)
• Set the properties on that column for Enabled to No & change title to “Existing Position”
• Insert a NEW column on the grid with the same original criteria as “PositionFK” (Enabled = Yes; limit restrictions)
• Give this column a title such as “Select Position”

This should result in a display as:

This establishes a connection to Table2 using the Existing Position column, but only allows specific choices from the Select Position column to update the actual field in Table1.

If this answers your question please click on the (upper left area of answer).

[Ferineum]

Thank you for providing this answer. I also like EasyTrieve’s approach and I’m also thinking about writing a macro. But I’m not sure if it’s really worth the effort so maybe I will realize this solution. Thanks a lot!

[EasyTrieve]

I like Ratslinger’s answer, but also wanted to add how I normally take care of this situation, because I encounter it a lot, and I don’t like having two fields in my Table Control that really are about the same thing.

For me this occurs most often when a record status becomes “obsolete”. I want to keep the obsolete selections (records), but I don’t want them as my normal selection choices.

Here is an example. What I do is use the status to do two things: 1) sort the obsolete records to the end (bottom), and 2) flag them with their status, in this case I often attach the word " --Obsolite" to their name.

NOTE: I think it would also be possible to write a macro that would dynamically populate the pull down list with only the items with a given status, but there may be a gotcha along the way, and I’d rather keep things simple if at all possible. (I hesitate because I recently ran into another event bug in LO and I wasted a day because of it. So somethings that might be possible, need to be proved to work, before I will believe they will really work.)

[Ratslinger]

This is not about having two fields in a table but rather in a Table grid control. They both refer to the same field in the table. If you don’t want to see the field in the grid, just hide it; the actual value for the record still shows in the sub-form and the functionality remains. With your method you can still set a field to a value which is unwanted.

[EasyTrieve]

@Ratslinger, Thanks, Sorry for my lax language. Revised: “table” to “Table Control”. No, I was not suggesting a 2nd data field.