12 Replies Latest reply on Jan 16, 2012 8:27 AM by greginchrist

    Apple Script - cell data from query

    greginchrist

      This works...

      cell "Customer_CompanyEntity::CustomerCode" of current record

       

       

      This does not any ideas why not?

      set flexJobNumber to text returned of (display dialog "Please Scan The Job Ticket" default answer "11103103")

      cell "Customer_CompanyEntity::CustomerCode" of (every record whose cell "jobNumber" = flexJobNumber)

       

       

       

      NOTE: The following is not supper relevent, I just want to try to avoid some advaice that will not pertain

       

       

      FYI this also give me a number of lists. I use this line in diffent contexts. I use this to bring up the record corisponding to our job number.

      every record whose cell "jobNumber" = flexJobNumber


       

       

      And yes each statment is wrapped in

       

      tell application "FileMaker Pro"

      tell database "myDbName"

      each code goes here

      end tell

      end tell

       

       

      I have also tryed first doing the AppleScript equivilent of command "J" before each line... IE.

       

      try

      do menu menu item "Show All Records" of menu "Records"

      end try

       


       


        • 1. Re: Apple Script - cell data from query
          greginchrist

          I kept editing that post and forgot to ask the main question...

           

          How do I get call data from FMP with out first bringing a record into focus. I am looking to use something like...

           

          -- Does not work

                              cell "Customer_CompanyEntity::CustomerCode" of (every record whose cell "jobNumber" = "11120201")

           

           


          • 2. Re: Apple Script - cell data from query
            sporobolus

            on 2012-01-05 15:59 greginchrist wrote

            How do I get call data from FMP with out first bringing a record into focus. I am looking to use something like...

             

            set flexJobNumber to text returned of (display dialog "Please Scan The Job Ticket" default answer"11103103")

            cell "Customer_CompanyEntity::CustomerCode" of (every record whose cell "jobNumber" =flexJobNumber)

             

            you're on the right track; first of all be sure the front window is on a layout

            whose base table is Customer_CompanyEntity (found set shouldn't matter); this

            is necessary for the object specifier to work

             

            then leave the table name out of the cell reference, like this:

             

               cell "CustomerCode" *of* (*every* record *whose* cell "jobNumber" = 
            flexJobNumber)
            

             

            • 3. Re: Apple Script - cell data from query
              greginchrist

              Question 1 -

              Why am I not able to get the data of a related related table in this instance? Again why does the following work?

               

              cell "Customer_CompanyEntity::CustomerCode" of current record

               

              or what is the difference between 

               

              of current record

               

              and

               

              (every record whose cell "Order::jobNumber" = "12345678") <----- With this query I am only getting one found record

               

               

               

              Question 2 -

              1. I did not create this database and I do not have a layout for that table, nor the ability to create one. Everything it related through a pretty much one main table accessed by related fields or portals. So selecting a different layout will not work in my case.
              2. Also if I was to make a layout based on that table wouldn't I still have the same problem becase the line would rather have to become...

               

               

                   cell "CustomerCode" of (every record whose cell "Order::jobNumber" =flexJobNumber)

               

              Would I not be creating the same problem only backwards?

               

               

              By the way, thanks for the quick response yesterday!

              • 4. Re: Apple Script - cell data from query
                greginchrist

                So I just tried the following, which should be exactly as you told me, but trying to pull data from the main table, just to try what you said. However it does not seem to be working

                 

                tell application "FileMaker Pro"

                     tell database "Master DB"

                         cell "Order_ID" of (every record whose cell "jobNumber" = "11122811")

                  end tell

                end tell

                 

                 

                It says object not found. I am pulling from the table that is accociated with the layout.

                 

                Not working...


                • 5. Re: Apple Script - cell data from query
                  greginchrist

                  Just so people can follow where I am at I got the the following working. I think the solution will just be two phase.

                   

                  --gets ID of a record from my main table

                  tell application "FileMaker Pro"

                    tell database "Master DB"

                    cell "Order_ID" of (every record whose cell "jobNumber" = "11122811") of table "Order"

                    end tell

                  end tell

                   

                   

                  I plan to take that returned ID and use it for a second query on the related table.

                   

                  I'll post back if it works.

                  • 6. Re: Apple Script - cell data from query
                    sporobolus

                    on 2012-01-06 07:34 greginchrist wrote

                    greginchrist https://fmdev.filemaker.com/people/greginchrist created the discussion

                     

                    To view the discussion, visit: https://fmdev.filemaker.com/message/66086#66086

                     

                    • 7. Re: Apple Script - cell data from query
                      sporobolus

                      foiled again by the infernal Jive email mangling

                       

                      on 2012-01-06 07:34 greginchrist wrote

                      > Question 1 - Why am I not able to get the data of a related related table in

                      this instance? Again why does the following work?

                      >

                      > cell "Customer_CompanyEntity::CustomerCode" of current record

                       

                      it works because it isn't using the object specifier (the "whose" clause part

                      of your other version)

                       

                      there are quirks in FileMaker's object model; as i noted, when referencing

                      cells i believe you can't use an object specifier except to address a table

                      that is the base table of the current layout

                       

                      however see below ...

                       

                      > Question 2 -

                      > 1. I did not create this database and I do not have a layout for that table,

                      nor the ability to create one. Everything it related through a pretty much one

                      main table accessed by related fields or portals. So I selecting a different

                      layout will not work in my case.

                      > 2. Also if I was to make a layout based on that table wouldn't I still have

                      the same problem becase the line would rather have to become

                      >

                      >

                      >       cell "CustomerCode" of (every record whose cell

                      "Order::jobNumber" =flexJobNumber)

                      >

                      > Would I not be creating the same problem only backwards?

                       

                      to understand your question, i had to infer from the above an important detail

                      you hadn't previously mentioned: that jobNumber is in the Order table (whereas

                      CustomerCode is in Customer_CompanyEntity)

                       

                      so do i have this right: you have a layout based on Order and

                      Customer_CompanyEntity is a related table?

                       

                      if so, you can use the document/layout aspect of FileMaker's object model, as

                      opposed to the database/table aspect your example code uses; the distinction is

                      confusing — the former aspect allows you to access what's on the layout in the

                      current found set, including related values, where the latter accesses fields

                      in specific tables and (regardless of found set or what's on the layout); if i

                      understand correctly, and assuming the "Order" layout is the current layout and

                      the found set is all records, try this:

                       

                         tell application "FileMaker Pro Advanced"
                           tell layout "Order" of front document
                             cell "Customer_CompanyEntity::CustomerCode" of (every record whose cell 
                      "Order::jobNumber" = flexJobNumber)
                           end tell
                         end tell
                      

                       

                      if you can't control the found set, you'll still have trouble with the above,

                      your two-step approach may be best

                      • 8. Re: Apple Script - cell data from query
                        greginchrist

                        I just tried what you last posted.

                        steve harley wrote:

                         


                        
                           tell application "FileMaker Pro Advanced"
                             tell layout "Order" of front document
                               cell "Customer_CompanyEntity::CustomerCode" of (every record whose cell 
                        "Order::jobNumber" = flexJobNumber)
                             end tell
                           end tell
                        

                         

                        if you can't control the found set, you'll still have trouble with the above,

                        your two-step approach may be best

                         

                        No luck.

                         

                        I have been doing some testing and understand what you were saying

                         

                        steve harley wrote:

                         

                        you're on the right track; first of all be sure the front window is on a layout

                        whose base table is Customer_CompanyEntity (found set shouldn't matter); this

                        is necessary for the object specifier to work

                         

                        I am almost shocked to think that FileMaker Pro is locked down to a layout. I have used the following and it works when the layout being called is the layout selected in the FMP GUI, but renders a bad result if any other layout is open in the GUI.

                         

                        tell application "FileMaker Pro"

                             tell database "test"

                                  tell table "users"

                                       --name of every field

                                       cell "UserName" of (every record whose cell "id" = "1")

                                  end tell

                             end tell

                        end tell

                         

                        Can you make a query on a table that is not the specified table of the layout currently visible in the FMP GUI?

                        • 9. Re: Apple Script - cell data from query
                          sporobolus

                          on 2012-01-06 14:27 greginchrist wrote

                           

                          I just tried what you last posted.

                           

                          No luck.

                           

                          it worked for me using a very simple test case; i'll attach it to a subsequent

                          comment (since it's impossible to intersperse quotes in the web interface) so

                          you can perhaps see what is different in your scenario ...

                           

                           

                          I am almost shocked to think that FileMaker Pro is locked down to it's layout. I have used the following and it works on one layout but renders a bad result if the wrong layout is open in the GUI.

                           

                          the AppleScript implementation is definitely quirky, but so is almost every

                          other application's implementation, and many are much, much harder to work with

                           

                           

                          tell application "FileMaker Pro"

                          •     tell* database "test"

                          •          tell* table "users"

                          --name of every field

                                         cell "UserName" of (every record whose cell "id" = "1")

                          •          end* tell

                          •     end* tell

                          *end tell

                           

                           

                          Can you make a query on a table that is not the specified table of the layout currently visible in the FMP GUI?

                           

                          you can definitely access data in a table which is not in the current layout

                          (using the database/table aspect of the model); the problem is that some

                          object-specifier forms do not work, but some do, and i can't tell you why; for

                          example i suspect that the following subtly different script will work for what

                          you are trying above, even if the table is not the base of the current layout:

                           

                             *tell* database "test"
                               cell "UserName" *of* (*every* record *of* table "users" *whose* cell "id" 
                          = "1")
                             *end* *tell*
                          

                           

                          at least, i am sure that the following similar construct works in the

                          to-be-attached db, regardless of which layout is active

                           

                             cell "customerCode" of (records of table "Customer_CompanyEntity" whose cell 
                          "cust_id" is "1")
                          

                           

                          • 10. Re: Apple Script - cell data from query
                            sporobolus

                            attaching the above-referenced test case

                             

                            (btw, note that in the preceding message we see a new species of email-mangling ... in my quote of Greg's code, the special coding for bold and the special coding for bullets have come into conflict in a way that shows Jive's own markup in some cases can't survive a round trip)

                            • 11. Re: Apple Script - cell data from query
                              greginchrist

                              Your example works, however it is not working on our existing solution. I will keep trying and post back. Thank you for all your help so far!

                              • 12. Re: Apple Script - cell data from query
                                greginchrist

                                Steve Harley provided the solution to my situation. Thank you for your time and effort. The Lord Jesus Christ bless you sincely!