How update a result field in table

markyT

Dear folks, I've two decimal type fields in a table and I've created a new one that has to multiply these two. The code is simply:

Result field:=("field one")*("fields two");

I put it on validate trigger from field one and field two.

If you write manually in these fields the result field changes, it runs well; but if the quantities change during any proces the result filed remains with the last quantities. I suppose that I have to do somenthig on the table to be continually updating this field. Any idea please?.
Thanks in advance for help.

kapamarou

Do you have a call to Rec.MODIFY after the validation?

markyT

Thanks. No; how it should be, please?

DenSter

If the code is:

"Field One" := 'some value';

Then the validation code is not executed. To make it so that the validation code is run, you have to use the VALIDATE method:

VALIDATE("Field One",'some value');

This way, your field validation code will run.

Belias

in your process you have code like this:

tablevariable.field1 := 10;
tablevariable.field2 := 2;

you should change it to

tablevariable.validate(field1,10);
tablevariable.validate(field2,2);

VALIDATE function unleash the power ( :!: :!: ) of OnValidate trigger... the ":=" does not. this is an important and basic difference a nav developer should know...remember it!

EDIT: i've seen denster answered before me after clicking "submit", but hey...i can't waste my already written post, isn't it?

markyT

Thanks. Sorry but I don't understand. I can't put a value because I don't know it, and it will be different because it is always changing.
Thanks for help.

Belias

if your field is calculated automatically in the onvalidate, you don't have to calculate it, you have only to input the 2 factors with the validate function...if i still didn't understand you problem, please explain it better.

markyT

Sorry, I've try to explain it better.

Table Bom component. First field is "Quantity per" and second field it's "Order quantity", it's a flowfield that I've created. I want to multiply "Quantity per" by "Order quantity" and show the result in "Total quantity", it's a field that I've created also.
When quantity on "Quantity per" field changes "Total quantity" do correctly the operation and show the result, but not when changes the quantity form "Order quantity" field; I supposed that it was because was a flowfield, but I've put the calcfields instruction but the result is the same. Any idea please?.
Thanks for help.

Belias

let me summarize...
"Quantity per" --> not flowfield, input by process
"Order quantity" --> flowfield, therefore it's calculated by the system
"Total quantity" --> it's "quantity per"*"order quantity", shouldn't be a flowfield

Order quantity must be calculated with calcfields before doing the moltiplication, if the situation is the one shown in this post

markyT

Thanks; it's exactly as you have explain, but doesn't work; "Order Quantity" is already calculate with calcfield like this onvalidate trigger from "Order quantity" field:


bomtable.CALCFIELDS("Order quantity");
"Total quantity":=("Order quantity")*("Quantity per");

I cant' find the problem.
Thanks.

Belias

you did calcfields on bomtable variable, but you use the value of the "rec" variable (rec is implicit when you write "order quantity").
change your code to

CALCFIELDS("Order quantity");
"Total quantity":=("Order quantity")*("Quantity per");

kapamarou

In your code you need to have:

CALCFIELDS; <- To calculate any flowfields you need.
Field.VALIDATE; <- To fill your value and call the validation code.
Rec.MODIFY; <- To save your changes on the record.

Debug your code. Place a breakpoint at the line where you fill the value and see step by step what happens.

markyT

Thanks; but this code on each field or on result field?.
Thanks for help.

kapamarou

Examine Belias's point:

Belias wrote:

you did calcfields on bomtable variable, but you use the value of the "rec" variable (rec is implicit when you write "order quantity").
change your code to

Have you debugged it to see what happens?

The code can be anywhere depending on your design.

The modify could be on your process.

markyT

Thanks everybody for help but it doesn't work; I've try:

CALCFIELDS("Order quantity");
"Total quantity":=("Order quantity")*("Quantity per");

But the result is the same, when "Order quantity" changes "Total quantity" doesn't change. I also active the debugger but I don't see anything strange. Any idea please?

Belias

well...if i'm correct, you have a list form with these fields: you have to code under the onaftergetrecord trigger to calculate your values, and then do modify to the rec as kapamarou suggested, and also a currform.update can be helpful.
Can you post the whole code you have in the onaftergetrecord trigger under the form, please?

markyT

Yes, I've a form,"Bill of materials" form, but on aftergetrecord from form I've not any code, I'm doing this in "Bom component" table, because, after, the "Total quantity" field I want to get it to another table, and do the calculation on form did not seem to me a good idea. I'm wrong?.
Thanks for reply.

Belias

it's getting messy in my opinion: you were right that is not very good to put code on forms, but sometimes is necessary.
after rereading all the posts, i realized that you have no problems with the form, so, don't mind...
debug your code and see what happens, it's the only thing i can say, as i don't have the whole picture of your code...

