Can't get last record of Sales Price

catiamatos1991

Hi everyone,

I've created a xmlport to import a text file and fill the Sales Price table.
The problem is when I import the file for the first time it creates a new line in Sales Price table. But if the user for some reason wants to upload the same file (with the same info or anything different than the Starting Date) it will modify the last existing record.
If i want to upload a new line which doesnt exist in the Sales Price table I need to fill the "Ending Date" field but somehow with this code "IF "Sales Price".FINDLAST" it doenst do anything.

My code:

IF NOT "Sales Price".INSERT(TRUE) THEN BEGIN
  MESSAGE('begin1');
  MESSAGE('check %1',"Sales Price".FINDLAST);
  IF "Sales Price".FINDLAST THEN BEGIN
    MESSAGE('begin2');
    MESSAGE('Item last %1', "Sales Price"."Item No.");
    MESSAGE('Item starting date %1', "Sales Price"."Starting Date");
    MESSAGE('Item ending date %1', "Sales Price"."Starting Date"-1);

    "Sales Price"."Ending Date":="Sales Price"."Starting Date"-1;
    "Sales Price".MODIFY(TRUE);
  END;
END;

My text file

1010100004	25/10/18	1	14,08
1010100002	26/10/18	1	12,65

where the first column is ITEM_No, the second is the Starting Date, the third is quantity and the last column is the price.

For some reason is not poping the message ("begin2") which means that this condition IF "Sales Price".FINDLAST THEN BEGIN is not being verified.

My table have a few rows and I don't know why he can't get the last record.

All the code of xmlport is here below,

Sales Price - Import::OnBeforeInsertRecord()
nRecNum += 1;
dlgProgress.UPDATE(3, nRecNum);

"Sales Price".RESET;
"Sales Price".SETRANGE("Sales Price"."Item No.",PRODUTO); 
"Sales Price".SETRANGE("Sales Price"."Sales Type",1);
"Sales Price".SETRANGE("Sales Price"."Sales Code",'ALL');
"Sales Price".SETRANGE("Sales Price"."VAT Bus. Posting Gr. (Price)",'NAC');
"Sales Price".SETRANGE("Ending Date",0D); 
"Sales Price".SETRANGE("Currency Code",''); 
"Sales Price".SETRANGE("Variant Code",'');
"Sales Price".SETRANGE("Unit of Measure Code",'');
//"Sales Price".SETRANGE("Minimum Quantity",QT)

DataInicioText:=COPYSTR(DATAINI,1,8);
dia := COPYSTR(DataInicioText,1,2);
mes := COPYSTR(DataInicioText,4,2);
ano:= COPYSTR(DataInicioText,7,4);

EVALUATE(DataInicio,dia+mes+ano);

"Sales Price".SETRANGE("Starting Date",DataInicio);

"Sales Price"."Starting Date":=DataInicio;

IF "Sales Price".FINDFIRST THEN BEGIN
   IF DataInicio < "Sales Price"."Starting Date" THEN
     "Sales Price".DELETEALL;
   END;


"Sales Price"."User ID":=USERID;
"Sales Price"."Data criacao":=TODAY;
"Sales Price"."Item No.":=PRODUTO;
"Sales Price"."Allow Invoice Disc." :=TRUE; 
"Sales Price"."VAT Bus. Posting Gr. (Price)":='NAC'; 
"Sales Price"."Allow Invoice Disc.":=TRUE;

"Sales Price"."Sales Code":='ALL';
"Sales Price"."Sales Type":=1;
"Sales Price"."Currency Code":='';
"Sales Price"."Variant Code":='';
"Sales Price"."Ending Date":=0D;

"Sales Price".VALIDATE("Sales Price"."Sales Code",'ALL');

Item.GET(PRODUTO);
"Sales Price"."Unit of Measure Code":=Item."Base Unit of Measure";


//Quantity

Quantity:=FORMAT(QT); 
EVALUATE("Sales Price"."Minimum Quantity",Quantity);

//Unit Price

Price:=FORMAT(PVP); 
EVALUATE("Sales Price"."Unit Price",Price);

//Unit Price with vat

VATPercentageProd:=Item."VAT Prod. Posting Group";
VATPercentageBus:=Item."VAT Bus. Posting Gr. (Price)";
VATPostingSetup.GET(VATPercentageBus,VATPercentageProd);
VATPerCent:=VATPostingSetup."VAT %";

EVALUATE(PriceVAT,PVP);
"Sales Price"."Unit Price Including VAT":=ConvertPriceToVAT(VATPerCent,PriceVAT);
 
IF NOT "Sales Price".INSERT(TRUE) THEN BEGIN
  MESSAGE('begin1');
  MESSAGE('check %1',"Sales Price".FINDLAST);
  IF "Sales Price".FINDLAST THEN BEGIN
    MESSAGE('begin2');
    MESSAGE('Item last %1', "Sales Price"."Item No.");
    MESSAGE('Item starting date %1', "Sales Price"."Starting Date");
    MESSAGE('Item ending date %1', "Sales Price"."Starting Date"-1);

    "Sales Price"."Ending Date":="Sales Price"."Starting Date"-1;
    "Sales Price".MODIFY(TRUE);
  END;
END;

RockWithNAV

May be if you can make your Question Short and let us know what exactly you unable to achieve?

catiamatos1991

I have a few rows in sales price table for a specific product ok? So when I upload a textfile using a xmlport I want to fill the last row filled, before the import and with my code I ca'nt find it ( IF "Sales Price".FINDLAST) liek the image.

At the moment i'm filling the last row but the previous before the last is not being filled

Juhl

Close the previous record in the OnInsert or OnModify event of the new record.
Or put it all through price worksheet/ journal

catiamatos1991

But where I will do that? In the code unit I created? If yes in each event?

Juhl

LOCAL [EventSubscriber] SalesPriceOnAfterInsert(VAR Rec : Record "Sales Price";RunTrigger : Boolean)
IF NOT RunTrigger OR Rec.ISTEMPORARY THEN
  EXIT;

CLEAR(xSalesPrice);
xSalesPrice.SETRANGE("Item No.", Rec."Item No.");
xSalesPrice.SETRANGE("Sales Type", Rec."Sales Type");
xSalesPrice.SETRANGE("Sales Code", Rec."Sales Code");
xSalesPrice.SETFILTER("Starting Date", '<%1', Rec."Starting Date");
xSalesPrice.SETRANGE("Currency Code", Rec."Currency Code");
xSalesPrice.SETRANGE("Variant Code", Rec."Variant Code");
xSalesPrice.SETRANGE("Unit of Measure Code", Rec."Unit of Measure Code");
xSalesPrice.SETRANGE("Minimum Quantity", Rec."Minimum Quantity");
IF xSalesPrice.FINDLAST THEN BEGIN
  xSalesPrice.VALIDATE("Ending Date", Rec."Starting Date" - 1);
  xSalesPrice.MODIFY(TRUE);
END;

catiamatos1991

But I define that piece of code in a codeunit created by me but where I link that event to the subscriber itself in
table Sales Price?

Juhl

yes

catiamatos1991

But I don't need to define anything in Sales Price table? In this table I don't have anything in OnInsert() function linked to any event...

Juhl

No, no need to touch that.

catiamatos1991

With this piece of code it will run ALWAYS when a OnInsert occurs in this table? Sorry but i'm new in navision events world !

Juhl

Yes, if runtrigger is true

catiamatos1991

Thanks. Just a question. xSalesPrice is a global (record of type Sales Price). Is not necessary to initialized this var? Or say that xSalesPrice:=Rec ?