finding unique records + non unique - repetitions

[3rdparty]

In simplfied terms I have a db with one field per record, 8 records. The values in that field are:

record value

1 10

2 2

3 45

4 10

5 2

6 5

7 23

8 10

I want to be able to select (find) records that, between then, have ONE occurance of each of the value in the field. ie I want the find to return

record value

1 10

2 2

3 45

6 5

7 23

It doesnt matter which of the records containing a non unique value is returned, providing only one instance of that value is in the returned list. ie the found set could contain record 1, 4 or 8 (all of which have value 10), but only one of these.

How can this be done via "find"? I cant seem to get any combination of fins and omits to give this result.

Alternatively is there any way of making a script or calculated field that looks at the value when it is input and asses (via the field's index??) whether it is a first occurance in the db of that value and sets a flag (or returns a 0, 1) in another field on the record accordingly? If this was possible the find could then easily be based on that flag.

i am going demented trying to solve this and guess that the solution is infact easy, but.....

thanks for any pointers

jw

[Ender]

If this is simply for display or printing, then a summary Count field and sub-summary parts may be easiest.

Do you really need to work with these records as a found set? Or is this more for a report?

[3rdparty]

i need to work these records as a found set. also worth noting that

- the db is grwoing continually, ie i am not dealing with a fixed number of records, and new records need to be findable based on the same criteria

- the records are not complete duplcates, there are many other fields in each record, many of which have unique values.

[QuinTech]

Hi, jw. One clunky way i can think of, in pseudocode:

Find all records

Sort by yourField

Go to the first record

Set someGlobal to the value of yourField

Begin a loop

[Lee Smith]

I started to answer this earlier, and then realized you are using v7 which I don't have. I like the way this Script works, but it is done in v6 and I'm not sure how that converts. Anyway, it is one that I modified from a Sample file provided by CobaltSky in this thread:

http://www.fmforums.com/threads/showflat.php?Cat=0&Number=46851

It requires these three Fields. (Note: that the YourNunberField is equal to your current field, and you will need to subsititute your field name for it in the Script)

Field Name

[3rdparty]

thanks for answers. will try this out in next few days and post back re fmp7 issues

(if any)

jw

[Lee Smith]

Hi JW,

[color:"blue"]

This question is directed to those familiar with v6 and v7.

If I attached a v6 file would it help, or muddy the waters?

In other words, could he add the two fields to his current database, and change the name of my Number field in the v6 file (so the script will have the correct name for the number field), and then import the script directly from the v6 file to the v7 File. Or does the file have to be converted to v7 first.

Is the conversion process to migrate a file from v6 to v7 as easy as it was migrate from v4 to v5? or is it easier to just type these steps in directly?

Just wondering.

Lee

[Ender]

If you try to import a script from a v6 file, FM7 will automatically prompt you to convert it. The conversion IS just as easy as earlier versions. Just rename the old file and a progress bar later you're done.

Of course, for fully developed solutions there's a lot more work to do, but for a sample file, it should be no problem.

[Lee Smith]

Hi Ender,

Thanks for the info, now I know.

Hi JW,

Here is the file. Just follow the Steps I outlined in my previous post, and it should make it easy to implement. But, be sure and let us know if you have problems.

The file is Zip, all you need is StuffIt Expander to open it.

HTH

Lee

SampleScriptforJW.fp5.zip

[3rdparty]

translates just fine to fm7.... and ends up as follows. note that some of the formulae get changed in the process but functionality is retained. many thanks lee for your help.

jw

Show All Records

Sort Records [ Speci?ed Sort Order: SampleScriptforJW::cSortField; ascending ] [ Restore; No dialog ]

Go to Record/Request/Page [ First ]

Set Field [ SampleScriptforJW::gScriptTemp.txt; SampleScriptforJW::YourNumberField ]

Go to Record/Request/Page [ Next; Exit after last ]

Loop If [ SampleScriptforJW::gScriptTemp.txt = GetAsText(SampleScriptforJW::YourNumberField) ]

If [ Get(RecordNumber) = Get(FoundCount) ]

Set Field [ SampleScriptforJW::gScriptTemp.txt; SampleScriptforJW::YourNumberField ]

Go to Record/Request/Page [ Previous ]

If [ SampleScriptforJW::gScriptTemp.txt = GetAsText(SampleScriptforJW::YourNumberField) ]

Go to Record/Request/Page [ Next ]

Omit Record

End If?Unsort Records?Go to Record/Request/Page[ First ]

Exit Script

End If?Omit Record?Else

Set Field [ SampleScriptforJW::gScriptTemp.txt; SampleScriptforJW::YourNumberField ]

Go to Record/Request/Page [ Next; Exit after last ]

End If

End Loop

Unsort Records

Go to Record/Request/Page [ First ]

[-Queue-]

It seems like it would be much simpler to create a calculation field of serial = selfrel::serial, where selfrel is a self-relationship based on the unique value field. Then you only need perform a find for 1 in this field to find unique records only.

[3rdparty]

Queue, I see your logic but dont see how to do it in practice. can you make an example (using the 8 record sample i gave in my first post) and post as an attachement? I see you use fmp5 and an example in this version is ok

thanks

jw

[-Queue-]

I don't have time to make a sample file right now. But just relate the field that contains 10, 2, 45, etc. in your example to itself. Then use this relationship as the selfrel in the above calculation. Someone else will probably help you if I don't get another chance today.

[3rdparty]

ok. any help appreciated. when I try what Queue suggests I just get a circular calc warning.....

jw

[3rdparty]

update.... got that to work (see attached), thanks Queue. The key field returns 1 or 0 depending whether the number in the data is a first occurance.

I like scriptless solutions and although this is just as effective as the solution proposed by Lee, this seems simpler.

unique.fp5.zip

[-Queue-]

Note that you don't need the If or a Case function since serial = selfrel::serial is a boolean test.

[cbum]

I have a related but variant Q: I need to count the number of cases matching certain criteria, but without counting duplicates (which are only duplicate for the unique identifyer, e.g. multiple records from the same patient, only count each patient once).

serial = selfrel::serial will identify all cases with more than one entry, but I still need to count them once...