6 Replies Latest reply on Sep 16, 2010 12:30 PM by fmnewb

    Look up related field using Get (Account Name)?



      Look up related field using Get (Account Name)?



      I'm just getting into FileMaker so not quite used to the scripting environment...

      I have a layout called "Projects". Each project record can be assigned to a person - this is represented by a field called "assigneeId". Assignee Id references another table called "Assignee".

      "Assignee" table has the relevant fields:




      So on my layout, after the project record is committed, I would like FileMaker to look up the assigneeId based on the current user (I believe "Get (Account Name)" achieves this), and to automatically update  Project::assigneeId, silently behind the scenes.

      I know SQL much much better - so maybe an example will help clarify what I'm trying to do - consider trying to update project #1


      UPDATE Project

      SET assigneeId = (SELECT assigneeId FROM Assignee WHERE accountName = "whateverCurrentAccountName")

      WHERE projectId = 1


      Thanks in advance!

      Also, is there an awesome scripting book that anyone recommends....?

        • 1. Re: Look up related field using Get (Account Name)?

          Freeze Window
          Go To Layout [Assignee]//any layout that refers to this table
          Enter Find Mode []
          Set Field [Assignee::accountName ; Get ( AccountName ) ]
          Set Error Capture [on]
          Perform Find []
          If [ Get ( FoundCount ) > 0 ]   // just Get ( FoundCount ) will also work here
             Set Variable [$AssigneeID ; Value:  Assignee::AssigneeID]
             Go To Layout [Original Layout]
             Set Field [Projects::AssigneeID ; $AssigneeID ]
             Show Custom Dialog ["No records found for " & Get (AccountName)]
             Go To Layout [Original Layout ]
          End If

          • 2. Re: Look up related field using Get (Account Name)?

            Hi Phil,

            Thank you so much for this! It works great however filemaker seems to freeze (I see an hourglass with the word "esc"); once you click "esc" it brings you to the Assignee layout (if a record is found). If no record is found, it seems to function ok without freezing.

            It doesn't like something about the last "set field" step. I replaced that with a custom dialog to display the assignee id and it was correct. I tried to commit it to see if that would resolve the problem, but it didn't.

            If it matters, I am using a SQL SERVER datasource.

            Thanks again!

            • 3. Re: Look up related field using Get (Account Name)?

              This script will work smoothly when the data source is a local file so I suspect your SQL datasource is the source of the problem here.

              If you have advanced, run the script with the debugger enabled and see where it actually "hangs". If you don't have advanced, you should get a copy as it can solve a lot of designer headaches. In the meantime, insert a bunch of Show Custom Dialog steps to see where the script hangs.

              Since interrupting the script leaves you on the other layout, I don't think it ever got to the Set Field step.

              • 4. Re: Look up related field using Get (Account Name)?

                Instead of replacing "Set Field [Projects::AssigneeID ; $AssigneeID ]", I added after it the "show custom dialog".

                The dialog displays the assignee id, and I hit ok, and it reappears again. Seems like a possible infinite loop.

                We don't have Advanced yet - I'm actually testing out the sql server integration capabilities so that we can decide if it's worthwhile.

                • 5. Re: Look up related field using Get (Account Name)?

                  By putting a series of Show Custom dialog steps in the script, you can detect where exactly the script hangs. This might give you a clue as to which step has an issue with the SQL Server data source.

                  • 6. Re: Look up related field using Get (Account Name)?

                    Problem solved. Forgot there is a last modified field which gets updated, which in turn caused the infinite loop. Nothing to do with the SQL Server data source.

                    Thanks again and lots of geeky admiration your way!