[SOLVED] Reload Dropdown Element

[marcq]

Hi,

I have created a form with among others :

- 1 date Element
- 2 databasejoin dropdown Elements (called A and B)
- 1 calc Element which is counting if the date already exists in the "slot-taken" table and which is hiding or showing the appropriate databasejoin dropdown Element A or B (A if count = 0 and B if count > 0).

When the form is loaded the dropdown databasejoin Element A is loaded.

When a user is selecting a date which do not already exist into the "slot-taken" table, then the databasejoin dropdown Element A remains. If Yes then the databasejoin dropdown Element A is hidden and the dropdown databasejoin Element B is shown (Element Javascript).

When the date already exists, the dropdown databasejoin Element B should be shown (and the dropdown databasejoin Element A hidden) , which is not the case. So after the date is entered, I would need to reload the dropdown databasejoin Element.

Now the user needs to go back into the date field element and to go out with the cursor to load the dropdown Element B : please see the video :

http://www.screencast.com/t/liKXWCttF

How can I solve this issue ?

Thanks a lot for your support.

Cheers, marc

 

[cheesegrits]

I've had a look at that page, but I don't seem to be able to find a date that generates a "> 0" result for the calc. Can you give me exact instructions on how to replicate this on your page.

Also, I haven't seen an email form you on this one:

http://fabrikar.com/forums/index.ph...element-solution-from-hugh.45171/#post-233142

I spent about 1.5 hours working on that query for you, which you said you'd pay for ...

-- hugh

 

[marcq]

Hi Hugh,

Concerning the Union Query, I sent you an Email, please reply and send me your Paypal id + amount.

Concerning this issue :

On load the databasejoin dropdown Element "fab_booking___book_starttime_all" is shown and the databasejoin dropdown Element "fab_booking___book_starttime" hidden.

I have created a calc element "book_count" which count the

$db = JFactory::getDbo();
$user = JFactory::getUser();
$query = $db->getQuery(true);
$query = "SELECT COUNT(*) FROM fab_booking_taken WHERE book_date = '{fab_booking___book_bookingdate}'";
$db->setQuery($query);
$count = $db->loadResult();
return $count;

I also added some Javascript to this elements to show or hide accordingly the form elements :

on load : When element "book_count" > "0", show element "fab_booking.book_bookingstartdate"
on load : When element "book_count" > "0", show element "fab_booking.book_landingtime"
on load : When element "book_count" > "0", hide element "fab_booking.book_starttime_all"
on load : When element "book_count" > "0", hide element "fab_booking.book_landingtime_all"
on load : When element "book_count" == "0", show element "fab_booking.book_starttime_all"
on load : When element "book_count" == "0", show element "fab_booking.book_landingtime_all"
on load : When element "book_count" == "0", hide element "fab_booking.book_starttime"
on load : When element "book_count" == "0", hide element "fab_booking.book_landingtime"
on change : When element "book_count" > "0", show element "fab_booking.book_starttime"
on change : When element "book_count" > "0", show element "fab_booking.book_landingtime"
on change : When element "book_count" > "0", hide element "fab_booking.book_starttime_all"
on change : When element "book_count" > "0", hide element "fab_booking.book_landingtime_all"
on change : When element "book_count" == "0", show element "fab_booking.book_starttime_all"
on change : When element "book_count" == "0", show element "fab_booking.book_landingtime_all"
on change : When element "book_count" == "0", hide element "fab_booking.book_starttime"
on change : When element "book_count" == "0", hide element "fab_booking.book_landingtime"

I have added into the databasejoin dropdown Element "fab_booking___book_starttime_all" a "Notempty" Validation with the following condition :

return '{    fab_booking___book_count}' === 0;

I have added into the databasejoin dropdown Element "fab_booking___book_starttime" a "Notempty" Validation with the following condition :

return '{    fab_booking___book_count}' > 0;

When return value is "> 0" then the databasejoin dropdown Element "fab_booking___book_starttime" is shown and "fab_booking___book_starttime_all" hidden.

Is it clear enough Hugh ?

I have added some info into My Sites" LA NEPTUNE into the "Notes" so that you can find the list, form, elements.

Thank you in advance for you support.

Cheers,

Marc

 

[cheesegrits]

I still can't find a date which returns anything than than 0 from that calc.

I suspect the problem is that you are comparing the full date/time, so an example query the calc is generating is ...

SELECT COUNT(*) FROM fab_booking_taken WHERE book_date = '2016-10-21 11:34:28'

I've changed your calc code to this:

$query = "SELECT COUNT(*) AS tot FROM fab_booking_taken WHERE DATE(book_date) = DATE('{fab_booking___book_bookingdate}')";

... so it only compares the date parts, ignoring the time.

That seems to return counts of > 0, although the resulting dropdown doesn't have any dates in it.

-- hugh

 

[marcq]

Hi Hugh,

Thank you for your feedback. You have several dates which should return a value > 0.

Please try with the following booking date (date de location) :
20/10/2016
21/10/2016

As you can see on the video :

http://www.screencast.com/t/QCdlJLI1QIk

if the user is choosing the 21/10/2016 no value are returned, but if I go back into the date field and go out again then the "Validation" is running again and "miracle" the dropdown return the appropriate values starting with "15:30".

Cheers, marc

 

[cheesegrits]

Thank you for your feedback. You have several dates which should return a value > 0.

Please try with the following booking date (date de location) :
20/10/2016
21/10/2016

Yes. As I said, I fixed that problem by adding the DATE() handling to your calc code, so it's only comparing the date part. Until i did that, nothing was returning > 0.

And it looks like the issue you are hitting now is basically the same problem. In the WHERE clause for the starttime you have ...

AND p1.book_date = '{fab_booking___book_bookingdate}'

Which "should" work, because you don't have a time format on bool_bookingdate, but for some deeply weird reason, the first time that join's AJAX update runs when you change the booking date, the time part is being set to current time. As soon as the date's validation has run again, that issue goes away.

But basically, whenever you do anything in MySQL with dates where you don't want the time part to be used, you should always use the DATE() function ...

AND DATE(p1.book_date) = DATE('{fab_booking___book_bookingdate}')

... rather than assuming that your datetime elements will have 00:00:00 as the time. Defensive coding, which can save you hours of frustration.

I modified that WHERE clause, and it now seems to work.

BTW, to anticipate your next issue. When a join element gets updated like this (on the fly, via AJAX) we can no longer guarantee ordering of the options in the dropdown. Which means that your time slots may get out of order if you change the start date several times. This is something that could maybe be done (I took a look at it a while back, and think I see a way to do it), but not as part of subscription support.

-- hugh

 

[marcq]

Thanks Hugh send me a quote for the dropdown element ordering

 

[cheesegrits]

Can you remind me later in the week, Monday is always a busy day catching up fo0rum support.

-- hugh