Go To Layout [Pick a layout based on the USER table]
Enter Find Mode
Set Field [UserName ; Get ( AccountName ) ]
If [FieldYouAreInterestedInConfirming = WhatYouWantToSee ]
Show Custom Dialogue [ Get ( AccountName ) & " is indeed one of those people we want!" ]
Show Custom Dialogue [ Get ( AccountName ) & " - sorry, you are not welcome here" ]
So I would have to switch to a layout based on that table first? Hmmm...
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?
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.
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.
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.
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.
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.
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.
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.
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...)
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.