product-background

Efecte Tools for Excel Support

1. Installation
1. Get the installer file, if you don’t have it yet

Start your trial now!

2. After downloading the file, unzip it

3. Close your Excel, if its open

4. Save and run Setup.exe

Installer

5. Click Install

6. Start Excel and check that you have a new ribbon item named EFECTE

2. Connecting to Efecte server

1. Open the EFECTE ribbon

excel_ribbon-1024x250

2. Click Connection Settings

add-on_login-1024x506

3. Type in your Efecte Server Address, personal User Name for Efecte and Password

NOTES:

  • Note: All the actions and data available for the Excel Tool is dependent on the user right of the Efecte User entered here
  • Remember to include the http or https protocol
  • Remember to include the full path to the Efecte service e.g. https://domain.com/full-path-to-efecte
  • Note: The User Name must have WEB-API access enabled in Efecte: Permissions – Users and Roles – Role (Web API checked)
  • Note: There might be connection problems with non-trusted ssl certificates when using https connections
  • Note: When using Single Sign On, you have to use your domain username and password

web_api_setting

 

4. Click Connect to Server on the Excel EFECTE ribbon

5. If the connection is succesful the ribbon activates

add-on_connected-1024x351

6. Click the View Log Window button on the ribbon to see if there were any errors on the connection

log_window_addon-1024x532

3. Downloading Data - Options, Templates and Folders

1. The Efecte template that you will be working with (download or upload) should be selected from the Template drop-down menu

list_of_templates-1024x306

2. Modify what data will be downloaded and how, click the Download Options on the EFECTE ribbon

3. You will see the options for the template selected on the Template drop-down

download_options-1024x597

4. All the options that you select here are stored and remembered for this template, so you don’t have to set the options again the next time you use the Excel Tools

5. The download options contain three tabs; Basic Options, Attribute Columns and Advanced Options

6. Basic Options:

  • Option: Limit results to selected folder
    • Include all folders – The folder selection (on the EFECTE ribbon below the Template drop-down) is ignored and data rows are downloaded from all folders
    • Include only selected folder – Data rows are downloaded only from the selected folder and all other data rows are ignored even if they match other download criteria
  • Option: Limit result row count – Limits the maximum number of rows downloaded, WARNING: downloading massive number of rows will jam your Excel, e.g. all Service Desk tickets from the past 5 years will result in thousands of rows – Test your download with 20 or 100 rows first
  • Option: Download hidden/closed – Sets if you want to load also hidden data cards such as closed tickets etc. NOTE: Loading the full history of closed tickets can result in a massive amount of downloaded rows!
  • Option: Download trash/deleted – Sets if you want to load also deleted data cards that can be found from the Efecte trashcan

7. Clicking the Attribute Columns lets you select which attributes are included in the download NOTE: The default is “All Attributes” even if the top left checkbox is not selected

Attributes

8. Clicking the Advanced Options lets you set Efecte Query Language (EQL) conditions to which rows are downloaded

AdvancedOptions

9. Efecte Query Language

  • Works similar to SQL (Standard Query Language)
  • The row columns are represented by the column names (attribute codes in Efecte) marked with the $ sign. Example: $user_name$ would equal the User Name field in Efecte
  • The operators are familiar from SQL. Example: ‘like’, ‘=’, ‘<‘, ‘>’
  • Example: $user_name$ like ‘Jaan%’ Would download all users that start with Jaan
  • Efecte Query Language Description Guide is available for download here

10. After setting the donwload options you can download the data by clicking the Download Data button. NOTE: Download will overwrite all data on the selected Excel sheet. A log window will be also shown to see the progress.

Screenshot-2019-04-03-at-14.20.22-1024x258

11. The header column has the attribute column names (attribute code in Efecte). NOTE: You can see more details about the attribute column (like name and type) by hover over the comment area with your mouse

attribute_metadata-1024x231

12. Two extra columns are added to the left side of the table #Row# and “#Entity#”. NOTE: The headers of these rows are escaped by the # sign and they will not be added to any future uploads, so any header escaped with the # sign is for user reference only

