rvfowler2
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
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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:-)
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:-)
ASKER
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.