Can I populate Ending Date on Sales Price Table

Business Central/NAV Developers Forum
1

Ending Date has not been enterted on the sales price. so where there is new line with new start date I’d like to enter a ending date for the old sales price. This will enable the std nav funct. to select the correct sales price against orders.

Sales Price Table Sample.

Item No. Sales Type Sales Code Starting Date Ending Date Unit Price
197764 Customer Price Group FRANCHISE 08/06/2010 13.04
197764 Retail Price Group ALL 08/06/2010 16.66
197764 Retail Price Group ALL 11/11/2010 20.83
197764 Retail Price Group EIRE 08/06/2010 19.83
197764 Retail Price Group SHOW 01/04/2011 12.49

therefore order should pick up £20.83 rather than £16.66.

I tried the following…

SalesPrice.SETRANGE(“Item No.”,“Item No.”);
SalesPrice.SETRANGE(“Sales Type”,“Sales Type”);
SalesPrice.SETRANGE(“Sales Code”,“Sales Code”);
IF SalesPrice.COUNT > 1 THEN
REPEAT
IF SalesPrice.“Ending Date” = 0D THEN
TmpSalesPrice := SalesPrice;
TmpSalesPrice.INSERT;
UNTIL SalesPrice.NEXT = 0;

//CLEAR(SalesPrice);
SalesPrice.LOCKTABLE;

Expdate := ‘1Y’;
//TmpSalesPrice.RESET;
IF TmpSalesPrice.FINDSET THEN
REPEAT
// SalesPrice := TmpSalesPrice;
Refdate := “Starting Date”;
SalesPrice.“Ending Date”:= CALCDATE(Expdate, Refdate);
SalesPrice.MODIFY(FALSE);
UNTIL TmpSalesPrice.NEXT = 0;

2

Hi Paddy ,

Do you have such volume that you cannot update manually ?

Is this a report that you built?

Where do sales type and code come from in your code ?

g.

3

Hi Faludigabor…

The Volume is LARGE, hence why I want to create a one off report to populate the ending dates

The Sales Type comes from the Retail Price Grp.

4

Ok here is how I would go about it :

run a report over price records that have 0D as end date.

Check if for its Sales Code item code there is an earlier start date if so update that earlier record(s) with end date of this record.

This then would cover all items.

What do you reckon ? Make sure you create proper backup.

Gabor