We're currently migrating from Ask to Discourse, read the details here

Ask Your Question

[Base] Display in Listbox only "active" items [closed]

asked 2017-05-13 15:51:48 +0200

Ferineum gravatar image

updated 2021-05-30 10:42:42 +0200

Alex Kemp gravatar image

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).

edit retag flag offensive reopen merge delete

Closed for the following reason the question is answered, right answer was accepted by Ferineum
close date 2017-05-25 19:04:21.754927


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)
End Sub
Ferineum gravatar imageFerineum ( 2017-05-25 15:57:04 +0200 )edit

2 Answers

Sort by » oldest newest most voted

answered 2017-05-13 19:18:31 +0200

Ratslinger gravatar image

updated 2017-05-13 19:20:32 +0200

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:

image description

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).

edit flag offensive delete link more


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!

Ferineum gravatar imageFerineum ( 2017-05-25 12:23:08 +0200 )edit

answered 2017-05-14 18:16:03 +0200

EasyTrieve gravatar image

updated 2017-05-14 19:49:42 +0200

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.

image description

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.)

edit flag offensive delete link more


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.

Ratslinger gravatar imageRatslinger ( 2017-05-14 19:36:35 +0200 )edit

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

EasyTrieve gravatar imageEasyTrieve ( 2017-05-14 19:48:55 +0200 )edit

Question Tools

1 follower


Asked: 2017-05-13 15:51:48 +0200

Seen: 432 times

Last updated: May 14 '17