Mapping Templates

Mapping Templates in Compound Direct define how the columns in a CSV or Excel (.xlsx) file correspond to customer, prescriber, product, prescription, draft, order, account, delivery, and e-prescription information. A mapping template is required before starting a bulk import, and can be reused whenever a source file follows the same layout.

Templates can also supply default values, require important information, and apply rules that standardise or validate each row before it is imported. Setting up the template carefully reduces repeated data entry and makes each bulk import easier to review.

This guide will help you:

• Create a mapping template from an existing spreadsheet or by selecting mapped fields.
• Configure source columns, default values, required fields, and mapping rules.

• Clean source values, split a row into several items, and prepare full addresses for lookup.

• Partition one spreadsheet into separate bulk imports.
• Download a blank CSV template, start an import, and manage existing templates.

Finding a mapping template

1. Click the settings gear icon in the top-right navigation bar to open ‘Workspace settings’.
2. In the left-hand side menu, under ‘Templates’, select ‘map Mapping Templates’.
3. The ‘Mapping Templates’ page shows ‘Template Name’, with the description below the name, followed by the ‘Modified’ and ‘Last Used’ dates.
4. Begin typing the template name in the search search bar to refine the list.
5. Click the desired ‘Mapping Template’ to view or edit it.

Alternatively, find a template through the ‘Bulk Import Dashboard’:

1. Click ‘More keyboard_arrow_down’ in the top navigation bar to open the drop-down menu.
2. In the drop-down menu, under ‘Advanced’, select the ‘table_view Bulk Imports’ link.
3. Click ‘Manage Templates’ on the ‘Bulk Import Dashboard’.
4. Find and open the template in the ‘Mapping Templates’ list.

Planning a mapping template

Before creating a template, identify which columns your source system provides and which Compound Direct fields they should populate.

1. Use one row per item, or use a splitting rule when a row contains several eScripts or a list of items.
2. Keep the source column headings consistent between files. A saved Source column should match the corresponding spreadsheet heading.
3. Include enough information to identify the required customer and product for every active row.
4. Add prescription, prescriber, delivery, account, or order fields only when they are needed for your workflow.
5. Decide which values are supplied by the spreadsheet and which should be supplied by a template Default Value.
6. Decide which fields must be completed before drafts can be created, then mark those mappings as Required.
7. If one file should create separate imports, decide which source value will be used as the Partition Code.

Creating a mapping template

1. Click the settings gear icon in the top-right navigation bar to open ‘Workspace settings’.
2. In the left-hand side menu, under ‘Templates’, select ‘map Mapping Templates’.
3. Click ‘Create Template’.
4. Enter a clear Template Name.
5. Enter a Description that identifies the source system, file type, or intended workflow.
6. Build the ‘Column Mapping’ in either of the following ways:
   1. To scan an existing layout, upload or drag and drop one .csv or .xlsx file into ‘Upload import file to be scanned’.
      1. Set ‘Read Offset’ to the number of rows above the headings (for example, 2 skips two introductory rows). The next row supplies the headings. If an Excel workbook has multiple sheets, choose ‘Worksheet’.
      2. Where a heading resembles an available Compound Direct field name, the corresponding Mapped field is selected automatically.
      3. Review every automatically selected field before saving.
   2. To build the template manually, click ‘Add Mapped Field’ or ‘Add Mapped Fields’.
      1. Search for a field or expand a category.
      2. Tick each field you want the template to support.
      3. Click ‘Done’.
7. Configure each row in ‘Column Mapping’:
   1. In ‘Source column’, enter the heading used in the spreadsheet.
   2. In ‘Default Value’, optionally enter a value to use when the source value is blank or the source column is not supplied.
   3. Tick ‘Required’ when the mapped value must be present before the item can proceed.
   4. Under ‘Mapped field’, select the Compound Direct field that receives the value.
8. Drag the reorder icon to arrange the mappings in a practical review order.
9. To remove a mapping, click the delete_outline icon beside it.
10. Add any required mapping or splitting rules. Hover over or focus an info_outline help icon for explanations and examples.
11. Click ‘Create Template’.

Understanding column mapping

Each mapping row controls how one value is prepared for the import.

Common mapped-field formats

1. Enter dates in your workspace date format or as YYYY-MM-DD.
2. For a Yes/No field, use TRUE, YES, Y, or 1 for Yes. Other or blank values are treated as No.
3. Separate multiple tags with commas in fields such as Tags, Customer Tags, or Order Tags.
4. Enter percentages as numbers between 0 and 100 where a percentage field is used.
5. Enter currency and quantities as numbers without explanatory text.

Mapped fields that affect later processing

Mapping full addresses for lookup

When address lookup is enabled for your workspace, map a complete address to ‘Customer Address (Lookup)’ or ‘Delivery Address (Lookup)’. These fields retain the imported address and request lookup during bulk-import reconciliation; the template itself does not look up the address.

1. Open the template and click ‘Add Mapped Fields’.
2. Select ‘Customer Address (Lookup)’ under Customer or ‘Delivery Address (Lookup)’ under Delivery Address.
3. Set ‘Source column’ to the heading containing the complete address, then click ‘Save Template’.
4. Start the import and review the result in ‘Drafting’. A successful lookup fills street address, address line 2, suburb, city, state and postcode where supplied by the address provider.

Configuring partitioning

Partitioning uses a value on each spreadsheet row to split one uploaded file into separate bulk imports. This is useful when one source file contains groups that should be reviewed and processed independently.

1. Create or open a mapping template.
2. Under ‘Column Mapping’, click ‘Add Mapped Fields’.
3. Expand ‘Mapping’ and select ‘Partition Code’.
4. Choose how the code will be supplied:
   1. Map a spreadsheet heading that already contains the required code.
   2. Enter a Default Value when every row should receive the same code. This produces one named import unless a mapping rule changes the code for some rows.
   3. Use a Mapping Rule to copy, standardise, or construct the code from other source data.
5. Click ‘Save Template’.
6. Use the template to start a spreadsheet import and review the partition groups before creating the imports.

Adding mapping rules

Mapping rules use ‘When’ conditions and ‘Then’ actions to change or validate values. They can work on imported source columns even when those columns are not mapped to a Compound Direct field.

1. Open the template and, under ‘Mapping Rules’, click ‘Add Mapping Rule’.
2. Enter an optional ‘Rule Name’ and leave ‘Rule enabled’ on.
3. Choose ‘Values to Update’:
   • ‘Mapped Fields’ changes the output values that will be imported, such as Barcode or Product Name.
   • ‘Source Columns’ changes a working copy of the spreadsheet values for later rules and splitting. Select a heading, or type it and press Enter. No direct column mapping is required.
4. Under ‘When’, choose a source column or mapped field, a comparison and, when needed, a comparison value. Use ‘Add Condition’ with ‘All conditions are true (AND)’ or ‘Any condition is true (OR)’. Remove all conditions to run the rule for every row.
5. Under ‘Then’, choose an operation and its destination. For example, copy a value, remove a prefix, trim whitespace, change a number, clear a field, or add a row error. Use ‘Add Action’ for further actions; they run from top to bottom.
6. Under ‘Advanced’, optionally turn on ‘Skip When’ and enter conditions that prevent this rule from running.
7. If appropriate, turn on ‘Skip Later Updates’ to protect matching output rows from later updates. In source-column mode this is ‘Skip Later Source Rules’, which stops later source-column rules for that original row. Rules that add extra rows still run.
8. Click ‘Save Mapping Rule’, review its card, then click ‘Save Template’.

To change the sequence, drag the drag_indicator handle. Use ‘Edit’, ‘Duplicate’ or ‘Remove’ on a card to manage the rule. Turning ‘Rule enabled’ off keeps the rule saved but skips it during import; its card displays ‘Disabled’. On a narrow screen, the creation buttons read ‘Mapping Rule’ and ‘Splitting Rule’.

Cleaning source values before splitting

For a source column named Consumables containing Items: Syringe 1ml, Swab pack 10PK, remove the prefix before creating a row per item:

1. Open the template and click ‘Add Mapping Rule’.
2. Choose ‘Source Columns’ under ‘Values to Update’.
3. Set ‘When’ to source column Consumables, ‘starts with’, and Items: (including the space).
4. Set the ‘Then’ operation to ‘Remove a prefix’, choose or enter Consumables as the source column to update, and enter Items: as the prefix.
5. Click ‘Save Mapping Rule’ and place it before the splitting rule that reads Consumables.
6. Click ‘Save Template’.

To clear a cell unless it contains a phrase, use a source-column rule with no ‘When’ conditions and a ‘Clear the field’ action. Turn on ‘Skip When’ and add a ‘contains’ condition for the phrase you want to keep. This uses the same conditions and actions as other mapping rules; there are no separate before/after-splitting cleanup steps to configure.

Adding splitting rules

A splitting rule creates one row per value and copies the other details, such as the customer and delivery address. Use it for several eScript columns or a list of items in one cell.

1. Open the template and click ‘Add Splitting Rule’.
2. Enter an optional ‘Rule Name’ and leave ‘Rule Enabled’ on.
3. Choose ‘Split From’:
   • ‘Multiple Columns’: select or enter every ‘Source Columns’ heading in the order its rows should appear, including the first value.
   • ‘Separated Values in One Column’: select or enter the ‘Source Column’ and choose a ‘Separator’. Common options include comma, semicolon, pipe, new line, tab and space. You can enter a custom separator and press Enter; \n means a new line and \t means a tab.
