If the tables are indeed related properly why not place the phone number fields on the layout based on Names? Why use SQL?
Thanks for the post Rick. The layout is just fine. What they want is what they call a copy-field on the layout where they can copy and paste from (to use in other applications). That field will contain Name, Addr, etc. and phone numbers properly formatted for them. The phone numbers are already displayed as a portal on the layout, but when I tried a simple calculation to copy all the necessary fields into this copy field, I only get the first number from the phone number portal. I imagine there are ways to grab all the portal records (I'm not aware of something easy off-hand), but I thought a simple SQL statement to just grab them and put them in the copy-field would work well. But, as I'm finding in FM, nothing is "simple".
There are a number of syntax errors in your SQL. Table.Field name is the correct syntax for referring to a field, not Table::Name. But you are also referring to data in fields from two tables and this requires a Join clause to define the relationship. SQL queries evaluate independently of your relationship graph as they use join clauses to define their own "context".
It's also possible to produce the result you want with out ExecuteSQL:
Define a calculation field, cFullPhoneNumber as CountryCode & "-" & PhoneNumber in your PhoneNumbers table.
Then define a calculation field to return text with this expression in the Names table:
List ( PhoneNumbers::cFullPhoneNumber )