How to create a table for companies with multiple user

[nusilmar]

It's hard for me to explain but will try :

Need to create an app with at least 2 tables ( lists).

One table to enter companies ( need to assign several users to each company )
Other table to enter support requests by company

Users of a certain company should see all the requests made by that company
special group should see all requests

Any help on how should i do this ??

 

[nusilmar]

This is what i have done so far :

I created a list of all companies (with the only enterprise data: name, address, location, etc ...)

I created a second table with the users of each company, with two fields (besides the id and date): Company (done with a database join to the list of companies) and user (with the user plugin).

My problem is how can I use this information when creating new lists with different information and filter by logged in user so that each user only see the data that relate to the company to which they are indexed.
for example: create a list of services provided in each company, with fields: Customer; Date; Type of Service; Spent time; Description ..., this list being completed by site administrators (me and my colleagues) and can be consulted by my clients, but so that each customer only see data related to the company they belong to. As some companies may have multiple users logged on the website, I need this explanation of how I can put it to work ...

If i choose the simplest solution just need to attach a user to the company but that way i'm putting a limit access to only one user per company ...

 

[troester]

Add a list prefilter, eg
Where
field: company
condition: EQUALS
value: SELECT company FROM your-user-company-table WHERE {$my->id}=user
Type: Query
Apply to: what you need

See also http://fabrikar.com/forums/index.php?wiki/pre-filters/

 

[nusilmar]

it worked !!! thanks troester !!!

didn't know exactly where to define this ... i was trying to insert an hidden element on a list but haven't tought on prefilters. Once again thank you ...

now what i have is the following ( for those of you that would like for something similar :

A list with companies, a list with users per company ( using user plugin and database join to companies), a list with services made in a company ( with a database join to companies ) with one line per service and per company.

All data is to be filled by site admins, users only have frontoffice access with view previleges.

Created a menu item to Fabrik List pointing to Service, then applied a prefilter with the conditions troester gave ... AND IT WORKED !!!! , eheheheh

 

[nusilmar]

then again it didn't work,

I get the following error if a user as access to more than one company :

500 - Fabrik has generated an incorrect query for the list : <br /><br /><pre>Subquery returns more than 1 row SQL=SELECT SQL_CALC_FOUND_ROWS DISTINCT `fab_empresas`.`id` AS `fab_empresas___id`, `fab_empresas`.`id` AS `fab_empresas___id_raw`, `fab_empresas`.`date_time` AS `fab_empresas___date_time`, `fab_empresas`.`date_time` AS `fab_empresas___date_time_raw`, `fab_empresas`.`cod_mindafil` AS `fab_empresas___cod_mindafil`, `fab_empresas`.`cod_mindafil` AS `fab_empresas___cod_mindafil_raw`, `fab_empresas`.`empresa` AS `fab_empresas___empresa`, `fab_empresas`.`empresa` AS `fab_empresas___empresa_raw`, `fab_empresas`.`morada` AS `fab_empresas___morada`, `fab_empresas`.`morada` AS `fab_empresas___morada_raw`, `fab_empresas`.`localidade` AS `fab_empresas___localidade`, `fab_empresas`.`localidade` AS `fab_empresas___localidade_raw`, `fab_empresas`.`cod_postal` AS `fab_empresas___cod_postal`, `fab_empresas`.`cod_postal` AS `fab_empresas___cod_postal_raw`, `fab_empresas`.`email` AS `fab_empresas___email`, `fab_empresas`.`email` AS `fab_empresas___email_raw`, `fab_empresas`.`telefone` AS `fab_empresas___telefone`, `fab_empresas`.`telefone` AS `fab_empresas___telefone_raw`, `fab_useremp`.`id` AS `fab_useremp___id`, `fab_useremp`.`id` AS `fab_useremp___id_raw`, `fab_useremp`.`date_time` AS `fab_useremp___date_time`, `fab_useremp`.`date_time` AS `fab_useremp___date_time_raw`, `fab_useremp`.`empresa` AS `fab_useremp___empresa_raw`, `fab_empresas_0`.`empresa` AS `fab_useremp___empresa`, `fab_useremp`.`empuser` AS `fab_useremp___empuser_raw`, `j25_users`.`name` AS `fab_useremp___empuser`, `fab_empresas`.`id` AS slug , `fab_empresas`.`id` AS `__pk_val` FROM `fab_empresas` INNER JOIN `fab_useremp` AS `fab_useremp` ON `fab_useremp`.`empresa` = `fab_empresas`.`id` LEFT JOIN `fab_empresas` AS `fab_empresas_0` ON `fab_empresas_0`.`id` = `fab_useremp`.`empresa` LEFT JOIN `j25_users` AS `j25_users` ON `j25_users`.`id` = `fab_useremp`.`empuser` WHERE ( fab_empresas.id = (SELECT empresa FROM fab_useremp WHERE empuser=62) ) ORDER BY `fab_empresas`.`empresa` ASC LIMIT 0, 25</pre>​

​

For help , and to easy understand of my language :

empresas : companys

fab_empresas : my company list
Fab_useremp : my company/user list

​

 

[troester]

If a user can belong to multiple companies you have to use condition=IN.

 

[nusilmar]

And once again troester to the rescue ( thank god ... )