showing unique fields in portal

[Jim Lah]

I am using a self relationship to identify unique records based on several field entries. A simple calculation field sets text to equal "unique" or "duplicate" in each record based on the ID of the records found. This part works OK.

I then use a global field in another table - with the text "unique" entered and relate it to the calculation field above but the portal displayed gives unpredictable results. Sometimes it shows nothing, sometimes it shows both unique and duplicate entries.

The attached simplied file illustrates the problem. Can anyone identify where I am going wrong.

Jim

unique_lookup.zip

Edited June 12, 2006 by Guest
forgot to add file

[aldipalo]

There is no attached file.

[Søren Dyhr]

The calc'field can not be stored because it descripe a condition a relation away, therefore can't it act as foreign key of any relation!

sometimes it shows both unique and duplicate entries

This is a little hard to believe! You must have ignored the warling message, or the calc'field isn't a calc'field after all?

Presumably something in the vicinity of this:

http://www.kevinfrank.com/download/df_invalid_relationships.zip

Even with fm7 should it be done, as it always have been done, I guess that you wish to portalize the duplicates ...this is done scripted by a plain and simple search for duplets, is then taken to a layout where only the record ID is shown. Here do you issue a Copy All Records ....in you global field do you the paste this multilinekey created in the clipboard. The global field is then primary key for a relation to the recordID.

Checkout:

http://previews.filemakermagazine.com/videos/550/PortalResults_full.mov

--sd

[Jim Lah]

Thanks for help so far. I've now attached the file - and now, of course it gives different results. I can't get any of the matches to work.

I made some progress using a lookup of the calculated fields so that I could store the result and match fields again - but even this doesn't seem to be working now (but it did yesterday!).

I'm not using the copy all records route to remove duplicates as the duplicates also will also include data that I want to summarise - hopefully within the unique record by referencing.

Grateful for further assistance.

[Søren Dyhr]

Yes lookups will work, but issueing a relookup in say 20000 records, has a penalty to pay in a networked environment.

But it seems like what wish to do more or less what a subsummary report does for your printout, you wish to gather various instances of each match's important figures or numbers in the first. Last week did I do something similar to that, what it does is to write backwards to the record which holds the match for the first time.

http://www.fmforums.com/forum/attachment.php?attid/7565/

...where the issue in the template was to fill the holes in the cheese with something occuring down the line - hence the use of ValueListItems(.

In your case must you take first record in each groups value field and add the value from each of the following.

--sd

[Jim Lah]

Thanks. You are right about the application - but it seems strange to have to write a complex loop script when the desired values already exist in the print preview view as you suggest. I was expecting to be able to use the summary fields to be able to extract the data directly from an appropriately sorted portal.

Is this one of the quirks of Filemaker or are there other ways around this?

and - wow - I see what you mean by slowing things down - I just tried to perform a self lookup on a table with 300,000 records in it - made a cup of tea and it had barely completed the first 10k.

Edited June 13, 2006 by Guest

[Razumovsky]

Hi Jim,

I remember feeling the same way several years ago when first encountering a similiar 'obstacle.'

When you really consider the situation, it makes quite good sense. Do you want your computer's resources to be tied up by constantly evaluating evey single record against every single other record every second of every hour of every day to see if it is unique, or do you really only need that information at certain times (like when you want to look at it, or produce a report that factors that in)? It depends on whether you want your tea just before the big report, or every single time you change a field.

And the script can be as uncomplex as a simple "replace field" step, once you have gathered the records you want to consider in your found set.

It is also not quite true that "the desired value already exists in the print preview view." More precisely, the desired value is CREATED as you enter preview mode. How is it created? Well, through something quite similiar to a script, I would imagine...

Concerning your original topic, my approach would be to attach your Lookup TO directly to your Items TO Month=Month. This would allow you to view all items of a particular month through a portal which was set to sort by Unique to see all the unique items at the top. If seeing the non unique ones below still bothered you, you could create an unstored calc field that would hide them.

-Raz

Edited June 13, 2006 by Guest
tea...

[Søren Dyhr]

t seems strange to have to write a complex loop script when the desired values already exist in the print preview view as you suggest.

Other methods exists:

http://www.nightwing.com.au/FileMaker/demos8/demo803.html

--sd

[Lee Smith]

Soren, you're quoting one person, and responding to a different one?

Please use the tools available, by either using the Reply or Quote Button in the Body the post you are quoting, or use the persons name in the quote. You can do that by modifying the Quote by typing and [color:red]=Name in the Quote lead box, i.e.

=Jim Lah in this case, and it will then look like this.

TIA

Lee

[Søren Dyhr]

Sure, although it's easy to forget!

--sd

[Jim Lah]

Thanks for that - looks much more elegant - I'll have to study it a bit to get to understand properly.

regards, Jim

[Søren Dyhr]

Rubbish, it's the other way round it has to be a relative simple found set you're deling with, then can an unstored calc'field with a recusion which is trigged every one clicking outside a field.

http://fmforums.com/forum/showtopic.php?tid/176284/hl/subsummary%7CJohn%7CMark%7COsborne/tp/0/all/1/

--sd