AnsweredAssumed Answered

Fetching a field-contents from a related table without switching layouts

Question asked by tkemmere on Dec 8, 2017
Latest reply on Dec 13, 2017 by tkemmere

Dear all,


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.