Simple List Box Filter

[baserider]

I have replaced a Table Control column with a List Box. I would like to filter the contents of the List Box by using an int field on the MainForm.

The SQL command I’d like to use is something like:

SELECT "street1", "address_id" FROM "addresses" WHERE "customer_id" = "MainForm.customer_id"

For my odb specifics, I’ve set up 3 tables: customers, addresses, and payment_methods. Each customer can have multiple addresses. One of the addresses needs to be assigned to a payment_method. The Form design is as follows:

MainForm
--- customer_id
--- other customer info...
--- SubForm: addresses
--- SubForm: payment_methods

Each of the SubForms are Table Controls. In the payment_methods Table Control, I’ve set up a List Box to select an address from the addresses table. When I click on the List Box, I would like it populated with ONLY the addresses related to the customer_id from the MainForm.

I’ve glanced through some of the threads on openoffice.org, including topics like cascading list boxes, filter tables, etc. TBH, it’s way over my head. My situation doesn’t seem nearly as complex as some of those examples. Hopefully, I just need to learn the proper syntax. Thanks in advance for help!

[Ratslinger]

In edit mode, bring up the properties for the List Box. On the Data tab, set ‘Type of list contents’ = Table, under ‘List Content’ use dropdown to select the table where the addresses are, and finally under ‘Bound field’ enter the field position of the address. ‘Bound field’ is an index so the first field in the table with addresses is 0, the second field is 1 and so on. Save your changes.

In connection with the comments, I’ve modified my understanding of the question. To better understand my comment regarding a working model I include a sample here: PayMethodFilter.odb Both forms work the same. Only significance is to show address table control on form is not needed to complete. After selecting a customer, click button. Choices in CUSTADDR field is a list box displaying only addresses for that customer.

[baserider]

Thanks for your reply. Unfortunately, your solution causes the List Box to be populated with all entries from the Table. I want to filter the entries in the List Box so only those that match a Secondary Key value appear. The Secondary Key value is contained in the MainForm.

[Ratslinger]

After further investigation, you can certainly do this without macros if you use table filtering. Some good examples at another forum:Filter1 and:Filter2

[Ratslinger]

Honestly this has bugged me. The problem is that the address table control plays NO part in this. In reality, the Cust Listbox is connected to a Filter (very simple), the payment method is tied to the filter, and the payment type address(as a list box) is obtained from the address table (need not be on the form) filtered by the filter record. Now only the addresses associated with the account appear in the payment type listbox! Got to use Push Button to confirm Customer. Got this working.

[EasyTrieve]

I just learned that Bound field can also be set to -1 (ref: Data - LibreOffice Help), in which case you don’t even have to include an ID in your lookup table’s result set. It appears that with Bound field=-1 the List Box uses the FROM table’s primary key. What is shown in the pull down is still from column 1. This means that someday List Box might be able to be more easily improved to have multiple columns shown, rather than just one.

[Ratslinger]

@EasyTrieve Your comment has no relation to this question. In fact your statement in the comment about the primary key is not correct and is not even in the link you present. Suggest you test this to see what it actually does. Since your posting, I have tested and discovered it actually answers a portion of one of your earliest questions. Happy hunting!

[EasyTrieve]

@Ratslinger, Whatever. But I checked the link. It’s correct. Search down for “-1”.

[Ratslinger]

Not only did I find the -1 weeks ago as my comment states, but I actually tested it at that time. In no way does the link state it uses the ‘primary key’ nor do the tests show this. I would like you to specify where it states -1 related to primary key. Specifically what I see is ‘index’ which is quite different and tests different than a primary key. Unless we have different dictionaries, index is not primary key.

[EasyTrieve]

@Ratslinger, I see, it’s the index of the displayed pull down items, 1,2,3… Thanks for straightening me out.

[peterwt]

The problem is that the constraint you want to place on the List Box contents is contained in the customer_id in the MainForm. This will obviously change depending on the selected record on the MainForm.

As I see it this value can only be obtained using a Macro. As a test run the macro from a form button. The macro would first store the value of the customer_id of the selected record in a variable. The List Box would be based on a Query of the addresses Table, like the Query you quoted but the WHERE clause being set to

WHERE "customer_id" = 0

The 0 is just a temporary marker.
The Macro would then modify the Query to change the value in the WHERE clause to the value in the variable and then refresh the Form. The List Box contents would then show only the addresses for the selected Customer.