Seeing Joined Data on a Form

M

markbhai1

New Member
Hi.

I am now moving into the next phase of development on my Fabrik app.

I am designing a task scheduling form, which will allow users to set a task, with details and dates to do etc. - I also have a database of properties for sale and would like to be able to have a choice of linking a property with a task. I would like to see in the task form property address and price etc.

I have the two tables set up - 'tasks' and 'properties' and I have created an element 'property_id' in the task group which has a database join to the property table linking to the foriegn key od 'properties___property_id' and the foriegn value of 'properties___property address'. I have also created an 'inner join' from the task table 'property_id field to the properties table property id field.

When I view the form (default template) - i see that the initial dropdown join works as expected as I can see my properties in the dropdown list. I also see that the table join has worked as I can now see very field from the properties table. However when I select a propoperty I would like the property data to pre-populate but is doesn't.

Any ideas what I am doing wrong?

PS the primary key on tasks is 'task_id' and the primary key on properties is 'property id' but if I save a blank form I get the following...

Unknown column \'date_listed\' in \'field list\' SQL=INSERT INTO tasks ( `task_id`,`opened_date`,`detail`,`required_date`,`closed_date`,`priority_id`,`creator_id`,`owner_id`,`email_update`,`propstatus_id`,`property_id`,`property_address`,`hf_ref`,`notes`,`email_notes`,`tasktype_id`,`taskprogress_id`,`date_listed`,`date_lastpricerev`,`date_nextpricerev`,`date_lastad`,`date_lastcall`,`date_nextcall`,`date_exchange`,`date_aml`,`date_pma` ) VALUES ( \'\',\'0000-00-00 00:00:00\',\'test\',\'0000-00-00 00:00:00\',\'0000-00-00 00:00:00\',\'\',\'\',\'\',\'\',\'\',\'\',\'\',\'\',\'\',\'\',\'\',\'\',\'0000-00-00 00:00:00\',\'0000-00-00 00:00:00\',\'0000-00-00 00:00:00\',\'0000-00-00 00:00:00\',\'0000-00-00 00:00:00\',\'0000-00-00 00:00:00\',\'0000-00-00 00:00:00\',\'0000-00-00 00:00:00\',\'0000-00-00 00:00:00\' )Please ensure you have selected a primary key for your table, Fabrik can not process this join until you have done so

Cheers

Mark.
 
Hi Hugh

I posted the link via PM, hope you can help as I keep looking at it and can't see what I have done wrong.

Cheers

Mark.
 
Thanks Hugh

I have checked those threads and I am not sure they will do what I need, I am part way through checking it out but could not see how it would work.

When entering a 'task' I want to select a property and see only a few of the property details and these will be read only. I am thinking instead that I could duplicate these 'property' elements in the 'task' table (I know its not very relational) but is there a way in which I can get the fabrik 'tasks' form to populate these fields from the properties table? Such as some code entered in the 'Default Value' based on the selected 'tasks___property_id'?

Ideally, this would be dynamic i.e. if the property_id changes so do the other elements, but I would be happy if I have to do a form refresh to achieve this effect.

Thanks

Mark.
 
Can someone confirm if this would work and give me a steer on some Java (if possible)?

I have created some additional elements on my 'task' table to replicate the elements I want to see from my 'properties' table, lets say, 'price' and 'status' - I also have 'task___property_id which I use a simple join to the properties table with the key set to 'property_id' and the value set to 'address'.

I am thinking that I could create some php to run on form load using a SELECT query to select the current values of price and status from the properties table set them as variables and then use this to over write the values stored in 'task__price' and 'task__status' - Is this feasible?

Second question - Is there a Java command I can run 'OnChange' which will force the form to reload? - I want to do this so that if I change the 'property_id' field it forces the form to reload thereby grabbing the new values again?

Thanks

Mark.
 
The best way would probably be some JS that does an AJAX call to populate those fields each time the form is displayed or the property_id is changed.

See the user_ajax_example.php file for some pointers on how to achieve this. You would also need a form JS file to load the data on page load, which called the same function as the onchange event, using a Window.addEvent(domready, function(){...}) call.

Alternatively, this might be something that could be addressed with an underlying MySQL 'view', but I'd have to do a lot of digging to work out if that's possible. Something I've been meaning to do for a while, but as usual I'm slam dunked busy, so definitely not something you'd want to hold your breath for.

Have a look at the AJAX examples, and give it a go. As usual, I'll help out with pointers as and when you need 'em.

Alternatively, for a reasonable donation to the "Paying Hugh's Rent" fund, I'll write it for ya. Something in the region of $50 would get it done. If interested, post in the Commercial Services (Fabrik Projects) forum.

-- hugh
 
Cheers Hugh.

I would like to have a go at this myself first, for the sake of learning how to do stuff, but if I fail the donation route is a welcome alternative.

I will look into the AJAX stuff first.

Mark
 
user_ajax attempt

Hi Hugh.

I have hacked this together but it is not returning any values...any help?

To remind you ..... with this code I am attempting to populate a field called hf_ref from my 'properties' table and populate an element called hf_ref on the 'tasks' table.

here is the JS attached to my task___property_id element....

var pid = this.getValue();

//I need 'SELECT hf_ref FROM Properties WHERE property_id = $pid'

var remotetable = 'properties';
var lookupfield = 'hf_ref';
var remotekey = 'property_id';

// this is the html id of the element we want to update on this task form
var datafield = 'tasks___hf_ref';