markyT

Thanks for your help and time. I post the code from "Bom component" table, let me know if you are very kind and find somethig wrong:

OBJECT Table 90 BOM Component
{
  OBJECT-PROPERTIES
  {
    Fecha=15/12/09;
    Hora=10:54:16;
    Modificado=S¡;
    Lista versiones=NAVW13.70;
  }
  PROPERTIES
  {
    DataPerCompany=No;
    OnInsert=BEGIN
               Item.GET("Parent Item No.");
             END;

    CaptionML=[ENU=BOM Component;
               ESP=Componentes L.M.];
    LookupFormID=Form36;
    DrillDownFormID=Form36;
  }
  FIELDS
  {
    { 1   ;   ;Parent Item No.     ;Code50        ;TableRelation=Item;
                                                   CaptionML=[ENU=Parent Item No.;
                                                              ESP=N§ L.M.];
                                                   NotBlank=S¡;
                                                   Description=MONOLITIC 20->33 }
    { 2   ;   ;Line No.            ;Integer       ;CaptionML=[ENU=Line No.;
                                                              ESP=N§ l¡nea] }
    { 3   ;   ;Type                ;Option        ;OnValidate=BEGIN
                                                                "No." := '';
                                                                "Variant Code" := '';
                                                              END;

                                                   CaptionML=[ENU=Type;
                                                              ESP=Tipo];
                                                   OptionCaptionML=[ENU=" ,Item,Resource";
                                                                    ESP=" ,Producto,Recurso"];
                                                   OptionString=[ ,Item,Resource] }
    { 4   ;   ;No.                 ;Code50        ;TableRelation=IF (Type=CONST(Item)) Item
                                                                 ELSE IF (Type=CONST(Resource)) Resource;
                                                   OnValidate=BEGIN
                                                                TESTFIELD(Type);
                                                                "Variant Code" := '';
                                                                IF "No." = '' THEN
                                                                  EXIT;

                                                                CASE Type OF
                                                                  Type::Item:
                                                                    BEGIN
                                                                      Item.GET("No.");
                                                                      Item.CALCFIELDS("Bill of Materials");
                                                                      "Bill of Materials" := Item."Bill of Materials";
                                                                      Description := Item.Description;
                                                                      "Unit of Measure Code" := Item."Base Unit of Measure";
                                                                    END;
                                                                  Type::Resource:
                                                                    BEGIN
                                                                      Res.GET("No.");
                                                                      "Bill of Materials" := FALSE;
                                                                      Description := Res.Name;
                                                                      "Unit of Measure Code" := Res."Base Unit of Measure";
                                                                    END;
                                                                END;
                                                              END;

                                                   CaptionML=[ENU=No.;
                                                              ESP=N§];
                                                   Description=MONOLITIC 20->33 }
    { 5   ;   ;Bill of Materials   ;Boolean       ;FieldClass=FlowField;
                                                   CalcFormula=Exist("BOM Component" WHERE (Type=CONST(Item),
                                                                                            Parent Item No.=FIELD(No.)));
                                                   CaptionML=[ENU=Bill of Materials;
                                                              ESP=Lista de materiales];
                                                   Editable=No }
    { 6   ;   ;Description         ;Text50        ;CaptionML=[ENU=Description;
                                                              ESP=Descripci¢n] }
    { 7   ;   ;Unit of Measure Code;Code20        ;TableRelation=IF (Type=CONST(Item)) "Item Unit of Measure".Code WHERE (Item No.=FIELD(No.))
                                                                 ELSE IF (Type=CONST(Resource)) "Unit of Measure";
                                                   CaptionML=[ENU=Unit of Measure Code;
                                                              ESP=C¢d. unidad medida] }
    { 8   ;   ;Quantity per        ;Decimal       ;OnValidate=BEGIN

                                                                 CALCFIELDS("Order quantity");
                                                                 "Total quantity":=("Order quantity")*("Quantity per");
                                                              END;

                                                   CaptionML=[ENU=Quantity per;
                                                              ESP=Cantidad por];
                                                   DecimalPlaces=0:5;
                                                   MinValue=0 }
    { 9   ;   ;Position            ;Code20        ;CaptionML=[ENU=Position;
                                                              ESP=Posici¢n] }
    { 10  ;   ;Position 2          ;Code20        ;CaptionML=[ENU=Position 2;
                                                              ESP=Posici¢n 2] }
    { 11  ;   ;Position 3          ;Code20        ;CaptionML=[ENU=Position 3;
                                                              ESP=Posici¢n 3] }
    { 12  ;   ;Machine No.         ;Code20        ;CaptionML=[ENU=Machine No.;
                                                              ESP=N§ maquina] }
    { 13  ;   ;Production Lead Time;Integer       ;CaptionML=[ENU=Production Lead Time;
                                                              ESP=Plazo producci¢n] }
    { 14  ;   ;BOM Description     ;Text50        ;FieldClass=FlowField;
                                                   CalcFormula=Lookup(Item.Description WHERE (No.=FIELD(Parent Item No.)));
                                                   CaptionML=[ENU=BOM Description;
                                                              ESP=Descripci¢n L.M.];
                                                   Editable=No }
    { 5402;   ;Variant Code        ;Code20        ;TableRelation=IF (Type=CONST(Item)) "Item Variant".Code WHERE (Item No.=FIELD(No.));
                                                   OnValidate=BEGIN
                                                                IF "Variant Code" = '' THEN
                                                                  EXIT;
                                                                TESTFIELD(Type,Type::Item);
                                                                TESTFIELD("No.");
                                                                ItemVariant.GET("No.","Variant Code");
                                                                Description := ItemVariant.Description;
                                                              END;

                                                   CaptionML=[ENU=Variant Code;
                                                              ESP=C¢d. variante];
                                                   Description=ANTES 10 }
    { 5900;   ;Installed in Line No.;Integer      ;OnValidate=BEGIN
                                                                IF "Installed in Line No." <> 0 THEN BEGIN
                                                                  IF "Installed in Line No." = "Line No." THEN
                                                                    ERROR(Text000,FIELDCAPTION("Installed in Line No."));
                                                                  BOMComp.RESET;
                                                                  BOMComp.SETRANGE("Parent Item No.","Parent Item No.");
                                                                  BOMComp.SETRANGE(Type,BOMComp.Type::Item);
                                                                  BOMComp.SETRANGE("Line No.","Installed in Line No.");
                                                                  BOMComp.FIND('-');
                                                                  BOMComp.TESTFIELD("Quantity per",1);
                                                                  "Installed in Item No." := BOMComp."No.";
                                                                END ELSE
                                                                  "Installed in Item No." := '';
                                                              END;

                                                   OnLookup=BEGIN
                                                              BOMComp.RESET;
                                                              BOMComp.SETRANGE("Parent Item No.","Parent Item No.");
                                                              BOMComp.SETRANGE(Type,BOMComp.Type::Item);
                                                              BOMComp.SETFILTER("Line No.",'<>%1',"Line No.");
                                                              CLEAR(BillOfMaterials);
                                                              BillOfMaterials.SETTABLEVIEW(BOMComp);
                                                              BillOfMaterials.EDITABLE(FALSE);
                                                              BillOfMaterials.LOOKUPMODE(TRUE);
                                                              IF BillOfMaterials.RUNMODAL = ACTION::LookupOK THEN BEGIN
                                                                BillOfMaterials.GETRECORD(BOMComp);
                                                                VALIDATE("Installed in Line No.",BOMComp."Line No.");
                                                              END;
                                                            END;

                                                   CaptionML=[ENU=Installed in Line No.;
                                                              ESP=Instalado en n§ l¡n.] }
    { 5901;   ;Installed in Item No.;Code20       ;TableRelation=IF (Type=CONST(Item)) Item;
                                                   OnValidate=BEGIN
                                                                IF "Installed in Item No." <> '' THEN BEGIN
                                                                  BOMComp.RESET;
                                                                  BOMComp.SETRANGE("Parent Item No.","Parent Item No.");
                                                                  BOMComp.SETRANGE(Type,BOMComp.Type::Item);
                                                                  BOMComp.SETRANGE("No.","Installed in Item No.");
                                                                  BOMComp.FIND('-');
                                                                END;

                                                                VALIDATE("Installed in Line No.",BOMComp."Line No.");
                                                              END;

                                                   OnLookup=BEGIN
                                                              BOMComp.RESET;
                                                              BOMComp.SETRANGE("Parent Item No.","Parent Item No.");
                                                              BOMComp.SETRANGE(Type,BOMComp.Type::Item);
                                                              BOMComp."No." := "Installed in Item No.";
                                                              BOMComp.SETFILTER("Line No.",'<>%1',"Line No.");
                                                              CLEAR(BillOfMaterials);
                                                              BillOfMaterials.SETTABLEVIEW(BOMComp);
                                                              BillOfMaterials.EDITABLE(FALSE);
                                                              BillOfMaterials.LOOKUPMODE(TRUE);
                                                              IF BillOfMaterials.RUNMODAL = ACTION::LookupOK THEN BEGIN
                                                                BillOfMaterials.GETRECORD(BOMComp);
                                                                VALIDATE("Installed in Line No.",BOMComp."Line No.");
                                                              END;
                                                            END;

                                                   CaptionML=[ENU=Installed in Item No.;
                                                              ESP=Instalado en n§ prod.];
                                                   Description=MONOLITIC 20->33 }
    { 50000;  ;Order quantity;Decimal     ;FieldClass=FlowField;
                                                   CalcFormula=Sum("Sales Line"."Outstanding Quantity" WHERE (No.=FIELD(Parent Item No.),
                                                                                                              Document Type=CONST(Order),
                                                                                                              Type=CONST(Item)));
                                                   OnValidate=BEGIN

                                                                CALCFIELDS("Order quantity");
                                                                 "Total quantity":=("Order quantity")*("Quantity per");
                                                              END;

                                                   DecimalPlaces=0:0 }
    { 50001;  ;Total quantity      ;Decimal       ;DecimalPlaces=0:0 }
  }
  KEYS
  {
    {    ;Parent Item No.,Line No.                 }
    {    ;Type,No.                                 }
    {    ;No.                                     ;SumIndexFields=Quantity per }
  }
  CODE
  {
    VAR
      Text000@1000 : TextConst 'ENU=%1 cannot be component of itself.;ESP=%1 no puede ser componente de si mismo.';
      Item@1001 : Record 27;
      Res@1002 : Record 156;
      ItemVariant@1003 : Record 5401;
      BOMComp@1004 : Record 90;
      BillOfMaterials@1005 : Form 36;
      tablabom@1103351000 : Record 90;

    BEGIN
    END.
  }
}

