Relationship/Transient Not Applied In New Related Record

Archived from the Xataface Users forum.

00Davo — Mon Feb 22, 2010 1:18 am

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.

---

shannah — Tue Feb 23, 2010 10:17 am

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

---

00Davo — Tue Feb 23, 2010 1:03 pm

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.

---

00Davo — Tue Apr 06, 2010 4:58 pm

*bump* Still having this issue.

---

shannah — Thu Apr 08, 2010 9:58 am

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…