Finding by Comparison Problem

[RTINSC]

I am working with a database of 100,000+ property records. The file contains numerous fields of data and each record is anchored by the parcel number. Previous grantees and grantors are in the file so when I sort the file by parcel number, I get anywhere from 2 to 6 of the same parcel numbers, each with current and previous owner name. Each record also has a date field with the dates of previous transactions.

I want to be able to do a "find" with this file that will give me only the records with the most current date (current owner) for each parcel. Looks like this:

105-20-33-090 Smith John 02/09/1984

105-20-33-090 Davis Sandra 11/10/1989

105-20-33-090 Stuck Ima 02/23/2004

109-45-00-101 Jones James 10/12/1978

109-45-00-101 Rogers Matty 04/21/2002

The most recent records (underlined) are the ones I want to export to another file..

Any suggestions will be appreciated. RT

[Lee Smith]

Why don't you just do a find for Duplicates, and then sort?

Lee

[RTINSC]

Thanks for your help. I have tried that with my text editor. It removes exact duplicates when all fields are identical. In this case the parcel number is the same for several records and names and dates are different. I need a way to find the records with the most current date from those with same oarcel numbers.

[Lee Smith]

Okay, I think I'm confused as to what you are trying to say.

Is this Data,

105-20-33-090 Smith John 02/09/1984

in three fields, or all in one?

If different fields, then just do a [color:blue]Find in the field containing this part

105-20-33-090

using the exclamation point [color:blue]! in that field. This will pull all of the duplicates out of the mix. Then do a sort my that same field, and it should put the duplicates together.

If this data is all in one field, then you will need to parse it out.

Lee

[RTINSC]

Hi Lee..

Parcel numbers, names and dates are in separate fields in each record. The only common field is the parcel number of which there may be anywhere from 2 to 6 records with the same parcel number. I want to retrieve a list of records that containing only those records with the most current date. Using ! gives me a file of over 40000 dupe records.

I'm thinking the date field has to come into play somehow, giving me only the record with the most current date of the records with the same parcel number.

A list view looks like this:

105-20-33-090, Smith John, 02/09/1984

105-20-33-090, Davis Sandra, 11/10/1989

105-20-33-090, Stuck Ima, 02/23/2004

109-45-00-101, Jones James, 10/12/1978

109-45-00-101, Rogers Matty, 04/21/2002

Your help is appreciated. Roger

[Lee Smith]

Okay,

Yes the date field would be of help to you, first be sure that it is formatted as a DATE.

If so, then do a search using the ! and 1/1/2006...1/31/2006 as an example for the date range of your choice.

Then sort by the parcel number

Lee

[RTINSC]

Hi again..

Another way to look at this is:

Where Field 1 has duplicate numbers, "find" only the records that have the most current date based on the date in Field 3.

Thanks, Roger

[RTINSC]

Hi..

With respect to the date, I can't set a definite period as the dates are unknown in each record. The records may show several dates anywhere from this month to 10 years ago.

Roger

[Lee Smith]

Then set up a self relationship based on the number, sort by the date, and view by portal.

Lee

[RTINSC]

Hi Lee.,

My objective is to export only the records with the most current date (of the records that have the same parcel number). There may not be a way to do this other than going through the 40000+ dupe records and manually removing the records with previous dates, leaving the ones with most current date. This is very frustrating and may not have an FM solution.

Thanks,

Roger

[Lee Smith]

What you need is becoming a moving target.

[RTINSC]

From my first post:

"The most recent records (underlined) are the ones I want to export to another file..

Any suggestions will be appreciated. RT"

Hi Lee..

Sorry if I have difficulty explaining this problem. Identifying the dupes is no problem. I am looking for a way to select only the records with the most recent date from the records that have the same parcel numbers so I can export only those records to another file. Your help is appreciated. Roger

[RTINSC]

To better illustrate, this is how file appears in Excel or FM table. These entries are in separate fields, Parcel #, Name, Date. I want to identify and export only the duped records with the most recent date. Thanks for any help. Roger

001-16-01-004 GUSTAV SCHICKEDANZ TRUSTEE 4/5/2005

001-16-01-004 DANNY L GREENE & JULIE S GREENE 1/1/2003

001-16-01-002 CHASE MANHATTAN MTG CORP 9/1/2005

001-16-01-002 CLARENCE HARRIS & JOHN E GAMBRELL 12/4/1997

001-15-01-007 ANGELINA L RUTLAND & LARRY GRANT NEWMAN 9/1/2005

001-15-01-004 JENNIFER L RUVO 12/3/2003

001-15-01-004 OLIVER S BURRELL & TINA M BURRELL 6/19/2003

001-15-01-004 ALFRED C WARD 5/4/1998

001-15-01-004 MARY FAYE BUTLER & WILLIAM P BUTLER 11/30/1995

[comment]

Define a self-join relationship (to the same file), named "SameParcel", based on:

ParcelNumber = ParcelNumber

Sort the related records by Date, descending.

Define a calculation field (result is number) =

SerialID = SameParcel::SerialID

