3 Replies Latest reply on Mar 4, 2017 12:52 PM by philmodjunk

    How to retrieve data from one field of an unrelated table

    BMyers

      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

        • 1. Re: How to retrieve data from one field of an unrelated table
          philmodjunk

          The whole point of Selector Connector is to eliminate such "can't get there from here" relationship issues.

           

          But you can make your field a global field and use it is a search script to find records in another table. It's just not really necessary if you simply link the layout's table occurrence directly to the table being searched. To me, it seems like a fresh look needs to be taken at you relationships graph. (And adding one more TO really isn't that big a deal anyway.)

          • 2. Re: How to retrieve data from one field of an unrelated table
            BMyers

            Phil,

             

            Yes, I could just drop a TO on the relationship graph simply to create a single button, but that seemed a little excessive.  And yes, I could use a script like the one I posted, but that seemed a little kludgy.  I was simply curious about whether there was a simple direct method.

             

            I don't follow your suggestion that I "can make your field a global field and use it is a search script to find records in another table."  I'm quite familiar with that method.  It doesn't seem to fit to this use.  I'm not looking for FMP to return an entire record.  I curious to see if I can find a method of scripting so that my input is a RecordID plus Table::Field, and the computer returns the string only in that Table::Field for that one record.  It's similar to the concept of a lookup table.

            • 3. Re: How to retrieve data from one field of an unrelated table
              philmodjunk

              If you find the record, you then set a variable to the value of the desired field and then return to your original context.

               

              You could also use a subscript with the the value of this field returned as the script result.

               

              You could also use ExecuteSQL to get this value.

               

              I haven't used AppleScript in years, but since it can access fields in your file, you shouldn't have to copy data to the clipboard, but you certainly could, after finding the record, copy the value of a field of that record to the clipboard.