I need to get data from a field of an unrelated table. I want a script or calculation that says: go to record number X of table Projects and get the corresponding data from the field Projects:FileNumber and copy it to the clipboard (or put it in a variable).
The goal is to be able to do a quick switch from looking at a Project's information in FMP to looking at the same Project's folder in Finder. The purpose is for navigation from a menu. A button will call a script that uses Spotlight to navigate to the particular Project folder in Finder. Folders have metadata just like files and the plan is to put the Project number into a metadata field so Spotlight can find it. This is just a shortcut to save the user the hassle of switching to Finder and scanning the list of Finder folders. The challenge is that this will be from a layout made on a table unrelated to the Projects table but that will have data from a Project on it.
Some of you might be wondering how this layout has Project data on it but the Projects table isn't related. It's too much to explain, but this app uses a Selector-Connector table and so the place where this button will sit does not have a relationship to the table Projects. In short, the S-C table has a relationship to Projects and a separate relationship to Files. I could create another TO just for this one button but I'm looking for an alternative. I'm learning that using the Selector technique to create multi-portal layouts can be a powerful tool, but it also causes a few glitches like this.
I'm looking for a direct easy way to do this. Is there a script that can call data in this way? I understand that SQL has this ability be sadly I'm not fluent in SQL. If someone can supply the command for the FMP Execute SQL script step I would be thankful.
I know I could do this by opening a separate window, such as below. I'm just curious about a more direct method.
Open new window off screen somewhere
Open a Project layout, do a find for the particular Project
Copy the Projects:FileNumber to clipboard
Close the window
Then use a Applescript to open OSX Spotlight and insert this string into OSX Spotlight and hit enter