Force Ajax update on database Join at form load.

I have a database join element that is intended to show a list of Counties in a State based on the selection of the state from another dropdown/database join on that form.

Table structure
"states" - > id, DateTime, state, abbr
where state is the full name of the state and abbr is the 2 letter abbreviation for a state.

Counties -> id, DateTime, county, state
where state is the same as abbr above.

In my form I use database join for "County" with the "data where" filter of

WHERE {thistable}.state = (SELECT abbr from states where id = '{assist_9_repeat___state}')

then "Ajax update" is on - so the database join is updated when the {assist_9_repeat___state} is changed.

90% of the time the "state" will be a particular state (Colorado) - so to make things simpler - "state" , {assist_9_repeat___state}, has a default of "CO" form load.

Flow:
New record.
state is loaded with "CO"
the County dropdown is empty because "state" has not changed, so the AJAX update has not fired.
The user must select some other state, then "CO" again - which fires the AJAX update in the county database join and populates the County dbjoin with Colorado counties.

-> This is the problem. I need to populate the County dropdown with Colorado Counties on load - but ajax only fires it the state dropdown is changed - it does not fire on load.

There is an Ajax default that I can set - but that only returns 1 string for County, it does not populate the county list.

I can get rid of the default for 'State', but I'm 100% sure my users will say "can't you make the default state Colorado - that's what we use all the time".

Thoughts? Maybe a helper run on load for the form that populates the county dropdown?

-----------------
Similarly, on loading of a record to edit, the County is populated, but the, but again the AJAX update is not fired - so if the user needs to change the county, they have to change "state" 2 times, to force the county dbjoin to update.

-----------------
This seems like such an obvious need for AJAX update on dbjoin - what am I missing?

-Bob

 

In element javascript (e.g. id element), event = load, try this:

jQuery('#mytable___your-select-element-id').val('CO').trigger('change');

If CO has a different "raw" value, use the raw value instead.

 

Thank juuser - I get the idea but I'm not quite there. I'm not sure what field I should be putting this javascript in

You say "e.g. id element", but I have multiple id elements -

It seems like I should be firing the 'change' trigger on load of the state element
so - essentially I'm emulating that "state" changed.
So - in my table
jQuery('#assist_9_repeat___state').val('CO').trigger('change');
or maybe
jQuery('#assist_9_repeat___state').val('6').trigger('change'); (6 is the index for CO which is the actual value stored)

But I'm not seeing the trigger on load as far as I can tell.
I'm loading up in phpstorm to see if I can catch the java....

 

You can add this to your main table "id" element if you have joins and therefore several id elements. Actually it doesn't even matter as long as this element gets loaded / shown when form is loaded.

jQuery('#assist_9_repeat___state').val('6').trigger('change'); should work, but I see from your element name that this dropdown is in repeat group?

In this case there should be _0, _1 etc. in the end of the element id.

 

Okay - I think I see. By adding it to any id 'onload' we'll get it called.
Yes it is in a repeat group - which complicates matters - and in fact, it is a repeat group with "0" minimum repeats which means on load of the form there isn't even a "_0" element. Until you push the 'add group' button.
I think that may be what is happening - on load, there is no instance of the repeated group. I'd need it to fire on add group.

Hmmmm

Let me see if I can get it working on a dummy element outside the repeat group. If I can get that working, I'll tackle the load in the repeat group.

 

Sigh - okay.
I moved state and county out of the repeat group to simplify the problem (avoiding repeated groups) for debugging purposes.
So the fields are "assist___state" and "assist___county" now.

I added an onLoad java event to "assist___state" to call function loadCountyList(el) in the form_x.js (again - do assist debugging - I can break in the form_x.js and step)

I get the onLoad() and my function is called.
That function is simply

function loadCountyList(el) {
console.log("onload fired");
jQuery('#assist___state').val('6').trigger('change');
}

I'm hitting the function - I get the console.log and can set a breakpoint (in phpstorm). I can step through it seems to operate - jQuery calls, I see the value ('6') and so on.

BUT - nothing is loaded in "assist___county" - so I do not get the county list populated.

So close yet so far.

Seems to me i've seen something about a mock trigger?

 

Okay - digging through forum posts I stumbled on this and it works - at least for the non-repeat group test.

Added onLoad Java script to "assist___county"
javascript: loadCountyList(this);

That function is in form_x.js and is

function loadCountyList(el) {
var $usedID = $('assist___state');
$usedID.fireEvent('change');
}

This fires a change event on 'assist___state' which causes the assist___county dbjoin to run.

Seems to work.

Now I'll move it into the repeat group - and I'll post the result here.

UPDATE:
In the repeat group I had to:
1) Change the "where" clause in assist___county to reference "assist_9_repeat___state" of course.
2)
My loadCountyList() function became

Code ( (Unknown Language)):

 
function loadCountyList(el) {
    var repeat = el.getRepeatNum();
    var $usedID = $('assist_9_repeat___state_' + repeat);
    $usedID.fireEvent('change');
}
 

I'm a little nervous about the $usedID function - never used that before - but I can't figure out hot to use el.form.formElements... to get it.
I'm going to head over to the Wiki and document this under the dbjoin element.

 

Glad it works. I tested my example with calc element ajax update, but seems in your case it wasn't enought