Thanks for your help.

Belias

and what about the code of the process? i think that the table itself works as expected, as you previously said

markyT

Sorry, how I get the code of the process please? (not mastered so much the subject :oops: ).

Belias

you said that there's something that fills this table...well...what is it?some codeunit,report,standard functionality...in other words, something that fills the table without having the user digit the fields manually...

markyT

Ops!!, sorry, it's "Outstanding quantity" field form "Sales Line", this is the content from "Order quantity" flowfield. When "Outstanding quantity" field from "Sales line" changes "Order quantity" changes, but no "Total quantity", this is the problem.
Thanks.

Belias

Then you're in trouble...that field does not have an onvalidate, so you have to intercept all the places where it is evaluated, and then do your moltiplication on your table too...but I won't do this, too much places (at least i think so), too risky, too unmaintainable...i hope that someone else have an idea...

markyT

Buf!!, don't say me that!! #-o . It's strange, because I've got the two fields correctly, the only problem is that I can't multiply them; it's the kind of thing that seems very easy, multiply two fields, but after, you see.
Well, thank you very much for your help and time.

manlue

Hi,
in the case when FlowField Functionality is changing the value of the FlowField there I think is no trigger which is initiated. You are not able to react on this event like in "OnValidate"-Trigger.

So if I understand what should be: Updating a RecordLine in Subform should initiate updating your "Total Quantity" in Record which is shown in the Mainform, right?

