Xataface Forums

First - this package is amazing and if I can ever make proper sense of it, I'll be telling everyone I know!

And now - the problem.

I'm putting together a simple CRM/HelpDesk application for my business - and the database is very simple - 4 tables with a master ID from the Clients table. I got that working just fine with the job history as a text field in the linked HelpDesk table - and everything was working fine.

I then decided to replace the single text field with a linked table called JobHistory (properly linked as a foreign key in phpmyadmin) - which was then linked from the HelpDesk table with the appropriate 'relationships.ini' file. This works to the point that it shows the records I inserted for a given call through phpmyadmin... but when I tried to add another entry to that call using the Zataface interface, clicking on the save button just cleared the fields above it and didn't do anything; no record was saved.

Now - I'm new to this framework - and I'm more inclined to think C rather than php.... so I've tried replicating this issue on a fresh installs of xataface... and not one install has actually managed to save the desired record.

Is there something I've not read about; is there a bug..... or am I just being particularly thick? Previous experience has been with Access (all versions), Approach, Notes and other assorted apps.... and I prefer writing my queries in plain SQL rather than rely on the MS query builder... but I really want to make this work so that I can access the database from anywhere.....

What version of Xataface/MySQL/OS/Web Server are you using?
Are there any clues in your error log?
What is the relevant table schema (e.g. create table statements)?
Please post the relationships.ini file.

Maybe these will offer some clues...
-Steve

