Setup

Creating Customized Topics

The Customized Topics datagroup is intended for storing any type of specific information.
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.

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

Any information within each topic, any quantity 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.

Click  Button to create a topic:

1. Enter Code and Topic Name.
   
   Sort defines the order of topics ('0' means the very first).
   
   Is Passive: if set, the topic becomes not visible.
   
   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.

2. Here you have a possibility to add an image (.bmp) to the topics and views.
   
   Click Manage button in the Icon section and choose the way of adding an image (from file/clipboard).
   
   

   Then this image will be displayed near the topic name in Customized Topics datagroup:
   
   
   
   Choose Clear to remove the image.
   
   
   

3. Fields Definition section:
   
   Click to add a field, type needed text, define field type.
   
   You can define fields of different types (by selecting from drop-down list):

   Text - note that you cannot use special symbols like ', " for this type.

   Date - it will be possible to use a calendar for a date selection. Field has fixed length.

   Number - you can store a numeric value only. Please define a field size.

   Password - a field of this type will be displayed using *** symbol.

   Dropdown - for this type you need to specify items available for a user selection.

   Url – for weblink.

   Enclosure – it will be possible to attach a file for the record.
   
   
   Click 3 dots near Items. List editor box appears:

   

   
   
   Click

   

   to insert a new item with description or

   

   to remove an unnecessary item from the list.
   Click OK to save information.
   
   Checkbox - the box that can define a simple state like Yes/No or True/False.
   Lookup - a field to offer a Drop-down with values taken from one of the general codes tables or organization levels. 
   
   Click 3 dots in the Size column. In appeared List editor box:

   

   
   You can select either the General Code table or the Organization level for corresponding values to be offered when filling this field.
   
   Also, you can set whether a field will be mandatory for input or not. Set the check-box in needed line in Mandatory column to make the field obligatory for filling. 
   For some field types, you can specify a mask, e.g.: for time (see Masks Specifying section). 
   
   
   You can define as many fields as you like, set their types, sizes. New fields may be added at any time. However due to database consistency requirements, no changes to field type can be made if a field has related records.
   
   It is also possible to delete posted fields, even if they do have related information. In this case a warning is shown to prevent data deletion by mistake.
   
   it is possible to set a field of a Topic to Passive, so that it would be hidden but already existing data will not be corrupt;
   
   We added possibility to add new fields under Fields Definition even if the Topic is already in use. If a field type is Dropdown – then it is also possible to add new Items even if already in use.
   
   Click

   

   to delete the field.
   
   
   
   

4. If you want, you can switch to Editor Form Layout tab. Here you can review what the form for creating a new record will look like in the Customized Topics datagroup.
   
   Click Customize button:
   

   

   
   You can also change the layout template.
   
   Possible modifications:
   drag field line and move it where you want:
   

   

    
   
   change fields horizontal/vertical positions using right-click menu commands: 
   

   

   
   
   change the field width by clicking the line and moving its borders:
   

   

    
   
   double-click the filed name to edit it: 
   

   

    
   
   change field name positions using right-click menu commands: 
   

   

   
   Click

   

   to undo the action.
   Click

   

   to bring it back.
   
   
   Click OK to save the layout.
   
   To back the default layout, click

   

   button.
   
   Do not forget to click Refresh button

   

   , each time you come back to the Customized Topics datagroup after any layout changes.|

Specifying Masks

For some field types, you can specify a mask.
Masks consist of three fields with semicolons separating them.

• The first part of the mask is the mask itself.

• The second part is the character that determines whether literal characters within the mask are saved as part of data.

• The third part of the mask is the character used to represent un-entered characters in the mask.

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

Any character that does not appear in the preceding table can appear in the first part of the mask as a literal character.
Literal characters must be matched exactly in the edit control.
They are inserted automatically, and the cursor skips over them during editing.
The special mask characters can also appear as literal characters if preceded by a backslash character ().
The second field of the mask is a single character that indicates whether literal characters from the mask should be included as part of the text for the edit control.
For example, the mask for a telephone number with area code could be the following string, let's mark the 3 fields with different colors:

• For convenience, we marked the 3 fields with different colors.

The 0 in the first field indicates that Text property for edit control would consist of 10 digits that were entered, rather than 14 characters that make up a telephone number as it appears in edit control.
A 0 in the second field indicates that literals should be omitted, any other character indicates that they should be included.
The third field of the mask is the character that appears in the edit control for blanks (characters that have not been entered).
By default, this is the same as the character that stands for literal spaces.
The two characters appear the same in an edit window.
However, when a user edits the text in a masked edit control, the cursor selects each blank character in turn, and skips over the space character.
Another example:
You need to input the mask for time (like 00:00), you need to input the mask
You can see that the mask consists of 3 fields, separated by semicolons.

• For convenience, we marked the 3 fields with different colors.

