Unique Records in Portal

[RSpisketts]

Hi,

Can anyone tell me how you create a portal containing only unique items from a table.

I used the FM Help as a start - I now have a table occurence with a field correctly displaying either "Unique" or "Duplicate", but cannot make the jump to isolate those records with "Unique" in the field.

I'm trying to display the unique records in a portal, and created a self-join with a text field containing the value "Unique", but can't get the portal to work.

Any ideas ?

Thanks,

Roger.

[Inky Phil]

Your logic seems to be fine. Check that you got the fields from the right place when you placed them in the portal. They have to be placed from the self join relationship that you established and not just from the current file.

HTH

Phil

[comment]

See Ugo's method here.

http://www.fmforums.com/forum/showtopic.php?tid/153197

Edited February 27, 2008 by Guest
fixed broken link

[RSpisketts]

I've realised what the problem is...

The Unique/Duplicate approach in the help requires an "unstored" calculation field to provide the text "Unique" or "Duplicate".

If you then define another self-join with the above, against a text field with "Unique" in it, the portal doesn't work (doesn't restrict records to unique ones).

The only way round it is to provide another field (NOT unstored) for the self-join, but then this ceases to be dynamic.

I cannot see a way to solve this.

Cheers,

Roger.

Please see attached for further info.

Test2.zip

[Fenton]

You create an Unstored calculation field, like you said, and a self-relationship on a criteria field AND the parentID. But you produce the child ID as the result of a match, not generic text. This means that ONLY the unique records will have a child ID in that calculation field (childID being a unique serial ID in its table).

You can use this unstored calculation field as the left hand side of a relationship to a further table occurrence of the child. When viewing from the parent through the child to the final table occurrence, you will see only the parent's unique occurrences of children based on the criteria (which can be any field in the child table).

Probably easier to see than read about. It's Ugo's method, simplified.

Portal_UniqueRecords.zip

[RSpisketts]

Hi,

The test file I attached didn't really explain the whole problem I'm having.

Not only do I need to restrict records to unique ones alone (which your example adequately solves), but the first part of the self-join (the criteria-criteria join in your example) is actually an unstored calculation field.

This field, is part of a record created when a record is added to another table. It is an unstored text calculation of the form Table A::Field A. It appears that it has to be an unstored calculation, otherwise the field value doesn't change when the record in the other table changes.

As a result, the criteria-criteria relationship can't seem to determine whether it's unique of not.

In your test file, this problem is equivalent to making the Criteria field an unstored text calculation with any value.

Thanks,

Roger.

[Ugo DI LUCA]

Hi,

If the criteria field cannot be indexed, then for sure you cannot base your relationship on it.

It is not clear what you have in mind. You were referring to a single table but we're now discussing about a relationship to a second table. Moreover the field you'd be willing to use for the match comes from the first Table through a related calculation...and this calculation (Table A::Field A) just returns the "left hand" key of the first Table...but we have no clue on the nature of the relationship to the Table A.

We're going circles here...

[RSpisketts]

Hi,

I have used your approach, introducing a "Parent" table above my main table and it has worked.

Many thanks - apologies for the confusion.