Generic Licence Transactions Import | v7 and above
Generic Licence Transactions Import | v7 and above
A new feature has been introduced to allow users to import Generic licence transactions.
This is available under Licensing > Data Imports .
This feature allows users to bulk-upload Generic licence transactions into the Certero Platform.
File Types and File Formats
The file to be imported must be in .xls or .xlsx format. You can't import .csv , .txt , .doc or other formats.
If there are multiple tabs, Certero will only read the first tab of the spreadsheet. It is recommended that the column headings on the spreadsheet are in the first row of the first tab of the spreadsheet. This will help with mapping the columns once the data has been imported. There's a downloadable template at the end of this article.
Importing multi-step process:
Step 1 – Import the spreadsheet.
Step 2 – Column mapping.
Step 3 – Data resolution.
Step 4 – Data import.
Step 5 – View the transactions.
Step 1: Import the Spreadsheet
In this step the Excel spreadsheet is imported into the Certero Platform.
Go to Licensing > Data Imports .
Click Upload .
Browse to the .xls or .xlsx file.
Click Upload .
This will import the spreadsheet and it will appear in the list of uploaded files. It will display the following information:
Uploaded – The date the spreadsheet was imported.
Owner – Which user imported the spreadsheet.
Filename – The name of the file that was uploaded. You can click on the file to download a copy.
Current Step – This displays where the file is in relation to the steps required to turn the purchase information into license entitlement.
Categories , Agreements , Suppliers and Transactions will be zero until Step 2 is completed.
Step 2: Map Columns
In this step the columns in the spreadsheet are mapped to the columns in Certero. Right-click the row of the file you want to map and select Actions > Map Columns :
In the Column Mappings dialog select the column in the source data spreadsheet that matches to the column in the Destination (Certero tables):
If the column headings in the source spreadsheet match the columns in Certero, the columns will be matched automatically.
If there is no data in the source spreadsheet for some of the columns, use the
button to set a default value. Details of the destination table columns and descriptions are shown below:
Destination Table Columns and Descriptions
Column Name | Description | Mandatory | Set Default Values |
Item Name | The software publishers name that goes with the part number sometimes called item name. | Yes |
|
Quantity | Total quantity of licenses purchased. | Yes |
|
Created | The date the transaction was created. This could be the purchase date, invoice date or the date you were importing the data. dd/mm/yyyy | Yes | Yes |
Part Number | The software publishers part number, sometimes called the SKU (Stock Keeping Unit). | No |
|
Supplier Name | The organization the supplied the license i.e. reseller or direct from the software publisher. | No | Yes |
Agreement Name | The name of the license agreement if there is one. | No | Yes |
Customer Name | The legal entity name on the agreement. | No | Yes |
Agreement Number | The agreement number or contract number. | No | Yes |
Reference Number | An additional number associated to the agreement or contract. For example in the case of Microsoft this would be the enrolment number. | No | Yes |
Description | A detailed description of what was purchased. | No |
|
Downgrade Rights | Define the downgrade rights of the licenses. The choices are: 1 previous version 2 previous versions 3 previous versions 4 previous versions 5 previous versions All previous versions None | No | Yes |
Start Date | The start date for subscription or maintenance transactions. dd/mm/yyyy | No | Yes |
Expiry Date | The end date for subscription or maintenance transactions. dd/mm/yyyy | No | Yes |
PO Date | The purchase order date. dd/mm/yyyy | No |
|
PO Number | The purchase order number. | No |
|
Invoice Date | The invoice date. dd/mm/yyyy | No |
|
Invoice Number | The invoice order number. | No |
|
Program Type | The license program type. The choices are: FPP/Retail Freeware OEM Open Source Other Shareware Volume License | No | Yes |
Purchase Price | The total purchase price of the licenses. | No |
|
Currency | The currency the licenses were purchased in e.g. £, $ etc. Must match a currency code in the Certero Platform. | No | Yes |
It is often useful to bulk set Default Values where those values do not appear in the source spreadsheet. Examples of where Default Values may be set are shown in the table above. It is useful to have a copy of the spreadsheet open to check the columns in the source data.
Once all the columns have been mapped from the source to the destination and default values added where necessary, click Validate to check the mappings. A 'Success' message will be shown If the mappings are successful:
If the mappings are not successful, then the column mappings will need to be adjusted. Click Save to save the column mappings. The Current Step will change from 1. Map Columns to 2. Data Resolution:
Step 3: Data Resolution
In this step the license purchases from the spreadsheet are mapped to product categories in Certero. Once the purchases have been mapped, they can be imported and will appear as a transaction under Licensing > Transactions .
Go to Licensing > Data Imports .
Drill down on Transactions to show a list of records imported from the source spreadsheet.
Right click one of the transactions and select Actions . This provides a menu to make changes to the record:
Resolve: Match the item to a product in Certero.
Set Downgrade Rights: Options as per the table above.
Change Product Type: Options are License , Maintenance or Subscription .
Looking at Properties will allow the editing of the record.
If the Product Type and Downgrade rights are correct then select Resolve .
Click on the magnifying glass next to the Category to show a list of product categories. Use the search or filter row to search for a publisher, product and version based upon the item name or description in the source data:
Highlight the appropriate record and click OK . If the product is a subscription or maintenance you should pick the category with no version against it.
Pick the supplier from the list or click the + sign to add a new supplier.
Pick the agreement from the list if there is an agreement associated to the purchase record or click the + sign to add a new agreement.
Click Save to save the product resolution.
It should be noted that you can only Resolve a single transaction at a time; however if there are multiple transactions with the same Item Name, Part Number and Product Type these will be automatically resolved as part of the process.
Step 4 – Data Import
In this step the license purchases are imported to create transactions under Licensing > Transactions . The resolved transactions will show on the data grid as ‘ Ready to Import ’:
Right click the transactions and on the actions menu then you can:
Import – This will create a transaction for selected records under Licensing > Transactions . Where the product type is ' Maintenance ' this will create the record under Licensing > Maintenance .
Set Downgrade Rights – Define the downgrade rights for this purchase if different from the downgrade rights selected in the Map columns stage.
Change Product Type – Change the Product Type for this purchase if different from the Product Type selected in the Map columns stage.
Selecting Import will create a transaction for selected records under Licensing > Transactions and the status will change to ‘ Imported ’. Right clicking the records will allow the transaction to be rolled back. This will delete the transaction from Licensing > Transactions and set the status to ‘ Ready to Import ’.
Step 5 – View the Transactions
In this step the license purchases from the spreadsheet that have been imported will count towards the license entitlement:
Licenses will appear as a transaction under Licensing > Transactions .
Maintenance transactions will appear as a maintenance transaction under Licensing > Maintenance .
Where maintenance transactions have been added these must be mapped to the base license under Licensing > Maintenance .
Here is a handy template you can use for Generic transaction imports: