[Solved] Listbox filtered based on another listbox entry

Creating and using forms
Post Reply
Maax555
Posts: 127
Joined: Tue Mar 28, 2017 11:56 am

[Solved] Listbox filtered based on another listbox entry

Post by Maax555 »

Hi, i have looked through a few similar queries on this forum but have struggled to apply the solutions to my own problem.

My current REPLACES listbox lists all partnumbers in the database. I would like this REPLACES listbox to filter and only show the partnumbers related to a particular customer which has already been selected in an earlier listbox.

I can manage to get this working inside query design by simply adding a customer column and typing in a customer. When i run the query this way the results are restricted to the customer I have entered in the criterion field. However i need the criterion field to be updated according to the CUSTOMER listbox on the form.

So I guess my question is what do I enter into the Customer criterion field?

thanks
Attachments
Project Tracking test.odb
(116.65 KiB) Downloaded 305 times
Last edited by Maax555 on Wed Mar 21, 2018 10:23 am, edited 1 time in total.
Regards Maax
LibreOffice 6.1.4.2 on Windows 7
UnklDonald418
Volunteer
Posts: 1540
Joined: Wed Jun 24, 2015 12:56 am
Location: Colorado, USA

Re: Listbox filtered based on another listbox entry

Post by UnklDonald418 »

Look on page 210 of the LO Base Handbook.
https://documentation.libreoffice.org/e ... tion/base/

By the way your PartNumber table is what is referred to as a flat database table. There are several 1st normal form violations. Probably the worst is storing the Customer name rather than the CustomerID. The argument for this normalization is that if a customer changes their name you must locate and change the names for all the relevant records in the PartNumber table. In a normalized table you only need to make the change in the Customers table.
You might also consider normalizing CatType, Complexity and Employees.
If your problem has been solved, please edit this topic's initial post and add "[Solved]" to the beginning of the subject line
Apache OpenOffice 4.1.14 & LibreOffice 7.6.2.1 (x86_64) - Windows 10 Professional- Windows 11
Maax555
Posts: 127
Joined: Tue Mar 28, 2017 11:56 am

Re: Listbox filtered based on another listbox entry

Post by Maax555 »

Hi Unk, many thanks for taking the time to look and advise. After starting this data base I did put it to one side and start to go through a medical database example which was quite interesting.
However due to what must be a year away from this i found myself returning to my original database as I felt I was learning a little with all the help form forum members such as yourself.
I may in parallel start it again and see how I get on.

thanks again.
Regards Maax
LibreOffice 6.1.4.2 on Windows 7
UnklDonald418
Volunteer
Posts: 1540
Joined: Wed Jun 24, 2015 12:56 am
Location: Colorado, USA

Re: Listbox filtered based on another listbox entry

Post by UnklDonald418 »

I saw your other thread but I think most of your questions there can be addressed here.

Since you already have a Customers table, normalizing the Customer field in the table PartNumber can be handled with a little SQL

Code: Select all

ALTER TABLE "PartNumber" ADD COLUMN "CustID" INTEGER;
UPDATE "PartNumber"
SET "CustID" = SELECT "CustomerID" FROM "Customers"
WHERE "PartNumber"."Customer" = "Customers"."CompanyName";
I'm not sure why but I ran into a problem using SQL to delete the Customer field so I used the table design GUI to open the PartNumber table and manually deleted Customer.

You will also need to make changes to the PartNumber form. Open the Data tab of Properties: Text Box dialog for “txtCustomer” and change the Data field to CustID then change the query to

Code: Select all

SELECT "CompanyName", "CustomerID" FROM "Customers" ORDER BY "CompanyName" ASC;
and finally I would recommend on the General tab you change the Name to lbxCustomer to more accurately reflect what is stored in that control.

You could use a similar process to normalize CatType, Complexity and Engineer fields.

