CONFIGURATION

It is necessary to save the import contexts before running them.

Create an import context

The creation of a new import context is done from General settings > Right click on Events Import > Create an import context.

The corresponding part is then enabled:

Then enter any differing characteristics, then click on:

  • Validate to save the context,

  • Reset otherwise.

These characteristics are as follows:

Name

Give this import context a name.

Description

Give a description of this context.

History

This represents the creation date and time and the most recent change to the context, together with the login of the user who implemented the actions.

This characteristic cannot be edited.

Definition

This is a definition of this context.

See Definition of the events import context.

URL "call"

The activation of this option authorizes the triggering of this context using a URL from outside of VISUAL PLANNING.

Activating authentication

The activation of this option is used to protect the URL using Basic Access Authentication.

User ID

Define the identity (login) for the URL if authentication has been activated.

Password

Define the password for the URL if authentication has been activated.

Event creation/modification

This checkbox is used to activate the triggering of the context during the creation and/or modification of events.

Caution

It cannot be activated for files on the disk or for an Access database.

Activated

This checkbox is used to activate the import by service trigger.

Triggers

This option is used to define the triggers for an import by service.

Reporting activated

This checkbox is used to transmit a report, by mail, for the results of each import trigger.

Warning

Prerequisites: An SMTP server must be configured as automatic mails are involved. See Automatic sending of e-mail

Sender

Define the e-mail address to be used as sender for the reporting by mail.

This parameter is only active and needed when the Reporting activated checkbox is selected.

Recipients

Define the address(es) to be informed during the reporting by mail.

This parameter is only active and needed when the Reporting activated checkbox is selected.

Definition of the import context

Source tab

The unique import destination can be of the type:

Depending on the type chosen, the parameters required for the import are different.

Standard format
For *.CSV files

You must define the following parameters:

Type

In this case the value should be CSV file.

Separator

The separator in the source file can be:

  • Semicolon,

  • Full stop,

  • Space,

  • Comma,

  • Tab,

  • or Vertical Bar.

Encoding

The type of encoding is to be chosen to match the OS used on the server:

  • Default (System encoding),

  • UTF8,

  • Windows-1252 (Windows).

Path

Specify the access path, the latter may contain:

  • A CSV type file,

  • A URL-type address,

  • A standard text of the type Export=NomDuContexteImport used for the exports of imports.

For Excel files

You must define the following parameters:

Type

In this case the value should be Excel file.

Path

Define the path for access to the file in the format *.XLS or *.XLSX.

Worksheet

Define the worksheet of the source file to be imported.

Databases

If the import is from a database, you must choose the type “Database”.

You must define the following parameters:

Type

In this case the value should be Database.

DBMS

Define the Database Management System used:

  • MSAccess,

  • MySQL,

  • Oracle,

  • SQLServer,

  • PostgreSQL,

  • or MariaDB.

Connection settings

Connection settings differ with the type of DBMS chosen. By clicking on Connection Settings a new window will appear:

 

MSAccess

MySQL, SQLServer, PostgreSQL, MariaDB

Oracle

SID

  

Password

Path

  

Port

 

Server

 

Login

 

To validate these settings, click on:

  • OK,

  • or Cancel to return backwards.

Database

Define the name of the database to be used for the import.

Table

Choose the table or view to be imported.

This list is empty until the database has been identified.

SQL Condition

It is possible to define an SQL condition, notably to filter the data to be imported.

This condition is exclusively dependent on the structure of the external table used as a source.

It is not actually an SQL request, but is an SQL Condition.

For a request, defined by SELECT * FROM TABLE WHERE CONDITION, simply remove the SELECT * FROM TABLE WHERE, retaining only the CONDITION.

Examples:

Professional meaning

Example of SQL request

Corresponding SQL Condition

All persons from Nantes

SELECT * FROM Personnel WHERE Agency LIKE ‘Nantes’

Agency LIKE ‘Nantes’

All worksites with a duration of 10.5 days

SELECT * FROM Client.Worksite WHERE Duration = 10.5

Duration = 10.5

All persons with a name containing the letter B

SELECT * FROM Personnel WHERE Personnel.name LIKE '%B%'

Personnel.name LIKE '%B%'

Specific values

This makes it possible to import without a source file.

The values to be imported are defined directly for the context of the import, entering them directly into the field Value.

This type of import is particularly suitable for the automatic assignment function.

Date format

Choose one of the date formats.

  • dd/MM/yyyy,

  • yyyy-MM-dd,

  • MM/dd/yyyy,

  • or dd.MM.yyyy.

Import mode

There are three potential import modes:

  • Creation only: this option is used for a simple insertion of events,

  • Modification only: this option, along with the choice of keys, is used for a simple update of existing events,

  • Modification/Creation: this option, along with the choice of keys, is used for a simple update of existing events and for the creation of new events.

Events filter

It is possible to define the events to be considered as being present within the schedule when carrying out an import for modification.