4. In ‘Put Each Value In’, select the output field, such as ‘Barcode’ or ‘Product Name’.
5. Choose ‘Existing Rows’:
   • ‘Replace With Split Rows’ replaces each current row with its split rows. Use this for the main items, such as two eScripts becoming two prescription rows.
   • ‘Keep and Add Split Rows’ keeps current rows and adds the list once per original spreadsheet row. New rows start with the original column-mapped details. Use this for extras, such as consumables, that must not be repeated for every prescription row.
6. In ‘Clear Fields’, choose any values to clear on the rows this rule creates, such as ‘Barcode’ for consumables. Other existing rows keep those values.
7. Leave ‘Trim Spaces Around Values’ on when spaces surrounding each item should be removed.
8. Click ‘Save Splitting Rule’, position it in the rule list, then click ‘Save Template’.

Example: prescriptions plus consumables

For one spreadsheet row with EScript1, EScript2 and Consumables containing Syringe 1ml, Swab pack 10PK:

1. Map shared customer and delivery columns as usual. Add any source cleanup rules first.
2. Add a splitting rule using ‘Multiple Columns’, ordered EScript1, EScript2, with ‘Put Each Value In’ set to ‘Barcode’ and ‘Existing Rows’ set to ‘Replace With Split Rows’.
3. Add a second splitting rule using ‘Separated Values in One Column’, source Consumables, a comma separator and destination ‘Product Name’.
4. Set this second rule to ‘Keep and Add Split Rows’. In ‘Clear Fields’, select ‘Barcode’ and any other prescription or product identifiers that should not carry into a consumable row, such as ‘Product Code’ or ‘Repeat On File’ if supplied by this template.
5. Save both rules and the template, then check a sample import in ‘Uploaded’.

The result is four rows: two prescription rows, followed by one row for each consumable with blank barcodes. Customer and delivery details are copied. Consumables are added once per source row.

Using the JSON rule editor

Both mapping and splitting flyouts offer ‘Visual’ and ‘JSON’ modes. Start in Visual to build the rule, then switch to JSON when you need an expression that the visual controls do not provide.

1. Open the rule and switch to ‘JSON’.
2. Edit the existing expression and resolve any validation message.
3. Switch back to ‘Visual’ if the expression can be represented there. Otherwise continue in JSON.
4. Click ‘Save Mapping Rule’ or ‘Save Splitting Rule’, then ‘Save Template’.

Testing and downloading a mapping template

1. Save the mapping template.
2. Click ‘Get Template file_download’ to download a blank CSV containing its source headings.
3. Open the file and confirm that the headings match the export produced by your source system. ‘Get Template’ downloads direct mapping headings without defaults, with blank rows for ‘Read Offset’. Add any headings used only by rules or splitting if they are missing.
4. Add a small set of representative rows, including any unusual customer, product, order, or prescription scenarios.
5. Start a test import and review the ‘Uploaded’ and ‘Drafting’ stages before creating drafts.
6. If columns map incorrectly or repeated issues appear, return to the mapping template, make the correction, and use a new test import.

Starting an import from a mapping template

1. Open a saved, unarchived mapping template.
2. Ensure there are no unsaved changes.
3. Click ‘Start Import’.
4. In the ‘New Import’ modal, either:
   1. Upload or drag and drop a completed .csv or .xlsx file; or
   2. Click ‘Create Without File’ to add rows manually.
5. Continue with the Starting a bulk import guide.

Editing and managing mapping templates

1. Click the settings gear icon in the top-right navigation bar to open ‘Workspace settings’.
2. In the left-hand side menu, under ‘Templates’, select ‘map Mapping Templates’.
3. Select the desired template.
4. Update the Template Name, Description, column mappings, defaults, required fields, or mapping rules.
5. Click ‘Save Template’.

To duplicate a mapping template:

1. Open the mapping template.
2. Click the ‘More actions’ menu.
3. Select ‘Duplicate Template’.
4. Update the duplicate's name or settings, then click ‘Save Template’.

To archive or unarchive a mapping template:

1. Open the mapping template.
2. Click the ‘More actions’ menu.
3. Select ‘Archive’ or ‘Unarchive’.

Customising import columns with a mapping template

The table layout used during an import is saved with its mapping template, helping repeated imports use a consistent review layout.

1. During an import, open the ‘Uploaded’, ‘Drafting’, ‘Queued’, or ‘Dispensed’ tab.
2. Click ‘Columns’.
3. Use ‘Search columns...’ to find a field in a long list.
4. Tick the fields you want displayed and untick fields you want hidden.
5. Click ‘Done’.
6. Drag a column boundary to resize it, or double-click the boundary to reset its width.
7. Open the more_vert menu in a supported column heading to:
   1. Select ‘Sort ascending’ or ‘Sort descending’. Select the active direction again to remove the sort.
   2. Select ‘Pin column’ or ‘Unpin column’.
   3. Select ‘Export column CSV’ to download the values currently included by the table's search and sort.
   4. Select ‘Hide column’ to remove the column from the current view.