How do I link from one table to another, filtering on a value?

[rob]

Subtitle ... Whats the "Tables with database join elements linking to this table" all about?

This basically allows you to jump from one table to another table, filtering the data in the second table based on the row selected in the first table.

(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"
The following tutorial will explain how, for each country record, we can create a link that will point to the towns table only showing the country's towns.

heres the table structure for "counties"

id 	        int(11) 	pk	 	
label 	         varchar(255)

and here's table 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.



Cheers
Rob

 

[celinahex]

it would be great to see a page of this in action. Can you please make one and link to this answer?
Also, a tutorial for registered users on how to really do this (with screen shots) or populate fields based on selections, and the other advanced techniques that folks have been asking about again and again on these boards would be awesome. I love the tutorials here and is the reason that I subscribed, but aside from the same tutorial that's available for free on your old mosforms page, I did not learn terribly much.

At least, some tutorials for these frequently answered questions would be amazing.

 

[cheesegrits]

Unless we can round up some volunteers to do this, right now we simply don't have the time. Rob is totally immersed in getting Fabrik 2.0 finished and working out a lot of Joomla 1.5 transition issues, and I'm swamped trying to keep up with forum support and bug fixing Fabrik 1.4. I do this on a voluntary basis, so I can only devote so much time to it.

We do understand that subscribed users expect more by way of tutorials, and once 2.0 is out we have a long list of things we intend to do to improve the 'extras and goodies' available to you. But for now, the two main membership perks we can offer are the detailed Fabrik documentation and prioritizing your support requests in the forums.

-- hugh

 

[rob]

as Hugh says I really want to focus on getting a beta version of 2.0 out, I've made a note of your request for the tutorial, and will schedule that in after 2.0b is realeased

Cheers
Rob

 

[neomarck]

Hi Rob, thank you for your great help, i need to do exactly what you explained and i did everything you said, but only this happens:

1. when i only selected "link to table" and save, nothing happens, there isn't any new column in the table view.

2. when i only selected "link to form" and save, there are two new columns one for view tables, but there isn't any link, and the other one for view the forms, but i only selected "link to form"

3.when i selected both, "link to table" AND "link to form" and save, there are two new columns one for view tables and the other one for view the forms, and only in this case, everything is allright.

i have the lastes version of fabrik, 1.0.4, so i dont know what is the problem with the link to table, so i need to use only that link, i don need to link to the form, only to the table,please help me if you can.

other question when i save i have a popup that says:

"A script on this page is causing Internet Explorer to run slowly. If you continues to run, your computer may become unresponsive.

Do you want to abort the script?

Yes No
"
i always click on NO, is this a bug?

ok, thankyou for listen me and i hope you can help me as soon as you can.

PD: i don't speak english so good, i´m from colombia, so sorry if you can´t understand me so good.

 

[rob]

Hi

You will need to update to the latest version from the SVN.
details of how to do so are in my sig

Cheers
Rob

 

[neomarck]

ok rob, thanks for your help, the only problem is to learn to use the SVN and all the files that came with the download, but i dont know wich one to download, 1.0.x or 2.0.x and how to use them to work with fabrik.

thanks

 

[neomarck]

Sorry, i can install everything and now all is working good, the only thing is the popup still show the message about the script.

Thanx

 

[rob]

glad you got it working, i think i might have fixed that script error yesterday - again if I have an update from the SVN will soon let you find out

Cheers
Rob

 

[neomarck]

Hi, i updated today the SVN files, but the popup dialog is still showing, and i have other problem, when i use the tables counties and towns to test the join link, everything's allright, but when i use my own tables, its show me the view table join "empty" but the add column is well, here is the code in mysql of the tables i use, maybe i'm doing something wrong:

-- phpMyAdmin SQL Dump
-- version 2.10.2
-- http://www.phpmyadmin.net
--
-- Host: localhost
-- Generation Time: Feb 12, 2008 at 02:28 PM
-- Server version: 5.0.45
-- PHP Version: 5.2.3

SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";

--
-- Database: `semet`
--

-- --------------------------------------------------------

--
-- Table structure for table `juntas`
--

CREATE TABLE `juntas` (
`IdJunta` int(2) NOT NULL,
`NombreJunta` varchar(30) NOT NULL,
`Personeria` varchar(50) default NULL,
`DirJunta` varchar(30) default NULL,
`TelJunta` varchar(30) default NULL,
PRIMARY KEY (`IdJunta`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Dumping data for table `juntas`
--

INSERT INTO `juntas` VALUES (1, 'ALBORADA VAINILLA', '360 del 7 de Octubre de 2000', 'Barrio el Nogal', '315 361 0416 - 662 2049');
INSERT INTO `juntas` VALUES (2, 'MARCO ANTONIO PINILLA', '', 'SECCIONAL', '6705941');
INSERT INTO `juntas` VALUES (3, 'OLIMPICO POPULAR', '', 'SECCIONAL', '6705941');

-- --------------------------------------------------------

--
-- Table structure for table `voluntarios`
--

CREATE TABLE `voluntarios` (
`IdVol` varchar(10) NOT NULL,
`FechaIngreso` date NOT NULL,
`IdJunta` int(2) NOT NULL,
`NombreVol` varchar(50) NOT NULL,
`ApellidosVol` varchar(50) NOT NULL,
`Doc` varchar(12) NOT NULL,
`TipoDoc` varchar(2) NOT NULL,
`DocDe` varchar(30) default NULL,
`FechaNacimiento` date NOT NULL,
`LugarNacimiento` varchar(30) default NULL,
`EstadoCivil` varchar(15) default NULL,
`DirResidencia` varchar(50) default NULL,
`CiudadResidencia` varchar(50) default NULL,
`Tel` varchar(20) default NULL,
`Cel` varchar(12) default NULL,
`RH` varchar(3) NOT NULL,
`Profesion` varchar(50) default NULL,
`Empresa` varchar(30) default NULL,
`DirEmpresa` varchar(30) default NULL,
`TelEmpresa` varchar(20) default NULL,
`EPS` varchar(20) default NULL,
`NumEPS` varchar(20) default NULL,
`VigenciaEPS` tinyint(1) default NULL,
`NombrePadre` varchar(50) default NULL,
`NombreMadre` varchar(50) default NULL,
`NombreConyugue` varchar(50) default NULL,
`NumHijos` int(2) default NULL,
`ProcesoPPActual` tinyint(1) default NULL,
`ProcesoPPPasado` tinyint(1) default NULL,
`NomRef1` varchar(50) default NULL,
`OcupacionRef1` varchar(30) default NULL,
`TelRef1` varchar(30) default NULL,
`NomRef2` varchar(50) default NULL,
`OcupacionRef2` varchar(30) default NULL,
`TelRef2` varchar(30) default NULL,
PRIMARY KEY (`IdVol`),
KEY `IdJunta` (`IdJunta`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Dumping data for table `voluntarios`
--

INSERT INTO `voluntarios` VALUES ('ME-125', '0000-00-00', 3, 'CARLOS MAURICIO', 'JIMENEZ RIVAS', '17348651', 'CC', 'Villavicencio', '0000-00-00', 'Cartago, Valle', 'SOLTERO', 'CRA 33A # 36-13B', 'VILLAVICENCIO', '6667365', '', 'AB+', 'OPERATIVO', 'SECURENET', 'ED. COMIT', '6706452', 'SISBEN', '', 0, 'JHON JAIRO JIMENEZ', 'LUZ EDILMA RIVAS', '', 0, 0, 0, 'CARLOS JULIO MORENO', 'CONDUCTOR', '0', '', '', '');
INSERT INTO `voluntarios` VALUES ('ME-137', '0000-00-00', 3, 'NILSON REY', 'LOPEZ CAICEDO', '97611586', 'CC', 'SAN JOSE, GUAVIARE', '0000-00-00', 'SAN JOSE, GUAVIARE', 'UNION LIBRE', 'MANZ. I CASA 12, VILLAS DE AGUSTIN', 'VILLAVICENCIO', '0', '', 'O+', 'VARIOS', '', '', '0', '', '940400537', 0, 'CARLOS EDUARDO LOPEZ', 'OLIVA CAICEDO', 'SANDRA MILENA GUTIERREZ', 1, 0, 0, '', '', '', '', '', '');
INSERT INTO `voluntarios` VALUES ('ME-38', '0000-00-00', 2, 'NELSON', 'LE', '17341006', 'CC', 'VILLAVICENCIO', '0000-00-00', 'VILLAVICENCIO', 'CASADO', 'CRA 27 # 4A-92, B. ALBORADA', 'VILLAVICENCIO', '6682043', '', 'O+', 'T', 'ESC. AUTOMOVILISMO DON PRUDENC', 'CL 35 # 37-35, B. BARZAL', '6629023', 'SALUDCOOP', '2827605', 0, 'JOSE MIGUEL LEON', 'GLADYS MARINA VEL', 'RUBIELA ROLD', 6, 0, 0, 'ALFREDO ROJAS', 'COMERCIANTE', '6630354', 'CARMEN JULIA LEON', 'COMERCIANTE', '6653364');
INSERT INTO `voluntarios` VALUES ('ME-416', '0000-00-00', 3, 'ORLANDO YESID', 'ROJAS MOLANO', '17328733', 'CC', 'VILLAVICENCIO', '0000-00-00', 'VILLAVICENCIO', 'SOLTERO', 'CRA. 37 # 26-18 PISO 2, B. 7 DE AGOSTO', 'VILLAVICENCIO', '6633201', '', 'B+', 'PSIC', 'INSTITUTO T', 'BARRIO NOGAL', '0', '', '', 0, 'JOS', 'ISABEL MOLANO DE ROJAS', '', 0, 0, 0, 'ELIDA SONIA VARGAS S.', 'PSIC', '6683597', 'ROSANA GALEANO C', 'DOCENTE', '6607695');
INSERT INTO `voluntarios` VALUES ('ME-473', '0000-00-00', 2, 'ANA ROSA', 'CAMARGO MARTINEZ', '40397793', 'CC', 'VILLAVICENCIO', '0000-00-00', 'SAN VICENTE DEL CHUCURI, SANTA', 'CASADA', 'MZ T # 7 VILLA MELIDA', 'VILLAVICENCIO', '6601206', '', 'A+', 'TECNICO EN CRIMINALISTICA', '', '', '0', 'SISBEN', '', 0, 'JOSE GREGORIO CAMARGO', 'MARIA I. MARTINEZ', 'BENJAMIN ESTEPA REYES', 3, 0, 0, 'LUCILA ORTIZ PARDO', 'OPERADORA', '6600205', 'MARITZA DIAZ', 'BACTERIOLOGA MEDICINA LEGAL', '0');
INSERT INTO `voluntarios` VALUES ('ME-486', '0000-00-00', 1, 'HERNAN DARIO', 'SARAY MEZA', '86077380', 'CC', 'VILLAVICENCIO', '0000-00-00', 'VILLAVICENCIO', 'SOLTERO', 'CL 9 # 31-16, VILLA CLAUDIA', 'VILLAVICENCIO', '6725250', '315 361 0416', 'A+', 'TECNICO INGENIERIA INDUSTRIAL', '', '', '0', '', '', 0, 'JOSE RAMIRO SARAY ROJAS', 'MERCEDES MEZA GUZMAN', '', 0, 0, 0, 'ALEJANDRA ORTIZ', 'ADMINISTRADORA', '3103031914', 'MIGUEL MONTENEGRO', 'EMPLEADO', '3152965105');
INSERT INTO `voluntarios` VALUES ('ME-487', '0000-00-00', 1, 'ERNEL FRANCISCO', 'BENAVIDES RINTA', '86078820', 'CC', 'VILLAVICENCIO', '0000-00-00', 'BOGOTA', 'SOLTERO', 'CRA 5B # 32-68', 'VILLAVICENCIO', '6721500', '311 808 5135', 'O+', 'ESTUDIANTE', 'UNILLANOS', 'KM 12 VIA PTO LOPEZ', '', 'SERVIMEDICOS', '35491562', 0, 'ERNEL MEDARDO BENAVIDES', 'ANA BEATRIZ RINTA', '', 0, 0, 0, 'FERNANDO ROJAS', 'LIC. EDUCACION FISICA', '315 615 96 01', 'JAGLA MAFRAD GALVIZ', 'ESTUDIANTE', '310 308 28 61');
INSERT INTO `voluntarios` VALUES ('ME-492', '0000-00-00', 1, 'MARCO ANTONIO', 'SILVA SALAZAR', '86070107', 'CC', 'Villavicencio', '0000-00-00', 'Villavicencio', 'SOLTERO', 'CALLE 37 27 101', 'VILLAVICENCIO', '6622049', '310 618 5552', 'A+', 'ING. SISTEMAS', 'LA VACAY EL TORO', 'CALL CENTRO', '7777777', 'COLSANITAS', '134654863', 0, 'RAUL SILVA', 'STELLA SALAZAR', '', 0, 0, 0, 'RAUL E. SILVA L.', 'COMERCIANTE', '6666666', 'JUAN G. RODRIGUEZ', 'ING. DE SISTEMAS', '6633376');
INSERT INTO `voluntarios` VALUES ('ME-56', '0000-00-00', 2, 'LUIS ALBERTO', 'RINCON', '17312510', 'CC', 'VILLAVICENCIO', '0000-00-00', 'TAURAMENA, CASANARE', 'SEPARADO', 'MZ L CASA 1, VILLA ORTIZ', 'VILLAVICENCIO', '6710572', '', 'O+', 'TECNICO EN RADIO Y TV', 'INDEPENDIENTE', '', '0', 'SISBEN', '', 0, 'PABLO GUERRERO', 'CARMEN RINCON', '', 2, 0, 0, 'CLARA RODRIGUEZ GUERRERO', 'ARQUITECTA', '6667393', 'ORLANDO VARGAS JIMENEZ', 'GANADERO', '6664219');

--
-- Constraints for dumped tables
--

--
-- Constraints for table `voluntarios`
--
ALTER TABLE `voluntarios`
ADD CONSTRAINT `voluntarios_ibfk_1` FOREIGN KEY (`IdJunta`) REFERENCES `juntas` (`IdJunta`) ON DELETE CASCADE ON UPDATE CASCADE;



i hope you can help me, because yesterday all was working perfect.


thanx

 

[neomarck]

Sorry, i forgot to show you the images.

and this is the popup

 

[squeaky]

ahh, the same link not appearing problem I'm having :-\

 

[neomarck]

actually, i changed the fields names, because i was using Caps in the names, so now, there is no problem with the join link, i think there is a problem when you use Caps in the fields names.

 

[rob]

yes for historical reasons fabrik expects all fields to be lower case