Versions Compared

Version Old Version 6 New Version 7
Changes made by

Daria Chebotaieva

Viktoria Karakiko

Saved on

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.

Contents

Table of Contents
minLevel1
maxLevel3
excludeContents

...

Before starting working with the datagroup, you should set up Customized topics and Customized Topics views codes.

Go to Setup > General Codes > Customized > Topics Codes and add the customized topics you need.

Info

  • ou may You can define an unlimited number of Customized topics in the system.

  • Any information within each topic, any quantity number of fields (all with an individual label and length), may be added.

  • A topic being in use cannot be deleted. You can only modify its name.

...

  • Code and Topic Name.

  • Sort defines the order of topics ('0' means the very first).

  • Is Passive: if set, the topic becomes not visible.

  • Icon - the .bmp should be used

  • Security Code field. When a security code other than '0' is defined, you can set up Read, Write or None access to this Customized Topic in Adonis Control Centre > General Codes > Customized Topics. Customized topics with blank Security Code or = '0' are available for all users.

  • Options allow defining the number of records in the topic.Select the Unique Entry option to add a single record to the topic. In case the option is cleared, you can add multiple records to the topic.


Steps 2: Add Icon

For any topic you can add an Icon - the .bmp should be used

...

These are the special characters used in the first field of the mask:

Character

Meaning in Mask

!

If the *!* character appears in the mask, optional characters are represented in the text as leading blanks. If the! character is not present, optional characters are represented in the text as trailing blanks.

>

If the > character appears in the mask, all characters that follow are in uppercase until the end of the mask or until the < character is encountered.

<

If the < character appears in the mask, all characters that follow are in lowercase until the end of the mask or until the > character is encountered.

<>

If these two characters appear together in the mask, no case checking is done and the data is formatted with the case the user uses to enter data

**

The character that follows the ** character is a literal character. Use this character to use any of the mask special characters as a literal in the entered data.

L

The L character requires an alphabetic character only in this position. For the US, this is A-Z, a-z.

l

The l character permits only an alphabetic character in this position, but doesn't require it.

A

The A character requires an alphanumeric character only in this position. For the US, this is A-Z, a-z, 0-9.

a

The character permits an alphanumeric character in this position but doesn't require it.

C

The C character requires an arbitrary character in this position.

c

The c character permits an arbitrary character in this position but doesn't require it.

0

The 0 character requires a numeric character only in this position.

9

The 9 character permits a numeric character in this position but doesn't require it.

#

The # character permits a numeric character or a plus or minus sign in this position, but doesn't require it.

:

The : character is used to separate hours, minutes, and seconds in times. If the character that separates hours, minutes, and seconds differs from the regional settings of the Control Panel utility on your computer system, that character is used instead.

/

The / character is used to separate months, days, and years in dates. If the character that separates months, days, and years differs from the regional settings of the Control Panel utility on your computer system, that character is used instead.

;

The; character is used to separate the three fields of the mask.

_

The character automatically inserts spaces into the text. When the user enters characters in the field, the cursor skips the character.

Any character that does not appear in the preceding table can appear in the first part of the mask as a literal character.

...

So, for customized topics replication, you need to add:

  1. 3 triggers (in SQL Studio);

  2. 3 replication fields (in SQL Studio);

  3. Replication tasks and task direction types (in APM):

  • Odd numbers are for the Host (main site). Even numbers will be for the Satellite tasks.

  • Tasks need to be linked to the correct task directions types.

...

To set-up replication for customized topics please follow the below points:

  1. After a new customized topic code in General Codes Tables (GCT) is created, a new table is added to the database, like dbo.PW001UXX (where XX is the sequential number) - further U01.

  2. With replication, only a code to GCT is replicated. If you want to add this topic for a person you will get a message that table U01 is not found.

  3. To fix the error in point 2 you need to open GCT > Customized > Topics, go to the code in question, open it, make any change (e.g. add and remove the space in the name) and confirm.
    The message will say that table U01 doesn't exist and if you want to recreate it, that is successfully done after clicking OK.

    Image Modified

  4. None of the updates are replicated until you set up the replication.
    Don't enable replication tasks on any sites until database structure is in synchrony on all sites!!

