I'm having relationship problems

Classic Servoy
1

Refer to the MySQL script below for details on this question

I have created a form based on the contact table. I also created a relation called contacts_to_company which relates contacts.company_id_company to company.id_company. In the contacts table the company_id_company column has valid values. In the form I have a field (Company Name) with the dataprovider as contacts_to_company.company_name.

The problem: Nothing shows in the company name field. It should use the relation to grab the name from the company table and display it. I have the same problem with other tables. I think this may be due to constraints in the database.

Any suggestions?

Here is the MySQL script for the related database

# Host: 10.0.0.4
# Database: es
# Table: 'company'
# 
CREATE TABLE `company` (
  `id_company` int(10) unsigned NOT NULL auto_increment,
  `company_name` varchar(255) NOT NULL default '',
  `fisc_year_end` varchar(15) default NULL,
  `gross_sales` decimal(10,2) default NULL,
  `num_employees` int(10) unsigned default NULL,
  `industry` varchar(255) default NULL,
  `parent_company` varchar(255) default NULL,
  `client_company` tinyint(1) default NULL,
  `website` varchar(100) default '',
  `stock_exchange` varchar(100) default '',
  `ticker_symbol` varchar(100) default '',
  `top_company` tinyint(1) default NULL,
  `fortune_1000` tinyint(1) default NULL,
  `forbes_private` tinyint(1) default NULL,
  `last_update` timestamp,
  PRIMARY KEY  (`id_company`)
) TYPE=InnoDB; 



# Host: 10.0.0.4
# Database: es
# Table: 'mailing'
# 
CREATE TABLE `mailing` (
  `id_mailing` int(10) unsigned NOT NULL auto_increment,
  `letter_id_letter` int(10) unsigned NOT NULL default '0',
  `mailing` varchar(255) NOT NULL default '',
  `description` varchar(255) default NULL,
  `date` date default NULL,
  `strategy` varchar(255) default NULL,
  `mailing_type` enum('Marketing','Thoughts') default NULL,
  `officers_mailed_to` int(10) unsigned default NULL,
  `num_responses` int(10) unsigned default NULL,
  `method` enum('Email','Postal') default NULL,
  PRIMARY KEY  (`id_mailing`),
  KEY `mailing_FKIndex1` (`letter_id_letter`),
  CONSTRAINT `mailing_ibfk_1` FOREIGN KEY (`letter_id_letter`) REFERENCES `letter` (`id_letter`)
) TYPE=InnoDB; 




# Host: 10.0.0.4
# Database: es
# Table: 'contacts'
# 
CREATE TABLE `contacts` (
  `id_contact` int(10) unsigned NOT NULL auto_increment,
  `company_name` varchar(100) NOT NULL default '',
  `Company_id_company` int(10) unsigned NOT NULL default '0',
  `first_name` varchar(100) NOT NULL default '',
  `last_name` varchar(100) NOT NULL default '',
  `middle_name` varchar(45) default NULL,
  `title` varchar(255) default NULL,
  `address_1` varchar(100) default NULL,
  `address_2` varchar(100) default NULL,
  `address_3` varchar(100) default NULL,
  `city` varchar(100) default NULL,
  `state` varchar(100) default NULL,
  `country` varchar(100) default NULL,
  `zip` int(10) unsigned default NULL,
  `phone_number` varchar(45) default NULL,
  `phone_ext` varchar(45) default NULL,
  `fax_number` varchar(45) default NULL,
  `fax_ext` varchar(45) default NULL,
  `alt_phone` varchar(45) default NULL,
  `alt_ext` varchar(45) default NULL,
  `assistant` varchar(100) default NULL,
  `assistant_phone` varchar(45) default NULL,
  `assistant_ext` varchar(45) default NULL,
  `email_address` varchar(100) default NULL,
  `male_female` enum('Male','Female') default NULL,
  `age` int(10) unsigned default NULL,
  `id_status` enum('Active with Anett','Client','Client/Contact','Contact','Dead','Do Not Mail','Remail','Responder','Thoughts') default NULL,
  `referred_by` varchar(100) NOT NULL default '0',
  `receive_thought_email` tinyint(1) default NULL,
  `receive_thought_post` tinyint(1) default NULL,
  `last_mail_date` date default NULL,
  `last_email_date` date default '0000-00-00',
  `responder_name` varchar(255) default '',
  `last_mailing_response` varchar(100) default '',
  `response_type` varchar(100) default '',
  `response_date` date default '0000-00-00',
  `who_responded` varchar(100) default '',
  `referrals_given_to` varchar(100) default '',
  `discount` tinyint(9) default '0',
  `price_quoted` decimal(10,0) default '0',
  `responder_notes` varchar(255) default '',
  `interests` varchar(255) default '',
  `last_mailing_response_id` int(10) unsigned default NULL,
  `private_coaching_notes` varchar(255) default NULL,
  `contact_for_notes` varchar(255) default NULL,
  `strategy` varchar(100) default NULL,
  `last_update` timestamp,
  PRIMARY KEY  (`id_contact`),
  KEY `Contacts_FKIndex2` (`Company_id_company`,`company_name`),
  KEY `last_mailing_response_index1` (`last_mailing_response_id`),
  CONSTRAINT `contacts_ibfk_1` FOREIGN KEY (`last_mailing_response_id`) REFERENCES `mailing` (`id_mailing`) ON DELETE CASCADE ON UPDATE CASCADE
) TYPE=InnoDB; 





