Database Join Elements and linking - advice?

[johnson76]

Hi,

A friend and I are working on a fabrik/joomla powered front end for a custom database app. Nothing especially complicated about it. It uses some PHP and MYSQL that we wrote ourselves, but does not interfere with Joomla/Fabrik in any way

We are having trouble getting an element join to work. What we want is to call up a list of users. That is easy enough. We then want to link the 'record_id' in the 'userdata' table with the 'record_id' in the 'consultation' table. The 'record_id' should show up the the table, and clicking on it should take you to all the consultations that have a value of 'record_id'

The problem I have here is that a column named 'record_id' is created in the table - but it is blank. If I want to edit any row in the table, one of the fields I get is a drop down list of 35,000 'record_id' - not what I wanted. I want the 'record_id' to be a static link from one table to another.

Here is an image to illustrate where things went wrong in the element settings:
http://bl.ul.ie/fabrik/problem.jpg

I can get what I want with an SQL query using a left join - all I want is it to give me the user list as normal, and join the 'record_id' in table A with the 'record_id' in table B, and link through to all entries in table B with that 'record_id'

It may be possible that we are not understanding what element joins are meant to do - if anyone can point us towards a general example we can use that would be a huge help. Or I can try adn clarify what it is we want from Fabrik.

If nothing else, we could really use a decent example of how to use element joins to link to other tables based on matching columns in differnet tables - thats essentiall teh root of our problem

We've been running it on MAMP, XAMPP, and it will live on a CentOS server in the long run. Long story short, there are no platform issues or server issues that I can identify.

We have no problem using Fabrik for data retrieval, creating forms, and basically doing anything we want with our database.


Any help would be most appreciated.

Thanks

 

[johnson76]

By the way - that image isn't an attempt at making the element join work - its a product of screwing around in frustration. but if you could tell us what we should have in there to make it work, that would be appreciated.

We're essentially looking for a tutorial on the workings of element joins and getting them to link up to other tables. Its lost us completely.

 

[cheesegrits]

Rob and I are trying to understand your requirements at the moment. I think it'll involve a table filter. Will get back to you soon. Sorry about the delay.

-- hugh

 

[rob]

I hope I've understood what you are trying to do here, so here goes and sorry if ive got the wrong end of the stick....

(this is presuming you are working form the latest version of the SVN, as parts of what I am describing have changed since the release of 1.0.4)
For my example I will have two tables; "countires" and "towns"

heres the structure for "counties"

id 	        int(11) 	pk	 	
label 	         varchar(255)

and here's the structure for "towns"

id 	        int(11) 	pk	 	
country_id 	    int(11) 	
label 	        varchar(255)

For the town's table, I want "country_id" to be a drop down of all the countries contained in the "countries" table. So we edit the "country_id" element and mak it into a database join element, with "countries" selected for drop down table, id" selected for "Table's Foreign Key Column" and "label" selected for the "Table's Foreign Value Column".

Having saved this element, we now go back to edit the "countries" table.
At the bottom of the data tab there is a section called "Tables with database join elements linking to this table".
As we have created a database join element in "towns" that points to the "countries" table there should be one row listed in this section:

Table 	Link to table 	Label 	Link to form 	Label
towns 	No/Yes 		view towns No/yes 	   view form

selecting "yes" for the "link to table" option will create a new column in your table view (with the heading specified in the adjacent "label" field)
Each of rows in this column will contain a link which will direct you towards the "towns" table, but only show those towns within the country's row you came from

Likewise selecting "yes" for the "link to form" option will create a link (again with the heading specified in the adjacent "label" field) that will point you to the "towns" form, with the "country_id" drop down value selected as the country name the link was in.

i hope that is what you were asking, if not please feel to explain your issue further

Cheers
Rob

 

[johnson76]

Thanks Rob
I will read through it and get back to you once we have some more time.

Any idea of when you are going to release a stable 2.0?
Thanks
Kevin

 

[rob]

nope - when its done
hopefully a first beta in 3 or 4 weeks time, then probably a couple of release candidates as there are a lot of new features and substantial changes in the codebase which will require thorough testing

Cheers
Rob