I’ve had great luck here in the past with fantastic responses, so here’s another go:
I’m trying to use AS to extract data from FMP (8.5) to then format into InDesign. Everything was going really smooth and easy until I found that some of the data was trapped in a Portal! (I think this is the right name for it.)
Data is displayed in the layout “Collateral_Data” on record 49. However, the data that I want to refer to is being pulled from a table other than the layout’s specified table (we’ll call it “Other_Table”). On the “Other_Table” the records are sorted alphabetically by some other field. I want to be able to call up the data from every field of one column of the portal as sorted on the “Collateral_Data” layout. (The portal has data fields lined up horizontally, so when they’re populated each row is data from a record.)
Here’s the ineffective code:
tell application "FileMaker Pro"
tell record 49 of layout "Collateral_Data" of window 1 of document 1
get data of every field whose label is "Locale" --Locale is a label of one of the fields that exist in "Other_Table"
end tell
end tell
Evan,
Did you ever figure this out?
Craig
Craig,
No, I never did figure this one out, though I made some progress…
I can call up every field name on a layout, which includes portal fields and regular fields. This is a good step.
I also know that to refer to a field with info from a related table by using “relatedtable::thisfield”.
Now, one solution would be to use a “show related records” script. I believe this will work (and will experiment with it soon).
Regarding the trick of using portals though, I think that my endeavor is further complicated because the table that the portal is related to doesn’t actually exist (or so I’m led to believe). It’s a table made from relationships of several fields and other tables. I’m not sure how to best refer to it, at all.
Any insights into the A/S or FMP of this issue would be greatly appreciated!
-Evan
Ok, I CAN refer to the table directly. But I want to only reference the records from the initial Layout. I could do a complex find/sort by fields, but if there’s a faster and more elegant way to achieve this, I would prefer it.
Evan,
I don’t know if that’s elegant but it works… at least for me.
set recNum to 49
set targetLayout_name to "Collateral_Data"
set targetTO_name to "Other_Table"
set targetTO_field to "locale"
tell application "FileMaker Pro Advanced" -- Change to your needs
try
tell (first layout whose name is targetLayout_name) -- // Prefer the use of the layout ID rather than it's name ('cause you can have duplicates)
tell fields to set field_Names to name -- get All it's field names.
set RecordData to record recNum -- get All it's data.
end tell
on error
error number -128 -- Cancel -> either the record or the layout doesn't exist
end try
end tell
set RelatedRec_Column to targetTO_name & "::" & targetTO_field
if RelatedRec_Column is in field_Names then
repeat with j from 1 to (count field_Names)
if field_Names's item j is RelatedRec_Column then
set RelatedRec_ColumnData to RecordData's item j
--
--> do someting with RelatedRec_ColumnData
--
exit repeat -- in case you have more then 1 occurrence of the target field on the target layout.
end if
end repeat
end if
thisRelatedColumnData
hth
Clement
Kudos Clement!
It’s able to harvest some data thus far, and with a little more toying, I think it may, in fact, be able to harvest the right data! This is a fantastic step forward! Thanks!
Evan,
Does the script not answer this question ?
… It’s probably due to my lack of English 
Can be that one of two following scripts will make the deal. Who knows!
Get every value of row n of portal x:
-- FileMaker Pro / Advanced
-- Get every value of row n of portal x
set targetLayout_name to "Collateral_Data"
set targetTO_name to "Other_Table"
----------------- // Change values according to your needs
set recNum to 1
set portalRow to 3
-----------------
tell application "FileMaker Pro Advanced" -- Change to your needs
try
tell (first layout whose name is targetLayout_name)
tell fields to set field_Names to name
set RecordData to record recNum
end tell
on error
error number -128
end try
end tell
set |index| to {}
repeat with i from 1 to count field_Names
if field_Names's item i begins with targetTO_name & "::" then set |index|'s end to i
end repeat
if (count (RecordData's item (|index|'s item 1))) > 0 then
set thisPortalRow_Data to {}
repeat with i in |index|
set thisPortalRow_Data's end to (RecordData's item i)'s item portalRow
end repeat
else
return display dialog "No related records." buttons "Cancel" default button 1
end if
thisPortalRow_Data
-- > Result: every value of row n of portal x of record y
or Get every row of portal x of record y:
-- FileMaker Pro / Advanced
-- Get every row of portal x of record y
----------------- // HANDLERS
to ReshapeArray(aList)
script o
property l : aList
property colums : count l
property rows : count l's item 1
property ReshapedList : {}
end script
repeat with r from 1 to o's rows
set thisRecord to {}
repeat with c from 1 to o's colums
set thisRecord's end to o's l's item c's item r
end repeat
set o's ReshapedList's end to thisRecord
end repeat
return o's ReshapedList
end ReshapeArray
----------------- // END HANDLERS
set targetLayout_name to "Collateral_Data"
set targetTO_name to "Other_Table"
----------------- // Change value according to your needs
set recNum to 4
----------------- // End Change
tell application "FileMaker Pro Advanced"
try
tell (first layout whose name is targetLayout_name)
tell fields to set field_Names to name
set RecordData to record recNum
end tell
on error
error number -128
end try
end tell
set portalColumns to {}
repeat with i from 1 to count field_Names
if field_Names's item i begins with (targetTO_name & "::") then set portalColumns's end to RecordData's item i
end repeat
ReshapeArray(portalColumns)
--> Result: every row of portal x of record y, sorted as defined in relationship diagram or Format/Portal Setup.
I think we’ve covered here most common portal situations.
hth
Clement
Wow. That’s quite the extra mile of help there, Clement! Thanks!
The first one actually worked pretty well, and the subsequent ones work even better. I just had to manage to sift through my FMP database for the right table names, columns, related fields, etc…
For some reason, I greatly complicated how I thought about referring to tables using A/S. Your solution to use the “full field name” as a way to sort by which table each field is referencing, makes perfect sense.
I’m going to go read up more on script objects so that I can best understand how to maximize their potential, too.
Thanks again!