Script - Omitting ALL Records w/ Same Cust. No.

[HonkyCat]

Hello,

I have a script that gets a found set of records (reports) and then sorts them by customer number (field name Customer#). I want the script to go through the found set and omit EVERY record with the same customer number. I also have a global number field to "Set Field" Customer# into (for comparison).

My problem is that I can get it to omit the first several records with the same customer#, but then it still leaves one record (which had the matching customer# to begin with).

I tried doing a simple find with the criteria I'm looking for (including the "!" in the Customer# field and omitting those records), but that didn't work. I got a message that no records matched this request, but I know two records should come up (those two records have no other duplicate customer# on that day).

Does this make sense? Can anyone help me?? I can provide the script I have so far, but I was hoping someone might have a quick answer so I don't have to embarrass myself with the script I have so far.

Thanks!

Kristine

FileMaker Version: 6

Platform: Mac OS X Panther

[Ender]

If you have multiple criteria for your find, the "!" may not work.

One way to make your report omit duplicate Customer#s is to use Sub-summarize by Customer# part. This will show only the first record of each Customer# (make sure you sort by Customer# and remove the Body part.)

If you need to be more selective about which record to omit (like the one with the later Date should be shown), then you could try having your report script run through a loop to omit the duplicates:

Sort [ <by Customer#, Date (Decending)> ]

Go To Record/Request [ First ]

Set Field [ gCustNumLast, "" ]

Go To Record/Request [ Next ]

Loop

If [ Customer# = gCustNumLast ]

Omit Record

Exit Loop If [ status(CurrentRecord) = Status(CurrentFoundCount) ]

Else

Set Field [ gCustNumLast, Customer# ]

Go To Record/Request [ Next, Exit after last ]

End If

End Loop

Well, I'm not sure if that's exactly right, but something like that.

[HonkyCat]

Hi Ender,

Thank you, I tried what you said, but I don't think that's going to do what I need (unless I was doing it wrong...which is perfectly possible). Maybe if I explain better what exactly I'm trying to do, it might help.

The scenario is that we send results from a lab test to our customers via mail (generated from FMP). We want to start sending emails to some of those customers with basic data from their report just letting them know they've passed (just as a courtesy email while they wait for the full deal to come in the mail). We currently "PDF" these results and email them, but it has become VERY time consuming. So now I have created a calculation field with some of the information I want to extract from their "report", plus other text to go in the email. I'm using that calculation field as the text in my "send mail" script.

My problem is that in one day we may have 10 different reports to send to the same customer. Obviously we don't want to send 10 separate emails to the same customer. So I just want to do a find by a specific day for those customers who need these emails, then inside that find; whittle down the list to customers that have only ONE report for that day, then send the emails (without dialogue). Then in a whole separate find/script; find the customers for the same day with MULTIPLE Reports. I want the email to stop before it is sent in order for someone to manually enter the range of Report numbers in the email and send just the ONE email.

I hope I haven't over explained this!! Thanks for any additional help you or anyone else can give me!!

Kristine

[Ender]

I think I get it.

The answer I gave before would keep one record per set of duplicates. It looks like you want to omit any record that has a duplicate Customer#.

You can modify the script I showed before to do this:

Sort [ <by Customer#> ]

Go To Record/Request [ First ]

Set Field [ gCustNumLast, Customer# ]

Set Field [ gCustNumCount, 1 ]

Go To Record/Request [ Next ]

Loop

If [ Customer# = gCustNumLast ]

Omit Record

Go To Record/Request [ Previous ]

if [ gCustNumCount = 1 ]

Omit Record

End If

Set Field [ gCustNumCount, gCustNumCount + 1 ]

Exit Loop If [ status(CurrentRecordNumber) = Status(CurrentFoundCount) ]

Else

Set Field [ gCustNumLast, Customer# ]

Set Field [ gCustNumCount, 1 ]

Go To Record/Request [ Next, Exit after last ]

End If

End Loop

Another idea is to automate the process of putting the report numbers together on the email. I wouldn't think this would be too hard.

FileMaker Version: Dev 6

Platform: Mac OS X Panther

[BobWeaver]

For this it may be better to use a self join relationship to count how many of each customer there are for the day.

Create a calculated field "CustomerDate" that is the concatenation of the customer ID and the date:

CustomerID & " " & Date

Create a relationship "sj" with the CustomerDate field on both the left and right side of the relationship.

Make another calculated field "CustomerCount" with the formula:

Count(sj::CustomerDate)

Now all you have to do is do a find where

Date = Status(CurrentDate)

and

CustomerCount < 2

As this will be an unindexed search it may be faster (depending on how many records you have) to just search for all records of today's date and then use a looping script to omit records where CustomerCount > 1.

[Ender]

Bob's solution sounds good too. It's cleaner.

If there are a large number of records in the file (more than 5,000) then the unindexed find could be slow. If there are more than 100 found records for a given day, then the looping script could be slow. You might benefit from trying both and timing them for the fastest method.

[HonkyCat]

Hi Bob and Ender,

Thank you both sooooooooo much for your help!! I appreciate it!!!

Bob, your solution ended up working the best for me, except for one very strange thing (and I had my boss look at the script just to make sure I'm not going crazy).

When I create my script and tell it to omit records where CustomerCount > 1, it does not do it right. I have a batch of 18 records I'm practicing with and I know that the result (after omitting all the multiple records) should be 2 records left. Instead, it leaves 10 records. However, when I do the reverse; omit records < 2, then it works perfect. It leaves 16 records. Do you have any idea why this would be??

Here's the "omit" part of the script....

Got to Layout ["Detail View"]

Got to Record/Request/Page [First]

Loop

If ["CustomerCount[calc] > 1"]

Omit Record

End If

Go to Record/Request/Page [Exit after last, Next]

End Loop

[-Queue-]

Change

If ["CustomerCount[calc] > 1"]

Omit Record

End If

Go to Record/Request/Page [Exit after last, Next]

to

If ["CustomerCount[calc] > 1"]

Omit Record

Else

Go to Record/Request/Page [Exit after last, Next]

End If

When you omit a record, you're moved to the next record automatically. If you go to the next record after that, then you're skipping one record after each omitted one.

[Ugo DI LUCA]

Also if you use some calculation of type Choose(Count(Sj::CustomerDate)>1,"",ID) even if unstored, you could perform a copy all records script, which is pretty quick.

FileMaker Version: 6

Platform: Mac OS 9

[HonkyCat]

Thank you everyone!! It works great!!

Kristine

[HonkyCat]

I have one last question (or so I hope). Like Ender said above, maybe I could automate the process of putting the report numbers together on the email. This way the email can go without stopping for someone to put the report numbers in.

Does anyone have suggestions on how I can do this? Sometimes the report numbers are in order and sometimes they are not. So for example, I could not always put 04-00001...04-00015. They could be 04-00001, 04-00005, 04-00016, and so on. The calculation or script would have to pick each report number up from each record.

Thanks in advance!

Kristine