Archive for January, 2010

Size (KB) in database information (Tables)

Monday, January 18th, 2010

Some months ago, i was trying to reduce the space of a 40SP1 database ,and i was wondering why navision holds strange values in File->database->information->tables->Size(KB)
Example: i have Item Ledger Entry table = 27GB in navision, but when i go to the properties of the table through SSMS (2005) i have Index = 2gb, Data = 900MB.
I think to be smart in math, and i think that 2gb + 900mb = nearly 3gb…
after this, i tried to find out a script in order to retrieve the size of the tables through sql, and i found this
--**************************************
-- Name: Get SQL Table Size - Table and Index Space - Row Count
-- By: Zxtreme
--
--
-- Inputs:See commented code
--
-- Returns:See commented code
--
--Assumes:None
--
--Side Effects:none
--This code is copyrighted and has limited warranties.
--Please see http://www.Planet-Source-Code.com/xq/ASP/txtCodeId.732/lngWId.5/qx/vb/scripts/ShowCode.htm
--for details.
--**************************************
SET NOCOUNT ON
/*DATABASE TABLE SPY SCRIPT
Micheal Soelter
1/24/03
DESCRIPTION
Returns Table Size Information
SORTING USAGE
@Sort bit values
0 = Alphabetically by table name
1 = Sorted by total space used by table
*/
DECLARE @cmdstr varchar(100)
DECLARE @Sort bit
SELECT @Sort = 0 /* Edit this value for sorting options */
/* DO NOT EDIT ANY CODE BELOW THIS LINE */
--Create Temporary Table
CREATE TABLE #TempTable
( [Table_Name] varchar(50),
Row_Count int,
Table_Size varchar(50),
Data_Space_Used varchar(50),
Index_Space_Used varchar(50),
Unused_Space varchar(50)
)
--Create Stored Procedure String
SELECT @cmdstr = 'sp_msforeachtable ''sp_spaceused "?"'''
--Populate Tempoary Table
INSERT INTO #TempTable EXEC(@cmdstr)
--Determine sorting method
IF @Sort = 0
BEGIN
--Retrieve Table Data and Sort Alphabetically
SELECT * FROM #TempTable ORDER BY Table_Name
END
ELSE
BEGIN
/*Retrieve Table Data and Sort by the size of the Table*/
SELECT * FROM #TempTable ORDER BY Table_Size DESC
END
--Delete Temporay Table
DROP TABLE #TempTable

…and i found that Item Ledger entry hase 15GB(!) of unused space…Navision calculation, sums up also the sift data, then we can pair the Size (KB) amount of nav and the Size calculated from SQL Script(s):
Size (KB) = “table data” + “table indexes” + “unused space of the table” + “sum of all sift data” + “sum of all sift index(es)” + “sum of all sift unused space”
It appears that the Size (KB) field reads also the “unused space” of the table, while the “File->Database->Information->Database Size (KB)” does not. Moreover, that “unused space” is not even allocated by sql: it appears that only navision “size (kb)” fields considers it.

If you notice a large amount of this “unused space”, be careful and check your navision build, because you could be affected by this bug. If you see negative values, just close&reopen the client

Number Series Renew

Friday, January 15th, 2010

Sometimes new year means new no. series to create. Booooring…

To make this thing faster and less crappy for me or for a customer that does this alone, i’ve created this form (it’s rude, i know, but i’ve done it in 2 hour one morning after a week of holidays). I have to thank Michal K. from Czech Republic (a key user), who inspired me to do this.

And now, i’ll go for the explanation: the form only works in versions 5.00 onwards, because i use property “sourcetable temporary”. I load the form with the last No. series line of each No. serie: in this way the user does not have to browse no. series table and drill down through each record for each No. serie he wants to update. Moreover, the user can press F3 to insert a new line right under the no. serie he wants to update, copy the value of the no. serie with F8 and then populate the necessary fields:

