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
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:
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
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.
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)]
See Link [ ^] . I think he meant he wants to create a SQL Query that Alters.
A little knowledge is dangerous.
/TH
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
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.
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.
