Importing Events: DEFINITION

 

Importing events serves to create new events in a schedule, from an external source.

These new events may concern several dimensions and must have, as a minimum:

  • a resource,
  • and a start date.

Three types of import are possible:

  • Simple insertion of new events,
  • Simple update of existing events,
  • Update of existing events and insertion of new events.

An import of events, using a unique source, which might be:

 

  • A CSV file format,
  • A worksheet (or a tab) in an Excel file,
  • A database table of one of the following formats:
    • MSAccess,
    • MySQL,
    • Oracle,
    • SQL Server,
    • PostgreSQL,
    • MariaDB,
  • A web calendar,
  • Specific values.

Note

For an event to be imported, the resources it comprises must already exist in the schedule.

configuration

 

The import contexts must be saved before triggering them.

 

Creating an import context

 

A new import context is created via the Global Settings > Right-click on Events imports > Create an events import context.

The corresponding part is then enabled:

_import_event_context

Now enter the various characteristics, then click on:

  • OK to save the context,
  • Restore otherwise.

These characteristics are as follows:

 

Name

Give this import context a name.

If multilingual management is activated in the Admin Centre, you can enter an import name in the languages offered by clicking onv7_adm_en_parametres_generaux_symbole_multilangue button.

Note

Import name in French Import des réservations
Import name in English Booking Import
What an FR customer workstation shows Import des réservations
What an EN customer workstation shows Booking Import

 

Description

Give a description for this context. This data is optional.

 

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.

URL “call”

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

 

Return format

If the triggering of the context by URL is authorized, you can choose the message received when the URL is called in a browser.

There are two return formats:

  • Text: Default format
  • HTML

 

HTML editor OK or KO

If the return format is HTML, you can edit the return message received by clicking on .

The editing window is displayed in which you can personalize the message as appropriate:

_import_ressource_editor_html

Activating authentication

The activation of this option serves to protect the URL using Basic Access Authentication

 

User ID