So maybe it's the issue of "Updating Mainform out of the subform"?

A poor solution may be creating a timer which ongoing checks the "Total Quantity" if it is still the product of "Order Quantity" and "Quantity per". Another solution may be using the Automation XMLDom. This you may find by searching the forum. It's like: Change in subform initiates sth in XMLDom and Mainform reacts on the event. This is not simple but it's working...

Belias

The problem is that the code for the moltiplication is under the onvalidate of your fields: if the onvalidate is not called, the moltiplication is not done.
As the onvalidate of the flowfield is not called implicitly when it changes the value, you have to explicitly validate it in order to do the moltiplication in the trigger, or do the calculation manually everytime the source value of the flowfield (the outstanding quantity) changes...

manlue

Hi again

another issue in this forum is nearly the same problem.

What about calculating the product out of the OnValidate-Trigger of the Field which is referenced by the FlowField? Means in SourceField which dynamically gives value to the FlowField. This is in another table but I think it should be possible to reference to the table which owns the FlowField, make a CALCFIELDS-statement, compute product and modify the table...

I didn't try it by myself ... if anyone tries it please post result...

Belias

you can try it...but what if that field is not validated by some processes? your product is not calculated and your data is not consistent..

manlue

so you may insert this in the OnModify Trigger of the table holding the Field which gives the value into the FlowField. There may be checked by comparing xrec.Field with rec.Field if value has changed... Other idea?

Belias

if the record is modified with
MODIFY
instead of
MODIFY(TRUE)

the onmodify trigger is not fired...