Ok - Apache 2.2.21, MySQL server 5.5.9, php is 5.2.17 (I heard there might be some issues with the later version so dropped back to this one). The O/S is Mac OS/X 10.7.2 (although I've had the same results on Mint 12 linux and Windows 7 ultimate)

The following is the sql dump of the database - minus the insert commands.

Code: Select all

-- phpMyAdmin SQL Dump
-- version 3.3.9.2
--
-- Host: localhost
-- Generation Time: Jan 20, 2012 at 12:37 AM
-- Server version: 5.5.9
-- PHP Version: 5.2.17

SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";


/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;

--
-- Database: `crm`
--

-- --------------------------------------------------------

--
-- Table structure for table `ClientDetails`
--

CREATE TABLE `ClientDetails` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `ClientID` int(11) NOT NULL,
  `PhoneNo` varchar(20) DEFAULT NULL,
  `MobileNo` varchar(20) DEFAULT NULL,
  `FaxNo` varchar(20) DEFAULT NULL,
  `WebAddress` varchar(60) DEFAULT NULL,
  `EmailAddress` varchar(60) DEFAULT NULL,
  `Address` varchar(140) DEFAULT NULL,
  `Contactable` enum('Yes','No') DEFAULT NULL,
  `Comments` text COMMENT 'Information about the client',
  PRIMARY KEY (`ID`),
  KEY `ClientID` (`ClientID`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=2 ;

-- --------------------------------------------------------

--
-- Table structure for table `Clients`
--

CREATE TABLE `Clients` (
  `ID` int(11) NOT NULL AUTO_INCREMENT COMMENT 'Primary client index',
  `Company` varchar(60) DEFAULT NULL,
  `FName` varchar(50) DEFAULT NULL,
  `LName` varchar(50) DEFAULT NULL,
  `Status` enum('Client','Prospect','Supplier','Other','Blacklisted') DEFAULT NULL,
  `WhichCompany` enum('Computer Solutions','Hera Systems') DEFAULT NULL,
  `DateAdded` date DEFAULT NULL COMMENT 'The date the client was added',
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=4 ;

-- --------------------------------------------------------

--
-- Table structure for table `HelpDesk`
--

CREATE TABLE `HelpDesk` (
  `HDID` int(11) NOT NULL AUTO_INCREMENT,
  `ClientID` int(11) NOT NULL,
  `DateStarted` date DEFAULT NULL,
  `DateScheduled` date DEFAULT NULL,
  `DateCompleted` date DEFAULT NULL,
  `JobDescription` text,
  `DateInvoiced` date DEFAULT NULL,
  `DatePaid` date DEFAULT NULL,
  `HowPaid` enum('Cheque','Bank Transfer','Cash') DEFAULT NULL,
  `Completed` enum('Yes','No') DEFAULT NULL,
  PRIMARY KEY (`HDID`),
  KEY `ClientID` (`ClientID`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=4 ;

-- --------------------------------------------------------

--
-- Table structure for table `History`
--

CREATE TABLE `History` (
  `ID` int(11) NOT NULL AUTO_INCREMENT COMMENT 'Primary for the History Table',
  `HelpDeskID` int(11) NOT NULL,
  `EntryDate` datetime DEFAULT NULL,
  `Entry` text,
  PRIMARY KEY (`ID`),
  KEY `HelpDeskID` (`HelpDeskID`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=3 ;

-- --------------------------------------------------------

--
-- Table structure for table `Marketing`
--

CREATE TABLE `Marketing` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `ClientID` int(11) NOT NULL,
  `DateContacted` date DEFAULT NULL,
  `HowContacted` enum('Cold Calling','Press','Radio','Networking','Grey Power','Cartridge World','Direct Mail','Email','Newsletter','Other') DEFAULT NULL,
  `HowResponded` text,
  PRIMARY KEY (`ID`),
  KEY `ClientID` (`ClientID`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=2 ;

--
-- Constraints for dumped tables
--

--
-- Constraints for table `ClientDetails`
--
ALTER TABLE `ClientDetails`
  ADD CONSTRAINT `ClientDetails_ibfk_1` FOREIGN KEY (`ClientID`) REFERENCES `Clients` (`ID`) ON DELETE CASCADE ON UPDATE CASCADE;

--
-- Constraints for table `HelpDesk`
--
ALTER TABLE `HelpDesk`
  ADD CONSTRAINT `HelpDesk_ibfk_1` FOREIGN KEY (`ClientID`) REFERENCES `Clients` (`ID`) ON DELETE CASCADE ON UPDATE CASCADE;

--
-- Constraints for table `History`
--
ALTER TABLE `History`
  ADD CONSTRAINT `History_ibfk_1` FOREIGN KEY (`HelpDeskID`) REFERENCES `HelpDesk` (`HDID`) ON DELETE CASCADE ON UPDATE CASCADE;

--
-- Constraints for table `Marketing`
--
ALTER TABLE `Marketing`
  ADD CONSTRAINT `Marketing_ibfk_1` FOREIGN KEY (`ClientID`) REFERENCES `Clients` (`ID`) ON DELETE CASCADE ON UPDATE CASCADE;


The relationships.ini for the clients table is...

Code: Select all

[HelpDesk]
HelpDesk.ClientID = "$ID"

[Details]
ClientDetails.ClientID = "$ID"

[Marketing]
Marketing.ClientID = "$ID"

And the code for relationships.ini in the HelpDesk folder is...

Code: Select all

[Job History]
History.HelpDeskID = "$HDID"

This should all work beautifully... but it just fails to allow me to add a history record.

I'm doing the development on the Mac - based on the latest MAMP package - and then transferring what I've got to other systems in VMware VMs.... and wherever I take it, the save button on the history page just clears the fields, leaves me at that screen and fails to add the new record to the history table. And it doesn't matter whether I'm in a VM or not.

It's probably something completely stupid that I'm doing... but if it is, why does it only hapen on that one screen when the other screens work fine - just as I expected them to?

I think I see the problem. Relationship names can't have spaces. (This is a bug in the sense that it should throw a sensical error message in this case but doesn't). If you want the label of your relationship to have a space you could use the action:label directive to do that.
e.g.

Code: Select all

[Job_History]
History.HelpDeskID = "$HDID"
action:label="Job History"


-Steve

Thanks, Steve!

I've just tried it - and it's working fine! Damn - it was something stupidly simple!

Now I can try and make sense of the rest of the tutorials... and maybe it's time to add some serious php to my skill set! Next stage is to add some accounting tables so I can generate an itemised report for the invoices for the jobs.... - now that's gonna be fun....