Jump to content

Calculation won't work!


This topic is 6946 days old. Please don't post here. Open a new topic instead.

Recommended Posts

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!

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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!

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

This topic is 6946 days old. Please don't post here. Open a new topic instead.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share

×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.