The first field in the mask 00:00 shows that you need to input 2 characters then the : will be shown and then you input 2 characters again.
The second field of the mask 1 means that the : sign will not be hidden, but will be seen. If you wanted to hide it, you would input 0 instead of 1 here.
The third field _ will show you the _ sign instead of zeros (_:_), and when you input the values, the _ signs will disappear.

Defining Topics Views

After inputting Customized Topics codes, you can define one or more Customized Topics views.
For every view, you can define what customized topics will be shown on a corresponding page.
Go to Setup > General Codes > Customized > Topics View, click button to add a view.
We would like to recommend you to create at least one view - ALL or Overview (in Include Groups field input 0-999) - to show all customized topics.
Let's review an example:

Enter Code and Text (will be shown as a title of a corresponding page).
Sort defines the order of the pages.
Is Passive: if set, the view becomes invisible.
Include Groups field. Please define what customized topics codes will be included into the view. 0-999 means that all topics codes with Sort value between 0 and 999 will be included into the view.

If nothing is defined in this field, all customized topics codes will be shown in this view. 

Security Code field. When a security code other than '0' is defined, it is possible to set up different access rights (Read, Write, None) to this view for different users' levels. This can be done by an administrator in Adonis Control Centre > General Codes > Customized > Topics View:

Customized topics views with blank Security Code or = '0' are available for all users.


Icon section:
We added a possibility to add an image (.bmp) to the topic views.
Click the Manage button and select the way of adding the image (from a file/clipboard).

It will be displayed near the topic view name in Customized Topics datagroup:


Choose Clear to remove the image.

Click OK to save the view.

• Pay attention that customized topics are not replicated. You should perform the necessary steps for that.
  See detailed instructions in the Customized Topics Replication Setup section.

How to Set-up Replication for Customized Topics

Customized topics tables are not replicated (there are no replication tasks for them).
They should be created manually from General Codes Tables (GCT).
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:
Point 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.
Point 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.
Point 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.

Point 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:

Please follow the steps:

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

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

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

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

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

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

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.

CREATE TRIGGER [dbo].[TU_PW001uXX] ON [dbo].[PW001uXX] FOR UPDATE

AS

SET NOCOUNT ON

DECLARE @nSite as Varchar(8), @nOLDDATE as DATETIME, @nNEWDATE 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 @nOLDDATE = (SELECT REPL_GMTMODIFIEDDATE FROM DELETED)

SET @nNEWDATE = (SELECT REPL_GMTMODIFIEDDATE FROM INSERTED)

IF @nOLDDATE > @nNEWDATE

ROLLBACK TRANSACTION

END

END

GO

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

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

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

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

Standard query for HOST_ tasks is

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

Standard query for SAT_ tasks is

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

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

Step 5: 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).

Point 5. 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.

After all needed Customized Topics and Views are added in General Codes (see Setup section), it becomes available on Datagroups ribbon.
There are two optional ways of Customized Topics menu presentation depending on the state of Display Customized Topics as Drop-Down List global option:

When the option is ON, APM presents Customized Topics as a hierarchical menu structure of drop-down. 

Similar to Reports menu, here Customized Topics Views are sub-items having Customized Topics Codes as items: 

Suitable way for customers with the large number of Customized Topics Views and Codes.

Setting up a topic/view in General Codes you can add an icon for them. 
Using small (16X16) icons is preferable.
For details, see Customized Topics Datagroup Setup section. 

When the option is OFF, APM presents Customized Topics DIRECTLY on the Ribbon, looks pretty match the same as standard APM Datagroups. And expose Customized Topics Views as a Ribbon Group (Section):

Suitable way for customers with a few Customized Topics Views and Codes.

For example:

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.
Click button to add a record:

When you create a new customized record, you can see (in Details window), the fields you previously defined in General Codes.

Enter all needed information in each field. When you click any field, the way of data inputting/defining appears. It can be: text, check-box, drop-down, calendar, etc.

It depends on your settings in General Codes.
See Customized Topics Datagroup Setup: Inputting Customized Topics (Fields Definition) section).

If you create a planning record (The planned checkbox is set), the text of it will be shown green.

Click OK to save the record.

You can leave some additional notes. For this, click button under Manage menu.
Then the icon changes to , indicating the presence of information.
These comments also will appear for this person in Notes datagroup.

Customized Topics Report

A standard report for customized topics is available under Reports > Customized Topics - Customized Topics List:

Print Options box appears:

List of displayed topics depends on the access rights set in Adonis Control Centre. If a user has No access to a specific topic, this topic will not be displayed in the list.

Persons to print: select crew members that will be processed by report.

Limit data on person to - if there exist numerous customized topics, you can select one or several of them instead of listing the whole set. You should choose radio-box Selected & then select a topic (or several using Ctrl key) at the middle section for processing.

Only select with mode: filter topics by available modes to be displayed.

Fields for sorting: choose any available field to be used in sorting of displayed data from Available part and click

Click OK to preview the report.