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?
0
Comments
MVP - Dynamics NAV
My BLOG
NAVERTICA a.s.
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.
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.
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.
IF recItem.GET(Rec (Table 7002). "Item no.")) THEN
Validate (Rec (Table 7002). "Field_Y" reItem. "Field_X");
-awarn
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;
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
Let's see if I can explain a little better, more graphically.
I have the following.
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..
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
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
This would get the result we want, but with the code before that I do not work for me.
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;
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.
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...
RESET
SETCURRENTKEY
IF FIND THEN REPEAT
MODIFY
UNTIL rec.NEXT = 0;
then I don't see how the MODIFY is giving you an error?
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;
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?.
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;
ERP Consultant (not just Navision) & Navision challenger
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;
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