What Are Available Menue Commands In Filemaker
By Jon Rosen Manipulating Lists with FileMaker ProA list is a type of data, similar to text, numbers, dates, etc., but it can contain many items. Lists are one of the most powerful programming constructs, but haven't been available in FileMaker Pro. If you have ever programmed in a language that has good list manipulation commands then you know how much more easily certain programming problems can be solved. In some languages, like C or AppleScript, for example, a list may contain any combination of data types, including other lists. In FileMaker, by using the techniques in this article, a list must be a text field to work properly.
So, just about anything but a graphic or a sound can be coerced into a list.When would this be useful in FileMaker? Consider the situation when you have a found set and you want to do a series of finds on each record in the found set. However, when you do a find your current found set is replaced by the new found set.
In order to do what you are trying to do, you would be forced to create a temporary file, import record IDs into it and then loop through a script in that database that calls a script in the first database. It's not that this is so difficult to do, but it is inconvenient, a little time-consuming and causes a proliferation of cross-file subscripts.I will show you a much simpler way to do it, and it can all be done within the same file. Incidentally, using FileMaker Pro 3.0v3, we can get the nth value of a repeating field within a script by using the Get Repetition script step. But, there is no equivalent Set Repetition script step. It can only be done within a script by explicitly declaring the repetition number - which is found as the number you enter at the bottom of the dialog that's used when selecting a field to affect within ScriptMaker. If and when we can set the values of individual items in a repeating field by field value, creating lists will become even simpler and you may not need the following techniques. Until then, this can be a valuable tool for the FileMaker developer.Earlier I mentioned that a list is a text field.
It should be a global field of type text because global fields are available to the entire file. In the examples below, any field name preceded by a '' (bullet) is a global field. I use that convention in all of my files.
Aside from making them obvious, when you display fields by name, all of these global fields are displayed at the bottom of the field listing. Unlike many other databases, you can put a'Carriage Return,' or paragraph marker '¶', within a text field to get multiple lines.
FileMaker recognizes that a paragraph marker also delineates words. These two items allow you to create lists: a global (text) field with multiple paragraphs.
These lists can be manipulated easily using the text functions that deal with words.Here is an example of creating our list from a found set. The records we want have already been found. We go to a layout with a single field on it, the field we will use for finding records. Then, the Copy All Records command creates a list of all Record Numbers. Copy All Records copies to the clipboard. So, all that is left to do is to paste the clipboard into our list field.Script Step / Options Script ParametersGo to Layout Layout with only one field on itCopy All RecordsGo to Layout Layout with the List field on itPasteListSelect entire contentsHere is an example using a list within a loop:Set Error Capture OnLoopComment Data:Gets first item from list.Set Field Find Field: Calculation (LeftWords(List, 1)Perform FindComment Data:Error Code 401 is 'record not found.' If Calculation: Status(CurrentError)= 401Comment Data:A second list that gets the exceptions found.This calculation may look complicated, but allit does is to make sure that the first item onthe list isn't a '¶'.Set Field Exceptions: Calculation If(Exceptions, Exceptions & '¶'& LeftWords(List, 1), LeftWords(List, 1))Comment Data:If no record was found, we put the entry fromthe list into the exception list, otherwise wedo nothing.ElseEnd IfSet Field List: Calculation MiddleWords(List, 2, WordCount(List)-1)Comment Data: This removes the first item from the list.
We exit the loop when the list is empty.Exit Loop If Calculation List='End LoopFileMaker's text functions are recursive. Basically, that means the field can modify itself. It starts at the 2nd word in the list and gets the rest of the list and replaces List with that value. Therefore the 1st item on the list is simply deleted.Let's say we are designing a database for a company that owns bookstores, and the owner wants a report that will show him which books are sold in store A, but not in Store B.
What Are Available Menu Commands In Filemaker 1
How would we do this? Well, as I mentioned earlier, we would probably have to put the Bookcodes for Store A into another file and loop through the file. Using list techniques, we place the Bookcodes for Store A list field. Then we have several possibilities, depending on exactly what we want to accomplish. We could loop through the list with a counter. If we find a book from Store A that's not in Store B we could remove it from the list and place it in another list.
Or, we could only remove books from the list that are in both stores.