Table Sales Price updating all prices with SQL

Business Central/NAV Technical Forum
1

Hi, I am new to Navision and in the job…

I am charged with updating all prices and as far as I have found they are all in the table.

Hopefully somebody has done this earlier

I see in the table there is a lot of prices with no end date.

The job I have to do is:

All items with no Ending Date shall have end date (28.03.2009) .

Insert new price from a txtfile with Starting Date 01.03.2009 and Ending Date 28.02.2010

If the latest changed price has Starting Date older than 01.03.2009 and Item No_ is not in the sourcefile(txtfile) insert new line with copy of the old line but new start/end date and price 10% higher than the latest price.

Here are some preliminaries of an SQL to do this - but I would be very happy if somebody allready has this one solved.

Regards

Update set [Ending Date] = ‘2009-02-28’ where [Ending Date] is null and [Starting Date] <= ‘2008-03-01’

Update prices and in some of the raise with 10% (1.1)

Insert into ([Item No_], [Sales Type], [Sales Code], [Starting Date], [Currency Code], [Variant Code],
[Unit of Measure Code], [Minimum Quantity], [Unit Price], [Price Includes VAT],
[Allow Invoice Disc_], [VAT Bus_ Posting Gr_ (Price)], [Ending Date], [Allow Line Disc_])
Select t1.[Item No_], t1. [Sales Type], t1. [Sales Code], ‘2009-03-01’, t1. [Currency Code], t1. [Variant Code],
t1. [Unit of Measure Code], t1. [Minimum Quantity], t1. [Unit Price] * 1.1, t1. [Price Includes VAT],
t1. [Allow Invoice Disc_], t1. [VAT Bus_ Posting Gr_ (Price)], ‘2010-02-28’, t1. [Allow Line Disc_]
From t1
left join t2 on t1. [Item No_] = t2. [Item No_] and t1.[Sales Type] = t2.[Sales Type]
and t1.[Sales Code] = t2.[Sales Code]
Where t1.[Ending Date] = ‘2009-02-28’ and t2. [Item No_] is null

#primary keyes are expected to be [Item No_], [Sales Type] and [Sales Code]

2

Theoretically, you can achieve your goal by means of TSQL script, although it’s not recomended to manipulate NAV data this way - bypassing NAV logics. Tables you need to update are not very sensitive to such manipulations (in contrary to, say, Ledger tables, which NEVER should be manipulated like this !!) , BUT you must know WHAT you are doing and have a good understanding of data structure.

As

I would suggest you ask help to your Partner, because seemingly you don’t have the neccessary knowledge yet (for example, you have written “… IS NULL…”, but ALL fields in ALL tables in NAV have constraint NOT NULL, and empty Date field actually contains ‘1753-01-01 00:00:00.000’).

Even if I or somebody else here writes for you the correct script, the consequences of doing something wrong will be so cruical and afterwards corrections next to impossible - so I once more advise you NOT to mess with NAV data yourself and ask help from your Partner.

3

Hi Arnfinn,

Welcome to the user group and the world of Dynamics NAV! :slight_smile:

Before you start writing complex SQL scripts to update NAV, then I think it’s important that you understand Navision.

Dynamics NAV on SQL Server is NOT a SQL Database!
Yes it runs on the SQL Server, it looks like a SQL database. But in really it’s a NAV database.

So what do I mean with this statement? In most other SQL based applications you find that the application actually uses SQL Server and all the great things you can do with SQL Server. You most often sees a lot of Views and Stored Procedures. Navision doesn’t use that at all (except for AD integration). All of Navision business logic is stored inside the application and cannot be accesed directly from the outside.

That all together means that most of the logic is hidden from you when you see the database from the outside. It also means that you should really stay off changing the database from the outside of Dynamics NAV (ex. using SQL Scripts), unless you really knows the database and application 100% and has been working with it for many years.

And you don’t listen to my advise (which of course is up to you), then you should at least understand that you should always test this on a test database, never run such scripts directly on your production database. At least not if you like your new job and like to keep it! [;)]

4

Hi, Arnfinn;

Instead of direct answer to your question you got 2 almost simultaneous serious warnings from Erik and me what NOT to do… Believe, that it is right - you at least first asked for help BEFORE doing such things, but here in forum you can find many questions how to repair database AFTER such direct data manipulation. This is why our reaction was somewhat sharp - trying to prevent you from wrong actions…

What you CAN do - instead of TSQL look towards DataPorts (XMLports) - its the NAV way of importing data into the database. Sales prices/discounts are regularily inserted/updated by means of DataPort both in startup of NAV and further changes maintenace, as this is usually a great amount of data and takes long time to key in (especially, if you are, say, a wholesaler and have tenths of thousands of Items, many Customer Price groups, Customer Discount Groups and Campaigns). Of course, a simple DataPort won’t be enough in your case as you described it, you’ll need to add some code to proceess the data import, but such method is far more safer than TSQL script.

And, as Erik wrote - always first test your solution on non-production database, because you haven’t UNDO feature, if something goes as you didn’t expect.

Ha det godt, og på gjensyn [:D]

5

OK - info taken. The reason I asked for this is that as I understand it the table Sales Price is where all prices are stored. And the state of prices are not as the should be, that means I have to update all prices, set end date on a lot of them and all prices who have not been updated in the last year shall be raised by 10%.

Then over to the viable option as I understand it - dataports, any poiters to what I should read on this? And also reading about coding?

And I never start changing a live DB before I have validated the changes, and the first datadump into the DB is always only a few records so I can verify the changes in the table and the application.

Thanks for the swift reply and the advice

Regards

Arnfinn

6

Arnfinn,

Previously forgot to mention a simple opportunity - for Price update you can use Adjust Item Costs/Prices… (Inventory / Periodic Activities)…