# Host: 10.0.0.4
# Database: es
# Table: 'mailing_contact_rel'
# 
CREATE TABLE `mailing_contact_rel` (
  `fk_contacts_id` int(10) unsigned NOT NULL default '0',
  `fk_mailing_id` int(10) unsigned NOT NULL default '0',
  PRIMARY KEY  (`fk_contacts_id`,`fk_mailing_id`),
  KEY `mailing_contact_rel_index1` (`fk_contacts_id`),
  KEY `mailing_contact_rel_index2` (`fk_mailing_id`),
  CONSTRAINT `mailing_contact_rel_ibfk_1` FOREIGN KEY (`fk_contacts_id`) REFERENCES `contacts` (`id_contact`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `mailing_contact_rel_ibfk_2` FOREIGN KEY (`fk_mailing_id`) REFERENCES `mailing` (`id_mailing`) ON DELETE CASCADE ON UPDATE CASCADE
) TYPE=InnoDB; 

# Host: 10.0.0.4
# Database: es
# Table: 'program'
# 
CREATE TABLE `program` (
  `id_program` int(10) unsigned NOT NULL auto_increment,
  `Contacts_id_contact` int(10) unsigned NOT NULL default '0',
  `program_type` varchar(255) default NULL,
  `program_date` date default NULL,
  `program_cost` decimal(10,0) default NULL,
  `program_notes` varchar(255) default NULL,
  PRIMARY KEY  (`id_program`),
  KEY `program_FKIndex1` (`Contacts_id_contact`),
  CONSTRAINT `program_ibfk_1` FOREIGN KEY (`Contacts_id_contact`) REFERENCES `contacts` (`id_contact`) ON DELETE CASCADE ON UPDATE CASCADE
) TYPE=InnoDB; 



# Host: 10.0.0.4
# Database: es
# Table: 'letter'
# 
CREATE TABLE `letter` (
  `id_letter` int(10) unsigned NOT NULL auto_increment,
  `letter_name` varchar(255) default NULL,
  `letter_body` text default NULL,
  `letter_ps` text default NULL,
  `email_subject` varchar(255) default NULL,
  `email_heading` varchar(255) default NULL,
  `email_attachment` varchar(255) default NULL,
  PRIMARY KEY  (`id_letter`)
) TYPE=InnoDB; 



CREATE TABLE `letters_to_send` (
  `id_letters_to_send` int(15) unsigned NOT NULL auto_increment,
  `letter_id_letter` int(10) unsigned NOT NULL default '0',
  `id_mailing` int(10) unsigned NOT NULL default '0',
  `mailing` varchar(255) default NULL,
  `full_name` varchar(255) default NULL,
  `first_name` varchar(255) default NULL,
  `address` text default NULL,
  `date` date default NULL,
  `letter_body` text default NULL,
  `letter_ps` text default NULL,
  PRIMARY KEY  (`id_letters_to_send`),
  KEY `letters_to_send_FKIndex1` (`letter_id_letter`),
  CONSTRAINT `letters_to_send_ibfk_1` FOREIGN KEY (`letter_id_letter`) REFERENCES `letter` (`id_letter`)
) TYPE=InnoDB;
2

You need to create a valuelist based on your contacts_to_company relation and then connect your comany_id field to the valuelist.

You will probably also want to change the display type of the field to a combo box.

3

I’m working with chad on this project.

We previously had the field set up as a value list based off of all values from a column in a table. With this, the values show up in the value list, and you can select an entry for the company, but it will not save it.

I changed it to be a value list based off the relation under the contact table, and the value list doesn’t show any entries.

I think this may be a deeper problem, because non of our relations seem to work. We have tried using them in portal and they don’t work there either.

I think we are having problems setting up the relations. If we let servoy create the relations automatically, then they seem to be backwards. The primary key and foreign key are filled in correctly, but I can really use the relation. If for instance I try to use the relation to create a value list to be used in the contacts form, then I can only select contact fields for the value list, when what I want to do is select company fields. I want the company name to be displayed, and the company id to be returned to the dataprovider.

So I tried setting up the relation the other way, so it would show up under the contact table, this lets me choose the right fields, but ofcourse it doesn’t actually work.