(Note: you must have a serial number field in the file - if you don't, create one and populate it by finding all records and replacing the field's contents.)

Go to Find mode and search for "1" (without the quotes) in the new calculation field.

[RTINSC]

Hi...Thanks

I have created and populated a field (SerialID) with serial numbers and set relationship. Created a field "SameParcel" and sorted the file by date descending.

In calculation box I set

SerialID

=SameParcel::SerialID

When I click OK, I get "Field Not found." I can see that this will lead to a solution.

[Lee Smith]

Hi,

I was in the process of making a reply when comment solved your problem. I have made a small demo of the file, perhaps you will have some use for it.

[color:blue] BTW, ignore the portal as it has not value.

Lee

FindRecentParcel.fp7.zip

[RTINSC]

Thanks Lee..Unfortunately, I get "Need Version 7.0 to view". I have V.6

Nothing is simple, is it? Roger

[Lee Smith]

hold on, I'll change it for you

[Lee Smith]

here you go

[color:red]oops

In my haste to change this to v5, I forgot the sort in the Relationship.

Open the Relationship box, and Edit the Sort Records by Date Ascending, as comment had instructed.

Lee

FindRecentParcel.fp5.zip

Edited January 26, 2006 by Guest

[RTINSC]

Lee..

When I open the file, I see the data but the Relationship/Define menu items are greyed out..

Roger

[Lee Smith]

There are no passwords attached to this file.

You might have to Get Info at the finder level and change the access from read only to read and write. Since I'm on a Mac, I don't know the process.

Lee

[comment]

1. Make sure the sorting is defined in the relationship itself. There's no need to sort the file.

2. If you want the latest record, sort the relationship by Date, descending, so that the latest record is the first related record.

3. In the calculation definition, clear all, then double click your SerialID field name, type the equal sign, switch the popup to related fields from "SameParcel" and double click the SerialID again. This should give you:

SerialID = SameParcel::SerialID

but the field/relationship names have to be as they are in your file.

EDIT:

With that many records, expect the find to take a while. There are faster methods, but they are too complex to go into now. If you need to repeat the process periodically, it's something to think about.

Edited January 26, 2006 by Guest

[RTINSC]

Can't open it. However, I see the way to solution.

In your file, I set up a field "SerialID" and "CComment". I populate SerialID with numbers ad sort by descending date. I set relationship "Same Parcel" to "Same Parcel".

Can you take me through Field set up for SerialID and "CComment". Calc as numbers? What should calc look like? Do I need to set up any additional field?

Thanks - Cup of Peets Java on the way to you..via email..

[RTINSC]

Thanks..You and Lee have my thanks. A cup of Peets Java will be on the way to you if you can send me an email address :. Thanks Roger T

[RTINSC]

I'm not quite there.

I have set up relationship Same Parcel Same Parcel.

Created fields SerialID and SameParcel. Have populated SerialID with numbers.

Set SamePaecel field as calc with number. Double clicked Serial ID (Main relationship), entered = sign, switched to SameParcelSameParcel relationship and double clicked :SerialID,

Result is SerialID=SameParcel::SerialID

I click OK and get "-circular relationship error."

Are these only fields I need to set?

Thanks...

[RTINSC]

Hi Comment..

I just can't seem to make this work. I am unable to see serial numbers when I set a field to Autonumber. Is this right? Or am I missing something?

I see your solution will solve the problem, but I am simply missing something.

[Lee Smith]

You need to "Replace" the field value in [color:red]SerialID, by First placing your cursor in the SerialID field.

Next, go to the Main Menu, Select the [color:blue] Records >> Replace Contents you will then get a Dialog Box, choose the [color:blue] Replace with Serial Numbers and then click [color:red]Replace

HTH

Lee

[comment]

Ok, from the top, step by step:

1.

Define a new Number field named SerialID. In Options, click auto-enter serial number.

Goto Layout mode and put SerialID on the body of the layout.

Go to Browse mode, click into SerialID, and select Replace.. from the menu (I'm not sure where the command is in ver.6). Choose replace with serial numbers, starting at 1, update serial number in Entry Options.

2.

Define a new relationship (to the same file) named "SameParcel", based on:

ParcelNumber = ParcelNumber

In the relationship definition, click "Sort the related records" and select by Date, descending.

3.

Define a new Calculation field named cUnique. In Options, paste this:

SerialID = SameParcel::SerialID

Select a result of Number for the calculation.

4.

Goto Layout mode and put cUnique on the body of the layout.

Goto Find mode, enter 1 into cUnique and wait for the result.

Edited January 27, 2006 by Guest

[RTINSC]

It helped a lot when I finaly figured out that I wasn't seeing the serial numbers!

You and Comment deserve Bronze Stars, at the very least. Thanks. I have it figured out.

Roger T

Enjoy the Java..

[RTINSC]

It helped a lot when I finaly figured out that I wasn't seeing the serial numbers!

You and Lee have solved a "tough" problem..I owe ya!

Thanks, I appreciate you taking the time to help me.

Email me for that Java!

Roger T