Calculation

[sconolly]

We use Filemaker 5.5 to book advertising for a community newspaper. Advertisers get a free listing on our web site business directory. Salespeople book ads in "Ad Order.fp5", and scripts enter the appropriate data in "IP Bookings.fp5". Other scripts relay the data to a SQL Server using the Execute SQL script step. It all works, except:

When advertisers buy upgraded web packages ("B" or "C") Ad Order.fp5 adds a surcharge to the print booking through a relationship Ad Order to IP Booking based on Cust ID.

My problem is with advertisers with more than one print ad (more than one record in Ad Order). All the Ad Order records for that Cust ID get the surcharge.

Any ideas for limiting the surcharge to a single Ad Order record?

[andygaunt]

Can you have a field in the customer record as a flag that once an ad has been placed (namely the first one).

Then you can add into the script

If (IsEmpty(Customers::flag) )

Set field (Customers::flag, 1)

End If

If (Customers::Flag=1)

Set Field Surcharge, ""

Else

Set field Surcharge, Add surcharge amount)

End If

where customers is the relationship via id.

[sconolly]

Thanks, Andy. That is a possible solution, I think. Right now the surcharge is computed simply by a calculated field:

If(IP Booking::Pkg Class = "B", 7,if(IP Booking::Pkg Class = "C", 12, 0))

This "Surcharge" field is in my Ad Order file. If possible, I'd prefer to solve the problem with a plain calculation rather than a script.

[andygaunt]

Hi,

you said in your first post you are running some scripts.

So use these to set the flag field in the customers record.

Then you can simply add that into your surcharge calculation to make sure it only sets the first ad with a surcharge.

You know,

If(IP Booking::Pkg Class = "B" and IsEmpty(Customers::Flag), 7,if(IP Booking::Pkg Class = "C" and IsEmpty(Customers::Flag, 12, 0))

How about that. Would that work for you.