ApplicationXtender - Annotations Naming Convention

Summary

What is the naming convention for annotations?

Details

Annotation filenames are in the form of pp000000.ext where pp is the documents pathid, 000000 is the objectid, and the extension is .ano if the subpagenum is either 1 or 0. If the subpagenum is not 1 or 0, the 3-digit subpagenum itself will be used. The pathid, objectid, and subpagenum are encoded base 32 as follows:

Max Pathid 32^2 = 1024

Objectid 32^6 = 1073741824

Subpagenum 32^3 = 32768

When the objectid is greater than or equal to 1073741824, a new encoding scheme is used, the filename will begin with 'ZZ' and no pathid will be added. The number encoded for the objectid will be less the base of 1073741824.

Example:

Objectid = 1073741825

1073741825 / 1073741824 = 1

Annotation file name = zz000001.ext

If the subpagenums encoded extension name happens to be'.BIN', it will be changed to '.BI' to avoid confusion with document.bin files.

Note: Pathid is the document pathid not the annotation pathid.

Below is a script to determine the Annotation name and path using a SQL function. Run the below against the AX database. This creates a function to identify the path and annotation file name.

The script assumes no database schemas are in place. If a database schema is associated then the below script needs to be updated accordingly.

/****** Object:  UserDefinedFunction [dbo].[fn_objidToAnnoid]    Script Date: 8/10/2021 11:18:36 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [dbo].[fn_objidToAnnoid]
(
@val as BigInt,
@appname as VarChar(255)
)
returns varchar(max)
as
Begin
 
 DECLARE @base as int
 DECLARE @subpagenum as VarChar(3)
 DECLARE @pathid as TinyInt
 DECLARE @annopathid as TinyInt

 SET @subpagenum = ''
 SET @base = 32
 
  /* Check if value is valid and if we get a valid base (2 through 36) */
  If (@val<0) Return Null;
 
  /* variable to hold final answer */
  Declare @answer as varchar(63);
 
  /* Following variable contains all 
     possible alpha numeric letters for any valid base 
  */
  Declare @alldigits as varchar(36);
  Set @alldigits='0123456789abcdefghijklmnopqrstuvwxyz'
 
  /* Set the initial value of 
     final answer as empty string 
  */
  Set @answer='';
 
  DECLARE @oval AS BigInt
  
  SET @oval = @val
 
  /* Loop while the source value remains greater than 0 */
  While @val>0
  Begin
    Set @answer=Substring(@alldigits,@val % @base + 1,1) + @answer;
    Set @val = @val / @base;
  End
 
  DECLARE @path VARCHAR(3)
  
  SET @path = ''
  SET @pathid = (SELECT pathid from ae_apps where appname = @appname)
  SET @annopathid = (SELECT annpath from ae_apps where appname = @appname)
    
  While @pathid > 0
  Begin
    Set @path = Substring(@alldigits, @pathid % @base + 1,1) + @path;
    Set @pathid = @pathid / @base;
    
  End
  /* Return the final answer */
  DECLARE @annopath AS VARCHAR(MAX)
  
  SELECT @annopath = dbo.ae_paths.path FROM dbo.ae_paths WHERE pathid = @annopathid
  
  DECLARE @p1 AS VARCHAR(4)
  DECLARE @p2 AS VARCHAR(4)
  
  SET @p1 = CONVERT(VARCHAR, (@oval / 1024) / 1024)
  SET @p2 = CONVERT(VARCHAR, (@oval / 1024) % 1024)
  
  DECLARE @extension CHAR(4)
  DECLARE @ans VARCHAR(4)

  --SELECT @extension = CASE WHEN CONVERT(TINYINT, @subpagenum) < 1 THEN '.ano' ELSE '.' + REPLICATE('0', 3 - LEN(@subpagenum)) + @subpagenum END
  SET @oval = @subpagenum
  SET @ans = ''

  /* Loop while the source value remains greater than 0 */
  While @oval>0
  Begin
    Set @ans = Substring(@alldigits, @oval % @base + 1,1) + @ans;
    Set @oval = @oval / @base;
  End
  
  SELECT @extension = CASE WHEN CONVERT(TINYINT, @subpagenum) < 1 THEN '.ano' ELSE '.' + REPLICATE('0', 3 - LEN(@ans)) + @ans END

  RETURN @annopath + '\' + @appname + '\' + @p1 + '\' + @p2 + '\' + REPLICATE('0', 2 - LEN(@path)) + @path + REPLICATE('0', 6 - LEN(@answer)) + @answer + @extension;
End

Use the script below to access the function

SELECT dbo.fn_objidToAnnoid (objectid, 'Appname')
-- enter the objectid and the application name from AX to get the path to the annotation.

Use the script below to identify the objectid in AX to assist in determining the associated metadata.

--@base can be anything from 2 to 36. This work is licensed under a Creative Commons Attribution 2.5 License.
CREATE FUNCTION dbo.f_convert_to_base10
  (@string VARCHAR(255), @base TINYINT)
RETURNS INT AS 
BEGIN
  DECLARE @return INT
  DECLARE @len INT
  DECLARE @finished BIT
  DECLARE @pos INT
  DECLARE @thischar CHAR(1)
  DECLARE @thisasc INT
  DECLARE @val INT
  
  SELECT @base     = 32 --CASE WHEN @base < 2 OR @base IS NULL THEN 2 WHEN @base > 36 THEN 36 ELSE @base END
  SELECT @return   = 0
  SELECT @finished = 0
  SELECT @string   = UPPER(@string)
  SELECT @len      = DATALENGTH(@string)
  -- Failsafe
  IF @len = 0
     SELECT @finished = 1
  -- Loop over all characters: capitalize first character and those after spaces, replace underscores with spaces
  SELECT @pos = 0
  WHILE @finished = 0
  BEGIN
    SELECT @pos = @pos + 1
    IF @pos > @len
       -- If we've run out of characters, we're done
       SELECT @finished = 1
    ELSE
    BEGIN
       -- Get the character (from right to left)
       SELECT @thischar = SUBSTRING(@string, (@len - (@pos - 1)), 1)
       -- Get the character's ASCII value
       SELECT @thisasc  = ASCII(@thischar)
       -- Convert to a numerical value
       SELECT @val = CASE
                       WHEN @thisasc BETWEEN 48 AND 57 -- '0' AND '9'
                         THEN @thisasc - 48
                       WHEN @thisasc BETWEEN 65 AND 90 -- 'A' (= decimal 10) AND 'Z'
                         THEN @thisasc - 65 + 10
                       ELSE 0 END
       -- Add this portion on
       SELECT @return = @return + (POWER(@base, (@pos - 1)) * @val)
    END
  END
  -- Done
  RETURN @return
END
GO

Use the below to get the returned objectid. Take the first two digits off of the annotation file name as these are used to identify the pathid and are not part of the calculations.

Example of conversion back and forth.

We have an objectid we want to determine the path to the annotation

We would run the below to get the path for objectid 984 and application APENTRY.

SELECT dbo.fn_objidToAnnoid (984, 'APENTRY') as path_to_ano

This gives us results of

We can also take the annotation name of 080000uo.ano, remove the ‘08’ prepended for the image path and the .ano extension (use 0000uo) and put it into the second function to get the objectid.

Results -

From here we can use the objectid to get the docid and other metadata needed to perform triage.