Database tables problems

[franckt]

I'm using Sugar CE 5.2.0k (Build 5837).

I have developed my own module and deployed it.
I have created also other database tables linked to my module main table.
For example, considering that my module records data about houses to rent, I have :
* a table called "season" whih lists all the seasons with an unique id and the name (ie : id=1, name=summer / id=2, name=winter etc..).
* a table called house_season with 2 fields (id which is the unique id of an house and idseason which relates to the table "season").

My 2 problems are :

1) If I add the possibility to search house by season, I know how to build the dropdown menu on the search tab but I don't know where I can filter the results if a season is choosen.

2) When I create (or modify) a new record for an house, how can I display (with checkbox) in a panel all the different seasons ?

Let me know if I wasn't clear enough.

Sincerely
Franck

[datasponge]

1) If I add the possibility to search house by season, I know how to build the dropdown menu on the search tab but I don't know where I can filter the results if a season is choosen.

2) When I create (or modify) a new record for an house, how can I display (with checkbox) in a panel all the different seasons ?

If I follow you correctly, you are putting a selection box that lists the seasons on your search tab. So for (1) all you have to do is select the season (or seasons using Ctrl) and only the houses matching that season will be shown. So if you have the seasons input box on the search tab, this should already be working.

For (2), simply add the seasons field to your Edit view in Studio. If you implemented it as a dropdown, then it will be a dropdown on edit view, not a checkbox.

I'm not sure if that's what you were asking. If not, please clarify.

Phil

[franckt]

First of all, thanks for answering.
But your proposal doesn't fit my needs.
I'm trying again to explain.

I have a main module called module_house, with this mysql definition :
TABLE `module_house` (
`id` char(36) NOT NULL,
`name` varchar(255) NOT NULL,
`date_entered` datetime DEFAULT NULL,
`date_modified` datetime DEFAULT NULL,
`modified_user_id` char(36) DEFAULT NULL,
`created_by` char(36) DEFAULT NULL,
`description` text,
`deleted` tinyint(1) DEFAULT '0',
`assigned_user_id` char(36) DEFAULT NULL,
`house_reference` varchar(10) DEFAULT NULL,
`house_address` varchar(25) DEFAULT NULL,
`house_address_state` varchar(100) DEFAULT NULL,
`house_address_postalcode` varchar(20) DEFAULT NULL,
`house_address_city` varchar(100) DEFAULT NULL,
`house_address_country` tinyint(3) DEFAULT NULL,
`house_ratings` tinyint(2) DEFAULT NULL,
`house_phone` varchar(25) DEFAULT NULL,
`house_fax` varchar(25) DEFAULT NULL,
`house_email` varchar(255) DEFAULT NULL,
`house_website` varchar(255) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

I have a table called Season defined like this :
TABLE `Season ` (
`idseason` tinyint(1) NOT NULL AUTO_INCREMENT,
`nameseason` varchar(100) NOT NULL,
PRIMARY KEY (`idseason`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

Then a table called house_season (a 1-N relation between module_house and Season) defined like this :
TABLE `house_season` (
`id` char(36) NOT NULL,
`idseason` tinyint(1) NOT NULL,
UNIQUE KEY `hotelseason` (`id`,`idseason`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;



I don't have any problem to create the dropdown of the season in my search tab.
But if I choose one season, the query result doesn't take into account its value (there is no join definition in the query). I can imagine that I have to do something like email fields for accounts or contacts modules but from now, I'm not successfull.


And for the edit view, I'd like to propose all the seasons with checkbox in a panel.

Thanks.
Franck

[datasponge]

OK,

First, you're describing this in terms of the underlying table definitions rather than the Sugar Fields and Relationships, so it's hard to tell what you did in Sugar. Did you design the Houses and Seasons modules using Module Builder? It might help to tell us exactly what you did in Sugar to design your modules.

Second, your relationship table has only two fields and would normally have more than that. It would normally have these id fields:

1. id - a unique id for this entry in the relationship table
2. house_id - the id of the house in the houses table
3. season_id - the id of the season in the seasons table

It would also normally have a deleted field and a date_modified field. This also leaves uncertainty how these tables were designed.

Finally, I don't understand why you are designing Seasons as its own table (Module). If the list of seasons is a pre-defined list (e.g. Spring, Summer, Fall, Winter), then the values are known in advance and there is no reason to create a specific instance of a season, so no reason for it to have its own table. This would be much easier to implement by adding a custom field for each season in the Houses module. Then you could define the fields however you liked - as 4 checkboxes or as a dropdown.

The only time you would need a separate module for Seasons is if you needed to define a new unique value not known in advance, but that doesn't seem useful for categorizing by season.

If I'm still missing something, please explain your requirements for the Seasons, including whether you need to be able to dynamically define a new season at any time. Also explain how you designed your modules in Sugar rather than simply their table definitions.

Phil

[franckt]

Houses is a module created using Module Builder and I wrote U, on my previous message, how it's seen mysql speaking.
The other 2 tables, I have created them through phpmyadmin.

I understand your solution (ie creating 4 custom fields) but I have same kind of tables, for example one which lists different facilities (ie sauna, outdoor swimming poool, garden etc... ), with a unique id and a name. (I update those tables through an independant back-office).
I have already more than 50 facilities, so I'm not going to create as many custom fields and I intend to use the table "house_facilities" (idhouse - idfacilities).

Like I said previously, it seems that the solution should imitate the one for the emails in Contacts or Accounts with the table "email_addr_bean_rel". I surely need to define this relationship somewhere.

Thanks again
Franck

[franckt]

I solved my problem for the search query with the searchfields.php definition as below :

$module_name = 'module_house';
$searchFields[$module_name] =
array (
'name' => array( 'query_type'=>'default'),
'house_facilities'=> array(
'query_type' => 'default',
'operator' => 'subquery',
'subquery' => 'SELECT fac.idhouse FROM house_facilities fac JOIN module_house hou ON (hou.id = fac.idhouse) WHERE fac.idfacilities=',
'db_field' => array(
'id',
),
),
'current_user_only'=> array('query_type'=>'default','db_field'=>array('a ssigned_user_id'),'my_items'=>true, 'vname' => 'LBL_CURRENT_USER_FILTER', 'type' => 'bool'),
'assigned_user_id'=> array('query_type'=>'default'),
);

[datasponge]

I see what you're trying to do now. I haven't used the solution you showed, but it looks like the right approach.

Good job working it all out.

Phil