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
" ; "" ; "" )