Omitting all records with the same Id number

[forgedbyfire]

My question is how can I omit all multiple records with the same ID?

I have a table that has service records in it. The records spans may years. I would like to drill down and see who was added and who dropped.

I would like to simply pick a date range (I.E. 01/01/2008...12/31/2009) and omit all records with a customer ID that come up twice. This way anyone left over either dropped us last year to this or was added this year.

Omitting multiples only removes the extra records and leaves the first record.

[mfrapp]

i dunno if i understand right...

in a script try doing a first find for your Date range

with the foundset return to find mode and

put "!" in your ID field and select Omit

and then "Constrain Found Set"

hope this helps

[forgedbyfire]

That only omits the extra records also. Lets say I have 4 records with the customerId of 2. When Filemaker omits multiples it leaves the first record with the ID of 2. I want to omit all the records with the ID of 2, not just the last 3.

What I am trying to accomplish is to obtain statistical data. Typically I run a list of a certain type of call (Record) and then export them to a spreadsheet, where I sort the list by the foreign key (CustomerID). I then have to manually look and delete doubles per say. The left over records let me know who used that service last year and not this year and who used it this year.

Can this be done logically with an AND logical operator. For example, I run all records by date 2008...2009, If there is a way to omit records with the same ID if they have an occurrence in both 2008 and 2009.

[LaRetta]

where I sort the list by the foreign key (CustomerID). I then have to manually look and delete doubles per say. The left over records let me know who used that service last year and not this year and who used it this year.

You are missing a Customer table. If you had a Customer table, you would relate as:

Customers::CustomerID = thisOtherTable::CustomerID

... then you simply (from your customer table) count how many related records you have as:

Count ( thisOtherTable::CustomerID )

Of course you can hon-yok the process other ways but the bottom line is it indicates you are missing a ONE side to your MANY and will continue to bump into problems if not properly related. :wink2:

[forgedbyfire]

CustomerId is a Primary key from another table called customer list. I use this key to connect the 2 tables. The records I am sorting are the types of calls we do for these customers.

For example in the spring we open the customers pools and in the fall we close them. The types of service we do for these customers is designated by field called Servicetype. So in order to see who added and who dropped I set a field called datescheduled: to */*/2008...*/*/2009. I also set a field called Typeofservice: to Opening (or Closing) depending on what I am looking for. The result is all the "openings" done those 2 years.

I then want to omit the records with a Id that has been scheduled in both 2008 and 2009. Any records left over with just a 2008 date will indicate they did not use our services in 2009. Like wise any customers with a 2009 date only would indicate a new pick up.

[LaRetta]

You can use a relationship to restrict and identify then. But, after playing with both relationship and find, I feel a find is still the simplest.

You must restrict to the date range before you constrain further using !. After constraining to !, omit those records using Show Omitted. Then restrict THAT found set down to your range again.

The reason is that 1) you may have customers prior to 2008 who would end up matching as duplicate to same customer in 2008 or 2009 and 2) you might have a customer in 2008 with an opening service AND a closing service within same year which would appear a duplicate and it wouldn't be ... so you must include TypeOfService (Opening). I noted throughout the script but the concept is pretty simple (see attached). If I still missed the boat, speak up. :smile2:

OmitDups.zip

[forgedbyfire]

I greatly appreciate the time and effort you put into this. I have imported and executed you script to a the letter.

The problem I have come across is the script only sorts the clients who only used us 1 time in either year. I have reworked the script numerous ways and still cannot produce the outcome I am looking for.

Any Ideas on how to extract the data I am looking for?

[LaRetta]

the clients who only used us 1 time in either year.

I believe you asked to know which clients used you last year but not this and which clients used you this year and not last.

What I will need is some data which shows exactly what your fields contain. And then I'll need you to tell me exactly (out of the sample data) which records should be the found result (and what business rule you used to make that determination). Then we'll be able to fine-tune it for you.

Please produce a sample file because I also need to see how you are structured (if you can't zip and attach your working file). I suspected that you were missing something which would make this much simpler for you but I couldn't put my finger on it without more information.

Edited November 23, 2009 by Guest
Added paragraph

[bruceR]

Try this. See script Find V2. Sorts by ID; Puts ID in variable; uses GetNthRecord to see if next record has same ID then omits all with this ID.

OmitDups.fp7.zip

[forgedbyfire]

Once I plugged everything in it worked great. I greatly appreciate all the help I have been given. It works as I would like it to and I have a deeper understanding of other tools I have available to me through file maker.