Below steps are to be followed on all sites for all customized topics tables

  1. Recreate a table manually (see Point 3 above).

  2. Create missing fields (repl_ModifiedBySite, repl_ModifiedDate, repl_GMTModifiEDDate) with the help of the below script.

  3. Open SQL Manager Studio or another tool and run new query for corresponding database (e.g. Adonis):

    Code Block
    ALTER TABLE dbo.PW001uXX ADD repl_ModifiedBySite INT NULL, repl_ModifiedDate DATETIME NULL, 
repl_GMTModifiEDDate DATETIME NULL 
GO


    where XX is the sequential number, e.g. PW001U01, PW001U02, etc

  4. Create missing triggers (TI, TU, BD – for insert, update, delete) with the below scripts.

  5. Open SQL Manager Studio or another tool and run new query for corresponding database (e.g. Adonis):

    Code Block
    CREATE TRIGGER [dbo].[TI_PW001uXX] ON [dbo].[PW001uXX] FOR INSERT

AS

SET NOCOUNT ON

DECLARE @nSite as Varchar(8) , @PKeys as Varchar(300), @insGMT as DateTime

BEGIN

IF system_user != 'REPL' BEGIN

SELECT @nSite=SITENR FROM repl_SITE WHERE IsCurrentSite = 1;

UPDATE dbo.PW001uXX SET repl_ModifiedBySite = @nSite, repl_ModifiedDate = GetDate(), repl_gmtmodifieddate = GETUTCDATE()

FROM dbo.PW001uXX, inserted

WHERE dbo.PW001uXX.SEQUENCENO=inserted.SEQUENCENO

END ELSE

if substring([dbo].[ad_ReplicatorVersion](),1,1) < 4

BEGIN

SET @Pkeys = (SELECT LTRIM(STR(inserted.SEQUENCENO)) FROM inserted)

SET @insGMT = (SELECT inserted.REPL_GMTMODIFIEDDATE FROM inserted)

IF EXISTS (SELECT * FROM dbo.Repl_Deleted_Log where TABLENAME = 'dbo.PW001uXX' AND PKS=@PKeys AND GMTMODIFIEDDATE>@insGMT)

               ROLLBACK TRANSACTION

END

END

GO


    Where XX is the sequential number, e.g. PW001U01, PW001U02, etc.

    Code Block
    CREATE TRIGGER [dbo].[BD_PW001uXX] ON [dbo].[PW001uXX]

FOR DELETE AS

SET NOCOUNT ON

IF SYSTEM_USER <> 'REPL'

BEGIN

DECLARE

@SiteNr Integer,

@SqlStat Varchar(3000),

@Pkeys Varchar(300),

@RowId numeric;

DECLARE crs_Deleted CURSOR

FOR

