Calculation Field On Table

madm

I have a new table, in which i have a field called Controlled which is an option No, Yes.

This field is then used in table 2 as the USED Field.

Quantity, Used (Count = YES, Flowfield) and Remaining.

I have set on the onvalidate trigger of remaining a caculation

Remaining := Quantity - Used

The remaining figure is not automatically updated when the flowfield is updated. From reading about this I need to somehow validate this calculation so that it runs automatically. Am I correct in thinking this, and if so could someone some point me in the direction please.

The reason for the "REMAINING" field being neeed, is so that on Table1 when the user selects Controlled option, if the option is yes, and remaining = 0, then give an error.

At the moment, what i have done, is on the controlled onvalidate, is put trigger IF TABLE2.QUANTITY - TABLE2.USED := <1 THEN error...
(I did this to get round the remaining problem). However, the error only appears when the quantity = 0 (rather than the calculation)

Two Questions :

1 The remaining field is only going to be used to trigger the error, so is it needed? If so, how would i get it to automatically update?

2 How would i go about setting the error to appear, when Controlled = Yes and Remaining = <1

THanks for your help.

kine

What about :

TABLE2.CALCFIELDS(Quantity,Used);
if (Tab1.Controlled = Tab1.Controlled::Yes) and (TABLE2.Quantity - TABLE2.Used <=1) then
  Error(.........);

1) Why Controlled is Option if the values are Yes/No? Why you are not using Boolean?

2) In which case you want to have the error message to appear?

madm

IF (Controlled = Controlled::"1") AND (TABLE2.Quantity - TABLE2.Used <1) THEN
ERROR(Text001);

thank you for your reply.

i have changed the option to boolean

with regards to the remaining value, this value is not a necessity, more of a field to do the check on.

Therefore if i was to put the forumla in the validation check, then the field is not needed.

I have therefore removed that field, and decided to work on the calculation in the trigger.

I have put the above code in, however, the trigger point does not seem to be working. I want the error message to appear if Quantity - Used is 0 (or below!), but it doesnt seem to be working like that. If the Quantity = 3 and used = 3, then the next time controlled is selected, the error message should be shown, which it doesnt. If the quantity = 0 and used = 0, then the message is given, as you would expect.

Is there anything else that I need to do. (I have got this far, and dont want my NSC to end up finishing it )

Thanks for your help.

/edit

i have just tried this will "<=1" in the formula; and this does generate a different result (it wont let me select controlled below zero). But if the quantity = 1 and used = 0 (ie 1 free) then this isnt allowed either, as you would expect!

DenSter

Zero or less is programmed as <= 0, not <= 1

madm

DenSter wrote:

Zero or less is programmed as <= 0, not <= 1

i have also tried that

Controlled - OnValidate()

SWL.GET(Name);
IF (Controlled = Controlled::"1") AND (SWL.Quantity - SWL.Used <= 0) THEN
  ERROR(Text001);

This is the actual code. This table is linked to the SWL table (linked by Name field where the quantity and used (flowfield) fields are. (i have set SWL in the variables) Do i need to set some filters after doing this??

I seem to be only getting the error message when Quantity is at Zero, rather than the calculation

DenSter

If either of the Quantity or the Used fields are flowfields, you have to do a CALCFIELDS on them before you can access their values. Try putting the calculation itself in parentheses too, like this:

Controlled - OnValidate()

SWL.GET(Name);
IF (Controlled = Controlled::"1") AND ((SWL.Quantity - SWL.Used) <= 0) THEN
  ERROR(Text001);

By the way, I thought you changed the Controlled field to a boolean field. In that case, the above code wouldn't work.

madm

DenSter wrote:

If either of the Quantity or the Used fields are flowfields, you have to do a CALCFIELDS on them before you can access their values. Try putting the calculation itself in parentheses too, like this:

Controlled - OnValidate()

SWL.GET(Name);
IF (Controlled = Controlled::"1") AND ((SWL.Quantity - SWL.Used) <= 0) THEN
  ERROR(Text001);

By the way, I thought you changed the Controlled field to a boolean field. In that case, the above code wouldn't work.

Thanks - it would appear that the CALCFIELDS cures it. (I should have read Kines reply more carefully :oops: )

With regards to the controlled field i have changed it to a boolean field, but it seems to work as it wont let me tick anything where the quantity is 0 or less (not that it should be less now!) ( I presume the ::1 should be at true?)

Thanks for your help, and now onto more testing (well on Monday!)

kine

if you are using Boolean, you do not need use operators:

Controlled - OnValidate()

SWL.GET(Name);
SWL.CALCFIELDS(Quantity,Used);   <---still missing in your code

IF Controlled  AND ((SWL.Quantity - SWL.Used) <= 0) THEN
  ERROR(Text001);

Controlled alone have value true or false, you do not need to test it...

And if you want to not allow change the Controlled value if Q-U<=0, than this code is OK. Do not forget to the situation, when SWL have no record with primary key "Name"... (in your case there will be error that the record was not found)