To do this, simply select an events filter.

Important

This filter is useful for imports when updating existing events.

Stored procedure

Warning

This parameter is useful and can be accessed only for imports from a database (other than MSAccess).

The name of the stored procedure should be defined.

Stored procedures should only have the following two parameters:

  • operational retcode OUT and type INTEGER: must be set to the value 0 if successful,

  • operational message OUT and type VARCHAR: must contain explicative text, made available to the user.

Correspondence tab

This tab is used to produce an equivalence between the source, which may be a file, a worksheet or a table - and the destination, which is a set of events for the schedule.

This equivalence is defined on a field by field basis.

Simply fill in the table, which contains several columns:

Property

This column contains all the properties and headings to be imported.

By clicking on , it is possible to import:

  • The general and temporal properties of the events,

  • Each heading of each dimension,

  • The name of each resource, in the form of identification headings,

  • Several headings at once if the user clicks on (Select all attributes). This is useful when a large number of headings are involved in the import,

  • Each heading of each form,

  • The fixed value, the value per unit and the coefficient for each valuation item,

  • The internal identifier (UID) for the events.

Warning

  • For the importing of daily events, using the end date parameters, the value to be imported should be at D+1 for the event to end on Day D.

  • If an equivalence is made with the Operations-type heading, it will be automatically selected as a key.

Source

Select, for each heading or heading to be imported, the corresponding source field.

The name of each element to import should be chosen from the list of source fields. The name of the equivalent field does not need to be identical. This equivalence is valid for each time the source is saved.

The properties and headings without equivalence are treated as follows:

  • for the insertion of new resources, the values of these entities are empty,

  • for modifications to existing resource, the values are not modified.

Value

In the event that information is not included in the source, the values of certain fields can be entered manually, using this column. This field value is the same for all imported events.

Global variables

In a text heading, the following values can be added as a fixed value for an import:

  • $USERNAME yields the ID of the user who ran the import;

  • $NOW yields the date and time when the import was run;

  • $DATENOW yields the day when the import was run;

  • $TIMENOW yields the timestamp at which when the import was run.

Now

In Date-type headings or properties, it is possible to give the start data for the import (“Now”) as the value for the date to be imported.

To do this, check the box opposite the selection of date.

Multiple values

The insertion of multiple values is possible, in headings of the form types:

  • Multiple choice list type heading,

  • Attachment type heading.

To achieve this, in the source the values to be imported must be specified, separated by the character “/”.

Automatic assignment

For the importation of specific values, it is possible to do an automatic assignment of the resources to existing events. (See Assignment)

The following window appears:

Three pieces of information may be added:

Sort in ascending order

If this case is selected, the choice of the resources to be assigned will be in ascending order. If not, it will be in decreasing order.

Sort headings

Chose the headings that define the automatic assignment criteria.

Example: If the heading “Name” is the only sort heading and the ascending order box has been checked, the resources are assigned in the alphabetic order of Names:

  1. Assignment of Albert as the first event,

  2. Then assignment of Bertrand as a second event,

  3. Etc...

Resource filter

This filter is used to define the resources to be assigned to events.

To assure that the automatic assignment is only applied to the available resources, a resource filter should be chosen with the events filter criteria > does not contain an events filter > To be Requested.

Key

For each entry in the source of the import, it is possible to check whether the information already exists as an event in the schedule, thanks to the notion of a key.

A key is a Unique ID formed from one or more headings and/or properties. A key therefore allows a distinction to be made between insertions or updates.

There are two possible options:

  • If no heading/property is checked as being the key, this is the insertion of new events.

  • If one or more headings/entities are selected as being a key, this is an update of existing events, and potentially the insertion of new events.

See Import mode

Parent key
  • This functionality is only used for a vertical hierarchy (See Events hierarchy).

For each entry in the source of the import, it is possible to check whether the parent-event already exists as an event in the schedule, thanks to the notion of a key.

The parent key can therefore be used to find the parent event to create or update subsidiary events.

If the parent event does not exist, the subsidiary events cannot be imported or updated.

Modification to an import context

To modify an import context, go to General settings then Click on the context to be modified.

The corresponding part is then enabled. Modify the characteristics (see Creation of an import context), then click on Validate to save any modifications.

Delete an import context

To delete an import context, go to General settings then Click on the context > Delete.

The following message appears:

Click on:

  • Yes, to definitively delete the context,

  • No to cancel the deletion.

Amalgamation of values (snowballing)

It is possible, when importing into an events note or a text or multi-line text type heading, to amalgamate (snowball) the value imported with the existing value instead of replacing it.

this mechanism is added if the imported value starts with the character $+.

For a multi-line heading, the imported value is added to a new line.

There are three possible options:

  • Use of the Value column, with the prefix $+,

  • Import of specific values with the prefix $+,

  • Addition of the prefix $+ to the source.

ghostghostghostghostghost
loading table of contents...