I have three tables as follows, Customers, Customers and Sites.
We add a new job and select a customer from the CUstomers Table. We then select a Order_Site_Address from the sites table dependant on the customer Name. WHen we have added the order_site_address, I need to populate the extra fields in the jobs table which are Post_Code, Line1 where POst_Code is SPost_Code in the sites and SLine1 in the sites tables, is there an easy way of doing this from when the Order_SIte_Address is picked?
Problem I get is I have one site with two customer Names, as the building either pays for there own repairs, or the local council pay depending on who has done the damage, so if I have two site addresses the same, althought the Post_Codes are the same, it wont show up when the site address is selected?
Thanks
Paul
Field Type Collation Attributes Null Default Extra Action
CustomerId int(11) No None auto_increment
Customer_Name varchar(50) latin1_swedish_ci No None
Billing_Address longtext latin1_swedish_ci Yes NULL
Customer_Note longtext latin1_swedish_ci Yes NULL
Locked tinyint(1) Yes NULL
Line1 varchar(50) latin1_swedish_ci Yes NULL
Line2 varchar(50) latin1_swedish_ci Yes NULL
Line3 varchar(50) latin1_swedish_ci Yes NULL
Line4 varchar(50) latin1_swedish_ci Yes NULL
Line5 varchar(50) latin1_swedish_ci Yes NULL
Line6 varchar(50) latin1_swedish_ci Yes NULL
Line7 varchar(50) latin1_swedish_ci Yes NULL
Post_Code varchar(50) latin1_swedish_ci Yes NULL
Field Type Collation Attributes Null Default Extra Action
Customer_Name varchar(500) latin1_swedish_ci No None
site_address varchar(500) latin1_swedish_ci Yes NULL
LocationId int(255) No None auto_increment
SPost_Code varchar(20) latin1_swedish_ci No None
SLine1 varchar(100) latin1_swedish_ci No None
SLine2 varchar(100) latin1_swedish_ci No None
SLine3 varchar(100) latin1_swedish_ci No None
SLine4 varchar(100) latin1_swedish_ci No None
SLine5 varchar(100) latin1_swedish_ci No None
SLine6 varchar(100) latin1_swedish_ci No None
SLine7 varchar(100) latin1_swedish_ci No None
CustomerId int(11) No None
DoorCode varchar(20) latin1_swedish_ci Yes NULL
GateCode varchar(20) latin1_swedish_ci Yes NULL
AlarmCode varchar(20) latin1_swedish_ci Yes NULL
ExtraCodes varchar(100) latin1_swedish_ci Yes NULL
Field Type Collation Attributes Null Default Extra Action
Order_Date datetime Yes NULL
File_No varchar(50) latin1_swedish_ci Yes NULL
Job_No int(50) No None auto_increment
CustomerId smallint(10) Yes NULL
Order_Time varchar(50) latin1_swedish_ci Yes NULL
Contract longtext latin1_swedish_ci Yes NULL
Order_Site_Address varchar(200) latin1_swedish_ci Yes NULL
Job_Description longtext latin1_swedish_ci Yes NULL
VAT double(7,2) Yes NULL
Invoice_Printed tinyint(1) Yes NULL
Invoice_Printing_Date date Yes NULL
Invoice_Tax_Date date Yes NULL
Payment_Received tinyint(1) Yes NULL
Job_Finished tinyint(1) Yes NULL
CustomerRef varchar(50) latin1_swedish_ci Yes NULL
Customer_Name varchar(50) latin1_swedish_ci Yes NULL
Payment_Due_date date Yes NULL
Payment_Date date Yes NULL
Locked tinyint(1) Yes NULL
Note longtext latin1_swedish_ci Yes NULL
Date_Added timestamp on update CURRENT_TIMESTAMP Yes CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP
1st_Payment double(7,2) Yes NULL
2nd_Payment double(7,2) Yes NULL
3rd_Payment double(7,2) Yes NULL
1st_Payment_Date date Yes NULL
2nd_Payment_Date date Yes NULL
3rd_Payment_Date date Yes NULL
EngineerId varchar(50) latin1_swedish_ci No None
PDAAllocation varchar(100) latin1_swedish_ci Yes NULL
LocationId varchar(50) latin1_swedish_ci Yes NULL
JobTypeId varchar(50) latin1_swedish_ci No None
Job_Sheet varchar(50) latin1_swedish_ci No None
Post_Code varchar(200) latin1_swedish_ci Yes NULL
Line1 varchar(200) latin1_swedish_ci Yes NULL
DoorCode varchar(200) latin1_swedish_ci Yes NULL
GateCode varchar(200) latin1_swedish_ci Yes NULL
AlarmCode varchar(200) latin1_swedish_ci Yes NULL
ExtraCodes varchar(200) latin1_swedish_ci Yes NULL
Exported tinyint(1) Yes NULL
Logged_By varchar(200) latin1_swedish_ci Yes NULL
PhoneNumber varchar(20) latin1_swedish_ci Yes NULL
CRB_No varchar(50) latin1_swedish_ci Yes NULL