Calculation won't work!

[Mark Appleby]

I know that I am going to feel very foolish when I get the answer to this one but I am trying to create a sales entry system. Quite simply my relationships seem to be OK as I have lookups that work in a portal. But one of these will not work. It is driving me crazy!! It is a simple =field value calculation from the same database. Can anyone tell me why it isn't working!

[trevorg]

Is the calculation indexed? Try turning indexing off.

[Mark Appleby]

No. it is not indexed.

[trevorg]

Are the calculations operating on the same field types?

i.e. Number fields and Text fields?

If that's not it, I think a little more detail is required to make a better assessment.

[Mark Appleby]

Field types are correct as fdar as I can see. To give a little more detail without boring you. I have a sales database which is related to sale items, this in turn is related to Main stock file. On my form I have a portal into which I enter the stock reference, Price of item is a lookup from the stock file which works OK. Quantity is an Auto enter of 1 this is OK. Subtotal is a calculated field which is Qty*Price this too is OK non indexed. Total is the same calculation as Subtotal but is a Number field. This is where I am having the problem. I have tried both Qty*Price as my calculation and =Subtotal but to no avail. This field is not indexed, and is a number which is trying to calculate on number fields. I know that the answer to this probloem is going to make me seem really thick but I can not see the reason.

[trevorg]

Ok, I think I understand the problem.

When you are saying total. I think you are talking about a "Grand Total" of all the sale items.

I think you need a calculation field called Total that is set to: Sum(Subtotal)

This is assuming you have a Subtotal field that is calculating Qty*Price for each Sale Item. These fields are in the Sales Items DB, The total could be in the Sales DB, in this case you need to have the total calc set to Sum(Relation::Subtotal)

Calculate the total through the relation. If you want the total in the Sale Items DB, you would need a self-relation and then Sum it the same way.

I hope that makes sense!

[Mark Appleby]

No, I think you are getting the wrong end of the stick, I already have that grand total which is working OK.

My "total" field is on each item as when a sales person sells a piece he may give a discount to a particularly good customer. For instance if the subtotal is

[trevorg]

You cannot change the total field because it is a calculation.

Maybe you need a discount field. You could either make it the number of $'s off.

Subtotal calc would then be (Qty*Price) - Discount

Or, make the discount a percentage of the total sale.

Sorry, I couldn't get the Euro sign to work.

[Mark Appleby]

I must not be stating this correctly, the field in question is a number field that has the option set to give me a default field value of total, this is so that I can change the value but keep the default in the majority of cases.

[Kurt Knippel]

That method is really a recipe for disaster for a number of reasons. The best method really is the (Qty * Price) - discount. The the salesmen manipluate the discount field.

The fact that your current field is an autoentered calculation means that it is set as soon as the fields that make it up are initially set. So assume that both are set to 0, then the auto-entered calc will set to 0 and will not change unless you manually change it. This is very likely your problem.

Go with SubTotal =(Qty * Price) - Discount

[DykstrL]

One method of doing what you are trying to do is use 2 fields:

SubTotalEntry = number field

SubTotal (calculation) = Case(not Isempty(SubTotalEntry),SubTotalEntry [or SubTotalEntry*Qty],Price*Qty)

Place the SubTotalEntry field underneath the SubTotal field on your layout. Also set the entry field to not print. Make sure the SubTotal[calc] field is set to not allow entry and set its background color so it will hide the entry field.

[Mark Appleby]

Thanks Dykstrl,

I used the case function but altered it slightly, to Case(IsEmpty(Total),Price*Qty,Total) which seems to be doing the job as I had hoped. Incidentally, why should I set the entry "total" field to not print?