There are also issues is with Replaces and Similar. Will there always only be one Replaces item and one Similar item?
It the answer is Yes then it would be more efficient to store the ID of the Item rather than the Item string for those two fields.
If there could be more than one Replaces item then eliminate the Replaces field in the PartNumber table because would have a many to many relationship which requires an intersection table.
Likewise for Similar.

Once the table design issues have been resolved then we can look at some possibilities for
I would like this REPLACES listbox to filter and only show the partnumbers related to a particular customer
 Edit: It also just occurred to me, might you also need a Replaced By field? 
If your problem has been solved, please edit this topic's initial post and add "[Solved]" to the beginning of the subject line
Apache OpenOffice 4.1.14 & LibreOffice 7.6.2.1 (x86_64) - Windows 10 Professional- Windows 11
Maax555
Posts: 127
Joined: Tue Mar 28, 2017 11:56 am

Re: Listbox filtered based on another listbox entry

Post by Maax555 »

Hi Unk, thanks again for taking time to look at my database and offer advice. I will go through the above step by step.
The replaces and similar options are simply to pull up the details of the similar part and the part it is replacing so we can quickly see differences in BOM, labour, wires etc.
There will not always be a similar part or a part that the new part is replacing.
replaced by could be an option but i was considering that this could be done in a report by search on partnumber in replaces field and listing the new partnumber.
I have a lot to correct above and will get that working before moving forward. I have started a new database in parallel but have more tables rather than shoving all the fields into the main partnumber table.
in the new one there are a lot more ID's used. I have had a go at manually linking relationships but not so sure if I have covered everything. I posted for advice on another site as didnt want to have too many posts on the go in anyone site but have had little response as of yet.
Regards Maax
LibreOffice 6.1.4.2 on Windows 7
Maax555
Posts: 127
Joined: Tue Mar 28, 2017 11:56 am

Re: Listbox filtered based on another listbox entry

Post by Maax555 »

So I have gone through the above but have ended up with an error which i believe is because I have added a macro that refers to Customer which i have now changed and am using CustID as per your updates above.
The Macro updates the replaces and similar fields to only show partnumbers associated with the Customer on the same form. I have tried to update the macro replacing Customer with CustID but as expected this does not work as easily as that.
Attached for your reference.
Attachments
Project Tracking Original V1.01.odb
(120.37 KiB) Downloaded 266 times
Regards Maax
LibreOffice 6.1.4.2 on Windows 7
UnklDonald418
Volunteer
Posts: 1540
Joined: Wed Jun 24, 2015 12:56 am
Location: Colorado, USA

Re: Listbox filtered based on another listbox entry

Post by UnklDonald418 »

If you change the txtCustomer list box to a use the integer CustID field as the Data Field, then you can get the value with

Code: Select all

    iCustID = oField.BoundField.getInt()
and use a proper list box query something like

Code: Select all

		sSql="SELECT ""Item"", ""ID"" FROM ""PartNumber"" WHERE ""CustID"" = " & iCustID & "' ORDER BY ""Item"" ASC"
If your problem has been solved, please edit this topic's initial post and add "[Solved]" to the beginning of the subject line
Apache OpenOffice 4.1.14 & LibreOffice 7.6.2.1 (x86_64) - Windows 10 Professional- Windows 11
UnklDonald418
Volunteer
Posts: 1540
Joined: Wed Jun 24, 2015 12:56 am
Location: Colorado, USA

Re: Listbox filtered based on another listbox entry

Post by UnklDonald418 »

I had a little time to spend on this. Try the form PartNumber1 in the file I uploaded.
It uses the table PartNumberNEW.
I did modify the macro code in the refresh module to reflect the changes I made to the form and the table.
Attachments
Project Tracking Original V1.02.odb
(94.92 KiB) Downloaded 303 times
If your problem has been solved, please edit this topic's initial post and add "[Solved]" to the beginning of the subject line
Apache OpenOffice 4.1.14 & LibreOffice 7.6.2.1 (x86_64) - Windows 10 Professional- Windows 11
Maax555
Posts: 127
Joined: Tue Mar 28, 2017 11:56 am

