AnsweredAssumed Answered

Get Related Record's Field Value on Condition

Question asked by ddave on Nov 8, 2017
Latest reply on Nov 9, 2017 by ddave



I have two tables named job (parent) and Interface (child) related with jobID = jobID (one-to-many) relationship.


Now, in job table I have a field "art date" . I want this field to calculate or get the value of field "scandate" in Interface table with the condition : record's "code" field value starts with "2". I also want only the first record matching this criteria.


I tried with the following SQL query but doesn't seem to work:


ExecuteSQL ( "SELECT scandate FROM Interface I JOIN job J ON I.jobID = J.jobID WHERE Left(I.code; 1) = '2' " ; ""; "")


I hope there is an easier way to achieve this.