"No Holding Back FileMaker Blogging"
|
Navigation:
|
Support this site by clicking on a sponsor below or becoming a patron!
Create a Complete Contact Manager
Become a patron of this FREE web site!
|
|
Recent Blogs:
|
Currency Formatting
 |
Gathering Portals
 |
Multiple Choice Picker
 |
Popups and Pickers
 |
Window Locking
 |
|
Meta-Consulting
Meta-consulting is a service Database Pros offers to help other developers
over programming hurdles via GoToMeeting. Email John Mark Osborne or call John Mark at (909) 393-4664 to find out more about this service.
|
Create a FileMaker Calendar
|
Quick Tip:
|
Preventing Access to Fields
FileMaker Pro has a feature in the Inspector under the Data tab that allows you to prevent entry into a field. However, scripts can still enter a protected field. Just use the Go to Field or any other script step that places the cursor in a field to allow users into a field only when you want them. By the way, attaching a Go to Field step directly to a button will not override the Inspector settings. A script from Manage Scripts is required. |
|
|
$10.00 Beginner Video Training
|
Fun Stuff:
|
Dull Boy
FileMaker files were stored in clear text prior to FileMaker 7 so, if you opened a FileMaker file in a text editor, you could see the phrase "All work and no play makes Jack a dull boy" in the header repeated over and over. No, it's not a virus! My understanding is the developers just needed to take up some space in the header and that's what they came up with. Today, modern FileMaker files are stored in Unicode so I'm not sure if the phrase is still there. |
|
|
Create a Password Management solution!
|
RSS Feed
|
|
|
|
Multiple Choice Picker
|
Level: Intermediate
Version: FileMaker 20
Category: Scripting
Wednesday, May 29, 2024 |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
I've been using pickers in customer solutions for years but always as a single choice interface. It dawned on me, after reading a post in a forum, that it's often advantageous to make multiple selections, instead of having to keep clicking the picker buttons for multiple single choices. In this article, I'll use a simple invoicing solution to demonstrate how to enhance a picker to make multiple choices.
Single Pickers
If you haven't read my previous article about Popups and Pickers, you'll probably want to understand the content before proceeding since a multiple choice picker shares a lot of the same code. It will also get you familiar with the basics of pickers as well as teach you a little about how pop-up and drop-down menus work and can be enhanced.
Picker Basics
I'll assume for this article that you understand how to create a card window picker based on my article Popups and Pickers. This includes the layout itself, which should be a list view from the table where you want to make the selections. In this case, we'll be using an invoicing example so the picker layout will show records from the PRODUCTS table. There are three tables total in this basic invoicing solution:
The picker should also contain a button that closes the picker using the Close Window script step. In this case, the button will be titled "Cancel" since it will prevent any selections from being added to the invoice by simply closing the picker window. Lastly, the picker will be a list view with a global field at the top that filters the found set so you can find the products you want.
If this brief summary is not clear, either look at the example file included with this article or read the article referenced above. The article will show you exactly how to create the picker layout, close/cancel button and the filter. Displaying the picker from the invoicing layout is also quite easy with a simple New Window script step displayed as a Card and a Show All Records script step. There's a few more steps but that's the basic idea.
The Main Course
Now that you have familiarized yourself with a basic single selection picker, let's dive into what makes a multiple selection picker different. The first item is how selections are recorded. In a single choice picker, the primary key value for the product would have been placed in a variable, the window closed and the primary key placed into the foreign key to complete the relationship. In a multiple choice picker, the picker window stays open and the selections are stored in a global field. While you could store the values in a global variable ($$), the global field is a better choice to enhance the user interface, as you will see later in this article. Here's the single line script using Set Field:
Set Field [PRODUCTS::xpicks; PRODUCTS::xpicks & "¶" & PRODUCTS::_kp_products_id]
BTW: It's a good idea to clear the global fields before displaying the picker window. There will be two globals in this article. One global serves as the filter and the other stores the selections.
The xpicks field is a global field tracking the selections made. As you can see, xpicks is the target as well as a reference in the calculation of the Set Field step, allowing you to collect each record you click. Since this script is attached to a button in the body part, it allows the Set Field step to append each new selection into a return-separated list, much like a checkbox field stores multiple selections. A selection of three products might look like the following (pilcrows represent the invisible returns):
13¶
42¶
9
FYI: If you are unfamiliar with the append technique, look at this article titloed Set Field Skills.
Notice there are returns between each value but not a return at the beginning or end. This is how a standard return-separated list looks and should be maintained. This will be important now and later on in the article so become familiar with this standard format for a return-separated list. In the meantime, we need to fix the above script to prevent an extra return at the beginning of the list. This occurs when the xpicks global field is empty (the first click for a selection). It's easy to correct the problem with a simple Case statement:Set Field [PRODUCTS::xpicks; PRODUCTS::xpicks & Case(not IsEmpty(PRODUCTS::xpicks); "¶") & PRODUCTS::_kp_products_id] FYI: A single pilcrow doesn't need quotes around but I feels it makes it easier to read since it matches the rest of the text blocks.
Viewing the Selections
Making it clear to the user that a selection has been made and so they can see all the selections that have been made so far, a simple conditional formatting formula can search the xpicks global field to fill the button that's on each record.
Here's the conditional formatting formula so it's easier to read:
PatternCount(
PRODUCTS::xpicks;
PRODUCTS::_kp_products_id
)
A simple PattrnCount function searches the xpicks global field for the primary key from the current record. Each record is formulated individually so only records with primary keys that exist in the xpicks global field will highlight. If you are using plain serial numbers like the one's shown in the example, there's a possibility for an error. Let's take the following example of the contents from the xpicks gloabl field:
10¶
23¶
44
If you click on a product with a primary key of "1", the record for product "10" will also highlight. In fact, any product containing a "1" will highglight including "14", "112" or "231", to name a few. That's because the pattern of "1" exists in all these other choices. Luckily, there are two simple fixes. The easiest one for me is to not do anything since all my primary keys are preceded by three letters and leading zeros. The three letters represent the table where the primary key is generated so I can identify the serial number outside of the context of a primary or foreign key field, such as when they are collected into a global or variable as a return-separated list. The leading zeros allow the values in a text field to sort like a number. For example, a list in one of my commercial solutions might look like this:
PRD0000000022¶
PRD0000000477¶
PRD0000000001
Since I'm not using my standard serial number naming convention in this article or the attached example file, adding leading and trailing returns around the return-separated list and the search criteria solves the problem by making the search criteria unique. In other words, we are searching for "¶1¶" now so it can only find that unique number. Here's the new formula:
PatternCount(
"¶" & PRODUCTS::xpicks & "¶";
"¶" & PRODUCTS::_kp_products_id & "¶"
)
Adding the Line Items
Once the selections have been made, another button adds each of the product IDs to the LINES table along with the primary key from the invoice. Before we look at the script, an additional relationship is required:
The PRODUCTS_Choices table occurrence is a self-join relationship connecting the PRODUCTS table to itself using the xpicks global field as the primary key. The values in the xpicks global field are in a return-separated format so they create a multi-key relationship. The multi-key allows each return-separated value to match individually throgh the relationship, effectively creating an OR relationship. With the simple addition of a Go to Related Record script, the selected products can be placed in a found set:
The script starts by closing the card window and then immediately grabs the primary key from the invoice. A new window is created next, displaying records from the PRODUCTS table to set the context for the Go to Related Record (GTRR) script that follows. It doesn't matter what found set or record is selected in the PRODUCTS table since the GTRR step is based on a relationship using a global field. Once the found set is established, a layout based on the LINES table is selected and a quick import is used to move the primary keys from the PRODUCTS table into the LINES table. The final step is to populate the invoices foreign key using the Replace Field Contents feature so the products display on the invoice.
There are many ways to move data from one table to another but I chose the import method in this case since I could have dozens of products being selected. Trying to move all those products into the LINES table using a looping script would be slow, cumbersome to script and somewhat of hack due to all the jumping back and forth between products and lines. Always think of the variety of different solutions for a job and pick the best for the process at hand.
Enhancements
If you have a lot of products in your table, seeing which products are selected/highlighted can be difficult, especially if the list has been filtered. A good solution is to use the existing PRODUCTS_Choices relationship to display the selected items in a portal. In the example file, I've placed the portal in the footer along with the button that adds them. I tried the header part first but the footer seemed like the best interface to me.
Another enhancement is to add a delete button inside the portal so a product can be removed. This is trickier than it seems when you are trying to keep the standard return-separated list of the xpicks global field intact. For example, you could try to surgically remove a product from the xpicks list using a Set Field step and the Substitute function:Set Field [PRODUCTS::xpicks; Substitute(PRODUCTS::xpicks; PRODUCTS_Choices::_kp_products_id; "¶")] The same problem with uniqueness occurs as discussed above. You can solve the problem by adding the same surrounding returns. But, that doesn't solve the whole problem since there are now extra returns left behind at the beginning and end of the list. While the multi-key and conditional formatting will likely still work, it's best to keep a nice clean return-separated list. There are many ways to remove the extra returns but in this case, I find the solution below to be the easiest (just the formula from the Set Field step is show below):LeftWords(
Substitute(
"¶" & PRODUCTS::xpicks & "¶";
"¶" & PRODUCTS_Choices::_kp_products_id & "¶";
"¶"
);
999999999
) Notice the LeftWords function surrounding the entire Substitute function. Understanding how the LeftWords, MiddleWords and RightWords algorithm works will help you solve problems like the one at hand. The Words functions disregard word separators like spaces, returns, commas, periods, etc. unless they are surrounded by a word. We can use this knowledge to remove the leading a trailing returns using LeftWords and a very large number of words to grab. Where this technique won't work is if the content of your text blocks contains word separators not surrounded by words that you want to keep, such as a period at the end of a sentence.
This same script delete script, with a small change, can also be applied to the selection button in the body part. This will allow users to click once on the record to select and another click to deselect it. Here's the rewritten script for selecting a record:
The If statement tests to see if the primary key from the current record is in the xpicks field. If it is not, it runs the same selection script as discussed previously and adds the primary key to the xpicks return-separated list. If the primary key is already in the xpicks field, the script runs a variation of the deletion script shown above with one small change to the field being used in the PatternCount function. Instead of referencing the field from the PRODUCTS_Choices field in the portal, it uses the primary key from current record being clicked. That's the only change so the formula will not be shown (except what you see in the screen shot above).
It's All in the Details
One little addition I made to this solution is hiding the "Add" button in the footer if there are no values selected. It's a very simple Hide Object formula but I think provides feedback interface to the user that a selection needs to be made first.
IsEmpty(PRODUCTS::xpicks)
Useful?
I've only used this in one commercial solution but I can tell you the client loved it. It significantly reduced the number of clicks required to fill out an invoice which reduced employee work time and lines at the store. I'd like to hear about how you might use this solution or any variations that might might lead to additional efficiency.
Author:
John Mark Osborne
jmo@filemakerpros.com
www.databasepros.com
Example File (available to patrons at $5.00 per month): Download here if you are a patron
This blog is completely free. Please support it by clicking on one of the advertisers at the left side of the window or becoming a patron. Thanks so much!
|
|
