"No Holding Back FileMaker Blogging"
|
Navigation:
|
Support this site by clicking on a sponsor below or becoming a patron!
FileMaker 20 Video Tutorials
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 Password Management solution!
|
Quick Tip:
|
Date Search Techniques
Some date search features in FileMaker may not be obvious. For example, you can type in a range of "Monday...Friday" or "Mon...Fri" to locate all weekday dates. Or, you can use wildcards to locates all dates on January 1st, regardless of the year, by typing "1/1/*". You can use curly brackets to specify a range of months, such as "{1...6}/*/* to locate all the birthdays in the first 6 months of the year. Try these same wildcard techniques with time fields too. Play around with these tools, and I'm sure you'll be impressed with the flexibility. And let me know if you figure out some combination of these new features that let's you accomplish some really cool technique. |
|
|
Beginner, Intermediate and Advanced 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. |
|
|
Document Management Videos
|
RSS Feed
|
|
|
|
Gathering Portals
|
Level: Intermediate
Version: FileMaker 21
Category: Calculations
Tuesday, June 25, 2024 |
|
|
|
|
|
|
In a relationally sound FileMaker database solution, you will definitely need portals. When you have a one-to-many relationship, the most common way to display the many related records from a layout based on the parent is to employ a portal. But portals don't always do what we want. Sometimes you need to enhance them or reformat the data displayed in a portal. In this article, we'll explore a method for gathering the data from a portal and displaying it in a comma-separated list using the power of parsing and return-separated lists.
Related Articles
I've discussed return-separated lists quite a bit in this FileMaker blog. One of my favorite techniques uses a return-separated list as a multi-key in the five part article series discussing the preservation of found sets:
Preserving Found Sets
Saving a Found Set
Collecting Unique Values
Alternatives to Serial Numbers
Restoring Find Criteria
I have also used return-separated lists as an integral part of transforming check boxes into new find requests in the article titled Parsing for Features. If you want to create back and forward buttons like you see in a web browser, it's fairly easy to do with return-separated lists and variables using this article, Back and Forward. And, if you want to create an efficient log of changes to fields in a separate table, you'll need return-separated lists and this article, Scripted Change Log. I even have fun with return-separated lists in an article called Weight Lifting where I used an actual but esoteric solution to manipulate return-separated lists to achieve a goal otherwise not possible with FileMaker.
If you've read all these articles, you fully understand the power of constructing, parsing and manipulating return-separated lists. If you haven't, maybe after reading this article you'll be hooked and want to learn more.
What Started this Article
The inspiration for this article was a client who wanted a list of names from a portal to appear in an email. They wanted to say "Hi" followed by the names of some or all of the people listed in the portal for the current parent record. For now, we'll focus on just displaying all the names and in a comma-separated list. We'll enhance the technique as we progress through the article. We'll use an example of COMPANIES and EMPLOYEES throughout this article and the companion example file.
The basic formula for adding commas is actually quite simple:
Substitute(
List(EMPLOYEES::name_first);
"¶";
", "
)
The List function simply grabs all the field values from all the related records displaying in the portal. It might look something like this (the pilcrows are invisible but I've shown them to make it clear where the returns are):
John¶
Bob¶
Fred¶
Marc
The Substitute function searches for all the pilcrows and replaces them with commas so the substituted list would look like the following:
John, Bob, Fred, Marc
FYI: The Substitute function is Case sensitive so beware! Most functions and most features in FileMaker are not case sensitive so keep an eye on what you are trying to substitute.
Adding the AND
Replacing the comma between the second to last and last name with the word "and" is not as easy as it might seem. In order to complete the job, I'm going to employ the often forgotten Replace function. I'm not talking about the Replace Field Contents feature but a calculation function called Replace. It's a strange little function that seems like it might be related to Substitute but it's more akin to the Middle function. You'll understand once you see the parameters:
Replace(
text;
start;
numberOfCharacters;
replacementText
)
It may not be obvious how this will work until you understand we are going to combine it with the Position function. What we're going to do is use the Position function to locate the last comma and replace it with the word "and". Assuming the Substitute function has already replaced the returns with commas (@CommaList), the formula for locating the last comma will look like this:
Position(
@CommaList;
", ";
1;
PatternCount(@CommaList; ", ")
)
FYI: I use at signs (@) as my naming convention to designate variable names used in the Let function. Assume that @CommaList has already substituted the commas for the returns. The full formula will be shown below.
Note: Returns between parameters have been added to the formula to make it easier to read. They have no affect on how the performance of the calculation formula.
The fancy part about this formula is the last parameter designating the occurrence. Instead of typing in a static number, the PatternCount function tells us how many commas there are so we can locate the last one.
The Position function, from above, is then fed into the Replace function like so:
Replace(
@CommaList;
@Position;
2;
" and "
)
As you can see above, the Position function feeds into the starting parameter of the Replace function, allowing it to precisely locate the last comma. Since we know the separator consists of a comma and a space, we specify "2" as the length.
The Custom Function
Here's the entire formula as a custom function:
CommaList(ReturnList) =
Let(
[@Commas = Substitute(ReturnList; "¶"; ", "); //Replace returns with commas
@Position = Position(@CommaList; ", "; 1; PatternCount(@CommaList; ", "))]; //Locate last comma
Case(
IsEmpty(ReturnList) or ValueCount(ReturnList) = 1; //If no value or one value
ReturnList;
Replace(@CommaList; @Position; 2; " and ") //If more than one value replace last comma with "and"
)
)
Since the formula resides in a custom function named CommaList, the name of the parameter has been changed to @Commas to avoid confusion. The custom function would be called like this:
CommaList(List(EMPLOYEES::name_first))
The result of the formula might look something like this:
John, Bob, Fred and Marc
Any return-separated list can be provided to the custom function so you don't have to use the List function. You could use a Summary field with the List option, a static list of values in quotes separated by pilcrows, the ValueListItems function, ExecuteSQL function (as will be shown below) or any function that returns a return-separated list of values.
FYI: The reason for the custom function is so the code is centralized. If a change is needed to the formula, the change flows out to wherever the custom function is called inside the file.
Some commenting has been added to make the formula easier to read as well as a Case statement to handle exceptions. The first exception is if there is a blank portal. Without this exception, the word "and" would appear with no names. The second exception is if there's only one row in the portal. Without the test for more than one value, the Position function would find no commas and the Replace function would start at the beginning of the list and replace the first two letters of the name with "and".
Filtered Portals
If you have a filtered portal or you don't want to create a relationship to gather the related records, you'll need to use ExecuteSQL. Let's say we are filtering the portal for active employees. Unfortunately, the List function will not obey the layout level formula attached to the portal. You could construct the ExecuteSQL like the following to gather just some of the records shown in the related table.
CommaList(
ExecuteSQL(
"select name_first from EMPLOYEES where " & Quote("_kf_companies_id") & " = ? and active = 1";
"";
"";
COMPANIES::_kp_companies_id
)
)
There are two areas of the ExecuteSQL statement that need explaining if you are just beginning. If you are a novice, first read the following article titled Preferences and Options. The first oddity is the reference to the foreign key field in the EMPLOYEES table.
Quote("_kf_companies_id")
Normally, you can just refer to fields in the specified query table by their name as it appear in Manage Database (in this case, the EMPLOYEES table). Since the foreign key is preceded with an underscore, you have to put it in quotes or SQL will think it's a wildcard value. The second interesting addition is the question mark which refers to the argument parameter. You can have as many argument parameters as you want and they are typically used to refer to fields inside a FileMaker table other than the one you are actually querying.
Final Thoughts
I've used this technique in numerous applications including email correspondence, reports and even summaries within a portal to show a portal within a portal. I usually add the custom function to a project because I end up using it all the time. Let me know in the comments how you are going to use it.
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!
|
|
