Removing Duplicates -- Easier Way

[James Currie]

Saw the earlier posts from people trying to essentially create a subsummary report on a web page via CDML. I found them to be helpful (I'm totally new to this stuff and don't know much CDML yet -- or Filemaker for that matter), but a bit convoluted. Here's what I came up with that seems easier, and has the added bonus of not screwing up if you delete a record (an earlier suggestion used CurrentRecordNumber which can get a bit screwy). Hope this helps anyone trying to do the same thing. You can see an example on our site at http://www.ontaponline.com Just click on "events" on the left of the screen. If you need more info., feel free to email me.

How to Check for Duplicate Records

Uses:

1) Make sure you don't enter info. multiple times (example: enter two different shows at the same venue on the same night)

2) Used for website to return only one occurrence of a field when multiple records match a search criteria (ex: create a list of events by the date they are happening. You want the date to appear once, with all events that occur listed on that date underneath it)

Steps:

1) Determine what field you want to use to check for duplicates (ex: date the event occurs, business name, etc.)

2) Create a text field called "counter". Under options, select auto enter and check off serial with increments by one. Note: If you already have records in the database, tell it to start numbering with however many records you have, +1. IE: you have 10 records, tell it to start with 11. This is because an auto-entered result is only calculated when the record is created. If you have a small number of existing records, you can number them manually.

3)Create a new relationship entitled "findduplicates". This will be a self-join relationship, meaning you are matching fields from one record with fields from other records in the same database. So you are going to choose the database you are working out of. For the fields to match, use the field you determined in step one (the one you want to check for duplicates) and match it to itself.

4)Create a calculation field called "checkduplicates". The calculation should be: If(Counter=findduplicates::counter,"unique", "duplicate")

The "findduplicates::counter" is the way filemaker shows that this field is available via the relationship you defined in step 3. In the calculation setup screen, you'll see at the top left a list of available fields to use in your calculation. The fields that begin with double colons (: are the ones that are available via a relationship.

Once this is setup, for any new records you add, the field "checkduplicates" will auto enter either "unique" or "duplicate" according to whether the field you determined in step one is unique.

There are lots of modifications you can make to this to adjust it to your needs. In my case, I wanted to make sure that people entering live music calendars into a database didn't mis-type the date and end up listing multiple concerts on the same night at the same place (or accidentally type in info. that someone else had already entered).

How to Use this on the Web to Easily Create a Faux Subsummary Layout

Once you've set up the database as described above, it's a piece of cake to have a field only show up once, no matter how many records match it, while still listing other fields for each matching records (again, in our case, listing the date once and under each date all the shows that occur then).

Simply use the [FMP-If] tag. Create your [FMP-Record] tag. Inside of it, put an [FMP-IF] tag. Use the eq (equals) operator to check and see if the field "checkduplicate" contains the word unique. Then put the field you want to only appear once -- ex: [FMP-Field: date] -- and end the If condition --[/FMP-IF]. Now put any other fields you want to appear (band name, venue, etc.) and then end the FMP-RECORD. Here's my example:

[FMP-IF: checkdup.eq.unique]

[FMP-FIELD: date]

[/FMP-IF]

[FMP-FIELD: Venue]

etc.

The result is that you'll get the date only once, and the subsequent fields repeated under the date for as many records as contain that date.

[ February 18, 2002, 03:00 PM: Message edited by: James Currie ]

[Keith M. Davie]

link not working.

[Garry Claridge]

Site looks good and feels good.

Well done.

Garry

[Keith M. Davie]

Awesome, I'm posting one result, Garry is posting another.

I went right back and clicked again.

"File Not found

The requested URL was not found on this server."

[Garry Claridge]

Remove the . at the end of the url

Garry

[Anatoli]

Very nice work.

But why the details are in form:

Meet

the

man

behind

the

Chophouse's

legendarily

great

beer

:

[Keith M. Davie]

Ah yes, the period.

Yes, nice site. Yes I looked through D-Base. I hope you have been reading the security issue and the scripts issue threads now under way. I did nothing other than look.

[James Currie]

Just checked out all the feeback -- appreciate it. Most of the problems (links not working, odd formatting) have come up because I've been making adjustments to the live site (bad policy I know, but I use an IP relay service -- couldn't get a static IP from my service provider and we host inhouse -- so to be able to check out the appearance of things from the outside world I have someone else checking things as I make changes).

Keith: You asked about security -- I haven't checked that stuff out yet, but definitely need to. I don't have the faintest idea what I need to do to protect things. Any things that come immediately to mind, please let me know.

[Keith M. Davie]

James, the problem is with the link you posted. Go back and edit that post. Remove the period altogether or put a space between the url and the period.

As to the security issue, read the threads mentioned. Yes it will take some time, but it will open your eyes. FileMaker has been informed of these threads.

[James Currie]

Oops. Sorry about that period on the url.

Keith: I'm going to grab a couple of those books you mentioned in one of your other posts. Are those also good for security issues, or does anyone recommend anything else in particular I should read to learn the basics? (I'm going to go through whatever came with Filemaker, the stuff on this site and on Filemaker's site, but if there's anything else that stands out in your minds I'd love to hear about it).

Thanks again for your help.