Eliminating duplicates within found set

[OneTime]

I am working on a database with two tables SHIPMENTS and COMPANIES. They are all connected by the field Account #. In the Shipments table I created a Counter (Auto-enter serial #) field and a Check Duplicates field. In the Companies table I created a calculation field Min(Shipments::Counter). Where the value in minimum is equal to the Counter field, the Check Duplicates field returns Unique and when not Duplicate.

So far so good. This works when I Omit duplicates across the whole database, however when I try to do this for a found set it omits a number of Companies (first and subsequent hits) all together.

Any suggestions? Is there a way to debug a script by seeing what it returns for each and every record it treats? I know this last approach would be extremely time consuming, but I am worried about leaving out customers on important mass mailings...

Thanks

Edited August 19, 2010 by Guest

[Fitch]

Maybe you have only "Duplicate" shipments in your found set?

You have FM Advanced, so use the debugger and the data viewer to step through the script. And/or post the script here.

[OneTime]

Thanks, I was able to get on a computer with Advanced and I stepped through the script. My problem was that there were already values in the counter field from previous tests BEFORE I got the found set and entered new serial numbers. So some of my minimums were not in the found set and therefore gave me back Duplicate.

I have been unable to clear/delete the value in the counter field across the whole database. Any suggestions? Right now to get around the problem I am replacing field contents with 9999999 (my database currently has some 10000 records). I know this isn't very elegant, but it will work for another 5 years haha.

[Fitch]

I'm not really clear on what this Duplicates business is all about. Why do you have duplicate records in the first place?

[Vaughan]

a database with three tables Shipments, Shipments 2 (with a self-join to Companies) and Companies

Err, if Shipments joins to Companies then it cannot be a self-join. By definition.

So far so good.

I don't think so. The business with the counter field is confusing.

Answer this: how do you know when a record is a duplicate?

[OneTime]

The counter field is in the shipments table. I populate it with a serial number across the database. I also have a calculation field in the customer table that calculates the minimum of the values that have populated the counter field for any given company. Sorry, I had tried a self-join earlier to identify duplicates, but abandoned that approach. What I have is just a regular relationship between COMPANIES and SHIPMENTS by account #.

EG. COMPANY A (account #1) has 5 shipments from Rotterdam out of the 10000 shipments in my database. When I populated the counter field for this found set the counter numbers happen to be 17, 120, 500, 879 and 1210. Therefore the minimum that COMPANY A's minimum field will show is 17. My Check Duplicates field will compare the minimum 17 to the counter values for the same company and if Min=counter then it is unique and the rest are deemed duplicates.

HERE IS MY PROBLEM: still with the example. Lets COMPANY A has the 5 shipments above returned from a found set such as shipments from Rotterdam. And the counter values listed are from replacing field contents with serial number across found set. Unless I am able to clear the counter values across all the database before I get my Rotterdam found set there could be a COMPANY A shipment from somewhere else that has a value less than 17 (and therefore MIN will be less than all 5 counter values in found set) and all 5 in the found set will be deemed Duplicate and therefore COMPANY A will be completely omitted. (My workaround this problem is listed above in Post#363401, but there must be a better way.)

The reason I have to do this is because there may be shipments from 5 different ports and sometimes we have to mail out notices just to those companies that use Rotterdam for example. If a companies has had 100 shipments from Rotterdam, I don't want to send 100 emails, just one.

I am very much self-taught so please pardon my confusions with some of the concepts.

Edited August 19, 2010 by Guest

[comment]

If a companies has had 100 shipments from Rotterdam, I don't want to send 100 emails, just one.

I think you are making this much more complicated than it needs to be. In order to find companies with shipments from Rotterdam, go to a layout of Companies and search the related origin field for "Rotterdam". In a scripted way, this would look like:

Go to Layout [ Companies ]

Enter Find Mode []

Set Field [ Shipments::Origin ; "Rotterdam" ]

Perform Find []

Alternatively:

Go to Layout [ Shipments ]

Enter Find Mode []

Set Field [ Shipments::Origin ; "Rotterdam" ]

Perform Find []

Go to Related Record [ Companies, Match found set ]

[OneTime]

There is no field with Origin information. The information from the container manifests gets pushed over as a tab-delimited file. To find Rotterdam shipments I need to search by a number of markers across Bill of Lading, House Bill of Lading, Vessel etc. to separate from rest of worldwide imports. That part I am having no problem with.

My problem comes down to separating out my duplicates. I am able to do it, but I need to figure out how to clear/delete the contents from the Counter field across the whole database before I start my search. This is probably something really simple, yet I was unable to do it. My "fix" of replacing all the Counter values with 9999999, avoids the problem mentioned above but is REALLY SLOW.

Basically, at this point all I am asking is: does anybody know how to delete/clear the contents of field across the entire database?

[Fitch]

The Replace Field Contents command should not be all that slow for 10k records.

However, I think you're missing the point that Michael (comment) is making.

From what you described, the goal is to take a found set of shipments, and send a single email to each of the companies related to those shipments. Is this correct?

If so, as M. suggested, starting from the shipments, if you go to the related companies and use the "Match found set" option, you'll end up with the found set of companies that you need to send your emails.

[comment]

There is no field with Origin information.

It doesn't really matter. The point is either search related fields from the Companies layout, or do a GTRR [Match found set] after finding the shipments. In both cases you'll get the same result, and you don't need to deal with duplicates in the found set.

Another option is to sort the found shipments by AccountID, and loop through the found set.

[OneTime]

Go to Related Record [ Companies, Match found set ]

I have been trying to do just that for about two weeks. I cannot believe that it was already a pre-fabricated FMP script. Thank you Michael and Tom.

Just out of curiosity, how would that Loop script work for this case. I looked up Loop in FM Help but I couldn't figure out criteria I should be using for this example. If you are ever in Bermuda let me know. I owe you guys some beers. Cheers.

[comment]

The loop can be done in two ways:

Long, but simple:

Loop

If [ $accountID ≠ Shipments::AccountID ]

Set Variable [ $accountID ; Shipments::AccountID ]

// SEND MAIL HERE

End If

Go to Record [ Next, Exit after last ]

End Loop

The shorter way jumps directly to the first record of the next group, using a summary field and the GetSummary() function to calculate the size of the current group.

[OneTime]

OK thanks. That's good to know. Your help has been invaluable.