FM - Giving Customers Ability to Filter Portals via Calculations

Posted on 2010-09-17
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
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
LVL 28

Assisted Solution

lesouef earned 200 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 300 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

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Many companies are looking to get out of the datacenter business and to services like Microsoft Azure to provide Infrastructure as a Service (IaaS) solutions for legacy client server workloads, rather than continuing to make capital investments in h…
When table data gets too large to manage or queries take too long to execute the solution is often to buy bigger hardware or assign more CPUs and memory resources to the machine to solve the problem. However, the best, cheapest and most effective so…
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

749 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