Building a CRM in OOo

Sample Screendump:

Requirements:

OpenOffice.org installed and working, version RC3 or greater
MySQL installed and working
MyODBC installed and working, also to be familiar with adding ODBC datasources.
MySQL Control Centre installed and working [obtain all three MySQL programs from www.mysql.org]

Create a new database using MySQL Control Centre - give it a logical name eg 'CRM database'
Right click the new database and choose 'New Window from here...'
Click on the SQL button or press CTRL-Q
Enter the SQL at the bottom of this page [copy and paste all of the blue text and then click on the exclamation mark '!' button or press CTRL-E] MySQL Control Centre will now create two new tables and populate it with sample data.
Set up the new database with the correct access rights for use, eg usernames,
Start OOo and add a new datasource (type MySQL) for the new database created in step 1.
Download the sample CRM form (OOo spreadsheet - right click and select 'Save as...')
Open the spreadsheet
Change to form design mode
Open the Form Navigator and right click 'CRM Form' and select 'Properties' from the context menu [see screen dumps below]
Change the 'Data Source' to the name of the new datasource created in step 6 [In the screen dump it is 'MySQL']
Change the 'Content' to the name of the table 'contacts2' in your datasource. [The name will be different depending on the name given to your database']

Right-click 'Child' in the Form Navigator and choose 'Properties' from the context menu [screen dumps below]
Change the 'Datasource' to the name of the datasource created in step 6 ['Volunteer' in screen dump]
Save everything and try the form out.

You may like to build some queries and reports as well... see How to add 'live' query data to spreadsheets

SQL:

#
# Table structure for table 'contactevents'
#

CREATE TABLE `contactevents` (
`EventID` double NOT NULL auto_increment,
`ContactID` double default NULL,
`CallDate` date default NULL,
`CallTime` time default NULL,
`Subject` varchar(50) default NULL,
`Notes` text,
PRIMARY KEY (`EventID`)
) TYPE=MyISAM;

#
# Dumping data for table 'contactevents'
#

INSERT INTO `contactevents` VALUES("1", "1", "2003-09-02", "19:30:00", "testing contact", " \n");
INSERT INTO `contactevents` VALUES("2", "1", "2003-09-04", "19:50:33", "New test", "This is another test\n\n\n\n");
INSERT INTO `contactevents` VALUES("3", "1", "2003-09-04", "19:54:16", "Phone call", "Note 2 this is more on this note");
INSERT INTO `contactevents` VALUES("6", "2", "2003-09-04", "19:55:02", "Follow up call to set account", "custservice@jonesinductries.com");
INSERT INTO `contactevents` VALUES("5", "2", "2003-09-04", "19:54:35", "Initial call", NULL);
INSERT INTO `contactevents` VALUES("7", "2", "2003-09-04", "20:28:17", "Cancel account", "This is the last straw ®§§¿¿¿?");
INSERT INTO `contactevents` VALUES("8", "2", "2003-09-04", "20:29:27", "Reconcider decision", "They don\'t suck as much as I thought they did");
INSERT INTO `contactevents` VALUES("10", "3", "2003-09-04", "20:36:07", "Initial call", "They seem like nice people");
INSERT INTO `contactevents` VALUES("12", "9", "2003-09-06", "11:32:33", "Load account", "Kumeu Sports Cafe Opens Account\n");
INSERT INTO `contactevents` VALUES("18", "10", "2003-09-06", "11:52:40", "First call", "Opened an account with us");
INSERT INTO `contactevents` VALUES("14", "9", "2003-09-06", "11:34:52", "First call", "Organise morning coffee supply\n");
INSERT INTO `contactevents` VALUES("19", "9", "2003-09-06", "12:58:30", "Muffins", "Discuss quality of muffins used ");
INSERT INTO `contactevents` VALUES("20", "10", "2003-09-06", "14:13:43", "Second call", "Booked a tent for the weekend function.\n");
INSERT INTO `contactevents` VALUES("21", "8", "2003-07-06", "14:13:43", "Initial", NULL);
INSERT INTO `contactevents` VALUES("22", "8", "2003-09-06", "14:20:13", "Initial call", "Found in the local business directory");
INSERT INTO `contactevents` VALUES("23", "10", "2003-09-06", "19:05:23", "Return of tent", "Arranged to drop the tent back after the function ");
INSERT INTO `contactevents` VALUES("24", "3", "2003-09-06", "19:06:26", "Book polish", "Polishing session booked for next Tuesday");
INSERT INTO `contactevents` VALUES("25", "2", "2003-09-06", "19:07:07", "Decision", "Will not deal with this company anymore\n");
INSERT INTO `contactevents` VALUES("27", "4", "2003-09-13", "10:47:50", "First call", "Touched base with company to meet\n");
INSERT INTO `contactevents` VALUES("28", "2", "2003-09-13", "10:58:09", "Decided not to work with them", NULL);
INSERT INTO `contactevents` VALUES("29", "10", "2003-09-13", "11:14:02", "Return tent pegs", NULL);
INSERT INTO `contactevents` VALUES("30", "10", "2003-09-13", "13:26:39", "Return more tent pegs", "Found some more pegs in car");
INSERT INTO `contactevents` VALUES("32", "11", "2003-09-13", "13:38:18", "Set up account", NULL);

#
# Table structure for table 'contacts2'
#

CREATE TABLE `contacts2` (
`ContactID` double NOT NULL auto_increment,
`Company` varchar(50) default NULL,
`ContactName` varchar(50) default NULL,
`PO Box` varchar(50) default NULL,
`Street` varchar(50) default NULL,
`Suburb` varchar(50) default NULL,
`City` varchar(50) default NULL,
`Country` varchar(50) default NULL,
`Phone` varchar(50) default NULL,
`Fax` varchar(50) default NULL,
`Email` varchar(50) default NULL,
`AccountNum` varchar(50) default NULL,
`Notes` text,
PRIMARY KEY (`ContactID`)
) TYPE=MyISAM;

#
# Dumping data for table 'contacts2'
#

INSERT INTO `contacts2` VALUES("1", "Smith Industries", "John Smith", "PO Box 27", "Jones St", "Jonesville", "Fonestown", "England", "+649835698", "", "johnsmith@xtra.co.nz", "", "This company makes small injected plastic items\n\n");
INSERT INTO `contacts2` VALUES("2", "Jones Industries", "Jim Smith", "Po Box 28", "Smith St", "Smithville", "Smithtown", "France", "0800 258965", "", "custservice@jonesinductries.com", "", "");
INSERT INTO `contacts2` VALUES("3", "Johnson Floor Polishers", "Tom Polisher", "PO Box 879", "Rimu St", "Ponsonby", "Auckland", "New Zealand", "+649 123 4567", "+649 1234568", "shiny.floors@rimu.co.nz", NULL, "These people polish floors");
INSERT INTO `contacts2` VALUES("4", "Acme Industries", "Carol Pickle", "Po Box 87", "Taylor St", "Morningside", "Auckland", "New Zealand", "+649 8372389", NULL, "carol@acmeindustries.com", NULL, "\n\n");
INSERT INTO `contacts2` VALUES("5", "Potty LTD", "Chris Potty", NULL, "Potty St", "Pottyville", "Wellington", "New Zealand", "+649 2621793", NULL, "chris@xtra.co.nz", NULL, "Makes a range of potties");
INSERT INTO `contacts2` VALUES("6", "Oxford Developments LTD", "John Oxford", NULL, "Oxford St", "Green Glen", "Detroit", "USA", "0800 Oxford", NULL, "john@oxford.com", NULL, "Makes Oxford stuff");
INSERT INTO `contacts2` VALUES("7", "JimsTown Services", "Jim Brown", NULL, "Jim st", "Jimsville", "Hamilton", "New Zealand", "+649 837788", NULL, "jimbrown@jimtown.com", NULL, "Supplies cleaning products");
INSERT INTO `contacts2` VALUES("8", "Helensville Ditch Diggers", "Kay Digger", NULL, "Rata St", "Helensville", "Auckland", "New Zealand", "+649 456789", "+649 789456", "diggers@xtra.co.nz", NULL, "They have a wide range of excuvators available");
INSERT INTO `contacts2` VALUES("9", "Kumeu Sports Cafe", NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, "ab23", "This is the cafe next door to us");
INSERT INTO `contacts2` VALUES("10", "Henerson portable tent hire Co", "Jane Canvas", NULL, NULL, "Henderson", "Auckland", "New Zealand", "+649 874568", "+649 874568", "tents.r.us@xtra.co.nz", NULL, "This company supplies tents");
INSERT INTO `contacts2` VALUES("11", "Geographic Industries", "Geo Raphic", NULL, NULL, NULL, NULL, "France", "+31 20 4874115", "+31 20 4874119", "ngcustserve@geog.com", NULL, NULL);