Occupying selective data from another List in a Dropdown Element

[ArjanM]

Hi,

we are creating a planning-application where our customers can plan available generators on a certain date and for a period based on a Calendar visualisation. We have one List with the inventory of all the generators ("Generatoren") and one List with the periods that generators are booked ("Generators Data"). We have a Join between the two Lists, based on the unique Generator ID. Join works so that is Ok.

But now we want the Dropdown Element in de Form of "Generatoren Data" to be occupied only with the generators (from "Generatoren") that are not booked for a specific period, starting with the date that is clicked in the Calendar (Date Element "startdatum"). There is also a Date Element "einddatum" (which stands for "enddate") and marks the end of the period. This date is entered manually by the customer. So only the generators from "Generatoren" where "startdatum" > "einddatum" (in "Generatoren Data") should be visible in the Dropdown Element.

As far as we know, this should be done with a 'databasejoin' to the Element "generator_omschrijving" (which means "generator-description" in English) in the List "Generatoren" where "generator_gid" is the value that is being written to the List "Generatoren Data") and an additional WHERE clause that filters the generators that are available in that particular period.

I hope this is clear, because these thing (as simple as they are) are quite complex to explain in text.

Regards,

Arjan Myer

 

[cheesegrits]

I think you'll need something like this in your join element:

WHERE id NOT IN (SELECT generator_id FROM generators_data where '{generators_data___start_date}' < generators_data.end_date AND '{generators_date___enddate' > generators_data.start_date)

So that inner SELECT is selecting all the currently booked generator_id's in the generator_data table where the end_date is greater than the selected start date, and the start_date is less than the selected end date.

If you use the new AJAX update feature (introduced in a github update last month), this should update automatically when the user selects or changes the start or end date on the form.

-- hugh

 

[ArjanM]

Thanks Hugh,

we translated the elements for your convenience (because we can ;-) ). Based on your information we entered the following query:

WHERE generator_gid NOT IN (SELECT generator FROM generatoren_data WHERE `{generatoren_data___startdate}` < generatoren_data.enddate AND `{generatoren_data___endddate}` > generatoren_data.startdate)

But unfortunately we get the following error:

500 filter query error: generator Unknown column '' in 'where clause' SQL=SELECT DISTINCT(`generatoren`.`generator_description`) AS `text`, `generatoren_data`.`generator` AS `value` FROM `generatoren_data` LEFT JOIN `uq2ib_users` AS `uq2ib_users` ON `uq2ib_users`.`id` = `generatoren_data`.`executor` LEFT JOIN `generatoren` AS `generatoren` ON `generatoren`.`generator_gid` = `generatoren_data`.`generator` WHERE `generatoren_data`.`generator` IN ('EM010001','ZW066001','KO031501','HE011001') AND generator_gid NOT IN (SELECT generator FROM generatoren_data WHERE `` < generatoren_data.enddate AND `` > generatoren_data.startdate) ORDER BY `generatoren`.`generator_description`ASC LIMIT 0, 100

Looks like the Full Element Name is going down the drain because it is disappeared in the query. For your information: this Joomla-installation runs on a QNAP NAS with MariaDB 5.5.44. We don't know if this can be an issue, but one never knows.

Regards,

Arjan.

 

[cheesegrits]

Well, the first problem is using ` for data, which is why it's complaining about an unknown column. Data uses 'single quotes', `field_names` use backquotes.

I'll have to play around with some tests here to see what's going on. I'm not entirely sure whether the way the calendar passes the start date to the form in the popup when creating a new form will get picked up by the placeholder replacement.

And of course when the dropdown is first rendered, as the form is being created, the enddate won't have a value, but that's OK, as you won't want to offer any generator options until they've specified it.

BTW, one thing that worries me is that you aren't using the PK of the generators table. Even if the generator_gid is unique, not using the PK in a join can lead to problems.

-- hugh

 

[cheesegrits]

Can you do a github update, to pick up that new "AJAX Update" feature for the WHERE clause, which will automatically watch for changes on the form for any of the elements used in the WHERE, and rebuild the dropdown options when any of them are changed.

Also ... I would strongly suggest you switch from the old 'calendar' viz, to the new 'fullcalendar' one. We're deprecating the old one, and focusing all calendar related work on the new one, which uses the 3rd party Full Calendar jQuery plugin for calendar rendering. You may find a few short term issues with fullcalendar, as we iron out some kinks, but it'll be worth it in the long run.

Once you've done a full github update, you can discover, enable and publish the new viz through the standard J! extension manager.

-- hugh

 

[ArjanM]

thanks for your help and tips. Based on your instructions we are using the query as shown beneath and it works.

WHERE {thistable}.id NOT IN (SELECT generator FROM generatoren_data WHERE '{generatoren_data___startdate}' >= generatoren_data.startdate AND '{generatoren_data___enddate}' <= generatoren_data.enddate)

We also updated Fabrik from Github and using the Ajax-feature and the new calendar now. And you were absolutely right about using the Primary Key instead of the element "generator_id". The advantages are obvious...

-- Arjan.

 

[cheesegrits]

Great, glad that worked.

-- hugh