Xataface Forums

You can create a new Adult record, and a new Student record. You can link them as existing records. However, if you try to create a new Adult record from the New Related Record button in the Student table, it is created, but not linked. Additionally, the transient Role field is not saved in the new Adult (which may, in fact, be the cause of the linking issue...)
/tables/Student/relationships.ini

Code: Select all

[Parents]
adult.AdultID = family.AdultID
family.StudentID = "$StudentID"
vocabulary:existing = "Parents"

[Class]
class.ClassID = "$Class"
action:visible = 0

[Attendance]
attendance.Student = "$StudentID"

[Involvement]
production.ProductionID = studentinvolvement.ProductionID
studentinvolvement.StudentID = "$StudentID"

[Waiting List]
waitinglist.ListID = "$WaitingList"
action:visible = 0

/tables/Student/valuelists.ini

Code: Select all

[Statuses]
   S = Student
   A = Associate

[Payments]
   dd = Direct Debit
   cc = Credit Card
   c = Cash
   cqe = Cheque

[Enrolments]
   WL = Waiting List
   E = Enrolled
   WD = Withdrawn

[Classes]
   __sql__ = "SELECT ClassID, Name FROM class ORDER BY Name"
   
[Lists]
   __sql__ = "SELECT ListID, Year FROM waitinglist ORDER BY Year"

/tables/Adult/relationships.ini

Code: Select all

[Children]
   family.AdultID = "$AdultID"
   student.StudentID = family.StudentID
   action:condition = "mysql_fetch_row(mysql_query('SELECT * FROM AdultRole WHERE AdultID = '.$record->val('AdultID').' AND RoleID = 5'))"

[Classes]
   classes.AdultID = "$AdultID"
   class.ClassID = classes.ClassID
   action:condition = "mysql_fetch_row(mysql_query('SELECT * FROM AdultRole WHERE AdultID = '.$record->val('AdultID').' AND RoleID = 3'))"


[Roles]
    adultrole.AdultID = "$AdultID"
    role.RoleID = adultrole.RoleID
   action:visible = 0
   
[Involvement]
   adultinvolvement.AdultID = "$AdultID"
   production.ProductionID = adultinvolvement.ProductionID
   
[Attendance]
   adultmeeting.AdultID = "$AdultID"
   adultmeeting.MeetingID = meeting.MeetingID

/tables/Adult/valuelists.ini

Code: Select all

[Roles]
    __sql__ = "SELECT RoleID, Name FROM role ORDER BY Name"

[Statuses]
   S = Senior
   A = Assistant
   T = Trainee
   
[Tutors]
   __sql__ = "SELECT Adult.AdultID, CONCAT(LName, ', ', FName) FROM adult INNER JOIN AdultRole ON Adult.AdultID = AdultRole.AdultID AND AdultRole.RoleID = 3"
   
[Parents]
   __sql__ = "SELECT Adult.AdultID, CONCAT(LName, ', ', FName) FROM adult INNER JOIN AdultRole ON Adult.AdultID = AdultRole.AdultID AND AdultRole.RoleID = 5"

The Adult and Student tables are (supposed to) only be associated if the transient Role field includes RoleID 5, which is the Parent role. When you create a New Related Record, it doesn't seem to save the content of transient Role, which (I think) is why it doesn't relate properly.

Here's the set-up for the Role field.
/tables/Adult/fields.ini (excerpt)

Code: Select all

[Role]
widget:type = checkbox
transient = 1
relationship = Roles
order = 100
visibility:list = visible

/tables/Adult/valuelists.ini (excerpt)

Code: Select all

[Roles]
    __sql__ = "SELECT RoleID, Name FROM role ORDER BY Name"

/tables/Role/relationships.ini

Code: Select all

[Adults]
    adultrole.RoleID = "$RoleID"
    adult.AdultID = adultrole.AdultID

I think that's about it. Does the New Related Record form not work with transient fields, perhaps? Hmm.

Thanks.

Can you also post the SQL def for the relevant table so I can set up a test case in my server.

Code: Select all

