Mark Appleby Posted July 26, 2002 Share Posted July 26, 2002 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 More sharing options...
trevorg Posted July 26, 2002 Share Posted July 26, 2002 Is the calculation indexed? Try turning indexing off. Link to comment Share on other sites More sharing options...
Mark Appleby Posted July 26, 2002 Author Share Posted July 26, 2002 No. it is not indexed. Link to comment Share on other sites More sharing options...
trevorg Posted July 26, 2002 Share Posted July 26, 2002 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. Link to comment Share on other sites More sharing options...
Mark Appleby Posted July 26, 2002 Author Share Posted July 26, 2002 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 More sharing options...
trevorg Posted July 26, 2002 Share Posted July 26, 2002 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 More sharing options...
Mark Appleby Posted July 26, 2002 Author Share Posted July 26, 2002 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 More sharing options...
trevorg Posted July 26, 2002 Share Posted July 26, 2002 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 More sharing options...
Mark Appleby Posted July 26, 2002 Author Share Posted July 26, 2002 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. Link to comment Share on other sites More sharing options...
Kurt Knippel Posted July 26, 2002 Share Posted July 26, 2002 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 More sharing options...
DykstrL Posted July 26, 2002 Share Posted July 26, 2002 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 More sharing options...
Mark Appleby Posted July 27, 2002 Author Share Posted July 27, 2002 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? Link to comment Share on other sites More sharing options...
Recommended Posts
This topic is 7938 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 accountSign in
Already have an account? Sign in here.
Sign In Now