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

Hello,

 

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.

Outcomes