Summary based equations

[jdhunter]

I am having a problem with a summary equation that i'm using. Here is the problem.

I have a layout page that views all the orders i enter each day. It is in list mode so i can see a summary of each job. In the Footer I have a Summary field that gives me the total cost of the orders. Then i have another field (also in the footer) that allows me to add a volume discount to the orders, such as %2 discount on the total. I then have another field(caculation) that multiplies the total due * the discount. The problem i'm running into is that my final caculation changes according to which record is selected in the body of the layout. So if the first record is highlighted it gives me that records total - the percentage discount. If the next record is highlighted it gives me the first two records total - the percentage discount and so on. It would be nice to fix this problem even though for now i just select the final record and i get the grand total.

Thanks,

Jonny

[Søren Dyhr]

I'm afraid it's a structural problem, the found set is all sales that day, are every customer recieving the same discount? If it really were this way could turning the dicount field into global to do the trick.

volume discount to the orders, such as %2 discount on the total

...aren't this going to one specific client who have done an extra efford to gather a larger number of order?

Such found sets are usually established by relating the orders to a invoice and a client, ustilizing 3 tables, where the record in the invoice table holds the granted discount, which then can be seen a relation away say the summary report that passes as issued document to the client.

An alternative way to approach it is if you say that placing orders on specific days is what qualifies to a certain discount, then must a record be made for each day in a discount per day table, where we then link on the order data between the tables.

Even combinations are possible, where each salesperson grant's his discounts based on his judgement and management have made specific dates where extra dicounts are optainable ...but each belongs to specific records each in their own tables.

Then could it be tempting to make the summaryfield a primary key pulling discounts from a price matrix, which is going to land in an unstored calcfield due to the nature Summary fields exhibits via GetSummary( . But carefull here, invoices are historic documents so the result must be stored in a place where it isn't likely to change later if say a dicount structure is changed.

But I couldn't help experimenting with the getting the summary of the found set pick a relevant discount, but there exist some freshing issues which need to be scripted somehow, but the result is live as long we alter values in the found set. But by and large was it some stay thoughts occuring while answering ...there are probably event better approaches to be used here, although the aggregate functions not are made for larger found sets, might it prove at better way here?

--sd

test.zip

[jdhunter]

So your final solution worked pretty well for me. Since it meant not restructuring the database too much. I only use one table for invoices, basically found sets according to client and a couple of other sets and this allows for that type of solution. Not the best solution now, but when i wrote the database i didn't know better. Anyway thanks for the help.