List box shows to many options in subform (doesn’t filter out from the main form)

[JohnnyBee]

I’m setting up a (firebird embedded) database for a tourist operator. The forms have sub forms. A booking (dsBooking) is linked to one or more accommodations (dsAccommodation), in a specific order (dsBookAcco). While tourists go from one hotel to the next on foot, without their luggage, that has to be transported from one hotel to the next (dsLuggage). The ‘from’ and ‘to’ fields are to be selected via a (sql) list box with the selected accommodations for that booking. All subforms are linked (via master/slave fields) by the bookingID.

Unfortunately the list box displays all the existent combinations of booking_ID’s and accommodations, not just the hotels of the applicable booking.

(For building purposes I used this concatenation now) Whether I use this SQL code

SELECT DISTINCT "Book_ID" || ' ' || "nr" || ' ' || "Acco_ID", "nr" FROM "BookingAcco" ORDER BY "Book_ID" ASC

or this one

SELECT DISTINCT "B"."ID" || ' ' || "BA"."nr" || ' ' || "BA"."Acco_ID", "BA"."nr" FROM "Booking" "B", "BookingAcco" "BA" WHERE "B"."ID" = "BA"."Book_ID" ORDER BY "BA"."Book_ID" ASC

Looks like it should have a simple solution, but I can’t find it …

Any help appreciated,
Thanks in advance
Challenge.odb (13.1 KB)

[Ratslinger]

Hello,
Do not have an answer for you as I still do not fully understand what you are attempting. It may help if you could list the steps involved in this process & just what you expect to happen.
Based upon what you have, there are multiple problems. Even though you have data in one table that same as data in another, it does not mean it is linked in any way. The same is true for a sub form. You Base file has no relationships established not the sub forms any linkage to its’ parent.
In addition, when I try to establish a link in main & sub forms, and error occurs:

.
The error (tested on Ubuntu 20 with LO v7.3.1.3) is caused by a Field Type of Text (fix) [Char]. Even though this is stated as a length of 3, the length is 12. Changing to Varchar with a length of 3 seems to work. This appears to be a bug but I do not know if it is already reported. You can check (Bugzilla), and if not found, report.
.
On the surface this set up appears to have some design flaws but really first need to understand the results wanted.

Edit:
On the Text (fix)[Char] field, I don’t see a bug reported (doesn’t mean it isn’t there) but it is a bug. With size set to 3 then 12 is set. If I set to 5 then 20 is set and 4 is 16. Seems to be 4X’s the stated size no matter what is actually entered.

[Ratslinger]

Hello,
Just a sample to give you an idea of linking. Your same file with fixes I noted. New form - Booking2. Uses new table Booking 2 and AccomSched. Luggage movement filled in as needed. Can be done on a separate form and using a filter can display specific dates.
Sample → Challenge.odb (22.9 KB)
A LOT more can be done. This is just a sample for you to begin with.

[JohnnyBee]

Thank you for your well appreciated attention.

Unfortunately I attached the wrong file, among other issues I struggled with the described bug, and the master-slave relations in the forms weren’t set in the uploaded version. In the next one I added StartingDate (Booking) and nights (BookAcco) as well
Challenge2.odb (14.3 KB)

@Ratslinger

It may help if you could list the steps involved in this process & just what you expect to happen.

As an example a new request arrives for hiking holiday GHI – Great Hiking Itinerary, visiting hotels A, B, C and going back to A, starting at 10/04/22. When the guests go from one hotel to the next their luggage is to be transported by a service provider, a taxi

• A tuple in the Booking table is inserted (1001, GHI, 10/04/22, extra, info)
• The relation Booking – Accommodation is established, 4 tuples are added to the BookingAcco table (1001, 1, ACA, 2, extra, info), (1001, 2, ACB, 1, extra, info), (1001, 3, ACC, 3, extra, info), (1001, 4, ACA, 1, extra, info)
• A taxi has to bring the luggage from one accommodation to the next (1001, SP1, 1, 2, extra, info), (1001, SP3, 2, 3, extra, info), (1001, SP1, 3, 4, extra, info). For these ‘from’ and ‘to’ fields’ I want to select these locations from the hotels linked to this booking by the corresponding sequence number. For debugging purposes the Booking_ID is added to the these list boxes at the moment.

With starting date, number in line and number of nights I have all the data to generate reports for the tour operator to make the reservations at the hotels and service providers.

Hope I did a better explanation this time.

[JohnnyBee]

@Ratslinger

On the Text (fix)[Char] field, I don’t see a bug reported (doesn’t mean it isn’t there) but it is a bug.

Actually the bug does exist (147893)

[Ratslinger]

Have answered a VERY similar question as this years ago - probably on a different site as I don’t see it on this site. Same issue. Do not see the need for this drop down you want or the separate Luggage table. Did you look at the example I posted? Why not have from & through dates in the Accom table along with Provider when done?
.
Seems more complicated than needed.
.
Edit:
If you insist on this direction, then do away with the list box (it is based upon a table so there is no filtering) and set the grid up just like you did with dsBookAcco.

[JohnnyBee]

@Ratslinger

Seems more complicated than needed.

Thank you for being a useful sparring partner. Looks like the problem is of an other level, more a normalisation issue. Have been a bit to enthusiastic about tearing the data apart in separate tables. Hotel and taxi being separate entities, I put them apart. I’ll have to rethink the overall design.