Link to home
Start Free TrialLog in
Avatar of rvfowler2
rvfowler2Flag for United States of America

asked on

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.
-PortalFilterIssue.JPG
-PortalFilterIssue2.JPG
-PortalFilterIssue3.JPG
SOLUTION
Avatar of Member_2_908359
Member_2_908359
Flag of France image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of rvfowler2

ASKER

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.

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:
1
11
2
3
32
5

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:-)