table relation in Microsoft Navision

hi all once again…
hmmm my company is using microsoft navision for the ERP process
on form ‘Sales Order’ and "Purchase Order’ usually we didn’t input data (it means we just empty the field)
now, we have more than thousands record from it…
and we want to change all those empty record with word = ‘GENERAL’

if I inject it through sql query analyzer like this (on table Sales header) :
Update [My Company$Sales Header]
set [Shortcut Dimension 2 Code]=‘GENERAL’
WHERE [Shortcut Dimension 2 Code]=’’

is it connected to another table??
if i want to change field shortcut dimension 2 code at table Sales Header, which table i must change with my sql statement?


well, it all depends on what field are you changing but, generally, it is not recommended to change any navision data outside of navision.
as for dimensions:
there is a huge code behind dimensions management and if you are not deeeeply involved in it, don’t do it :slight_smile:

or copy all of the business logic that lies behind, including all hotfixes and custom modifications, to that sql script :slight_smile:

the table i want to inject the sql statement is Sales Header
so in sales order form every shortcut dimension 2 code will be ‘GENERAL’

Don’t do that through SQL (as I can see that you are new to Navision…)
there is a number of tables that needs to be updated too.
and, you have some triggers on Sales Header/Modify.

But, you can make a small batch/report in Navision that will update your sales headers, something like:

“Sales Header”.MODIFYALL(“Shortcut Dimension 2 Code”,‘GENERAL’,TRUE);

In that case you will change dimension on those headers and activate trigger on table.

Note: You will be, probably, asked some questions on every header that is changed like “Do you want to update dimensions on sales lines?”.

Note2: make sure that you have appropriate filter on “Sales Header” before you fire MODIFYALL.

You have already been told not to do it outside of Navision so I will not repeat it [:D] Have a look in the document dimension table for one instance of the dimension where you will see the GD2 for table 36 and 37. That is of course assuming you update it on the header AND the line as you would need to to get the dimensional analysis to work.

yes i’m truly new kid in navision :stuck_out_tongue:
by the way syntax you give me → “Sales Header”.MODIFYALL(“Shortcut Dimension 2 Code”,‘GENERAL’,TRUE);
where i must execute it?
through sql server or directly by navision?

This is C/AL syntax, which means you are executing it through Navision.

First of all, Don’t do anything on real production database!!! If you want to play & test, do it on some test database.

If you do have test database, you can make an empty report, insert record variable with name “Sales Header” that references to record “Sales Header” and than in report triggers you can put this line I gave you.

But, since you don’t know even where to put code, I strongly advise you to read some books about C/AL, Administration guide, first.
Some of the book you can find on Installation CD under Doc folder.

ok2 master :stuck_out_tongue:
thanx for your advices anyway…

It is only friendly advice…

I have $20 that says he’s going to modify those records in SQL Server directly anyway [;)]

And then post “How do I fix . . . . . .” [:D]

How about $50?:stuck_out_tongue:


once you do this, and you find you have destroyed your database, please let me know. I make a living from fixing mistakes like this, so its good to see I won’t be out of a job soon. [:D] [6] [<:o)]

LOL, I’m worried by his wording “SQL Inject”

See Link [ ^] . I think he meant he wants to create a SQL Query that Alters.

A little knowledge is dangerous.


NOTE! : Listen to what the guys have recommended to you we all have years of experience, and have tried (and failed) or seen others try (and fail) in doing what you are trying to do. It is possible but your script is going to be huge, and altering a lot of tables, checking a lot of info, and in the long run the recommendations you have been made is the best possible solution to your problem. Trust US!
Sorry if I just lost you some business David [:P]

just kidding man :stuck_out_tongue:
i just do it to other database, not my main database
but if i have trouble with my database , i will contact you then
what’s your email anyway?

Stan… don’t be foolish. It is obvious from the questions that you are posting that you are not only a novice to NAV, you are also very new to SQL Server. Do yourself a favor and listen to the advice on this board.

You will miss important, no, VITAL, business logic if you insert/modify data directly into your NAV database on SQL Server. Don’t say you weren’t warned.

I think that after his short run on the other side, he is now trying to flog his wares over here.

Ohhh you’re right that guy… same one you think?

On top of all that, if some lines have been shipped and invoiced, what then. This is a great job for someone who knows what to do but never should be tried by a new person working with Navision and never with SQL. I had to do this several years ago and had about a dozen tables to update as far as I recall. 1/2 days work for an experienced person i would say.