I was wondering... Imagine I'm in a Layout1 based on Table1. And I need to fill a field with a bit of text that is stored in a textfield in table2 that is related. In my Layout1 I have the ID number of the record in table2.
Not having had any education on Filemaker, but just being self-experimenting, I figured out, that I can do this with a script that goes up and down to the other layout, like this:
# In order to go back to the right record upon returning here, store this ID
Set variable ($RecordIDtable1, Table1::ID)
# Remember the ID that we need to fetch
Set variable ($SearchID, Table1::ID-of-record-to-be-searched)
# Go to the other layout, 2
Go to Layout (Based on Table2)
# Find the corresponding record
Perform find (Table2::ID = $SearchID) restore
# Remember text
Set variable ($FoundText, TextField)
# Go back to layout 1
Go to Layout (Based on Table1)
# Make sure we're in the right record
Perform find (Table1::ID = $RecordIDtable1) restore
# Fill the field with text
Set Field (Textfield, $FoundText)
Now. This works. But something is telling me there is a faster and more efficient way to do this. Every time this script is executed, you see the screen briefly flashing to the other layout to find the record to fetch the text and I think performancewise this is acrappy solution.
I was thinking of ExecuteSQL():
Set Field (textfield , ExecuteSQL ( " SELECT Table2.TextField FROM Table2 WHERE Table2.ID = ID-of-record-to-be-searched " , "" , "" ) )
But I can't get it to work. (puts a '?').
Am I on the right path?!
Thanks, Regards, Thomas.