Fabrik Filter by related table

[sohopros]

We are developing a application for a Joomla website. Our customer is a manufacturer's representative that sells electronic components through Sales Representatives.

The company has three division and represents dozens of manufacturers within many product categories. Manufacturers are related with Product Categories and Product Categories with a company division.
Manufacturer's authorize the company to sell products in certain regions. And Regions are a collection of zipcodes.

Representatives are assigned to product categories. Once they represent a product category they can to sell products from any manufacturer that is assigned to the same product category. The representative area (not related with manufacturer's region) is defined by zipcodes.

For the representatives list we decided to map the Joomla contacts table inside Fabrik in order to keep the contact forms and to make the management easier for the customer.

We mapped six main entities (or Fabrik lists) on our schema.
- Company Division
- Product Category
- Manufacturer
- Region
- Representative
- Zipcode

The database model is shown below:

We were able to create this model using Fabrik. The only issue that we need to create a interface where users will be able to find a Representative by selecting a Product Category or a Manufacturer along with a zipcode. (Please, see attached image).

In order to make the "Find a Representative" feature work properly we have two questions:
1 - How can we filter representatives by Manufacturer, once the manufacturer table is not directly associated with the representative table? Please, see attached database model.
2 - How can we make the list page display the representatives data only after a selection was made in the filters?
3 - How can we force the zipcode filter to be required along with the manufacturer's filter OR the Product Category Filter?
(Zipcode Filter) AND ((Manufacturer Filter) OR (Product Category Filter) = Display Data
Thank you,
SOHO Prospecting Team

 

[cheesegrits]

Can you fill in your My Sites details, as I'll need to take a look at your site. I'll probably need to get you on Skype as well, so we can walk through the setup you have.

Question 1, I'd really have to get logged in and take a look at yoru site so far, with you on Skype, to get a better understanding. You've provided some good information, but it's tough to just look at a schema and come up with that kind of specific answer without getting "hands on".

Question 2 is easy enough, there's a "require filter" option on the list as a whole (so at least one filter must be specified before the list will display anything).

Question 3 not so easy. We allow making a given element filter "required", in the element settings, but there is no "and / or" logic built in, filtering on any element is either required or optional. I'm not even sure if that would be possible to do with any existing customization options, it owuld probably require some changes to our core code, to add a plugin hook where we currently do the "does the list require filtering?" check in our list model. That's something we'd have to charge an hourly rate on.

BTW, that kind of looks like an InnoDB schema ... are you using InnoDB or MyISAMM for your tables? Note that InnoDB can cause problems with fabrik, especially with related data if you have any kind of foreign key constraints, as InnoDB then does some of the same "plicing" of join constraints that we do, and ends up interfering with our handling of joins.

-- hugh

 

[sohopros]

Hi Hugh!
Would be awesome to meet you on Skype and find solution for our issues.
Where are you located? Which timezone? What is the sooner that you can be available to meet?

Thank you,
SOHO Prospecting Team

PS: Event though I had design the database model on Workbrench all the tables were created inside Fabrik

 

[cheesegrits]

I'm technically in CST, I live in Huntsville, Alabama (don't ask, I'm as ex-patriot Brit, ended up here about 23 years ago, never found my way out). However, I pretty much live on UHT (Universal Hugh Time), which is somewhat elastic. Sometimes i'm fully nocturnal, sometimes semi-diurnal. I have no real set schedule or core hours, so it's just a case of finding me online. I'm 'cheesegrits' on Skype, or hugh phdot messenger phat gmail phdot com (the 'ph' are silent).

When you send a contact request, make sure to include some reference to Fabrik. I get a lot of Skype spam these days, and pretty much ignore any requests that don't mention Fabrik.

-- hugh

 

[sohopros]

Gentlemen!
We are still struggling trying to fit our needs using fabrik. After a conversation with our customer this afternoon the requirements changed a bit and we have a new database model.

By looking at the database model placed above can be seen that representatives (joomla contact table) are now assigned to divisions and zipcodes. Divisions have Product Categories, Product Categories are related with manufacturers and finally manufacturers authorize company to sell theirs products in regions. The regions consist of a group of zipcodes.

I can fit the whole model inside Fabrik. The issue is that we need to have a search/filter function that would present representatives when the user enters a zipcode associated with either a manufacturer or a product category as shown below. The representative that appears must have the zipcode entered by the user associated with it and the manufacturer has to be included in a region that contains the same zipcode.

I have no clue how to create filters that are not direct related with the representatives entity. We need immediate help with this project.

Please, contact us with a solution or some documentation that shows us the path to be followed.

Thank you,
SOHO Prospecting Team

 

[cheesegrits]

Just to update this one ...

We had a Skype session, and devised a wayof using pre-filters to create a sub-query that builds the list of valid representative id's, using {placeholders} based on query string args being passed in from a search form.

As I mentioned at the end of our chat, you are probably going to want to copy your list, and apply that to a copy, just for using to fisplay the filtered results. Pre-filters are applied to all ways a list is accessed, like displaying / editing forms, etc. Which would be problematic in any scenario where those query string args aren't present.

Also, if you need to do things like view or edit rows form that filtered list, you would then probably need to use custom URL's for the view/edit, pointing to the form URL for the non-filtered version of the list.

Not ideal, but I just can't think of any other way of building that search you need, given the "no direct relationship" setup.

-- hugh