12 Replies Latest reply on Oct 18, 2011 10:38 AM by philmodjunk

    Picking one field out of search results for variable?

      Title

      Picking one field out of search results for variable?

      Post

      Hey all.  I am fairly new at FMP, so forgive me.  I have done some searching for this but I just don't think that I know the proper FM lingo to get the answer that I am looking for.  I am also more familiar with SQL type queries and it is sometimes hard to get the new FM round-peg to fit in the SQL square hole.  :)

       

      I am trying to find some data (really a boolean) about the currently-logged-in user and trying to use that for an IF statement.  Here's the basic breakdown.

      **  Get the user's name:   setvariable ($name ; Get(accountname))

      **  Find that user's record in the USER table (this is a unique result, i.e. 1 record)

      **  From that result, pick out whether or not that person "is X?" (yes/no).  [Would perhaps be something like:  Setvariable ($isX ; USER::isX)  ?]

      ** IF ($isX) ...  Else ...

       

      In SQL I would think of it all like this:  SELECT isHuman FROM user_table WHERE user_name = "Bob Smith".  But the whole Find[] functionality confuses me a bit.

       

      I hope this makes sense. 

       

      Thanks,

      Justin

        • 1. Re: Picking one field out of search results for variable?
          Sorbsbuster

          Try:

          Go To Layout [Pick a layout based on the USER table]
          Enter Find Mode
          Set Field [UserName ; Get ( AccountName ) ]
          PerformFind []
          If [FieldYouAreInterestedInConfirming = WhatYouWantToSee ]
          Show Custom Dialogue [ Get ( AccountName ) & " is indeed one of those people we want!" ]
          Else
          Show Custom Dialogue [ Get ( AccountName ) & " - sorry, you are not welcome here" ]

          • 2. Re: Picking one field out of search results for variable?

            So I would have to switch to a layout based on that table first?  Hmmm...

            • 3. Re: Picking one field out of search results for variable?
              Sorbsbuster

              Is that an issue?  You can start anywhere, freeze the window, do the find, and end up anywhere.

              In SQL I believe you would have to have a FROM, so what's the difference?

              • 4. Re: Picking one field out of search results for variable?

                No, but is that the way that it is done, or needs to be done?

                 

                The difference is that in SQL the entire process is all one small line.  Yes, there is a FROM parameter as noted in my original post.  This seems like a rather intricate dance in FM to retrieve one piece of information. 

                 

                Now I just need to figure out what layouts are based on the USER table.  :)

                 

                Is there a habit by anyone to create a 'developers' layout for a table (yes, every table), with all the fields on it, that gets used only for searches like this?  I guess you wouldn't need to put the fields on the layout even, right?  But just to have a layout in place that you know is going to be there, with some nice naming convention to make it easy to find.

                 

                • 5. Re: Picking one field out of search results for variable?
                  philmodjunk

                  I suggest that you back and and describe why you want to do this and how you want to use this info when you are done. As you have discovered, FileMaker's approach to doing things is very different from SQL, Trying to pound that round peg in a square hole gets tiring and leads to inefficient design choices in FileMaker.

                  As you figure out how FileMaker works, you'll find that some actions that take rather arcane SQL expressions are very simple and straight forward in FileMaker. For others the reverse is true.

                  Best to start over with the big picture and then we might just describe a very different approach to doing what you want here.

                  • 6. Re: Picking one field out of search results for variable?

                    The ultimate goal is to automatically filter a layout based on whether or not the person is listed as the Lead Editor for that book.  It is a supervisory kind of overview.  Otherwise the layout shows all unfinished books.  This view is based on the Book table, and the information about whether or not that person is defined as a "Lead Editor" is over in the User table (not all Editors are Leads).  So, based on their account name (which is purposely set up to be the same as their user name) I want to find out if they are a Lead Editor and then apply, or not, the filter.

                     

                    Thanks,

                    J

                    • 7. Re: Picking one field out of search results for variable?
                      philmodjunk

                      This sounds like something I would use Manage | security to set up rather than this 100% scripted approach.

                      With security settings, you can limit a specific user's access to only those records they are permitted to see or only allow editing of those records they are permitted to edit.

                      See "Editing record access privileges" in FileMaker Help and check out this particular sub section: "Entering a formula for limiting access on a record-by-record basis" for a detailed description of how to set this up.

                      Please note, that any find your script performs will automatically filter out any "no access" records.

                      • 8. Re: Picking one field out of search results for variable?

                        It's more a convenience issue than a security problem.  We are just trying to set it up so that they only INITIALLY see what is theirs.  They still need access to all the other stuff, they just don't use it as much.  It seems like the security approach would exclude them from the other records.

                        • 9. Re: Picking one field out of search results for variable?
                          philmodjunk

                          Not necessarily. You have many options and this can be made specific to this layout.

                          A script can check to see if a user is the lead editor. One simple way to identify them is to give them a distinct privilege set name only given to lead editors. Then you script can see if the current user has a Lead Editor privilege set name and if so, performs the needed find. If you mark their records with an auto-entered account account name of the lead editor, you can perform the find on this specific layout. Otherwise, as previously discussed, you can switch layouts to perform a find to get the needed data (this is very common in FileMaker scripts) or you'd put the account name in a field that enables you to access the correct data via a relationship. This can be done with a global text field.

                          • 10. Re: Picking one field out of search results for variable?
                            Sorbsbuster

                            Just to help encourage your mind around to Filemaker's modus operandi: you say, "The difference is that in SQL the entire process is all one small line", but that's not much different from saying 'Filemaker does it in one simple script'.

                            Take the simple 'find' request described as '...WHERE InvoiceItem = Apples '  Any database would have to know if you mean to look in all the InvoiceItem records and return the list of them that equal 'Apples'.  Or do you mean to look at all the InvoiceHeader file and return the list of all Invoice Headers which contained an item 'Apples'?  Or do you mean to start with the Customer File and return a list of all Customers who have been invoiced for 'Apples'?  They are all very different searches when described more fully, and yet all have a similar element of 'WHERE...' in them.  Filemaker would know which you meant by starting the search from the InvoiceItem, InvoiceHeader, or Customer table.

                            From one of your earlier posts: you don't need to design a 'Search Layout' to contain all possible search fields, as scripted searches do not need the field to be on the layout.

                            • 11. Re: Picking one field out of search results for variable?

                              What about a layout that only has one field, or no fields (but still based in the layout)?

                               

                              I am wondering about performance; it seems that it would be a much lighter hit if the layout switched too had no fields on it.  And a bit of a convenience factor for the developer to know that to search for a field they would switch to a layout with a specific naming convention (making it a bit easier to find).

                               

                              It seems the typical sequence I have seen so far is to switch to the layout first; can you enter find mode and then switch?  That might handle performance issues that might be present for a layout switch.  (I know on some of the layouts I have seen that it takes a while for the layout to display because it has a bunch of calc fields, is sorted, etc.  So if you switch to it first...)

                               

                              -- J

                               

                              • 12. Re: Picking one field out of search results for variable?
                                philmodjunk

                                What about a layout that only has one field, or no fields (but still based in the layout same table occurrence)?

                                This is often done.

                                It doesn't really affect performance much as we can and shold use Freeze Window to keep the screen from updating anyway, but layouts can have layout based script triggers attached to them and this is one of the ways to establish a "table reference" without tripping those layout based triggers. Just be careful not use use any "insert", copy or paste script steps while on such a blank layout. Those script steps require that the referenced field should be physically present on the layout. (And this is why I don't use these steps unless I can't find an alternative.) Instead of those steps, use the Set field and set field by name steps to modify data in a field.

                                You may want to check out this tutorial on "Table Occurrences" to get a better understanding of the relationship between layouts, table occurrences and the current "found set" of records with which your script might interact. In some ways, you can think of a table occurrence as a kind of query/Recordset object, but without the WHERE clause.

                                Tutorial: What are Table Occurrences?