Jump to content

This topic is 6555 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 post
Share on other sites
  • Replies 11
  • Created
  • Last Reply

Top Posters In This Topic

Top Posters In This Topic

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 post
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 post
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 post
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 post
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 post
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 post
Share on other sites

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

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.
Note: Your post will require moderator approval before it will be visible.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.


×
×
  • Create New...

Important Information

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