How do that automatically fill a field?

FrgBonT
FrgBonT Member Posts: 28
edited 2008-08-22 in NAV/Navision Classic Client
Hello,
I would be interested in knowing how to achieve that by adding a "sale price" on Form 7002, once I put the "Item no." Form I automatically fill a field that I created in the table "Sale Price" with a value that has taken on the Table "Item" which corresponds to "Item no." added.

Any idea?

Comments

  • kine
    kine Member Posts: 12,562
    It is done without code, just through setting the filters on the fields. If the filters are on primary key fields, and the filter is single value, the value is automatically inserted into the record. This prevents the record from "falling out" the filter...
    Kamil Sacek
    MVP - Dynamics NAV
    My BLOG
    NAVERTICA a.s.
  • FrgBonT
    FrgBonT Member Posts: 28
    Thanks, I think I have either created the filter, but .....

    The problem is that the field you want to read the table Item, is already read from another table, and although I already have filters in 7002 created the table, TableRelation in the field that I created, I have; Item. "Field that wants to show" where (No. = FIELD (Item no.))

    with this it that when I'm adding a new Sales Price and put me on the field when you press F6 showed me the table ITEM noting the field that interests me is the product to which I am referring, but not against having to be filled automatically Not since the Item.
  • FrgBonT
    FrgBonT Member Posts: 28
    Well, I will try as best they can explain the situation.
    Table 27, has a field (let us call Field_Y), which owned TableRelation name a table (let us call Table_X) and then by C / LA in OnValidate has:

    IF "And Campo_" <> xRec. "Field_And" THEN BEGIN
    IF Table_X. GET ( "Field_X") THEN BEGIN
    IF "Gen. Prod. Posting Group =''THEN
    Validate ( "Gen. Prod. Posting Group", Table_X. "Def. Gen. Prod. Posting Group");
    IF "VAT Prod. Posting Group =''THEN
    Validate ( "VAT Prod. Posting Group", Table_X. "Def. VAT Prod. Posting Group");
    IF "Inventory Posting Group =''THEN
    Validate ( "Posting Inventory Group, Table_X." Def. Inventory Posting Group ");
    IF "Tax Group Code" =''THEN
    Validate ( "Tax Code Group", Table_X. "Def. Group Tax Code");
    Validate ( "Costing Method", Table_X. "Def. Costing Method");
    END;

    IF NOT ProductGrp.GET ( "Field_Y", "Product Group Code") THEN
    Validate ( "Product Group Code ",'')
    ELSE
    Validate ( "Product Group Code");
    END;

    Where data shows the Table_X once we are giving high and a new product so we can assign value to the product desired.
    I intend, by 7002 Table, a field Cree (let us call Field_Z), which owned TableRelation is as follows:

    Item. "Field_and where (No. = FIELD (Item no.))

    Let me add to a new Price Sales in the table, to put the Item No.. I automatically appear in Field_Z, the value of the Field_Y of Table 27. It is also true that using this table will be based on Form 7002, so if I put the code there is better.
  • FrgBonT
    FrgBonT Member Posts: 28
    First and foremost, to thank for all the readings he has had since it appreciates the intention to help.
    After being so many hours trying, finally got to me automatically leave the value of the field. And because the solution is almost always easier than it seems at first glance.
    Only need to create the table in 7002 a record that brings to the table Item (recItem)
    And changing the code; Item No.. -- OnValidate () placing at the end the following lines.

    recItem.SETRANGE (recItem. "no." Rec (Table 7002). "Item no.");
    recItem.FIND ('-');
    Validate (Rec (Table 7002). "Field_Y" reItem. "Field_X");


    Thanks again.
  • awarn
    awarn Member Posts: 261
    You might want to change the code to look more like this:

    IF recItem.GET(Rec (Table 7002). "Item no.")) THEN
    Validate (Rec (Table 7002). "Field_Y" reItem. "Field_X");


    -awarn
  • FrgBonT
    FrgBonT Member Posts: 28
    Thank you, because if you're right is a form of the code is better.


    Moreover, I am now doing a codeunit for me to run Field_Y alter the value of all the records that were previously in Table 7002, and currently have no value. And since he is good code that I then, but I fail to do so, because when it comes to MODIFY, I can think of a mistake and I closed Navision. Any idea?

    GLOBALS
    Name DataType Subtype
    recItem Record Item
    recTarifas Record Sales Price
    dlgProceso Dialog
    intRegist Integer
    intRegistTotal Integer


    CODE ONRUN
    recTarifas.SETCURRENTKEY("Item No.","Sales Type","Sales Code","Starting Date","Currency Code","Variant Code",
    "Unit of Measure Code","Minimum Quantity","Field_Y");
    recTarifas.FIND('-');
    intRegist := 0; //It is used for dialog updating the records go, and be able to control that process takes place
    intRegistTotal := 0;

    intRegistTotal := recTarifas.COUNT;

    dlgProceso.OPEN( '#1####/#2####');
    dlgProceso.UPDATE(2, intRegistTotal);

    REPEAT
    recItem.SETRANGE(recItem."No.",recTarifas."Item No.");
    recItem.SETCURRENTKEY("No.","Field_Y");

    IF recItem.FIND('-') THEN
    BEGIN
    recTarifas.VALIDATE(recTarifasMod."Field_Y",recItem."Field_X");
    recTarifas.MODIFY;

    END;

    intRegist := intRegist + 1;
    dlgProceso.UPDATE(1, intRegist);

    UNTIL recTarifas.NEXT = 0;

    dlgProceso.CLOSE;
  • awarn
    awarn Member Posts: 261
    I think I see your problem (if I read your question right).

    recTarifas.SETCURRENTKEY("Item No.","Sales Type","Sales Code","Starting Date","Currency Code","Variant Code",
    "Unit of Measure Code","Minimum Quantity","Field_Y");
    recTarifas.FIND('-');

    ...

    recTarifas.VALIDATE(recTarifasMod."Field_Y",recItem."Field_X");
    recTarifas.MODIFY;

    You are sorting the table recTarifas by Field_Y, and then later changing the value of field_Y, which puts you out of order.

    Look at this example:

    Customer Ledger Entries
    Customer Date Amount Entry No
    A 010101 $500 1
    A 010101 $600 2
    B 010101 $500 3

    Sort by Cusotmer, Date

    Loop and get first record, A,010101,$500.
    Change the Date to 013101

    now the record set looks like:
    Customer Date Amount Entry No.
    A 010101 $600 2
    A 013101 $500 1
    B 010101 $500 3

    Then your code hits the NEXT, which moves it to the last line (because you change the date, the order of the records changed). You end up skipping Entry #2.

    Does that make sense?

    -awarn
  • FrgBonT
    FrgBonT Member Posts: 28
    Hello again…

    Let's see if I can explain a little better, more graphically.

    I have the following.
    [b]TABLE 7002
