Data Management Tool
1. Overview
The Data Management Tool loads and changes data in Younium in bulk from a spreadsheet. You upload a file, tell Younium which column holds which field, and choose what to do with the rows: create new records, edit existing ones, or amend orders with a change order. It is how a customer base is migrated into Younium, how a catalogue of products is set up in one go, and how a change that has to reach hundreds of orders is made without editing each one by hand.
The tool works on three kinds of record: orders, products and accounts. Each has its own set of operations, and each operation offers its own columns. Every row is processed by the same rules as the equivalent change made in Younium itself, so an order created from a file is versioned, booked and numbered exactly like an order created on screen, and a row the product would refuse is refused here too.
A run happens in two stages. Younium first checks the whole file and stops before writing anything if any row is wrong, so a file either goes in or it does not. Only when every row is valid does it write them, one at a time, and report what it created or changed.
2. Core Concepts
Entity and operation
The entity is the kind of record a file works on: Order, Product or Account. The operation is what the file does to it.
Order
Import, Edit, Change
Product
Import, Update, Upsert
Account
Import
Mapping
Mapping pairs each column in the file with a field in Younium. A column that is not mapped is ignored, and Younium lists it in the result so nothing is dropped unnoticed. One column cannot be mapped to two fields.
Group keys
A single record often spans several rows: an order with three products, or a product with tiered prices in two currencies. Group keys tell Younium which rows belong together. Each entity has a hierarchy of levels, and each level has a key column.
Order
Order → Order Product → Order Product Charge → Order Product Charge detail
Product
Product → Charge Plan → Charge → ChargeDetails
Account
Account
Rows that share the same key at a level become one record at that level. For an order, rows with the same order key form one order, their order product keys split it into products, their charge keys split each product into charges, and their detail keys split a charge into price tiers. A key only groups rows within the file. It does not have to match anything already in Younium, and it is never stored.
The top-level key must always be mapped. A row whose top-level key is empty becomes a record of its own. A lower-level key may be left unmapped when the file does not use that level, but if a row has values for a level whose key cell is empty, the row is refused.
Reference fields
Many fields point at an existing record: an account, a product, a payment term, a currency. In a file, these are written as text and Younium finds the record. Most accept the record's number, name or id. Where a name matches more than one record, the row is refused rather than guessed.
Mapping template
A mapping, together with the date format and decimal separator chosen for the file, can be saved as a mapping template and reused. See Mapping templates.
3. How a Run Works
Checking the file
Before anything is written, Younium checks every row: that the mapping is complete, that rows group cleanly, that each value has the right type and format, that every reference resolves, and that the record would pass the same rules as in Younium itself.
If any row fails, nothing is written. The result lists every error with the row it came from, and the file can be corrected and run again. This is what makes a file safe to rerun: a failed check leaves Younium exactly as it was.
The mapping can also be checked on its own, before a run. This quicker check covers the mapping, grouping, value types and formats, but not references or business rules, so a file that passes it can still fail the full check.
Writing the rows
Once every row passes the check, Younium writes the records one at a time and shows progress as it goes. Each record succeeds or fails on its own. A record that fails at this stage is reported with its error, and the run carries on with the next one.
Note on partial results: The check is all-or-nothing, the writing is not. Records written before a failure stay written. Read the result before running a file again, and remove the rows that already succeeded, or an Import will create them a second time.
Cancelling
A run can be cancelled while it is in progress. Cancelled during the check, nothing is written. Cancelled while records are being written, Younium stops before the next record, and the records already written stay.
The result
Each run returns a log grouped by kind:
Errors
Problems with data, validation, mapping or grouping, and records that failed to write, each with the row it came from. Row numbers count the header as row 1
Operation results
One line per record created or changed, with its number
Info
Notes about the run, such as a recommended field left unmapped
Warnings
Warnings raised during the run
Not mapped
Columns in the file that were not mapped to any field
The log belongs to the run. It is not kept after the result has been shown.
4. Values in the File
Empty cell
Ignored. An empty cell never overwrites anything, so in Edit, Change, Update and Upsert a field keeps its current value unless the file gives it a new one
Clearing a field
Write NULL, CLEAR or [EMPTY] (in any case) to remove a field's value. On a field that takes several values, this removes all of them. Mandatory fields and fields with a fixed list of values cannot be cleared, and the row is refused with a message naming the field
Dates
Written in the date format chosen for the file: YYYY-MM-DD (the default), MM/DD/YYYY, DD/MM/YYYY or DD-MM-YYYY. Fields that hold a time also accept the date followed by a time in HH:mm or HH:mm:ss
Numbers
Written with the decimal separator chosen for the file, comma by default. Spaces are ignored. A number that uses the other separator, including as a thousands separator, is refused
Checkboxes
true, false, yes or no
Fixed lists
Values such as a charge model or an order type are matched regardless of case and spacing, so Sales Order and salesorder are both accepted
Several values in one cell
Where a field takes several values, such as invoice fee templates or product features, separate them with a comma, a semicolon or the word AND (in any letter case), and mix them freely: Admin fee, Paper invoice fee, Admin fee; Paper invoice fee and Admin fee AND Paper invoice fee are equivalent. Spaces around a value are ignored and a value repeated in the cell counts once
Values that contain a separator
Wrap the value in double quotes to keep a comma, a semicolon or AND as part of it, for example "Support AND hosting", Admin fee. A doubled quote inside a quoted value is a literal quote. A quote only counts at the start of a value and must be closed and followed by a separator or the end of the cell, otherwise it is read as an ordinary character
Empty rows
A file may contain at most five completely empty rows. More than that and the file is refused as a whole
Custom fields. Every operation offers the custom fields you have configured for each level it covers, under their own names. Clearing a custom field removes its value.
5. Orders
Import
Import creates new orders, each with its products, charges and price tiers. Rows that share an order key become one order.
Order
Account (required), Status (Draft or Active), Order type, Subscription type, invoice account, invoice and delivery addresses, payment term, currency, invoice currency, payment method, billing period, invoice batch group, invoice template, invoice fee template, effective start and end dates, order date, initial term, renewal term, notice period, auto-renewal, invoice separate, references, description, remarks, and the external ERP and CRM ids
Order Product
Product (required when the level is used), charge plan, product name, external ids
Order Product Charge
Charge (required when the level is used), picked from the product's charges by charge number, name or id. Also charge model and type, start and end options, milestones, effective start and end, quantity, price period, billing day, billing period, billing timing, period alignment, usage rating, unit of measure, revenue recognition rule, tax template, revenue accounts, invoiced-to date, estimated and ordered quantities, tax included, invoice lines per tier, tier decimal precision, description, remarks and external ids
Order Product Charge detail
Tier (required when the level is used), description, price base, price, list price, discount percentage or amount, from and to quantity, and whether the last tier is infinite
An imported order follows the same rules as one created in Younium. An evergreen subscription cannot have an initial term, a renewal term, auto-renewal or a notice period. Terms and notice periods cannot be negative. An active sales order needs an order date. An order that is not invoiced separately cannot carry its own payment term, invoice template or our reference. The invoice fee template applies only to orders that are invoiced separately; on other orders it is ignored. See Orders and Billing for what each field does.
Edit
Edit changes descriptive and invoicing fields on existing orders without creating a new version. It is meant for corrections: a reference typed wrongly at migration, a missing external id, a new invoice template.
Order
Order (required, by number, name or id), description, remarks, notice period, references, external ids, invoice batch group, use account invoice batch group, disable invoicing, payment term, invoice template, invoice fee template
Order Product
Order Product (by number or id), product name, external ids
Order Product Charge
Order Product Charge (required when the level is used), description, remarks, external ids, invoice subsidiary
Order Product Charge detail
The detail's id, and its description
Fields that are not offered here, such as prices, dates or quantities, are changed with Change.
On an order that is not invoiced separately, the payment term and references cannot be changed, and an invoice template given in the file is not kept. Invoice fee templates given in the file replace all the order's existing fee templates, and clearing the field removes them all. Changing the notice period recalculates the notice date.
Note on previous versions: Edit can target a previous version of an order, but only to change text and custom fields. A row that changes anything else on a previous version is refused with an error naming the fields. See Editing previous versions.
Where scheduled price adjustments are in use, Edit also offers the price adjustment columns, so a schedule can be set on many orders from one file. See Scheduled Price Adjustments.
Change
Change amends existing orders with a change order: each order in the file gets a new version from the effective change date, with its bookings and metrics recalculated, exactly as an amendment made in Younium. Use it for price changes, quantity changes, term changes and adding products.
Order
Order (required), Effective change date (required), and most of the order fields offered by Import, plus disable invoicing, Booking notes, Booking change type and Booking classification
Order Product
Order Product (by number or id) to change an existing product, or Product number to add a product from the catalogue, with an optional charge plan. Also product name and external ids
Order Product Charge
The charge, identified by Order Product Charge or by Charge number on the order product, and the same charge fields as Import, plus specific date for first invoice and invoice subsidiary
Order Product Charge detail
The detail's id or Tier, description, price base, price, list price, discount percentage or amount, and from and to quantity
Price tiers. On a charge with one price, the row's values update it, and if several rows target it, the last one wins. On a volume or tiered charge, a row updates the tier it matches by id, or failing that by tier number. A row that matches no tier adds a new one. If both an id and a tier number are given, they must point at the same tier.
Billing period. Setting a billing period on a charge turns off Billing period on order for that order.
Cancelled orders. A cancelled order can be changed only while its cancellation is still pending, and even then its effective end date cannot be changed.
Booking notes. A note given in the file is recorded on the booking the change produces. An empty cell records none.
Accepted values
Fields with a fixed list of values accept only the values below. Matching ignores case and spaces, so Sales Order is read as SalesOrder.
Status
Order (Import only)
Draft, Active
Order type
Order (Import only)
Subscription, SalesOrder
Subscription type
Order
Termed, Evergreen
Payment method
Order
Invoice, Stripe, GoCardless
Billing period
Order and Order Product Charge
Monthly, Quarterly, Annual, EndOfTerm, Biannual
Charge model
Order Product Charge
Flat, Quantity, Volume, Tiered, Rated
Charge type
Order Product Charge
Measured, Usage, Recurring, OneOff
Start on, End on
Order Product Charge
AlignToSubscription, SpecificDate, Milestone
Price period
Order Product Charge
Monthly, Quarterly, Annual
Usage rating
Order Product Charge
Sum, Max, Average
Billing day
Order Product Charge
None, FirstDayOfMonth, LastDayOfMonth, FromOrder, FromCharge, FromAccount, SpecificDay, DaysOffset
Billing timing
Order Product Charge
InAdvance, InArrears
Period alignment
Order Product Charge
None, AlignToOrder, AlignToCharge, AlignToMonth, AlignToCalendarQuarter, AlignToDate
Price base
Order Product Charge detail
Flat, PerUnit
Booking change type
Order (Change only)
Upsell, Downsell, Renewal, None, PriceAdjustment
6. Products
Import
Import creates new products with their charge plans, charges and prices.
Product
Name and Product Type (both required; Simple, MultipleCharges, MultipleChargePlans or Full), product category, activation date, end of sales, end of renewal, end of life, framework product, external ids
Charge Plan
Charge plan name (required when the level is used), effective start, end of sales, effective end
Charge
Name (required when the level is used), end of sales, model, type, price period, usage rating, measurements rule, billing day, specific billing day, offset billing days, billing period, billing period alignment, billing timing, default quantity, invoice lines per tier, tax included, unit of measure, tax template, revenue recognition rule, revenue accounts, recognised revenue template, external ids
ChargeDetails
Tier (required when the level is used), description, from and to quantity, price, price base, currency code, tier decimal precision, and whether the last tier is infinite
A product with more than one charge plan needs a name for each plan. Within a charge, each currency can have each tier only once. When a charge is priced in more than one currency, every price detail needs its currency code.
Update
Update changes products that already exist, and only what already exists: it never adds a charge plan, a charge or a price detail. The product is identified by number, name or id; charge plans and charges by number or id; price details by id. A reference that matches nothing is refused with an error saying which record was not found.
All product, charge plan and charge fields offered by Import can be updated. Dates cannot be earlier than 1 January 1950. This includes a charge's end of sales, and the date is checked before anything is written, so one row with an earlier date stops the whole batch. Clearing the end of sales column, as described in section 4, removes the date again. Price details on rated charges are ignored. Products shared from another legal entity cannot be changed from a file.
Upsert
Upsert updates a product's charge plans, charges and prices, and creates the ones it cannot find. At product level it offers only the product reference and its name. Below that:
References an existing charge plan, charge or price detail
Updates it
Gives no charge plan reference
Creates a new charge plan, which then needs a name
Gives no charge reference
Creates a new charge, which then needs a name
Gives a price detail without an id
Adds a new price detail
References something that does not exist
Is refused
A new charge plan created without any charge rows gets a default charge. A row with no charge plan data at all is refused. The upserted product is validated in full, as it would be when saved in Younium.
Note on Update versus Upsert: Both start from an existing product. Update is the safe choice for correcting values, because a mistyped reference fails rather than creating something. Upsert is the choice for extending a product, such as adding a charge plan or a currency, and creates whatever the file does not reference.
Accepted values
Fields with a fixed list of values accept only the values below. Matching ignores case and spaces, so Multiple Charges is read as MultipleCharges.
Product Type
Product (Import and Update)
Simple, MultipleCharges, MultipleChargePlans, Full
Model
Charge
Flat, Quantity, Volume, Tiered, Rated
Type
Charge
Measured, Usage, Recurring, OneOff
Price period
Charge
Monthly, Quarterly, Annual
Usage rating
Charge
Sum, Max, Average
Measurements rule
Charge
LatestDate, SumLatestDate
Billing day
Charge
None, FirstDayOfMonth, LastDayOfMonth, FromOrder, FromCharge, FromAccount, SpecificDay, DaysOffset
Billing period
Charge
Monthly, Quarterly, Annual, EndOfTerm, Biannual
Billing period alignment
Charge
None, AlignToOrder, AlignToCharge, AlignToMonth, AlignToCalendarQuarter, AlignToDate
Billing Timing
Charge
InAdvance, InArrears
Price base
ChargeDetails
Flat, PerUnit
7. Accounts
Import
Import creates new accounts. Each account is one row, or several rows with the same account key.
Account
Name (required), account type (Customer, Reseller, Subsidiary, Prospect or Other), parent account, currency, description, site, inactive, year founded, employees, organisation number, tax registration number, references
Invoicing
Invoice email, invoice email Cc, reminder email, invoice delivery method, payment term, tax template, invoice batch group, invoice setting group, invoice template, disable invoice reminders, attach UBL to invoice, attach ZUGFeRD XML to invoice
E-invoicing
E-invoice address, e-invoice address scheme, e-invoice operator
Accounting
Accounts receivable, unbilled accounts receivable, external ERP and CRM ids
Invoice and delivery address
Name, description, street, street 2, city, state, county, zip and country
An account without a currency gets the legal entity's base currency. Giving a parent account makes the account a subsidiary unless another type is given, and a subsidiary must have a parent that is not itself a subsidiary. Any address value requires that address's country, and the country must be active. Every Cc address must be a valid email address. Address fields cannot be cleared.
Importing accounts requires two permissions: Import Account in Data Management and Create accounts.
Accepted values
Fields with a fixed list of values accept only the values below. Matching ignores case and spaces, so Electronic Invoice is read as ElectronicInvoice.
Account type
Customer, Reseller, Subsidiary, Prospect, Other
Invoice delivery method
Email, Print, ElectronicInvoice, ExternalPrint, AutomaticElectronicRouting
8. Mapping Templates
A mapping template saves everything needed to run the same kind of file again: the entity and operation, which column maps to which field, and the date format and decimal separator. Templates belong to the legal entity, so a template saved by one user is available to everyone who uses the tool there.
Each template needs a name, and two templates cannot share the same name. Templates can be updated, deleted and downloaded. Younium provides two to start from, Standard Order Template and Standard Product Template, both for Import.
The date format and decimal separator a template saves are the ones described in Values in the File.
Last updated
Was this helpful?