FM - Giving Customers Ability to Filter Portals via Calculations

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.
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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.
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.

Experts Exchange Solution brought to you by ConnectWise

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
rvfowler2Author Commented:
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.

rvfowler2Author Commented:
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.
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:-)
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.