Creating a view across all companies–part 1

The Linked Table

image

Have you ever wanted to search across multiple companies in a NAV database simultaneously? It's really not that difficult as long as you're using a SQL database. In this series of blog posts, I'll give you a step by step guide on how to create a combined vendor list. This principal can be applied to any of the tables in NAV. First of all, we need to cover some basics.

NAV has the facility to create a table definition that is linked to a view in the database that has the same name as the table. This can be a useful way to directly access data from other systems from within NAV, but it can also be useful for aggregating data that already exists in the NAV database. This could be combining data from multiple companies into a single view, as in this example, or could be combining G/L Entries and Dimensions into a single screen with the ability to search and filter on the dimensions. You could even use this technique to speed up slow processes by using SQL's powerful set-based queries to replace a lot of effort needed by NAV's much slower cursor-based operations.


To follow this example you'll need a sample NAV database that you can play in without breaking anything and access to SQL Management Studio.

Getting Rid of Invalid Characters

If you’ve ever looked at the table names in a SQL database for Dynamics NAV, you’ll have noticed that the table names have the company name at the start and that certain characters have been replaced. In the demo database for New Zealand, we have two companies: CRONUS Australia Pty. Ltd. and CRONUS New Zealand Ltd. If I look for the Vendor table in SQL I’ll see the following two table names:

  • CRONUS Australia Pty_ Ltd_$Vendor
  • CRONUS New Zealand Ltd_$Vendor

As you can see it’s basically the company name with a dollar sign followed by the table name, but some characters have been replaced. This is because when NAV creates the underlying SQL tables, it replaces some “invalid characters”. It does the same for the field names too.

In this first part of our series on creating a view across all companies, we’ll create a function that will help us find the correct company name as used as the prefix for NAV tables.

The characters that get replaced are stored in a field called invalididentifierchars in a system table [$ndo$dbproperty] (the square brackets aren’t actually part of the table name, they just make tell SQL to ignore funny characters like the dollar sign and any spaces that may be part of the table name). There’s another useful field in the [$ndo$dbproperty] table that we’ll need for this exercise called convertidentifiers. This tells the system whether we want to bother converting identifiers or not. You can change both of these fields by going to the Classic Client for SQL and selecting Database > Alter from the File menu. Click on the Integration tab and you’ll see the options.

image

We need to check to see if the Convert identifiers flag is set and, if it is, remove each of the characters from our company name and replace with an underscore.

I’m going to create a SQL Function called ConvertInvalidChars that will do this for me. Here’s the SQL code. You should execute this in the NAV database.

IF OBJECT_ID( N'dbo.ConvertInvalidChars',N'FN') IS NOT NULL DROP FUNCTION dbo.ConvertInvalidChars;
GO
CREATE FUNCTION dbo.ConvertInvalidChars
(     
       @p_StringToConvert AS VARCHAR(250)
)
RETURNS
VARCHAR(250)
AS 
 BEGIN
    DECLARE @InvalidIdentifierChars AS VARCHAR(128)
    DECLARE @X AS INTEGER
    DECLARE @ConvertIdentifiers AS INTEGER
    
    SELECT     
               @ConvertIdentifiers = convertidentifiers,
               @InvalidIdentifierChars = invalididentifierchars
    FROM       
               [$ndo$dbproperty]
    
    IF @ConvertIdentifiers <> 1 RETURN(@p_StringToConvert)
    
    SET @X = 1
    
    WHILE @X <= LEN( @InvalidIdentifierChars)
    BEGIN
        SET @p_StringToConvert = REPLACE( @p_StringToConvert,SUBSTRING( @InvalidIdentifierChars,@X,1),'_')
        
        SET @X = @X + 1
    END
    
    RETURN(@p_StringToConvert)
END
GO

Testing our Function

That previous bit of SQL will give us a SQL function that we can use in our code later on. Here’s an example of a select statement that uses the function.

SELECT     
           Name,
           [No Invalid Chars Name] = dbo.ConvertInvalidChars( Name),
           [NAV Vendor Table Name] = '[' + dbo.ConvertInvalidChars( Name) + '$' + dbo.ConvertInvalidChars( 'Vendor') + ']'
FROM       
           Company

and here’s the output from that select statement.

Name    No Invalid Chars Name NAV Vendor Table Name
CRONUS Australia Pty. Ltd. CRONUS Australia Pty_ Ltd_ [CRONUS Australia Pty_ Ltd_$Vendor]
CRONUS New Zealand Ltd. CRONUS New Zealand Ltd_ [CRONUS New Zealand Ltd_$Vendor]

Hopefully you can see where I’m going with this. Next week, I’ll show you how we are going to use this function to build a SQL View for combining multiple company tables together.


Related
Recommended