Define the ID (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 button 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 button 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 button is used to send a report, by e-mail, of the results of each import trigger.

Warning

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

Sender

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

This parameter is only active and necessary when the Reporting activated button is enabled.

 

Recipients

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

This parameter is only active and necessary when the Reporting activated button is enabled.

 

Definition of the import context

 

Source tab

import_event_source_tab

The unique source of the import can be one of the following types:

  • CSV file,
  • Excel file,
  • Database,
  • Web calendar,
  • Specific values.

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

Standard format

For *.CSV files

_import_event_source_csv

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, which may contain:

  • A CSV type file,
  • A URL-type address,
  • A text of the type Export=Import_Context_Name used to export imports.

Case with Excel files

_import_event_source_excel

You must define the following parameters:

 

Type

In this case, the value must be Excel file.

 

Path

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

 

Worksheet.

Define the worksheet of the source file to be imported.

 

Databases

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

Case with a Database

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

import_event_source_database

You must define the following parameters:

Type

In this case, the value must be Database.

 

DBMS

Specify the DBMS technology used:

  • MSAccess,
  • MySQL,
  • Oracle,
  • SQL Server,
  • PostgreSQL,
  • or MariaDB.
Connection settings

Connection settings differ depending on the type of DBMS chosen. By clicking on Connection settings, a new window is displayed:

 

MS Access MySQL, SQLServer, PostgreSQL, MariaDB ORACLE
SID
Password
Path
Port
Server
Login
import_event_msaccess_settings
_import_event_mysql_settings
import_event_oracle_settings

 

To validate these settings, click on:

  • OK,
  • or Cancel to go 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 a SQL request, it is a SQL Condition.

For request defined by SELECT * FROM TABLE WHERE CONDITION, simply remove the SELECT * FROM TABLE WHERE statement to retain 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%’

Important

This filter is useful for imports when updating existing events.

Case with a Web calendar

import_event_source_web_calendar

You must define the following parameters:

Type

In this case, the value must be Web calendar.

 

Path

Specify the Path to the .ics file.

Case with specific values

This makes it possible to import without a source file.

The values to be imported are defined directly in the import context by entering them directly in the Value field.

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 possible 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 an update of existing events and for the creation of new events.

 

Checking constraints

If this button is enabled, the constraints will be checked when events are imported, in accordance with the rules below:

  • The constraints are checked event by event, in the order in which the data is processed during the import,
  • For prohibition constraints, the event will neither be created nor modified. The lines not imported will be listed in the import report, which will indicate that the reason is because of a constraint that was not respected,
  • For warning constraints, the events will be accepted by default, and marked if this was specified in the constraint’s settings,
  • Authentication constraints are not checked.

 

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 resources.

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 take the value 0 if successful,
  • operational message OUT and type VARCHAR: must contain an explicative text, made available to the user.

Correspondence tab

 

This tab is used to establish the correspondence between the source, which may be a file, a worksheet or a table, and the destination, which is a set of events in the schedule.

This correspondence is defined on a field by field basis.

import_event_correspondence_tab

Simply fill in the table, which contains several columns:

Property

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

This column contains all the properties and headings to be imported. By clicking on add button, you can 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 you click on (Select all attributes). This is useful when the import involves a large number of headings,
  • 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.

Properties and headings with no correspondence 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.
Case with the Web calendar

The event’s UID de is selected as the default Key. The following variables can be imported:

  • CALENDAR: calendar name. For Microsoft Outlook, this is the email address of the name associated with the account;
  • DTSTART and DTEND: event start and end,
  • SUMMARY: event purpose,
  • STATUS: event status,
  • LOCATION: event location,
  • DESCRIPTION: event content,
  • USERNAME: you can add a variable of the person’s name (corresponding to a resource in Visual Planning) in the URLs provided. This involves adding the following variable in the URL of the source: “&USERNAME=variable”.

 

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 the import was run.

Note

The output date formats adhere to the format determined by the server language.

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.

 

_import_correspondence_now
Multiple values

The insertion of multiple values is possible, in headings of a given type:

  • Multiple choice list type heading,
  • Attachment type heading.

For this purpose, in the source you must specify the values to be imported, separated by the | character.

 

Automatic assignment

When importing specific values, you can automatically assign resources to existing events. (See Assignment)

The following window appears:

_import_event_automatic_assignment

Three pieces of information may be added:

 

Ascending sort

If this button is enabled, the choice of the resources to be assigned will be in ascending order. If not, it will be in descending order.

 

Sort headings

Chose the headings that define the automatic assignment criteria.

For example: If the “Name” heading is the only sort heading and the ascending order option has been selected, the resources are assigned in the alphabetic order of their Names:

  1. Assignment of Albert as the first event,
  2. Then assignment of Bertrand as a second event,
  3. Etc.

Resources 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 resources filter must be chosen with the events filter criterion > 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.
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.

Modifying an import context

 

To modify an import context, go to the Global Settings pane, then click on the context to modify.

The corresponding part is then enabled. Modify the parameters (see Creating an import context), then click on OK to save your changes.

Deleting an import context

 

To delete an import context, go to the Global Settings pane, then Right-click on the context > Delete.

The following message appears:

import_event_deleting

Click on:

  • Yes to definitively delete the context,
  • No to cancel the deletion.

USE

 

Launching the import

 

After configuring the context, you must run it.

There are a number of types of trigger:

  • Manual import,
  • Import by service,
  • Automatic import,
  • Import by URL,
  • Import/Export chain,
  • Import of an export or export of an import.

 

Concatenating values

It is possible, when importing into the event note or into a “Text” or “Multi-line text” type form heading, to concatenate 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.

Case with the Web calendar

  • Events from a Web calendar will be updated (created and modified) according to the import trigger frequency. However, their deletion is not possible.
  • The management of participants for “meeting” events is not possible.
Print Friendly, PDF & Email
1 Etoile2 Etoiles3 Etoiles4 Etoiles5 Etoiles (3 votes, average : 5.00 on 5)
Loading...
Consulting training

Need training?

Use our consulting services!