What context does SQL have?
I'm trying to do something that seems like it should be simple. I am on a layout for table called Names. I want to extract Phone numbers from the PhoneNumbers table associated with this person. This person has pkNameID, the PhoneNumber table has fkNamesID. So, records in PhoneNumber have a matching key field in Names. I've created a button that uses a script to populate a field to place the phone numbers. I've tried numerous SQL statements, but something like this is what I'm trying to do:
ExecuteSQL ( "Select CountryCode, PhoneNumber from PhoneNumber WHERE fkNamesID = Names::pkNameID " ; "-" ; "¶" )
Seems like Names::pkNameID or simply pkNameID isn't in the context. I tried just hardcoding a value like 119 after the = sign and that works fine. I don't understand why pkNameID doesn't have the value for the record I'm on. Do I need to create a variable for the value I'm looking for? If so, how do I do that? I can't seem to get "Let" to work.