Multiple database joins into one DropDown

[sjmatheson]

Hello,

Just after some advice.

I want to create a DropDown list which is populated from several other lists.

For example I want to be able to get the user to choose a car registration number from 3 lists, in this case ?cars, trucks, boats?.

So the user could open the DropDown and select the registration number across all of the other 3 lists.

Wondering what the best way possible is to do this?

I see the database join element selection but that looks like it can only draw from 1 other list etc, although I maybe mistaken.

Thanks


Sent from my iPhone using Tapatalk

 

[cheesegrits]

Nope, no built in way of doing that.

One way to do it might be to create a view in MySQL, using UNION ALL ...

CREATE VIEW all_reg AS (SELECT registration FROM cars) UNION ALL (SELECT registration FROM trucks) UNION ALL (SELECT registration FROM boats)

Modify table and field names to suit.

That would create a view called all_reg, with the 'registration' columns from all three tables concatenated together, which you could then point to with a join element. It wouldn't have a primary key, but if all you want to save on your form is the registration string, not an FK to a table (which you couldn't anyway, as it could be one of three tables), that would be OK. Just set the value and label on the join element setup both to 'registration'.

To create the view, run phpMyAdmin (or your client of choice). Run just the query part (everything after the AS) first, make sure it returns the result set you expect. Then create the view by prepending the "CREATE VIEW whatever AS" part. Once created, the view should show up in Fabrik as an available table to use in your join element.

Also note that if the 'registration' field isn't called the same thing in all three tables, the view will use the name from the first SELECT in the union as the name.


-- hugh

 

[sjmatheson]

Ah I see. Understood. Thank you for the information. Very helpful


Sent from my iPhone using Tapatalk