Issues with Portal Filtering

[Brett W]

Quick question for all, I am trying to filter a portal in a somewhat difficult way and was hoping someone might have some advice.

I have a Calendar DB and a Lead Management DB. I want to display booked appointments from the Lead Mgnt DB in the Calendar for everyone to see through a portal. The two DB's are linked through a type and date. Now the complicated part is the Lead DB is of people, separated my numbers and letters; ex: 25823.AB or 35813.AD, 35813.BD, 35813.CD, ect. The extension is to id whether this is a part of a group of people or solo.

So I have the portal set up and it is now showing multiple booking lines for one family. If two people are in a family, I would only like to display one entry. Any ideas on how to filter the extra line?

[CobaltSky]

There are various ways you might approach your problem - depending on the structure of your data and the way you propose to use it.

For instance, if you have a table of family data, one way would be to simply set up a relationship between the calendar and the family table via the Lead Management table - so that families attending on a given date are filtered and presented in the portal.

Another way would be to use your existing relationship to the Lead Management table to retrieve the list of keys for people attending (eg using the List( ) function), then use a calculation with a custom function to filter the list to include only the first entry for each family. The resulting (filtered) list could then be used as a multikey source field for a second relationship via which only one person per family will be displayed.

I am attaching an archive containing a couple of rough mock-up files which show examples of each of the two methods I've outlined. Whilst either approach may require some adaptation to suit your purposes (and fit your structure), hopefully they will give you some ideas to work with. :D

FamilyFilters.zip

[Brett W]

Wow, this is just what I was looking for. Thanks for the attached files as well!! I am going to try the List Function way. My only problem is the family information and the appointment information is in the same table, set up before my time. I foresee issues with that, do you think this would be a problem? Either way, thanks for the timely response and your excellent examples.

[CobaltSky]

Hi Brett,

Glad you've found the examples useful.

My only problem is the family information and the appointment information is in the same table, set up before my time. I foresee issues with that, do you think this would be a problem?

For some purposes, such a structure will be non-optimal and may limit your options. For instance, the first technique I mentioned above would not be suitable with the present structure because it relies on the use of separate tables as a filtering mechanism. However, as I understand it, you should be able to meet the present requirement without substantial change to the table structure.

The custom function which is performing the filtering in the second example is set up to work on the basis that the family will always be identified by the first five characters of the personID. So provided that is true (and continues to be true) for all cases within your data, a calculation-based approach of this kind should suffice .

[Brett W]

That is pretty much what I thought. One quesiton about your custom function. I have a little more experience in C++( ie: while, for, do loops) and im trying to figure out how you cycled through the values in the list, without a loop function.

I am using a Let statement with value counts and get value functions while cutting up my list(), but when it comes to the Calc, I want to use something like a for or do while function (C++) to move through the values of the list to compare them. I can't find a function that does this, and wondering if you made a seperate custom() or have knowledge of one?

Thanks again.

[CobaltSky]

FileMaker's custom function syntax is proprietary and bears more similarity to syntax found elsewhere in the FileMaker calculation engine, than to other languages such as C++ - though there are some conceptual parallels. The custom function used in the second example uses a construct known as tail recursion to achieve the equivalent of a looping mechanism. Recursion is achieved by having the function call itself (ie it names itself within its own definition).

If you look at the function definition:

FamilyFilter ( personKeys )

------------------

Let([

vL = RightWords(personKeys; 2);

vN = GetAsNumber(vL) + 1;

vK = Left(vL; 5);

vC = ValueCount(personKeys);

vS = LeftValues(personKeys; vN - 1);

vQ = MiddleValues(personKeys; vN; 1);

vT = 1 - PatternCount(vS; Left(vQ; 5));

vX = LeftValues(vQ; vT);

vF = MiddleValues(personKeys; vN + 1; vC - vN - 1)];

Case(

vK ≠ "x‡›zg"; FamilyFilter(personKeys & "¶x‡›zg1");

vN < vC; FamilyFilter(vS & vX & vF & "x‡›zg" & (vN - 1 + vT));

LeftValues(personKeys; vC - 1)

)

)

------------------

...you will see that its construction is relatively straightforward. The first section simply plots and declares a number of interdependent variables to support the process. The main work is done within the Case( ) statement, wherein there are three actions.

The first action (which triggers only on the first pass) initializes a counter:

vK ≠ "x‡›zg"; FamilyFilter(personKeys & "¶x‡›zg1");

The second action triggers on all subsequent passes save the last and calls on the counter (vN) to cycle through the supplied values one at a time selectively eliminating those (vX) which duplicate the familyID part of the string (note that it calls self and increments the counter at each pass)

vN < vC; FamilyFilter(vS & vX & vF & "x‡›zg" & (vN - 1 + vT));

Then the final action, which is implicit (ie the escape condition arises when neither of the first two arguments is satisfied) discards the counter and returns the processed (filtered) list:

LeftValues(personKeys; vC - 1)

The syntax is somewhat different from what you've become familiar with in your C++ experience. However once you become acquainted with the basic operating principles, it is quite flexible.

As mentioned above, this particular formulation uses tail recursion (ie the accumulating result is passed intact through successive calls), so it is not limited by FileMaker's stack depth of 10,000. Instead it operates within the evaluation limit of 50,000 function calls. However that is probably immaterial in this instance - unless you expect to have more than 10,000 people with appointments on the same day... :wink2:

[Brett W]

RECURSION! Of course!

Didn't think that the values would carry through each call, but wow! Thank you again for all your help today. A++ Stuff!

Sincerely,

Brett

The Hollywood Group, Inc.