Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium


FM - Giving Customers Ability to Filter Portals via Calculations

Posted on 2010-09-17
Medium Priority
Last Modified: 2013-11-15
Learned how to use global fields (and use a calculated field to check if the global is empty) to allow customers to filter portals.  In the attached, you can see it worked well on the upper left side.  I've selected a customer code and it filtered by that code.  However, I wanted to also filter by our four departments:  W-Properties, W-Vacancies, W-Businesses, and U-Vacancies.  

However, because typically an Agent's note will only apply to one property or vacancy or business, most records have an empty field in one of the depts.  What happened when I added the first dept to the Relationship graph is that only those records that had a code in that field appeared in the Notes portal.  The rest disappeared.  If I add the other three more and more records disappear.  How do I force records to show even if their Prop Code or Vac code is empty (NOT that the global filter field is empty, which is already accounted for).  Thank you.
Question by:rvfowler2
  • 2
  • 2
LVL 28

Assisted Solution

lesouef earned 800 total points
ID: 33722730
basically you'd like '' to relate with '', but it does not. fm considers an empty field a bit like null in other languages.
the only workaround I can see is to populate the empty records with a string like "not specified" (can be auto-entered to be present untill a user alters it), and use this expression in your global field to link these as the others.

Accepted Solution

ErikCayre earned 1200 total points
ID: 33723812
Basics (just to frame my comments, not suggesting you don't already know...):

-a portal shows records from ONE directly related table
-all constrains defined in a relationship are evaluated with AND for EACH individual record

So you define constraints in the Define Relationship dialog. Some will include records, some will exclude records. Since the constraints are ANDed, each individual record to be shown in the portal must match ALL constraints. Ie. you can't get records to show which match just some of the constraints and not others...

What you see now is records disappearing because they don't match ALL constraints.

I don't have enough information to precisely describe how to fix your particular problem, but this is the direction I think you want:

In the related table (notes), make a calculated field to be used as target for the relation, along the lines of:

portalfilter= ("All"
If (W-Properties <> ""; "¶" & "W-properties"; "") & 
If (W-Vacancies <> ""; "¶" & "W-Vacancies"; "") & 
If (W-Businesses <> ""; "¶" & "W-Businesses"; "") & 
If (U-Vacancies <> ""; "¶" & "U-Vacancies"; "")

Where W-Properties is a field containing something, if that specific record is pertaining to W-Properties.

The portalfilter field will then contain the name of whichever dept. is actually represented in that particular record, and the value "All".

In the table starting the relation, you can add whichever dept. you wish to filter for in the correct field used in the relation dialog.
Assuming you have a portalfilter field here too, you can add the name(s) of the dept. you want shown to that field, or if you want all, you add "All". The relation will match either all notes, regardless of which dept. is concerned, or just the dept. the user chose to have shown...

I see on your layout that you offer the user to click a dept. name to have only that dept. shown. A click on a name should add the name of that dept. to the portalfilter field. You can use SetField, if the user only needs to see one dept. at a time, or you could make it more advanced, having scripts which add/remove the dept. name with each click if you want to offer the user to see more than one dept. at once.
You can add an Auto-Enter setting to the field, which will add "All" in case the field is cleared.

Author Comment

ID: 33738086
After reflecting a bit, I did it a less elegant way.  Since 3 of the 4 fields were serial numbers, I simply inserted a 0 in all empty fields.  I then put a conditional formatting on each field so that if they were zero, the text was white (though it still shows up a bit on the alternate portal records).  I then set all 4 fields to auto enter 0 on the creation of a new Note.  

Of course I'll award the points since you all moved me most of the way; however, just wondering if you see any flaw in what I've just described.  Thanks.


Author Closing Comment

ID: 33739135
Thanks.  Tested my derivation of your solution and it worked great.  (One caution:  make sure that you are consistent across all calcs and fields that you render it a number or text.  I forgot that one of my 4 fields needed to be rendered as text, since it had a mixture of text and numbers, and this affected the portal filter.

Expert Comment

ID: 33739537
Re: your derived solution, I don't see any flaw related to the stated intent. I would need to know more about how the solution is used, to uncover any potential unwanted side-effects:-)

Text and Number fields can both contain ...text and numbers, but FMP treats them differently internally.
For one, sorting a text field containing numbers will yield:

Also, you can only have line-delimiters "¶" in text fields, which comes in handy for relations, where you need multiple matches with different keys.

A text key field could contain "2¶4" and be used to show both related record id 2 AND 4 simultaneously. This lets you match select related records although they don't have identical values in any field. There are many uses for this, such as a portal filter where the user can select one or more options from a checkbox field, to have groups of related records shown in a portal. Eg. you could have an invoices portal on a customer record with a checkbox field to show all, only overdue, etc...

So yes! Get the datatypes right:-)

Featured Post

Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this blog post, we’ll look at how using thread_statistics can cause high memory usage.
In this article, I’ll look at how you can use a backup to start a secondary instance for MongoDB.
This is a high-level webinar that covers the history of enterprise open source database use. It addresses both the advantages companies see in using open source database technologies, as well as the fears and reservations they might have. In this…
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
Suggested Courses

579 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question