Fabrik with Civicrm

stevegjacobs

stevegjacobs

New Member
Hi
New here. I am developing a project using Joomla and Civicrm. It is at a fairly advanced stage, but there are some actions that we want our users to be able to do from the Joomla front end but that Civicrm doesn't seem to be able to do. We are thinking of using Fabrik to build forms and lists that will add and read information to one or two Civicrm tables. I am reading through documentation and it seems like it is possible, but I am wondering if anyone else has done this, and if so, can offer advice on how to start, so we don't spend too much time going down blind alleys

Specifically, our project is a database of 1600+ schools, imported into CiviCrm as organisations. Schools will have a principal who will log in from the Joomla front end and add students (registering some of his/her students as participants in a programme) as both Joomla users and Civi contacts. We need the principal to be able to also see the list of students participating in the programme, and view their profiles. The part that I am hoping to do with Fabrik is to first: establish the relationship between the student and school, and second: display the list with links to profiles. That's it really. Just starting on this today, any tips or advice welcome.

Thanks
Steve Jacobs
 
I'm interested in this as I may take a job for a client using CiviCRM, but at this point I don't know much about that software.

But I know Fabrik and one of its - many -great features is the ability to connect to any table in the database to add/edit/display/manipulate records. So you can perfectly create new Fabrik lists (and therefore forms) connected to CiviCRM tables. Just be sure, as every time you connect Fabrik to a db table that is used/created by another extension (including Joomla core tables), to set "Alter field types" to "No" (in the "Advanced" tab of the list settings) as soon when you create the list. That way you won't change the type of the columns in the table and you won't be able to add new elements to the table, both things that could alter the way these table were intended to work with their native extension. If you need new elements connected to this table, use the "join" feature of Fabrik.
 
Hi, first step done. Created first list that is reading data from two civicrm tables - the contact and the relationship table - a list of students. Goal here is ultimately to publish a list after the school principal logs in where principal only sees students of his school. I am trying to accomplish this using civcrm relationships - student to school or student to principal.

First I need to figure out how to limit a set of results -
'where civicrm_relationship.relationship_type_id = 5 AND civicrm_relationship.contact_id_a = [principle ID]'

Also, almost every time I create anything, I am getting data errors relating to foriegn keys, probably keys set by civicrm itself.

I am making progress though.
Steve
 
'where civicrm_relationship.relationship_type_id = 5 AND civicrm_relationship.contact_id_a = [principle ID]'
Click to expand...
This can be done with a list prefilter.
Is principleID = Joomla user ID? in this case you can use
civicrm_relationship.contact_id_a EQUALS {$my->id}
in the prefilter settings.
errors relating to foriegn keys
Click to expand...
It seems civiCRM is using ImmoDB tables + constraints. So maybe you must adapt your workflow, e.g. set validations in Fabrik forms etc.
 
Sorry I've not used CivicCRM so couldn't speculate on anything specific to editing its table's data with Fabrik. However, as jfquestiaux says if its simple add/edit/delete etc into the tables Fabrik is a good fit, where one has to be careful is if the CRM adds additional application logic when records are altered - for example if you added a lead does that then also add a message to the user assigned to the lead.
 
Thanks, kinda figured out prefilters before getting your message, but not sure I did it right yet. Only using civicrm tables so far- civicrm_contact and civicrm_relationships.

This is the error that I get when saving data - however usually when I go back and view the table in Fabrik I am seeing the results I expect:

Error message when saving database changes - when saving a filter:
Cannot change column 'id': used in a foreign key constraint 'FK_civicrm_contact_employer_id' of table 'pramerica_jos1.civicrm_contact' SQL=ALTER TABLE `civicrm_contact` CHANGE `id` `id` int(11) NOT NULL.

Haven't created any forms yet, and I think I only need one - to insert a record into the relationships table that creates a relationship between student and school.
 
Have you done this?
jfquestiaux
Just be sure, as every time you connect Fabrik to a db table that is used/created by another extension (including Joomla core tables), to set "Alter field types" to "No" (in the "Advanced" tab of the list settings) as soon when you create the list.
Click to expand...
 
found that and fixed it - tackling required form(s) now.

Work flow looks like this:
  1. Principal logs in and is able to add a Student as a Joomla user. We are using the AddUserFrontEnd Joomla component to make this happen. Email with login details is sent to student.
  2. Student logs in. The first successful log in creates a student contact and profile in Civicrm. However the student is not linked to his school. I am hoping to achieve this somehow using fabrik to insert a record in the civicrm_relationships table.
Maybe I am overcomplicating this. What I really want is for the principal to create a joomla users and civicrm contact AND link them to the school in one step
 
This should not be complicated with Fabrik, you don't need AddUserFrontEnd there's a Fabik juser form plugin doing this.

I don't know what is needed for civicrm but this is the principle in Fabrik
- a student profile table containing all fields necessary to create a Joomla user + additional ones (like school, address ...) (the civicrm profile?)
as an alternative you can join Joomla's #_users with the profile table via Fabrik (jfquestiaux has a nice tutorial somewhere how to do this)

- Fabrik list connected to the table(s)
- Fabrik form: add juser plugin and configure the elements
- school element: define a default evaluating the school depending on the logged in principal (or let the principal select the school)

Then the principal can add a student profile and create the student's Joomla user in one step.
 
Hi Troester,
In theory, this is a good idea, but I am having trouble because civicrm's data structures are too complex. And unfortunately my French is not up to following jfquesiaux's tutorial.

