3 Replies Latest reply on Dec 10, 2015 2:08 PM by coherentkris

    Get "column b" value where "column a" = x


      How can I get a value from one field in a one-to-many related table based on the value of another field without creating new many-to-many relationships? 


      The two tables are SubmitTimes (list of unique web form submission timestamps) and Entries (timestamp, form_name, field_name and field_value for each form component in its own record) related by timestamp.


      For example, I want to make a Company calculation field in SubmitTimes to return the related Entries::field_value data where Entries::field_name = "company" but I can't get anything to work. 

      I thought this ExecuteSQL calculation would do it, but did I phrase it wrong? 


      ExecuteSQL ( "

      SELECT Entries.field_value FROM Entries
      WHERE Entries.field_name='station' AND Entries.timestamp = SubmitTime.timestamp

      " ; "" ; "" )