Jump to content

Finding by Comparison Problem


This topic is 5722 days old. Please don't post here. Open a new topic instead.

Recommended Posts

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

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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 by Guest
Link to comment
Share on other sites

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

Link to comment
Share on other sites

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 by Guest
Link to comment
Share on other sites

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..

Link to comment
Share on other sites

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...

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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 by Guest
Link to comment
Share on other sites

This topic is 5722 days old. Please don't post here. Open a new topic instead.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share

  • Who Viewed the Topic

    1 member has viewed this topic:
    Daemon 
×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.