Item No.           Unit Price		 Sales Code            Field_Y[/b]
000001           12.33                  678          (empty field)
000001           5.65                      85          (empty field)
000005           1.27                      68          (empty field)
000002           5.56                      15          (empty field)
000001           85.16                  677          (empty field)

    As you can see, there may be Sales Prices of the same product with different prices, as they go according to the "Sales Code", which will do the tender offer. I applied to Table 7002 (Sales Price) a Key

    recTable7002.SETCURRENTKEY (Item no. "" Cod Product Category ");

    to obtain the goods ordered by Item No..
    [b]TABLE 7002
Item No.           Unit Price		 Sales Code            Field_Y[/b]
000001           12.33                  678          (empty field)
000001           5.65                      85          (empty field)
000001           85.16                  677          (empty field)
000002           5.56                      15          (empty field)
000005           1.27                      68          (empty field)

    Then I begin to go the table, taking the first record, looking for his "Item no." In the table 27 (ITEM) in relation to the field. "

    recItem.SETRANGE (recItem. "no." recSales. "Item no.");

    If you find the Item must modify the value of Field_Y from Table 7002 (Sales Price) for the value of Field_X of Table 27 (Item)

    Example:

    recItem.SETRANGE (recItem. "no." recSales. "Item no.");

    Where recSales. "Item no." Same as "000001" (no quotes)
    Then get
    [b]TABLE 27
Item No.		  Field_X[/b]
000001           Value Item 000001

    Then you have to adjust in Table 7002 (Sales Price) the value of the first record that matches the Item 000001 putting in Field_Y the value of Field_X, and moving to the next record from Table 7002 (Sales Price) to perform the same process getting the final Table 7002 (Salies Price) as follows
    [b]TABLE 7002
Item No.           Unit Price		 Sales Code            Field_Y[/b]
000001           12.33                  678          Value Item 000001
000001           5.65                      85          Value Item 000001
000001           85.16                  677          Value Item 000001
000002           5.56                      15          Value Item 000002
000005           1.27                      68          Value Item 000005

    This would get the result we want, but with the code before that I do not work for me.
  • awarn
    awarn Member Posts: 261
    Try it like this: (lets not set a key on the Sales Price table, since we get all lines anyway).

    SalesPrice.RESET;
    IF SalesPrice.FINDSET THEN REPEAT
    IF Item.GET("Item No.") THEN BEGIN
    SalesPrice.FieldY := Item.FieldY;
    SalesPrice.MODIFY;
    END;
    UNTIL SalesPrice.NEXT = 0;

    If that works, then the problem is your key on the sales price table. You cannot loop through a table based on a key, and alter a field in the key, and hope that the loop will behave properly.

    You can also structure the code like this: (note that FINDSET should be used in versions 4.03 and above). If you are going to alter a field in the key, then you should use TRUE,TRUE, and ALWAYS use a copy of the record (i.e. SalesPrice2)

    SalesPrice.RESET;
    SalesPrice.SETCURRENTKEY("Item No.","FieldY");
    IF SalesPrice.FINDSET(TRUE,TRUE) THEN REPEAT
    IF Item.GET("Item No.") THEN BEGIN
    SalesPrice2 := SalesPrice;
    SalesPrice2.FieldY := Item.FieldY;
    SalesPrice2.MODIFY;
    END;
    UNTIL SalesPrice.NEXT = 0;
  • FrgBonT
    FrgBonT Member Posts: 28
    Thanks for the help, but I can not use the FINDSET, but still adapt the code and nothing because the problem is the use of MODIFY

    The problem arises when I MODIFY makes me break the process and showed me where he puts a message:

    The Sales Price does not exist.
    Fields Identification and Values:

    and then just below shows the relationship of the fields in Table 7002 with a corresponding value of the line which was located in the recSales record, including the value of Field_Y updated with the value of Field_X, but neither has really put the value or is because I can show that error.
  • awarn
    awarn Member Posts: 261
    OK - I think I get it now - you get an error on the MODIFY line?

    This tells me that the code we are looking at is contained at the end of another process? (i.e. the code is not a stand alone routine but part of something else).

    Maybe you need to put a SELECTLATESTVERSION before the code - or take the code out and run it separately in its own codeunit. If the code runs in its own codeunit properly but not in your routine, then something has changed in the sales price table...
  • FrgBonT
    FrgBonT Member Posts: 28
    I am working on my own CODEUNIT, since it is a process that only run once, to update the value of Field_Y, which have recently established has no value at all the records and why I'm trying to create the process, so that referring to the "Table 27 (Item)" I update it. Once you run the CODEUNIT and modify the values of the fields, no longer need to use more because I have prepared the table so that by inserting a new record, it automatically put the value of Field_Y that appears in the Field_X "Table 27 (Item)"
  • awarn
    awarn Member Posts: 261
    If it is isolated, and you have:

    RESET
    SETCURRENTKEY
    IF FIND THEN REPEAT
    MODIFY
    UNTIL rec.NEXT = 0;

    then I don't see how the MODIFY is giving you an error?
  • FrgBonT
    FrgBonT Member Posts: 28
    I can not understand how I can produce errors in a code that has no such difficulty, and I have already done similar things at other times but this time, there is something that I am not leaving this.
  • FrgBonT
    FrgBonT Member Posts: 28 
    OnRun()
recItem.RESET;
recSales.RESET;

recSales.SETCURRENTKEY("Item No.","Field_Y");
recSales.SETRANGE(recSales."Field_Y",'');
recSales.FIND('-');

recItem.SETCURRENTKEY("No.","Field_X");

intRegist := 0;
intRegistTotal := 0;

intRegistTotal := recSales.COUNT;

dlgProceso.OPEN( '#1####/#2####');
dlgProceso.UPDATE(2, intRegistTotal);

REPEAT

  recItem.SETRANGE(recItem."No.",recSales."Item No.");

  IF recItem.FIND('-') THEN
    BEGIN
      recSales.VALIDATE(recSales."Field_Y",recItem."Field_X");
      recSales.MODIFY;
  END;

  intRegist := intRegist + 1;
  dlgProceso.UPDATE(1, intRegist);

UNTIL recSales.NEXT = 0;

dlgProceso.CLOSE;
  • awarn
    awarn Member Posts: 261
    Why don't you try a very simple, brand new codeunit with only these lines of code, and see if it works? If it does, then the other lines (which I cannot see as necessary anyway) can be added in.


    SalesPrice.RESET;
    IF SalesPrice.FIND('-') THEN REPEAT
    IF Item.GET("Item No.") THEN BEGIN
    SalesPrice2 := SalesPrice;
    SalesPrice2.FieldY := Item.FieldY;
    SalesPrice2.MODIFY;
    END;
    UNTIL SalesPrice.NEXT = 0;
  • FrgBonT
    FrgBonT Member Posts: 28
    Here is the mistake that gives me Navision at the time of the MODIFY

    http://img352.imageshack.us/img352/8027/errornavjd6.jpg

    With any code you use, when it comes to MODIFY the error occurs. Whether a RECORD to the Table with two RECORD and equal before changing to make the amendment in the second RECORD. Everything you never mind, Do any idea that can happen that error?.
  • idiot
    idiot Member Posts: 651
    Do you even have that record to begin with in the Sales Price?
    If there is no such record you do an insert instead of modify.
    You need to have a check:

    SalesPrice.SETwhateverRange
    IF SalesPrice.FINDFIRST THEN BEGIN
    //whatever validate code
    SalesPrice.MODIFY;
    END ELSE BEGIN
    //whatever code
    SalesPrice.ISNERT;
    END;
    NAV - Norton Anti Virus

    ERP Consultant (not just Navision) & Navision challenger
  • FrgBonT
    FrgBonT Member Posts: 28
    The problem is that registration error exists, all records exist because they are created, and only pretend to read the registration and change the value of the field that was just added Field_Y, as the old records have to default to white. Then once arrived at MODIFY, shows us where the error is clear that the end is the Field_Y with the value in theory he has been charged, but did not say that because you do not find that record when I'm only sending to amend a record that I do not find a record of those characteristics.
  • awarn
    awarn Member Posts: 261
    OK now that you have shown the error I know what ths problem is.

    You have added FieldY to the PRIMARY key of the Sales Price table! This means that when you change it you are not modifying it but RENAMING it. This is an important detail!


    I would suggest not renaming it (takes a long time), but rather inserting and deleting:

    //this will create the new records with the field filled in
    SalesPrice.RESET;
    //maybe here you need a SalesPrice.SETFILTER(FieldY,'<>%1','');
    IF SalesPrice.FIND('-') THEN REPEAT
    IF Item.GET("Item No.") THEN BEGIN
    tmpSalesPrice := SalesPrice;
    tmpSalesPrice.INSERT;
    SalesPrice2 := SalesPrice;
    SalesPrice2.FieldY := Item.FieldY;
    SalesPrice2.INSERT;
    END;
    UNTIL SalesPrice.NEXT = 0;

    //delete the old records
    tmpSalesPrice.RESET;
    IF tmpSalesPrice.FIND('-') THEN REPEAT
    SalesPrice := tmpSalesPrice;
    SalesPrice.DELETE; //<- you might need TRUE as the parameter, not sure of what code is in here
    UNTIL tmpSalesPrice.NEXT = 0;
  • FrgBonT
    FrgBonT Member Posts: 28
    Yes, you're quite right, I practically connected me to say that already had the problem and its solution when I saw who'd already posted it, my problem is that had created / modified, keys, "Tables Sales Price "and "Table Item" placing the field in the Key. And all for having created, in the field of Field_Y Table 7002 (Sales Price), a TableRelation not need, because everything is automatic to put the "Item no." and not let the user edit it on the "Form 7002"

    I can not, but thank you for being with me fighting against error, and also to all others because their desire to help / learn to read the post, it is also important

Categories