-”Line no.” is automatically set to last line “line no.” + 10000
-”starting date” is automatically set to last line “starting date” + 1Y.
-”starting no.” is automatically set to last line “starting no.”

In short, the user can insert a new number series by clicking F3, F8, edit the number, close the form. (multiple lines can be created, for sure). Also, existing no. series lines can be modified.
Thanks to the onvalidate triggers on the standard no. series table, all the business logic is correctly executed while inserting/modifying lines.

As i said, the table being modified in the form is only temporary, but if you take a short look to oncloseform trigger, you’ll notice that i flush the temptable to the real table.

I’ll also provide a <5.00 version (it also includes a codeunit to populate the temptable): No Series Renew for previous versions

I hope this form will be useful for you, and now, let’s get back to my work…have a nice day

Exchange Rates Handler

Wednesday, January 13th, 2010

I have had to deal with exchange rates, and a thing I found really boring is to manage all the cases below:
if i start from LCY amount and i put it on FCY amount then use function exchrateLCYtoFCY
if i start from FCY amount and i put it on LCY amount then use function exchrateFCYtoLCY
and so on….
the function i post here, receive the six parameters, and depending on the values of CDFromCurrency and CDToCurrency decides what is the function to run (and if it must be run). At the end, it rounds the value to the correct amount.
Here are the input parameters with a little explanation:

-vDECAmount: amount to be translated. if 0 nothing happens
-vcdfromcurrency,vcdtocurrency: self documenting…as always, blank is LCY. If they’re the same, only rounding is performed
-vgmadate: date reference to calculate the conversion factor
-vDECCurrFactor: if this is =0 or =1, the standard function ExchangeRate is calculated, otherwise, the input value becomes the currency factor
-vTFUnitAmount: true if the result have to be rounded with unit-amount rounding precision, otherwise the amount will be modified with amount rounding precision

i’m trying to understand how to post code in a good way :)…well, after some tries, i think i cannot post it correctly, so i’ll upload the files…if you don’t want to dowload the files here Exchange Rates Utility, just go to this topic in mibuso ;)
Enjoy!

My first blog

Wednesday, January 13th, 2010

Hi, I’m Mirko, i born in 19/02/1987 and i am a NAV developer since october 2006 and this is my first blog…i mean, my first blog ever, not only about nav… :)

As i’m a developer since i begun to work, i learnt that there are 2 important attributes that a developer should have in my opinion: lazyness and curiosity.

Personally, i have both these: i’m really really lazy, and this does not help in real-life…but what about my work?It is really helpful, because i found myself to develop functions that are saving me to write the same lines of code too much times (write a lot of code a waste of fingers skin ;) ), structure my code in a smart way, easily adapt my applications to change requests and more important, spare some time to satisfy my curiosity :)

In the always changing world of IT, curiosity is a must-have: through the net you can easily find nearly everything you need, keep yourself updated with new technologies and find better ways to do something you already know how to do. Books are important, too…but i don’t like to study even if it’s strictly necessary: i prefer to bang my head through the solution, the so called “learning by doing”

The idea of a blog came up in my mind this morning, and i was so happy when i’ve see how simple is to set it up.

I thought a lot about which title my blog should have, and i tried with “Working-around NAV”: the reasons of this name are 2:

I like a lot to think out of the box and try different/strange solutions :) in order to make my applications work. Sometimes i complicate my own life, sometimes i have brilliant ideas. I like to call the strange and complicated solutions “workarounds”.

The brilliant ideas, instead, are usually inspired by other developers’ ideas that i like to deeply analyze/modify/renew/destroy and rebuild/adapt to my needs. This is the literal “WORKING taking a look AROUND the net” meaning.

I hope to post something soon (i have 1 or 2 things to post) and i hope that you, readers won’t blame me too much…i’m a young and unexpert blogger :(

I hope that my english is clear enough for you all, have a nice day!