Problem understanding the DatebaseJoin principles

[jj-pilot]

Hi guys,
I'm new here, and my experience is a mix of numerous Relational Databases, plus of course Joomla.

I'm playing with a CRM system I want to build in FABRIK, so I have a COMPANIES table and a CONTACTS table.

I want to have a Company list that allows me to see the Contacts working within the Company.

So far so good, but the problem is that everytime I create a new Contact, it adds a new Company to my list - what do I need to switch off/on here?

(I actually only have two companies - that just get repeated a lot as I add more Contacts)

The following image is from my present CRM, where I inside the Company card, has got the Contacts related to the Company

Kind regards,

John.

 

[Stevebenson223]

In your Companies list, Data -> Joins, have you tried changing the "Display mode" option to "Merge rows" or "Merge rows and reduce data"? As you're not displaying the contacts in your Companies list, that might work, but I've not tested it.

 

[jj-pilot]

@Steve: yes I did that and it worked. So far so good - next issue is to open the single CONTACT from within the list inside the COMPANY (instead of having to go out, and enter the CONTACTS list)

Are there any tutorials somewhere you could recommend? (find the logic a bit hard to get my head around

 

[Stevebenson223]

Hi, yes Fabrik is an amazingly powerful extension but needs some study. The Wiki is the best place to start. I would really recommend you read it through until it starts to fit with your existing knowledge. And if you've a specific problem, search the forum too. Everyone here started out on the same journey and has probably encountered the same or similar problems. And if not, ask!

As for your next issue, I'm sure it's possible to open your list of contacts for a company from the companies list, but I'm guessing you will want to emulate your existing CRM and have some sort of dashboard open for each company, as in your screenshot. That will require a bit more thought and planning, I think.

 

[Stevebenson223]

Just thinking of your next issue, in relation to an application I created...

You might want to consider using Fabrik Groups within your Companies list for your Contacts (and the other pages within your dashboard) instead of separate lists. Using Groups, Fabrik will automatically create new db tables joined to your main list, instead of you creating separate lists and manually joining them. And you should make them Repeat Groups where you want multiple rows, such as Contacts (but not necessarily FRONT PAGE or INFO, where you don't.

With Groups, and using the Tabs layout, you could then use a copy of your Companies form to emulate your dashboard, as long as you disable all the elements and hide any buttons.

This might not make much sense just now, but you could try setting up a new Group for your Contacts and test it out.

 

[jj-pilot]

I don't mind thinking and planning - as long as it's possible to make it work

I'll give it a try...

Thanks!

 

[troester]

I assume you already have added a join from your company list to your contacts list set to repeat (so you got this "merge rows" effect), but you didn't set any of the contacts elements to display in your company list?
To get a link directly to a contact set (at least) one of the contact elements (in your company list, in a group like Company-[contact]) to display in the list and in element's "List view settings" add a "Custom link" like
index.php?option=com_fabrik&view=form&formid=your-contact-formID&rowid={contact___id_raw}
(assuming 'contact___id' is your internalid element of your contact list).

An other possibility is to enable "Related Data" in your company list settings, which will give you a button in each company line to display the list with contacts belonging to this company.

Or to use the content plugin to display the contacts e.g. in the outro of the company list when clicking on a company (also by setting a custom link).

Etc.
There are several options, depending on what you want to get.
You may also have a look at the list div template to get a card-like styling.

 

[jj-pilot]

Hey All,
I really appreciate your effort, but it hasn't brought me any closer to an understanding (my bad of course) - so to save your precious time I've made a short video explaining:

• What I want to achieve
• Where in the process I am

(it's so hard to do in words)

Please see the video here:
Link to video on Google Drive

Hope you can spare a couple of minutes... 01:24 to be exact

kr. J-)

 

[troester]

I don't get what you want:

In the CONTACTS group of your company you can add, edit, delete the contact.
Why do you want additionally to go to the contact form? It's the same data you are working on.

Or do you want to show the contacts in the CONTACTS group as read-only?

Then you could use a databasejoin element (instead of first/last name, title), pointing to the contact's list and using "or Concat label" to show name & title and enable "Link to joined record"( and maybe "Add option in front end" if you want to add new contacts from the company form directly) in the "Add option in front end" tab.
https://fabrikar.com/forums/index.php?wiki/database-join-element/

If you want to have both (e.g. for editing short contact information directly in the company form and having a button to open the contact form with more informations) you can add e.g. a display element or a calc element with text like "Details" and a custom link to contact record.

 

[jj-pilot]

@troester: sorry that I failed so dramatically when making the video.
What I want to do, is the way to navigate in the way I demonstrate in the video, that I'm able to do in my present CRM system.

In all relational databases I have worked with from Paradox, via Access, FileMaker, Ninox and more, the ability to move freely between data has been standard (a cornerstone, actually).

The reason why I want to access the "Contacts" information directly from the "Company" has to do with workflow, and the fact that the "Contacts" table will be having way more fields than just [First_name], [Last_name] and [Title], and when having a Client on the phone the user don't have time to do a lot of navigation back and forth in a CRM system - it has to be click, click, click between relations - and then you have the record (e.g. an invoice) the client is calling about, in front of you.

And it is that tiny feature that I'm trying to figure out how to do.

And I am sure that it is possible and I'll explore your suggestions

 

[troester]

Ok.
There are several options to switch forth and back.

Company list display:

• You can set (at least) one of the contact elements to display in list view and set a "Custom link" to open the contact form or details view on click
• You can set a custom link to one of the company elements to display the list of related contacts e.g. in the list outro
• You can enable the company list's "Related Data" (assuming you have a dbjoin element as FK in your contact list) to open the list of related contacts e.g. in a popup

Company form (with repeating contacts group):
You'll need one readonly element with a link to the full contact from/details view.
This can be e.g. a display element.

To get a link directly to a contact set (at least) one of the contact elements (in your company list, in a group like Company-[contact]) to display in the list and in element's "List view settings" add a "Custom link" like
index.php?option=com_fabrik&view=form&formid=your-contact-formID&rowid={contact___id_raw}
(assuming 'contact___id' is your internalid element of your contact list).

To go back where you've come from enable "go back" button in form settings.