rown_fields-1024x232

4. Uploading Data - Options, Templates and Folders

1. You can access the Upload Options by clicking the button on the EFECTE Ribbon

upload_options-1024x511

2. Upload options

  • Create and Update Cards – Lets you select how the rows in the Excel sheet affect the data cards in Efecte
    • Create and update card – [DEFAULT] Each row on the Excel is compared to the Cards in Efecte. If a card with a matching unique attribute is found then the matching data card is updated, otherwise a new data card is created to the folder selected in the EFECTE Ribbon
    • Create only, ignore update rows – Each row on the Excel is compared to the Cards in Efecte. If a card with a matching unique attribute is found the row is totally ignored and no update is done, otherwise a new data card is created to the folder selected in the EFECTE Ribbon
    • Update only, ignore create rows – [RECOMMENDED FOR UPDATES] Each row on the Excel is compared to the Cards in Efecte. If a card with a matching unique attribute is found then the matching data card is updated, otherwise the row is totally ignored
    • Create new card from every row – The rows in excel are not copared tot he data cards in Efecte at all, each row in the Excel automatically creates a new card to the folder selected in the EFECTE Ribbon. NOTE: This selection may create duplicate cards in Efecte
  • Search for cards to update in – Lets you select if the cards to be updated are limited to certain folder
    • All folders – [DEFAULT] Cards with matching unique attributes are searched from all folders
    • Only in selected folder – Only the cards in the folder selected (on the EFECTE ribbon below the Template dropdown) are checked for matching unique attributes. NOTE: This selection may create duplicate cards in Efecte
  • Empty Excel cell handling – Decides what is done when a cell in the Excel sheet is left empty
    • Empty cells remove values – [DEFAULT] This means that if an attribute cell for a certain row in the Excel is left empty then data for the row for that attribute is removed, even if it had data earlier
    • Empty cells keep old values – This means that if an attribute cell for a certain row in the Excel is left empty the old values of this attribute are changed or removed, the cell is just ignored
  • Reference and list value handling – Decides if new reference target and list values are created or ignored
    • Create both if missing – [DEFAULT] – Means that if a reference cell contains information that can’t be matched to a target card’s name in Efecte then a new reference target (of the target template) is created in the folder selected (on the EFECTE ribbon below the Template drop-down). And if a selection list (static value) cell contains data that is not found from the selection values list of that attribute then a new static value is created and added to that list
    • Create only list values – This means that new list values to selection lists are created when needed and reference values that can’t find a matching target are ignored (NOTE: If empty Excel cell handling is set to remove values, then this setting will result in removing the value if the reference target is not found)
    • Create only references – New values for selection lists are ignored and reference attributes which do not have a matching target cards are created as per the first option (Create both if missing)
    • Don’t create either – Reference cells that don’t have a matching target and selection list cells that don’t have a matching value on the selection list are both ignored (NOTE: If empty Excel cell handling is set to remove values, then this setting will result in removing the value if the reference target or selection list value is not found)
  • Character set upload – Defines which character set is used to upload the data. Some special UTF-8 characters are not supported by Efecte WEB API.

3. IMPORTANT NOTE FOR UPLOADING: The first column in the Excel sheet to be uploaded is used to check if the particular row should be uploaded and that column itself is not uploaded. Usually the first column is labelled #Row# and removing the row number from that column disables the upload for that particular row/card (The rows marked in orange in the picture below are not uploaded)

UploadSelectRows-1024x757

4. To start the upload, click Upload Data button in the EFECTE ribbon and confirm

UploadDataButton-1024x757

5. The upload process writes the upload results for each row to the Excel sheet column B. There are 3 basic options for the row report:

  • Created
  • Updated
  • Discarded

UploadResultsAndLog-1024x757

5. Updating data downloaded from Efecte

The process of updating Efecte data is as follows:

1. Select the template of which data you want to update (See section 3: Downloading Data – Options, Templates and Folders)

2. Set the download parameters so that you download the information you want to view / update (See section 3: Downloading Data – Options, Templates and Folders)