4 Replies Latest reply on Apr 23, 2010 5:44 PM by rgriff

# Calculating a value only for first occurrence of a field value

### Title

Calculating a value only for first occurrence of a field value

### Post

Greetings! I was a long-ago FileMaker Pro developer -- as in version 5, I think. I last did anything substantive in 8.5, so it's been a while. Now, however, I've got a need for a database to store a customer database for our products. We sell online, and pay a fee per transaction, plus a percentage of the revenue. So far so good...

The data I get from our online sales company just reflect gross revenue. Even worse, if a customer buys four products, that shows up as four rows in the downloaded file. Each row of a multi-unit sale will have the same internal transaction ID. So in Excel, it was relatively trivial to create a calculation that only creates a transaction cost when the transaction ID changes.

In FMPro, though, I'm uncertain as to how to do that. My recollection is that I somehow need to use a summary field, but that's about as far as I got. In essence, I need a calculation that is as follows:

If (first occurrence of TransactionID) then (calculate transaction cost) otherwise (leave blank)

All help appreciated;

-rob.

• ###### 1. Re: Calculating a value only for first occurrence of a field value

No help? Is this not possible? Did I not provide enough info? Pointers to resources that can help?

thx;

-rob.

• ###### 2. Re: Calculating a value only for first occurrence of a field value

A summary report that groups your records by transaction ID should do this. A summary field placed in a subsummary part will compute your subtotals for each sale.

Here's a link to a simple tutorial on setting up summary reports that you may find useful:

Creating Filemaker Pro summary reports--Tutorial

• ###### 3. Re: Calculating a value only for first occurrence of a field value

Thanks for the help, but ... I think I didn't write my request very clearly :). I have a customer database that comes in as a large flat file (so I have but one table). In that file, each purchase activity gets one line, even if it's two or three things bought in one transaction. The way our system works, I am charged only one transaction fee for such purchases. We also pay a percentage-of-sales fee, and that applies to all three rows.

So, assuming someone bought three items, I have this:

NAME ===== TRANSID ===== PRODUCT ===== QTY ===== AMT

Ted Smith  12345         WidgetA       1         15

Ted Smith  12345         WidgetB       1         10

Ted Smith  12345         WidgetC       1         20

Alice Ann  12346         WidgetA       1         15

What I need to do on each record is add two fields: one showing transaction cost, one showing percentage-of-sales fee. The seond one is simple, because it applies to every row. But the transaction cost should, in the case of Ted Smith, only be applied against one of the three rows; the other two are "free" transactions.

I can identify such transactions because the TRANSID is identical. But what I can't figure out how to do is create a field -- or use a script to place a value in a field -- that will correctly show just one transaction fee for multi-line related activities.

Does that make more sense?

regards,
-rob.

• ###### 4. Re: Calculating a value only for first occurrence of a field value

To follow up on my own post, I found the solution here:

http://www.fmforums.com/forum/showtopic.php?tid/212787/pid/349953/post/last/m/1/

I used the script in the last entry in that thread to set a 'unique' flag on each unique record, then added a calculated field based on the flag value.

-rob.