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

Discussion created by andycahn on Dec 10, 2015
Latest reply on Dec 10, 2015 by coherentkris

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

" ; "" ; "" )