CREATE TABLE `adult` (
  `AdultID` int(11) NOT NULL AUTO_INCREMENT,
  `LName` varchar(50) NOT NULL,
  `FName` varchar(50) NOT NULL,
  `HouseNo` varchar(10) DEFAULT NULL,
  `Street` varchar(50) DEFAULT NULL,
  `Suburb` varchar(50) DEFAULT NULL,
  `Postcode` int(11) DEFAULT NULL,
  `Phone` varchar(20) DEFAULT NULL,
  `Mobile` varchar(20) DEFAULT NULL,
  `Email` varchar(50) DEFAULT NULL,
  `Email2` varchar(50) DEFAULT NULL,
  `YPTReceiptNo` int(11) DEFAULT NULL,
  `LSPReceiptNo` int(11) DEFAULT NULL,
  `YPTFeePaid` double DEFAULT NULL,
  `LSPFeePaid` double DEFAULT NULL,
  `Involvement` text,
  `EOI` tinyint(1) DEFAULT NULL,
  `Contract` tinyint(1) DEFAULT NULL,
  `ATO` tinyint(1) DEFAULT NULL,
  `EDF` tinyint(1) DEFAULT NULL,
  `Status` enum('S','A','T') DEFAULT NULL,
  `Photos` tinyint(1) DEFAULT NULL,
  PRIMARY KEY (`AdultID`)
) ENGINE=InnoDB AUTO_INCREMENT=194 DEFAULT CHARSET=latin1;

CREATE TABLE `adultrole` (
  `AdultRoleID` int(11) NOT NULL AUTO_INCREMENT,
  `AdultID` int(11) DEFAULT NULL,
  `RoleID` int(11) DEFAULT NULL,
  PRIMARY KEY (`AdultRoleID`)
) ENGINE=MyISAM AUTO_INCREMENT=118 DEFAULT CHARSET=latin1;

CREATE TABLE `family` (
  `FamilyID` int(11) NOT NULL AUTO_INCREMENT,
  `AdultID` int(11) DEFAULT NULL,
  `StudentID` int(11) DEFAULT NULL,
  PRIMARY KEY (`FamilyID`)
) ENGINE=MyISAM AUTO_INCREMENT=198 DEFAULT CHARSET=latin1;

/*Table structure for table `role` */

DROP TABLE IF EXISTS `role`;

CREATE TABLE `role` (
  `RoleID` int(11) NOT NULL AUTO_INCREMENT,
  `Name` varchar(50) DEFAULT NULL,
  PRIMARY KEY (`RoleID`)
) ENGINE=MyISAM AUTO_INCREMENT=7 DEFAULT CHARSET=latin1;

/*Data for the table `role` */

insert  into `role`(`RoleID`,`Name`) values (1,'LSP Member'),(2,'Committee Member'),(3,'Tutor'),(4,'Patron'),(5,'Parent'),(6,'YPT Member');

CREATE TABLE `student` (
  `StudentID` int(11) NOT NULL AUTO_INCREMENT,
  `LName` varchar(50) DEFAULT NULL,
  `FName` varchar(50) DEFAULT NULL,
  `DOB` date DEFAULT NULL,
  `HouseNo` varchar(10) DEFAULT NULL,
  `Street` varchar(50) DEFAULT NULL,
  `Suburb` varchar(50) DEFAULT NULL,
  `Postcode` int(11) DEFAULT NULL,
  `Phone` varchar(20) DEFAULT NULL,
  `Mobile` varchar(20) DEFAULT NULL,
  `Email` varchar(50) DEFAULT NULL,
  `Email2` varchar(50) DEFAULT NULL,
  `EnrolmentStatus` enum('WL','E','WD') DEFAULT NULL,
  `SchoolYear` int(4) DEFAULT NULL,
  `WaitingList` int(11) DEFAULT NULL,
  `WaitingListPlace` int(11) DEFAULT NULL,
  `WaitingListDate` date DEFAULT NULL,
  `ClassPlacementDate` date DEFAULT NULL,
  `ClassPlacementAcceptance` tinyint(1) DEFAULT NULL,
  `ClassEnrolmentDate` date DEFAULT NULL,
  `EnrolmentYear` int(4) DEFAULT NULL,
  `Class` int(11) DEFAULT NULL,
  `Status` enum('S','A') DEFAULT NULL,
  `FeesPaid1` double DEFAULT NULL,
  `ReceiptNo1` int(11) DEFAULT NULL,
  `PaymentMethod1` enum('dd','cc','c','cqe') DEFAULT NULL,
  `FeesPaid2` double DEFAULT NULL,
  `ReceiptNo2` int(11) DEFAULT NULL,
  `PaymentMethod2` enum('dd','cc','c','cqe') DEFAULT NULL,
  `Productions` text,
  `Involvement` text,
  `Photos` tinyint(1) DEFAULT NULL,
  `Medical` tinyint(1) DEFAULT NULL,
  `MedDetails` text,
  PRIMARY KEY (`StudentID`)
) ENGINE=InnoDB AUTO_INCREMENT=251 DEFAULT CHARSET=latin1;

There we go. I think that's all the relevant tables. Note that I've included data for the Role table, 'cause the Role IDs are hardcoded in the .ini files.

*bump* Still having this issue.

Sorry... been really busy and haven't had a chance to try this out. Perhaps over the weekend I'll get some time to look into it...