var url = 'index2.php?option=com_fabrik&no_html=1&task=userPluginAjax&method=getFields';

url += '&lookupfield='+lookupfield;
url += '&table=' +remotetable;
url += '&key='+remotekey;
url += '&val='+pid;

new Ajax(url, {
method: 'get',
update: $(datafield)
}

).request();

Now I don't what to expect when I change the property_id field on tasks but I don't see the URL change so I suspect I may have something wrong here.

Just in case this is OK here is my user_ajax.php file...

<?php

defined( '_VALID_MOS' ) or die( 'Direct Access to this location is not allowed.' );
class userAjax {
function getFields(){
global $database;
//get all the variables passed in by the ajax objects url
$lookupfield = mosGetParam($_REQUEST, 'lookupfield');
$table = mosGetParam($_REQUEST, 'table');
$key = mosGetParam($_REQUEST, 'key');
$val = mosGetParam($_REQUEST, 'val');

//run a query on the database to get the matching fields
$sql = "SELECT $lookupfield AS value FROM $table WHERE $Key = '$val'";
$database->setQuery($sql);
$result = $database->loadObjectList();

//write the results of the query back to the browser - the javascript code will then assign
//the text to the second drop down
echo $result;
}
}
?>

Hope you can see where I am going wrong?

Thanks

Mark.
 
The first thing that springs out at me is:

PHP: 
$result = $database->loadObjectList();
        
        //write the results of the query back to the browser - the javascript code will then assign
        //the text to the second drop down
        echo $result;

The loadObjectList() does what it says, and gives you a database object (PHP data structure) which has a list of the selected row data in it. You are then just returning that object list directly. Which won't make any sense to the JavaScript at the other end, 'cos it doesn't understand PHP database objects.

As your query is only going to return a single column from a single row, you need to use $database->loadResult() instead, which instead of an object list, returns the first field of the first row returned by the query, as a simple string, which JavaScript will then understand.

I also noticed a $Key in there instead of $key. PHP variable names are case sensitive, so $Key is NOT the same thing as $key.

So ... fix those things. Then run it with FireFox, with FireBug installed. If it still doesn't work, look at the Response text from the AJAX call in the FireBug Net tab. That will show you what is coming back from the AJAX call, which will often give you a clue as to the problem.

-- hugh
 
Getting There

Hugh,

I seem to be getting closer.

I can see from firebug that the ajax code is giving the correct response i.e. it is returning the value but this is not being populated into the 'tasks___hf_ref' field. I have tried changing the javascript to referencing the field directly...update: $('tasks___hf_ref')

var pid = this.getValue();
var remotetable = 'properties';
var lookupfield = 'hf_ref';
var remotekey = 'property_id';

var datafield = 'hf_ref';

var url = 'index2.php?option=com_fabrik&no_html=1&task=userPluginAjax&method=getFields';

url += '&lookupfield='+lookupfield;
url += '&table=' +remotetable;
url += '&key='+remotekey;
url += '&val='+pid;

new Ajax(url, {
method: 'get',
update: $('tasks___hf_ref')
}



).request();

any more pionters?

Cheers

Mark
 
I am now 99.9% sure I have something wrong with where I an trying to place the response after using firebug to investigate the returned code.

Here is a copy of the HTML for the element AFTER the AJAX is run...

<div id="fb_element_38" class="fb_element">
<div id="fb_el_tasks___hf_ref_text" class="fabrikLabel">
<label for="tasks___hf_ref">Homefinder Ref</label>
</div>
<input id="tasks___hf_ref" class="inputbox text" type="text" value="" maxlength="15" size="15" name="tasks___hf_ref">
<input/>
HF783758020
</input>
</div>

The HF783758020 bit is the response from the server, but for some reason it is not getting to the field value.

Is the Update: command correct or should I try a OnComplete: as well/instead?

Thanks

Mark.
 
Hugh.

After all of those shinanigans with the forms not saving I have now re-built the database and have come back to this chestnut!

The issue remains after the rebuild.

As previously posted I have created my Java and attached to the OnChange portion of the first element. - According to firebug, this fires off the AJAX and returns the correct result, but it does not populate the required fields value.

Here is the returned data from firebug, you can see that the data gets to the element but it does not display.

<div id="fb_element_55" class="fb_element">
<div id="fb_el_jos_fabrik_formdata_8___hf_ref_text" class="fabrikLabel">
<label for="jos_fabrik_formdata_8___hf_ref">Homefinder Ref</label>
</div>
<input id="jos_fabrik_formdata_8___hf_ref" class="inputbox text" type="text" value="" maxlength="255" size="15" name="jos_fabrik_formdata_8___hf_ref">
<input/>
HF783758020
</input>
</div>
 
OK, fixed.

The problem was that when you use the 'update' option with the Ajax call, you are specifying an DOM element within which you want to put the returned content. So you would typically specify a DIV on your page, and insert some formatted text into it. So for instance

What you are actually trying to do is set the VALUE of a DOM element, which is an entirely different beast.

I replaced the 'update' with ...

onComplete: function(response) { $(datafield).value = response; }

... which does what you need. Should probably use onSuccess rather than onComplete, in case the Ajax call fails entirely.

-- hugh
 
You must log in or register to reply here.

Useful links

  • Create a Fabrik Account (Downloads)
  • Create a Forum Account
  • Fabrik Wiki
  • Fabrik 4 Changelog /Install /Upgrade
    • We are in need of some funding.
    More details.

    Thank you.

    Members online

    Total: 147 (members: 1, guests: 146)
    Back
    Top