Re: Listbox filtered based on another listbox entry

Post by Maax555 »

Thanks Unk. I downloaded the V1.02 file but get an error as soon as i open the PartNumber1 form. Contents of a combo box or list box could not be determined. Column not found: There are various parts that are no longer working, Type, Complexity, Replaces, Similar as well as the fields to the right of Replaces and Similar. I will have a play around and see if I can work it out.

thanks
Regards Maax
LibreOffice 6.1.4.2 on Windows 7
Maax555
Posts: 127
Joined: Tue Mar 28, 2017 11:56 am

Re: Listbox filtered based on another listbox entry

Post by Maax555 »

fixed the Type and Complexity simply by adding ID into the list content query. This has also fixed the error upon opening. Struggling to move forward with Replaces and Similar. I am gonna see if i can make this work by using PartNumberID etc. Uploaded the database with the couple of fixes i mentioned.
Currently Replaces and Similar list box only showing partnumbers for the customer with ID O no matter what i enter into the customer box. I think this is due to the criterion = '0' in the listboxes.
Attachments
Project Tracking Original V1.02.odb
(98.83 KiB) Downloaded 305 times
Regards Maax
LibreOffice 6.1.4.2 on Windows 7
UnklDonald418
Volunteer
Posts: 1540
Joined: Wed Jun 24, 2015 12:56 am
Location: Colorado, USA

Re: Listbox filtered based on another listbox entry

Post by UnklDonald418 »

Sorry about that, it didn’t occur to me that AOo and LO would handle combo/list boxes differently. V1.02 works fine with AOo so I this morning tried it with LO and saw the errors you mentioned.

My error was that I left the Type, Engineer and Complexity list boxes as I found them.
When using list boxes it is best not to mess around with the Boundfield setting and use a query like
SELECT “display column”, “ID column” FROM “List Box Source Table”
making sure that the list box Data field property is connected to an integer field. It appears you found that once the queries were corrected the error messages disappeared.

Looking more closely at some of the other fields in I see some potential issues with Labour, Complexity, Wires, Branches, BOM, AnnualQty, UnitPriceGBProto and UnitPriceOS which are all Numeric type. According to the HSQL User Guide “DECIMAL and NUMERIC with decimal fractions are mapped to java.math.BigDecimal and can have very large numbers of digits. In HyperSQL the two types are equivalent.”
Perhaps, some of the fields like Complexity, Wires, Branches and AnnualQty should be changed to Integer type.
Currently Replaces and Similar list box only showing partnumbers for the customer with ID O no matter what i enter into the customer box. I think this is due to the criterion = '0' in the listboxes.

Here is V1.03 that I tested with LO 6.0.1.1, see if it resolves that issue.
Attachments
Project Tracking Original V1.03.odb
(95.78 KiB) Downloaded 322 times
If your problem has been solved, please edit this topic's initial post and add "[Solved]" to the beginning of the subject line
Apache OpenOffice 4.1.14 & LibreOffice 7.6.2.1 (x86_64) - Windows 10 Professional- Windows 11
Maax555
Posts: 127
Joined: Tue Mar 28, 2017 11:56 am

Re: Listbox filtered based on another listbox entry

Post by Maax555 »

Many thanks Unk. I found that the replaces and similar fields were not updating correctly if I changed the customer. So if i added a customer and then went back and changed it without exiting the form the fields would not update in the similar and replaces ListBox inline with the new customer. I added another refresh to the customer listbox under the after updating event. Seems to have done the trick. I will now mark this as solved so i can look into your suggestion of intergers in place of decimals/numbers for certain other fields. I also want to study how i can emulate the replaces and similar ListBox queries which update according to the customer ListBox so i can use the same method on other fields. I think there is a macro helping out with this which will no doubt be a stumbling block for me.
Once again thanks for the time, help and advice you have given on this.
Regards Maax
LibreOffice 6.1.4.2 on Windows 7
Post Reply