I am now thinking of having the student log in and add his school by inserting a school contact id into the civicrm_relationships table
 
Translating the tutorial. This is raising a question. jfquesiaux's tutorial says to install a bunch of extra plugins. Howevver I am using Joomla 3.2 and a number of them don't exist for J! 3.2. Do I try installing j! 2.5 plugins?
 
I am going around in circles a bit here, trying different things. I do have fabrik creating a Joomla user, sending password, etc. On first log in, the student creates a civicrm profile - basically a lengthy application form for the programme. My problem is still tying the student to their school - about 1000 schools imported as organisation contacts in Civicrm. I am trying something now - I've created a stand-alone fabrik list that includes the civicrm school id and the students Joomla ID. Trying to tie things together this way.
 
Ok, here is what I want to try to do with Fabrik:
Create a form that allows the student to select his school from a dropdown [populated by civicrm_contact table filtered to show only schools - 973 in database] -
Then once selected, student will save the form, and once done, a new record will be created in civicrm_relationship table that inserts school id into [civicrm_relationship.contact-a] and student id into [civicrm_relationship.contact-b] and a single number [7 in this case] into the civicrm_relationship.relationship_type_id.

The first task is to figure out how to do a dropdown fed by the list of schools.
I'm trying to read through instructions and tutorials, but any advice to fast track this would be appreciated.
 
Still a bit lost. I have created a list called 'Relationships' based on the table civicrm_relationships, and another list called 'Schools' based on the table 'civicrm_contact' and limited to civicrm_contact.contact_type . What I need is a form that shows up after the student logs in that does the following:
  • Inserts his civicrm_contact_id into field civicrm_relationship.contact_id_b (Student won't know this id so I want his contact id inserted seamelessly)
  • Insert the school id that student selects or enters into the field civicrm_relationship.contact_id_a - this could be done with a dropdown, but it's also possible simply to have the student insert his 'school number' - a 4 digit civicrm contact id that could be told to him by the principal of the school
  • Insert the integer '12' into field field civicrm_relationship.relationship_type_id (this represents the relationship student - to - school)

Form should create a new record, then not be editable after save.

This is the logic of what I need to do. I have some work left figuring out how to do this, especially since I am new to fabrik.

I think it can be done - thanks for all the help so far.
 
I've managed to get Civic CRM installed now so I should be able to give more accurate advice :)
Also if you could fill in the site details here http://fabrikar.com/you/my-sites/ (only you and the Fabrik staff can see the information) then we can take a look at your site and offer more advice.

  • Inserts his civicrm_contact_id into field civicrm_relationship.contact_id_b (Student won't know this id so I want his contact id inserted seamelessly)
Click to expand...
In the database, how does civic crm map the civic_crm_contact.id to the #__users.id? When I view the contact in civic crm, I see 'CiviCRM ID / User ' which shows the user id , but I can't figure out where in the database that relationship is described.
Insert the school id that student selects or enters into the field civicrm_relationship.contact_id_a
Click to expand...
This sounds like a job for a database join element, I'd probably need to look at your database via phpmyadmin though to work out what it would be, as my site doesn't have any test data - so Im not sure how the relationship table is used.
Insert the integer '12' into field field civicrm_relationship.relationship_type_id
Click to expand...
Set the field to be hidden with a default of 12, that should do the trick.
Form should create a new record, then not be editable after save.
Click to expand...
You can set the lists' edit access level so that the student can't edit it.
-Rob
 
Hi Rob,
All I can say is, it's complicated. I have a plugin installed that behaves like this:
  1. Someone creates a Joomla user
  2. User logs in and at that moment, the plugin searches the civicrm email table for a match. If it finds a matching email, it links that contact to the Joomla user. If not, it creates a new one, and seems to populate a few fields in the civicrm contact, name and email mostly. All this behaviour is fine, except that the mapping is via an email address in J!users to an email address in civicrm_email - and from there to civicrm_contact.id - so inserting the students civicrm.contact_id becomes very messy.
On point two, I really would be happy of the school would just tell the student what the school id is, and let it go at that.

On hidden field with 12 in it, that sounds easy. Of course my biggest problem is that I am jumping into fabrik and trying to do stuff without the benefit of understanding how it works - I haven't had time to rtfm. Under pressure of a deadline.

It probably doesn't hurt if the student can edit. Just so he can create one and only one record. The relationships table in civicrm allows you to create multiple relationships for one contact id. child to parent, employee to employer, etc as well as the one I've set up to use here, student to school.

I just completed the details on the my sites bit in Fabrikar.com

I have to go to a class now (nothing to do with computers, databases or IT, thank God :)
Be back in a few hours and I'll be staying up a bit late to see if I can get something working.

Thanks
Steve
 
Inserts his civicrm_contact_id into field civicrm_relationship.contact_id_b (Student won't know this id so I want his contact id inserted seamelessly)...... the mapping is via an email address in J!users to an email address in civicrm_email - and from there to civicrm_contact.id
Click to expand...
Aie aie aie :) - in this case perhaps you could use a calc element to get the civiccrm_contact.id from the civicrm_email table?

This would I think be the php code to put in the calc element's calculation field:
PHP: 
$db = JFactory::getDbo();
$user = JFactory::getUser();
$query = $db->getQuery();
$query->select('contact_id')->from('civicrm_email')->where('email = ' . $db->quote($user->get('email')));
$db->setQuery($query);
return $db->loadResult();
 
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: 128 (members: 1, guests: 127)
    Back
    Top