Royalty payment database

[stefangs]

I have a file to calculate royalty payments from CD sales. It works fine, but the calculations are *really* slow (on a 9600/300 Mac), so I imagine that I have not made the most efficient code (so far at 2600+ records in the child file and counting). The master file holds one record per quarterly statement, the child file holds the line items, linked by the distribution date.

Payments are collected by the distributor in either US$ or Can$, but paid out to the artist in Can$. In some exceptions, even the artist is paid in US$. To accomodate all these scenarios, I have, among others, defined the following fields:

master file:

USD_conversion [number] - the conversion rate between US and Canadian dollars

child file:

currency [text, value list USD - CAD] - the currency received by the distributor

revenue_artist [number] - the royalty paid to the artist

payment_mode [text, value list: pay in CAD-pay in USD], the currency paid to the artist

CAD_revenue [calc]:

Case(

currency = "CAD", revenue_artist,

currency = "USD" and payment mode = "pay in CAD", revenue_artist * masterfile::USD_conversion, 0)

US_revenue [calc]:

If(currency = "USD" and payment_mode = "pay in USD", revenue_artist, 0)

The idea is to be able to convert the currencies where necesssary, using the same rate conversion as the distributor.

When I change the 'payment_mode' field in any one child record, the entire database gets recalculated, and that takes quite a bit of time. Would it be better to make the field USD_conversion a field in the child file and duplicate the number on each record? BTW, I have indexed/stored all fields where possible.

If you could follow this and can offer any advice, I'd really appreciate it, as I have a royalty meeting coming up and would like to use the data on my powerbook without holding everyone up. Regards,

Stefan

[LiveOak]

I think you might see a speed improvement if you use subsummaries and generate the totals in the child file. Even if the conversion factors were related values in the parent file (yes, these could be changed to lookups), this should speed up the process. -bd