Importing and updating purchases with a CSV file

 

Index

 

Introduction

In ITM Platform, you can manage purchases within any project. Purchases can be created and processed manually. Part of this process can be done either by importing a CSV file which contains updated information in one or more purchases. This CSV file could, for example, be exported from your company’s accounting or ERP system.

The principal use of this functionality is to update existing purchases in ITM Platform once you have paid the purchase invoices. The process can also be used to add new purchases to ITM Platform. Given that the scope of this process is paid invoices, purchases will automatically be set to a status that has an actual value (such as “Paid” or “Completed”).

The import process is carried out in an individual project. If you require to update purchases on several projects, you need to repeat the process for each project.

 

Configuration

Before using the import process  for the first time, we need to configure  custom fields between the CSV file and ITM Platform. This section requires Full Access permissions and can be found under the name “Import” in “Other Settings” in the “Configuration” tab. An “example” file is provided for practice use prior to importing your own files. Here, ITM Platform will read the column names.  In the section below,  you can match the CSV column name to its corresponding field in ITM Platform. For example… … All fields are optional except “Name”, “Status Date” and “Actual Amount”.

 

In the configuration screen above, additional options are provided:

  • Set as key field: required to select which field will be used to set the relation between each row in the CSV file and the purchase in ITM Platform. You can choose between “Name” or “Purchase No.”
  • Allow creation of new purchases: enable this option if you want to allow for the creation of new purchases from the CSV file (as described, more in detail in “Creating new Purchases”).
  • Allow grouping of multiple purchases into one purchase in ITM Platform: enable this option if you want the grouping of multiple purchases from the CSV file into one purchase in ITM Platform (as described, more in detail in ¨Grouping of purchases¨).
  • Actual Value: when updating or adding purchases to ITM Platform from the CSV file, these will automatically be set to a status with an actual value. If in the purchases parameters your company has multiple purchase statuses with an actual value, you can select the status to which purchases should be set in this setting.

 

Please note that these settings apply to all projects and for all users in your organization.

 

Updating existing purchases

The process is initiated by clicking the button “Import from CSV file” within the Manage Purchases section of a project.

Here you can create the content that will be used within the module.

This opens a pop-up window where you are asked to select your CSV file.

If we encounter any issues, normally related with the syntax/format of the file, you will receive an error message.

Upon clicking “Save” we will analyze your CSV file and make a proposal on how to update the purchases. In the screen below, you can make any necessary changes.

  • On the left side of the screen, you will see fields drawn from the CSV file ( fields with  grey background).
  • On the right side of the screen, you will see fields corresponding to purchases in ITM Platform with their respective values Any changes made will be applied to all fields.
  • In the center of the screen, you will findthe column Match Type, which shows you the type of matches that were found between the purchases from your CSV file and ITM Platform.
    • A strong match means that based on the purchase names in your CSV file we found a strong correlation with a purchase name in ITM Platform.
    • A weak match means that based on the purchase names in your CSV file we found a match with a purchase name in ITM Platform, but with a weaker correlation.
    • New Entry is shown if there is no correlation/strong match and the creation of new purchases from the CSV file is not allowed, meaning a new purchase will be created.
    • No match is shown if there is no correlation/strong match and the creation of new purchases from the CSV file is not allowed. In order to continue, you will either need to remove this purchase from the list or manually select a purchase from ITM Platform.
    • Manually selected means that you have manually set the relation with a purchase in ITM Platform.
  • In the column “Name”, we show the purchase that will be updated in ITM Platform. For easy identification in brackets, we show its due date.
  • For existing purchases, the budget account that is shown, cannot be changed.
  • By clicking on the button next to the purchase name, a pop-up window opens and allows you to change the purchase that will be updated.
  • The fields “Description”, “Purchase No.”, “Actual Amount” and “Status Date” can be changed manually, by simply writing in the desired field. The field “Type” can be changed by selecting an existing value from the dropdown menu.
  • You can (optionally) include a provider name or purchase type, that will be included in the purchase only if we find a matching value in ITM Platform.
  • The provider can be changed by clicking on the button next to it, and in return, opens a pop-up where you can either select an existing provider or create a new one.
  • You can also select or change the task of which a purchase depends.
  • The column “Group” displays which group a purchase pertains to. ( For more info, see  section ¨Grouping of purchases¨).
  • The ¨x¨in the final column allows the user to exclude individual purchases from the import process.

After you have reviewed the import summary and made your changes, if any, click “Confirm” to submit  changes. If there are any issues with the import, you will be informed via a pop-up screen and the changes will not be committed until you have fixed the issues.

 

Grouping of purchases

The grouping of purchases allows the user to group two or more purchases from the CSV  file into one corresponding purchase in ITM Platform. This means that the information from 2 or more purchases from the CSV file is combined and used to update 1 purchase in ITM Platform:

  • The status date that is set, is the one closest to the due date of the corresponding purchase in ITM Platform.
  • For the actual amount to be set in ITM Platform, we sum the amounts of the individual purchases of the CSV file.
  • To add or update information in ITM Platform, we use the data of the purchase of the CSV file that has the status date that is closest to the due date of the purchase in ITM Platform.
  • The column “Group” lists  which group a purchase belongs to.

 

Groups are automatically proposed based on the names we find in the CSV file.

To add purchases to a group, you can manually change the linked purchase in ITM Platform. If an  ITM Platform purchase is already linked to one or more other CSV purchases, a group is created or extended.

To remove purchases from a group, you can manually change the linked purchase in ITM Platform. By doing so, it will automatically remove the purchase from its corresponding group. Please note that by clicking on ¨x¨, the purchases are entirely excluded from the import, not just from the group.

Grouping of purchases can be done for both existing and new purchases.

Grouping of purchases can only be done if the corresponding setting in the import configuration screen has been enabled.

 

Creating new purchases

The process to creating new purchases is similar to that of updating existing purchases. Existing and new purchases can be mixed in one CSV file.

If ITM Platform does not detect a match between the purchase in your CSV file and ITM Platform, it proposes to create a new one. This can be identified by the value “New Entry” in the column “Match Type.” Some specific characteristics of creating new purchases are:

  • Select a budget account for each individual new purchase, by selecting it from the dropdown.
  • Purchase name of the CSV file in use cannot be changed
  • You can (optionally) include a provider name or purchase type, that will be included in the purchase only if we find a matching value in ITM Platform
  • If you include multiple entries of the same purchase in your CSV file, they will automatically be grouped (if grouping is enabled)
  • You can manually select an existing purchase from ITM Platform. By doing so, an existing purchase will be updated instead of creating a new one.

 

Import History

Click “Import from CSV file” within the Manage Purchases section of the  project and from the dropdown menu select the option “View Import History”.

This opens a pop-up window where you see a history of all performed imports, along with  its date, number of records and user.

By clicking on a row, you open the full detail of the import.

 

Requirements for formatting the CSV file

Please format your CSV file by taking the following requirements into account:

  • The first row should include the column names.
  • All columns should be separated with a comma, other separators are not supported.
  • Values can optionally be put inside quotes “”. This is necessary if, for example, you need to include commas for amounts or within a name.
  • The amount and date formats should coincide with the company settings in ITM Platform.