Filter Out Duplicates Q

[nick_jp]

Hi Guys,

I know this must come up every other month, but I'm struggling with how to filter out duplicates in a particular portal.

The relationship is:

[Clients] can [Purchase] a [Title] 3 tables, purchase being the join.

In the [Title] table there is a field called interest area. I want to see, from the context of an individual client, a distinct list of interest areas from books they have purchased. File attached.

I've tried working through some of the other threads like this one: http://fmforums.com/forum/showtopic.php?tid/194751/post/288040/hl/distinct/fromsearch/1/#288040 but it's just not clicking how I can use the self-join technique to work here.

Is anyone able to give me couple of pointers here?

Thanks in advance

[Tim W]

Hi,

There is a custom function on Brian's site that addresses this issue.

Called UniqueValues (ValueList)

Here is the link: Custom Function Link - Brian Dunning's Site

HTH,

Tim

[nick_jp]

thanks but I'm after a portal, not a value list... can something like this be used to dynamically populate a portal?

[comment]

A portal to which table? You can easily place a portal to Titles on a Client layout - it will show a list of titles each customer has purchased. You can place the interest area field from Titles in the portal - but if the field has multiple entries, then it cannot be solved by relationships unless the entries are turned into records in a new table.

[nick_jp]

It's a portal to the Interest Area field in the titles table. Each title can have only 1 interest area so Interest Area is not a separate table. I would like to show a list of distinct interest areas, at the moment if the customer has 2 purchase with the same interest area, that interest area will appear twice.

[comment]

It's a portal to the Interest Area field in the titles table.

There, in a nutshell, is the problem AND the solution. A portal is always to a table, not to a field. Of course, you could use the Ugo method to build a complex relationship structure that would eventually produce unique values of Interest Area from the related Titles. But the simple (and correct) solution is to add a table of Interest Areas, make it a parent of Titles, and direct your portal to there. It is the correct solution, because your request makes Interest Areas a tracked entity in your solution - and therefore it deserves its own table.

Each title can have only 1 interest area so Interest Area is not a separate table.

That's a rather peculiar argument: one could also say that each purchase can have only one client, so we don't need a Clients table.