SELECT 'DELETE FROM dbo.PW001uXX WHERE '+' dbo.PW001uXX.SEQUENCENO='+LTRIM(STR(deleted.SEQUENCENO))+' AND (( REPL_GMTMODIFIEDDATE < ''' + RTRIM(CONVERT(CHAR,GETUTCDATE(),121)) + ''')

OR (REPL_GMTMODIFIEDDATE IS NULL)) ' FROM deleted;

OPEN crs_Deleted;

DECLARE crs_Deleted_Pkeys CURSOR

FOR

SELECT LTRIM(STR(deleted.SEQUENCENO)) FROM deleted;

OPEN crs_Deleted_Pkeys;

SELECT @SiteNr=SiteNr FROM dbo.repl_SITE WHERE IsCurrentSite = 1;

FETCH NEXT FROM crs_Deleted INTO @SqlStat;

WHILE (@@FETCH_STATUS <> -1)

BEGIN

Exec repl_GenerateRowId @RowId OUTPUT;

INSERT INTO dbo.repl_Deleted (PK,SiteNr,Select_Stat,repl_ModifiedBySite,repl_ModifiedDate)

VALUES((@SiteNr*10000000)+@RowId,@SiteNr,@SqlStat,@SiteNr,GETDATE());

FETCH NEXT FROM crs_Deleted INTO @SqlStat;

END

CLOSE crs_Deleted;

FETCH NEXT FROM crs_Deleted_Pkeys INTO @Pkeys;

WHILE (@@FETCH_STATUS <> -1)

BEGIN

INSERT INTO dbo.Repl_Deleted_Log (TABLENAME, PKS, GMTMODIFIEDDATE)

VALUES('dbo.PW001uXX' ,@Pkeys, GETUTCDATE());

FETCH NEXT FROM crs_Deleted_Pkeys INTO @Pkeys;

END

CLOSE crs_Deleted_Pkeys;

DEALLOCATE crs_Deleted;

DEALLOCATE crs_Deleted_Pkeys;

END

ELSE

BEGIN

DECLARE @gmtDate DATETIME

DECLARE crs_Deleted_Pkeys CURSOR

FOR SELECT LTRIM(STR(deleted.SEQUENCENO)) ,REPL_GMTMODIFIEDDATE FROM deleted;

OPEN crs_Deleted_Pkeys;

FETCH NEXT FROM crs_Deleted_Pkeys INTO @Pkeys, @gmtDate;

WHILE (@@FETCH_STATUS <> -1)

BEGIN

INSERT INTO dbo.Repl_Deleted_Log (TABLENAME, PKS, GMTMODIFIEDDATE)

VALUES ('dbo.PW001uXX' ,@Pkeys,@gmtDate);

FETCH NEXT FROM crs_Deleted_Pkeys INTO @Pkeys,@gmtDate;

END

CLOSE crs_Deleted_Pkeys;

DEALLOCATE crs_Deleted_Pkeys;

END

GO

  6. Add necessary Task Direction Types and Replication Tasks manually to APM > SETUP > Adonis Replicator (see example below).

    ! Note: All Task Direction Types and Replication Tasks should be identical on all sites (numbers, names, queries, import table names, etc.)

    Image Modified

    Standard query for HOST_ tasks is

    Code Block
    SELECT * FROM dbo.PW001UXX WHERE repl_ModifiedDate >= #LR# AND repl_ModifiedBySite <> #DSN#


    Standard query for SAT_ tasks is

    Code Block
    SELECT * FROM dbo.PW001UXX WHERE repl_ModifiedDate >= #LR# AND repl_ModifiedBySite = #CSN#


    Where XX is the sequential number, e.g. PW001U01, PW001U02, etc.

    Image Modified

  7. Adjust/synchronize column names on all sites for all customized topics tables.

    Open SQL Manager Studio or another tool, find a table in question, expand the Column list and update the new column name accordingly (as it is replicated with the wrong name, like REPLFIELD_UFIELD7, but on originated site, it is added to the database with name UFIELD7).

    Image Modified

    ! Note: You need to follow the procedure described in point 4 with every newly added customized topic.

  8. Once followed all above steps, replication tasks and direction types can be enabled (set the check-boxes in Active column) to make sure that replication is working fine.

Functionality

The Customized Topics datagroup is intended to store any type of specific information about any crewmember.

...

There are two optional ways of Customized Topics menu presentation depending on the state of Display Customized Topics as Drop-Down List global option:

...



Ribbon icons corresponding to customized topics will indicate a number of records inside this topic for tagged person, applying a numeric badge. There is no need to open a datagroup to check for existing records.


To set it up, please:

  1. Remove checkbox for Global Options > Customized Topics > Display Customized Topics as DropDown List

  2. Set checkbox for User Options > Program > Display Record Count Badges for Customized Topics on a Ribbon

Creating Records

When a user accesses the Customized datagroup, he will see exactly the same layout as defined under Editor Form in General Codes.

...