Exporting Item with valid Sales Price and reimport

EasyEarrings

Hello All

I have been assigned the task of:

a) Creating an item export to excel that includes the most recent Sales Price from the 7002 Sales Price table.

b) The sales team then want to update the Sales Price table in excel and then import the updated Unit Price in the Sales Price.

Iam a bit of a newbie and have approached in a couple of different ways. I can export the item using a report or a dataport but im having problems getting the most recent Sales Price. This is what i have tried

1) Created a report based on the item table. Using GET to get the Unit Price from the Sales price table, this did not work i think because the item is not a key field.

2) I then added a new entry to the report for the Sales Price table but had a problem linking the most valid sales price

3) I have looked at other posts and tried to use the Sales Price Calc. Mgt. codeunit 7000. However i dont have all the valid parameters.

Does anyone know how i can add the most valid Sales Price to my item export report. Using either of the above methods or any other.

All Help will be greatfully appreciated.

Cheers

Alan

jversusj

Alan,

I do something similar - although instead of exporting I am just setting a custom boolean field (Current Price) true. I think the basic concept is the same.

I use the WORKDATE and the Starting Date, Ending Date (from Sales Price table) and check all of the possible combinations of interrelationships. Note that I wrote this when I first started this job and was trying to figure out how to program, so it isn't pretty and could probably be improved rather easily if i had time to revisit old stuff.

//the case where a price exists with no start data and no enddate
IF (SalesPrice."Starting Date" = 0D) AND (SalesPrice."Ending Date" = 0D) THEN BEGIN
...write a temp variable
//the cases where a start date exists, but no end date
IF (SalesPrice."Starting Date" <> 0D) AND (SalesPrice."Ending Date" = 0D) THEN BEGIN
         IF WORKDATE >= SalesPrice."Starting Date" THEN BEGIN
             ...write a temp variable 
//the case where end date defined, but not the start date
IF (SalesPrice."Starting Date" = 0D) AND (SalesPrice."Ending Date" <> 0D) THEN BEGIN
         IF WORKDATE <= SalesPrice."Ending Date" THEN BEGIN
             ...write a temp variable
//the case where start date and end date are defined
IF (SalesPrice."Starting Date" <> 0D) AND (SalesPrice."Ending Date" <> 0D) THEN BEGIN
         IF WORKDATE <= SalesPrice."Ending Date" THEN BEGIN
             ...write a temp variable

there is then some code to reference the temp variable to retrieve the approriate sales price and set the Current Price flag to true. It works. maybe this can help you get started?

EasyEarrings

Hello

Thank you jversusj that is very usefull when i get the correct Price from the sales price table. The Sales Price table has multiple entries for each Item. My plan was to use "Sales Price".GET("Item No.") to get the Item and then SETFILTER to make the Sales Code 02. However this is not pulling the data from the sales Price table i think due to the ITem no not being a primary key.

Do you know what function i can use to pull over the entries related to the Item code.

Cheers

Alan

jversusj

EasyEarrings wrote:

Hello

Thank you jversusj that is very usefull when i get the correct Price from the sales price table. The Sales Price table has multiple entries for each Item. My plan was to use "Sales Price".GET("Item No.") to get the Item and then SETFILTER to make the Sales Code 02. However this is not pulling the data from the sales Price table i think due to the ITem no not being a primary key.

Do you know what function i can use to pull over the entries related to the Item code.

Cheers

Alan

here is how i "start":

SalesPrice.SETFILTER("Item No.",Item."No.");
SalesPrice.SETFILTER(SalesPrice."Sales Type",'Customer Price Group');
SalesPrice.SETFILTER(SalesPrice."Sales Code",'02'); //the 02 is the code you want to look for, i edited it to use your code so it would make more sense than my codes
IF SalesPrice.FIND('-') THEN BEGIN
   REPEAT
...
UNTIL SalesPrice.NEXT = 0;

The stuff i posted first occurs in the repeat loop (along with some other stuff).

EasyEarrings

Hello

Thanks again. I will give it a go.

Cheers

Alan

Savatage

do you have form 57 Item Price Changes?
Report 792 Suggest Item Price Changes - Aloows you to filter on items or price groups or whatever - the options tab can let you adjust the prices or if you set the adjustment factor to 1 it will bring in the prices as they are.
You can do all your price editing there and then it's a click of a button to impliment them.

Plus you can filter the edited list when you impliment so you can do a little at a time.

It's really one of the greatest features in Nav as far as I'm concerned. We can adjust thousands of prices in seconds.

I've moved stuff around but it should be located in Inventory->Periodic Activities.

Note: No changes are made until you want them to be.
& I don't have table 7002 - Mine works off of table 28 Item Prices.

jversusj

Savatage wrote:

do you have form 57 Item Price Changes?
Report 792 Suggest Item Price Changes - Aloows you to filter on items or price groups or whatever - the options tab can let you adjust the prices or if you set the adjustment factor to 1 it will bring in the prices as they are.
You can do all your price editing there and then it's a click of a button to impliment them.

Plus you can filter the edited list when you impliment so you can do a little at a time.

It's really one of the greatest features in Nav as far as I'm concerned. We can adjust thousands of prices in seconds.

I've moved stuff around but it should be located in Inventory->Periodic Activities.

Note: No changes are made until you want them to be.
& I don't have table 7002 - Mine works off of table 28 Item Prices.

schucks, i don't have any of these objects. No table 28, no form 57, no report 792. What granule are those supposed to be part of?

i don't think we could use a method like that anyhow; our prices aren't updated by adjustment factors. Each item is manually adjusted. that value is put into a spreadsheet, and then the whole list gets imported via dataport. ..and yes, i know that they could just enter the price straight into the system, but i don't set policy and have a hard enough time getting valid prices from them (so maybe it is better that we use a buffer like import via dataport where i can program checks and balances).

EasyEarrings

Hello

Thank you jversusj and Savatage. I have made it work and learnt some stuff. The company also does not have those form 57 either but we can work from the export.

Thanks again for your help

Cheers

Alan

Savatage

Am I the only one? since the tables are low numbers it doesn't appear to be any kind of 3rd party.

Just to be clear this Suggest item prices report simply populates the Item Price Change table. From there you can edit & play around with the prices. Nothing is set in stone at that point. Only when your ok with the changes you can impliment them. It has an adjustment factor along with rounding precisions, but as I said you can manually change them too.

What's cool it that there are two price columns one is the original price & the 2nd is what you want to change it to. so you can see both.

When I import prices as you do I usually populate this table to double check instead of going directly into Item Price table.

EasyEarrings

Hi Savatage

I still can see it on this db or another that i have been working on. However im updating the sales price table which i think (but not sure) is different to the item price.

Cheers

Alan

David_Singleton

Savatage wrote:

...
It's really one of the greatest features in Nav as far as I'm concerned. We can adjust thousands of prices in seconds.
...

I could not agree more, it WAS a brilliant tool. But interestingly very few customers ever knew it existed. It seemed to always be one of those "hidden Navision features" that no one knows about, much like the "?" (question mark) feature in text fields, that people just seem to have missed when they read the manuals.

And for those that are asking "When was this introduced" well sorry I don't know, since it was in the first version of Navision I ever used (the DOS version) and has been in every version from then till 3.10. Of course they pulled it when they changed sales pricing in 3.60, and there really is nothing to match it now.