Product Creator - v1206

Created by Migration Bot, Modified on Fri, 23 Oct at 2:30 PM by Migration Bot

Originally written by John Hardwick, last updated May 16, 2024, on the Swan Retail knowledge base (FRS).


Based on SwanFRS version 1206 - Spreadsheet version 1206.2

Spreadsheets are now named commensurate with the version of SwanFRS they are released against.

The second digit relates to a major patch with the third component being a minor release or bug fix.

This is designed to make it easier to establish whether the spreadsheet is compatible with your version of SwanFRS and when used in partnership with the Last Updated date to establish if you are using the latest version of the spreadsheet.

It will only be possible to ‘download’ the latest version (patch) of the spreadsheet.

Click here to view / download the PDF version of this document.

FRS-ProductCreation-Spreadsheetv1206-240323-1117.pdf

Overview

This spreadsheet is based on Office 365 and designed to work using the Windows operating system.
The IOS operating system does not support all macro features - as a result this spreadsheet will not function correctly with IOS

Older Versions of Excel, using the spreadsheet when opened in SharePoint or disabling macros will cause the failure of the document.

Certain areas of the spreadsheet consume PC resources therefore a 64 bit operating system, with sufficient memory is strongly recommended.

When wishing to Paste data into the spreadsheet please use the CTRL-V or the Paste-Values option. Pasting data directly into a cell may result in the formula's assigned to that cell being lost and therefore the XML may not load successfully.

When deleting rows in tables please do not delete row 6 in any tab as this controls the formulas - deleting may cause the XML creation to error.

Pages are password protected to preserve formulas and indexing

SwanFRS uses the SKU as the base for stock management.

SKUs can be created within the application or imported via an XML product schema. For those wishing to create / maintain their products outside of SwanFRS the two most prevalent options are –

X-margin – a third party product and pricing tool which requires licence from In2Retail, this spreadsheet is a macro-based Microsoft® spreadsheet requiring a suitable licence from Microsoft®

This document details the spreadsheet solution and how to use it.

Spreadsheet Operation

The product creator spreadsheet consists of several tabs, each tab detailing a different aspect of the product structure.

The spreadsheet checks data entered and will produce warnings if the data entered a cell is not valid.

When hovered over the green help cells provide useful information relating to the spreadsheet or cell associated with the help message.

There are help messages in most column headings throughout the spreadsheet (indicated by a small red triangle in the top right of the cell, or by a green square with a question mark in it. Hovering will show the message as indicated below.

The spreadsheet hides columns once the Preview Create Catalogue Data button is selected.

Certain fields offer the user advice when the cell is selected.

Many tabs offer a series of images, displayed in the header section of the page.

These images perform specific tasks and are detailed below.

Icon

Purpose

Certain tabs offer the operator the option to sort the data. Clicking this icon instructs the program to sort the table contents.

Sorting enables pick lists to be presented in alphabetical order.

Clicking this image instructs the program to remove the row from the table.

Prior to clicking ensure you have clicked in the row to be deleted. The is no undo option for this process.

This option is also available by holding the control key and pressing D

Tip – To remove multiple rows, click in the first row to be deleted, hold the mouse key down and drag down to the last row and then press CTRL-D – this will delete the selected rows.

Clicking this image instructs the program to add a new row immediately ABOVE the current cursor position within the table.

This option is also available by holding the control key and pressing I

It is possible to set the default number of rows to insert in a single action (range 1 to 100) – note the large the number the longer the time it takes to process.

When inserting click into the row you wish to have new rows added below and click the icon or press CTRL-I, the following window will appear asking how many rows to insert.

The window will prompt with the default row setting however this can be changed. Invalid entries will be ignored, and the default rows value used.

The program is designed to ignore blank rows in the tabs – it is not necessary to remove blank lines before generating the output file for the ProductXML import.

Clicking this image instructs the program to clear the database of ALL ROWS.

The spreadsheet presents the following window ensuring the button was not clicked by mistake.

There are no undo options for the process.

Where visible, it is possible to toggle the data in ‘word wrap’ mode.

Word wrap shows all contents of a cell within the cell as several lines or will show a single line of narrative which max extend over the end of the cell.

This feature is useful if working with numbers, thus reducing the column height, where large descriptions are not as important.

There is no loss to data integrity toggling between these display modes.

Within the Items tab, this feature allows you to search and replace text.

Highlight the cells to be searched, then click this icon.

The program will ask for the text to be replaced and then what it will be replaced with.

All text matching the ‘replace’ text within the highlighted cells will be replaced with the characters entered in the ‘replace with’ entry field.

Where visible this icon, instructs the page to toggle the case of selected cells.

The toggle will cycle through all lower case, sentence (capital first letter), uppercase variants.

This icon unlocks protected cells on the Range, Options and Choices tabs, subject to the entry of a valid password.

These locked cells are to protect the data already loaded in SwanFRS.

For example, if you change an option choice from Red to Blue then all the history relating to that choice changes.

This button acts as a toggle locking and unlocking specific cells.

Unlocking allows typo’s to be corrected, for example, it should not be used to change the name, answer – always create a new and discontinue the old in these circumstances.

The password for this feature will be provided during training or by raising a support call.

This button is only available on the Items tab and generates a data dump of the tab without cell controls or conditions and may be used as desired.

The data file is a separate spreadsheet..

Wherever this icon is displayed, there is a video guide available.

Video’s are played in a browser via YouTube and require internet access.

This icon, where displayed, can be clicked to view the image associated with the selected cell.

The spreadshsheet must be configured to show images, and the cursor must be positioned in an image field to view the image.

Images with a full path (http URLS for example) cannot be displayed.

Where an image can be found, a popup window shows the image, click the cross or the yellow close button to close the window and return to spreadsheet operation.

By default, tabs have been protected. This means several cells are locked off and therefore are not accessible or amendable. This is to make the process of creating data easier.

Locked tabs preserve formulas and conditional checks on the cells, and therefore it is strongly recommended that tabs remain locked.

Please contact Swan if there is a something the spreadsheet is blocking you doing as we can often make a change or advise of an alternative way of achieving the requirement.

When pasting into the spreadsheets always use the PASTE VALUES options. This ensures the source contents are copied into the spreadsheet without the formatting of the source.

Certain features available for unlocked tabs are restricted however alternatives are usually available. If you are experiencing difficulties populating the spreadsheet, please contact your Swan representative.

Table Filtering

It is possible to filter the data displayed in the table by pressing the grey down arrow shown to the bottom right of the header block.

Clicking offers the operator the ability to filter the contents of the table. There are pick lists for specific data, keyword selections and advanced options for data filtering.

Once chosen, specific data rows may disappear and the triangle icon is replaced with a funnel icon.

This is of particular use in the items, choices, and refresh tabs where it can be used to restrict the scope of the price changes applied via the formula buttons.

It is important to note that leaving a filter on a tab may cause processes such as the importing of data / generating XMLs to fail.

Please ensure you reset the filtering once completed. I.e., make sure there are no filtered columns in the tab. There is a short cut to achieve this – to remove filtering from all tabs simply hold the control key down and press the y (lower case) key on the keyboard.

Alerts

Several tabs present an Alert column, this is designed to advise if there is an issue with the data on that row.

If one or more rows have an ‘red’ alert a yellow warning is displayed (as shown below) in the header row to advise the operator there are issues with the data set.

  • Text on a yellow or salmon background indicate a non-critical warning – these can be ignored from the perspective of generating XML. The message advises of the issue, the operator may elect to change the data thus removing the alert or proceed.

  • White text on a red background indicates a data inconsistency that will stop the successful loading of data into SwanFRS. These alerts should NOT be ignored and corrected. The routine will not allow you to save the XML data if there are data inconsistencies within the spreadsheet.

The GenerateXML button will not operate if one or more of the Range, Options, Choices or Items tab is presenting the CHECK ALERTS message.

Wait Screen

When processing data the spreadsheet may present the following page.

During this time screen updating within Excel is turned off and the program may appear to have frozen. Please allow the process to complete all features will become available once the process has finished.

Performance Note

Processes which may take time to complete, such as importing data, will show the status of the processes.

It is also important to note that when processing larger data volumes, it may take the program some time to complete its task. During this time other Microsoft Office products such as Outlook may freeze or run slowly.

It is recommended that prior to processing a large data set, ensure your PC / laptop is running on mains power and all other ‘programs’ are turned off or paused. Doing so will improve performance.

Performance issues have been experienced on older machines, perhaps running on 32 bit or older processes. The data crunching aspect of the spreadsheet does consume resource therefore is performance is an issue it is suggested that other programs are closed or perhaps a more powerful PC is used for this process.

The spreadsheet will advise the operator should a large data set be selected for processing.

Home Tab

There are some dos and don'ts re operating this spreadsheet.

Please ensure you are aware of the ramifications of making changes to the spreadsheet once data has been loaded into SwanFRS.

For example, changing the ID of a range or option item will change all trading histories of that product – Always discontinue the old and create new.

There are also a series of useful shortcuts which will make processing a little easier.

Control Key Shortcuts

There are a number of control key shortcuts which will improve productivity.

Note these controls are lowercase keys

To operate hold the <control> key down and press the appropriate key having positioned the cursor in the correct cell first.

<Control> &

Action

d

Deletes the selected row(s)

y

Clear any filters set on ALL tabs

i

Inserts one or more lines from the current cursor position

v

Pastes the contents on the clipboard into the selected cell

e

Shows the last exported value of the cell the cursor is currently in.

This option applies only to the Items Tab.

<space>

selects all items in teh coloum the cursor is currently positioned.

t

Moves the cursor to first cell in the table (usually C6)

h

Moves the cursor to the first active cell on the current row.

Spreadsheet Column Headers Colour Scheme

Each column in the spreadsheet with a column header will have one of the following colours.

Items with the blue background are mandatory

Items with the light blue background are optional

System populated columns are pink

Locked cells will also present with a pink background.

Cost / selling columns - These columns can be used to generate margin information and work in partnership with the dark blue cost control columns in the page header

These are optional cost columns, holding ancillary costs or work zone prices

Green column headings indicate ‘web’ related columns.

Row Visibility

Where the data table, due to the number of rows, exceeds the width of a ‘standard’ screen, the spreadsheet will highlight the selected row with a light blue background, which allows the operator to reference the specific line without losing focus when horizontal scrolling.

The active cell is shown with a darker blue background.

Admin Tab

This tab is password protected and is designed to allow a systems administrator to control how the spreadsheet is to be used.

Settings applied in this tab restrict features used elsewhere within the spreadsheet, access should only be enabled for operators empowered to make such changes.

Clicking the Admin tab presents a password screen and will continue to present it until a valid password is entered. The password is then not requested whilst the spreadsheet is open. Closing and re-opening requires the password to be re-entered.

The password is provided during training or can be obtained from Swan Support by raising a ticket.

Check boxes on the Admin and Catalogue tab show with a light purple background when unchecked to aid identification.

Control Fields

Field

Comments

Spreadsheet Administrator Controls

SwanFRS Version

Choose from

  • 1205

Features introduced in 1206 will not be exported when sending data to SwanFRS version 1205.

  • 1206

The XML output will only include Sales Departments and Is Till Product fields (Items Tab) in this mode.

SKU Format

This field is changed via the TOGGLE button and alternates between

  • Line ID (default) – The SKU will be the CatalogueID-RangeID-Line#

  • SupplierRef – The SKU will be CatalogueID-RangeID-SupplierRef

(If the supplier ref is blank the line ID will be used)

Mode

This field is changed via the TOGGLE button and alternates between

  • Full – allow access to all fields within the Items tab.

  • Price Only – all except price related cells are locked.

Web Defaults

This field is changed via the TOGGLE button and alternates between

  • Y - Turns on web defaults

  • N - Turns off web defaults

  • Not Set - no defaults in use.

Warn on Row Delete

This option allows you to turn off the ‘are you sure’ question when the delete row icon is selected.

Default # Of Insert Rows

Set the default number of rows the system will insert in any one action.

Range 1 to 200.

The larger the number the longer the program will take to complete the task.

Include Prices

Specify if the XML generated is to include prices.

  • Tick – Costs and selling prices are to be included within the XML Catalogue used to populate SwanFRS with items.

  • Unticked - excludes cost and selling prices when sending data to SwanFRS.

When generating new SKUs, the import will be rejected if costs are excluded.

Tax Rate

This field is used to calculate the margin of the item.

The margin is calculated as Selling – Tax – Cost / Selling – Tax expressed as a percentage.

If operating with Tax exempt (US model) set this field to zero otherwise set to the current prevailing tax rate for this supplier/catalog.

Exchange Rate

Specify the exchange rate to be used when converting the currency cost to a base cost within the item tab.

Tick to Overwrite Prices

Define how the calculate prices button in the items tab works.

  • Ticked - New prices will replace any existing values in the destination column.

  • Unticked – Only set prices for empty cells in the destination column.

Tick to Include Lead Time

Define if the XML export is to include lead times.

  • Ticked - Include lead times in the export (For new SKUs this is essential).

  • Unticked – Exclude lead times.

Ideal Margin

Specify the ideal margin percentage you wish to obtain when generating selling prices. (Range 0 to 100.00)

This field can be updated from either the Choices, Items or Refresh tabs, subject to Locking the Margin.

Export Landed instead of

This field should only be used if you wish to use the landed cost as calculated within the spreadsheet as the item purchase cost but do not wish to send individual ancillary costs to SwanFRS.

This field is changed via the TOGGLE button and alternates between

  • Not Set - Send costs and ancillary costs to SwanFRS.

  • Cost 1 - Send the ‘landed cost’ as Cost 1 when exporting XML product data omitting all ancillary costs from the export.

  • Cost 2 - Send the ‘landed cost’ as Cost 2 when exporting XML product data omitting all ancillary costs from the export.

When set to Cost 1 or Cost 2 importing data back into the spreadsheet from SwanFRS will cause all ancillary costs to be lost.

Tick to Show Picture Previews

Tick to show, where possible a thumbnail picture of the image X field.

Untick to turn this feature off.

Images are not stored within the spreadsheet. Operator access to the Image Folder Location used by SwanFRS is required to show thumbnails.

Tick to Turn on Multi User Mode

Tick to turn on a single user locking mechanism which prohibits two or more operators working on the same spreadsheet at the same time.

This progress uses the FilesInUse subfolder held in teh SwanFRS output Folder Location (C28).

To work this folder must be visible to both SwanFRS and all users editing the spreadsheet.

When in use the following message will display when the spreadsheet opens.

If the spreadsheet is ‘locked’ by another a message will display the username of the person editing the document along with the option to enter a password to bypass the lock.

Details of the password will be provided during training or by contacting Swan Support.

Untick to turn this feature off.

Tick to Lock Margin

Tick to stop operators from changing the margin via the Choice, Item and Pricing tabs.

Untick to allow unrestricted change to the margin target value.

SwanFRS Linked Refresh

Use the TOGGLE button to change this setting.

ON prohibits the manual creation and alteration of Product Groups, Attributes, Relationships and Sales Departments within this spreadsheet and to import them directly from SwanFRS.

SwanFRS issues updates of these data sets periodically however manual refreshes can be requested.

OFF enables the creation and amendment of this reference data within the spreadsheet.

Note product groups, attributes and relationships are not sent to SwanFRS and therefore any changes need to be manually replicated in SwanFRS.

When operational, the SwanFRS reference data is automatically reloaded each time the spreadsheet is opened.

The REFRESH NOW button can be used to refresh the reference data whilst the spreadsheet is open.

Please raise a ticket with Support should you wish to begin using this feature as changes to the configuration of SwanFRS are required.

Tick to View Change Log

Ticking Opens the Change Log tab for reference only.

Unticking hides this tab.

Tick to Lock Formulas

Ticking stops the operator from changing the formula price charming controls on the Catalogue Tab.

Unticking Allows general changes to the 5 formulas.

Title for Cost prices

(1 to 2)

Specify the name to be shown above the Cost 1 or 2 column as a reminder to its content / purpose.

Title For Ancillary Cost

(1 to 5)

Specify the name to show above the Ancillary cost column on the Choices, Refresh, Pricing and Items tabs.

Title for Sell prices

(1 to 3)

Specify the name to be shown above the Sell 1, 2 or 3 column as a reminder to its content / purpose.

Network Paths

Image Folder Location

If you wish the spreadsheet to show a thumbnail image of the cells contents (image place holder cells only), specify the path to the image folder as used by SwanFRS and ensure the Picture Previews checkbox is ticked.

If the spreadsheet cannot establish the folder path or image filename no thumbnail will be presented.

http image file paths will not display the image.

To view the image, click in the appropriate cell and click on the icon.

Images are not held within the spreadsheet. They must be saved in the image folder used by SwanFRS to display.

This feature can be turned on or off as desitred with no impact on the spreadsheet.

SwanFRS output Folder Path

This path is required when using SwanFRS Linked Refresh, and holds the path to the shared XML folder.

This folder is used for access control and autoload of SwanFRS data and contains the FRSData and FilesInUse subfolders. The folder therefore has to be visible to SwanFRS (if uploading reference data) and ALL operators using the spreadsheet (when using the single user lock functionality).

SwanFRS pushes data exports of the product group, attributes and sales departments to a subfolder within this path for the spreadsheet to refresh its ‘refrence copy’ automatically when opened.

If the file does not exist in the folder no upload will occur.

Untick to hide columns in the Items tab

Various

Control where the column is to display within the Items tab.

Y – displays the identified column.

N – hides the column

Please note hiding the column simply removes it from the display however if you paste multiple columns into a single row, with one of the columns hidden, the hidden cell will be included in the copy. I.e., pasting 3 cells into A, B, C with B hidden will populate B.

When generating XML hidden columns with content will be used in the output. Hiding does not remove content or stop its use.

Catalogue Tab

The Catalogue tab links the catalogue to a supplier / vendor within SwanFRS and enables the operator to influence the way the spreadsheet dispalys / operates.

Catalogue Version

The catalogue version can be found in cell O2 on the Catalogue tab and is used in partnership with the published date which is shown in cell O19.

To view new features in this version click the video icon in cell N14.

Control Fields

Field

Comments

Catalogue Header Information

Supplier (Vendor) Catalogue ID

This is a numeric field and must be linked to the supplier / vendor within prior to attempting to import the catalogue.

It is very important that the correct catalogue ID is set, importing data with the wrong catalogue ID will push the products into the wrong supplier which may ‘corrupt’ existing data. If unsure, please check via the CATALOGUE ID button within SET UP SUPPLIERS to make sure you have the correct ID before proceeding.

Access to this field is locked when in pricing only mode.

Catalogue Name

Optional catalogue name.

Version

Optional version number.

Tab Visibility Control

A yellow background indicates there is data in the tab which will be used when generating the XML output whether or not the tab is visible.

Sets

The Sets tab enables the construction of group codes, whereby if you sell a master code in SwanFRS, it will autoatically expand it into is component parts.

Tick to show the sets tab.

Untick to Hide.

Default Options

This page control the base or default options presented when ordering a product within swanFRS.

Tick to show the defaults tab.

Untick to Hide.

Data held in this tab remains event if the tab his hidden.

Web Feature

This feature is used as a ‘roll-up’ when linking to an e-commerce website

Tick to show the web features tab.

Untick to Hide.

Data held in this tab remains event if the tab his hidden.

Relationships

This feature instructs SwanFRS to recommnd the seling of linked products when raising a sales order.

Tick to show the contents of the relationship tab.

Untick to Hide.

Data held in this tab remains event if the tab his hidden.

Block Options

This tab allows specific option choices to be omitted from pick lists when ordering product.

Tick to show the contents of the blocked tab.

Untick to Hide.

Data held in this tab remains event if the tab his hidden.

Product Groups

This tab holds a copy of the product groups used in SwanFRS

Tick to show the contents of the product groups tab.

Untick to Hide.

Data held in this tab remains event if the tab his hidden.

Data held in this tab is NOT sent back to SwanFRS and therefore any changes applied must be manually updated within SwanFRS.

This tab forms part of the ‘reference’ tabs within the spreadsheet and is therefore colour coded dark blue.

Price Points

This tab enables the setting of a series of price ranges and a ‘golden price’ or price point that values within the range will be ‘charmed’ or charged.

Tick to show the contents of the price points tab.

Untick to Hide.

Data held in this tab remains event if the tab his hidden.

This tab forms part of the ‘reference’ tabs within the spreadsheet and is therefore colour coded dark blue.

Product Group X Ref

This tab is used in partnership with importing data and where set provides a cross reference between the ‘source’ product group and SwanFRS product groups.

Tick to show the contents of the product cross reference tab.

Untick to Hide.

Data held in this tab remains event if the tab his hidden.

This tab forms part of the ‘reference’ tabs within the spreadsheet and is therefore colour coded dark blue.

Sales Departments

This tab holds a copy of the sales departments used within SwanFRS.

Tick to show the contents of the sales departments tab.

Untick to Hide.

Data held in this tab remains event if the tab his hidden.

Amendments to this tab are prohibited in Pricing mode or when data is directly imported from SwanFRS.

It is possible to send data back to SwanFRS however this is via a separate csv output. Changes are not applied via the XML output structure.

This tab forms part of the ‘reference’ tabs within the spreadsheet and is therefore colour coded dark blue.

Price Refresh

This tab offers the ability to change prices without providing access to any other data element of teh product itself. It does not offer option price control.

Tick to show the contents of the price refresh tab.

Untick to Hide.

Data held in this tab remains event if the tab his hidden.

Prices changed in this tab remain separate to those held in the Items tab and therefore are not sent to SwanFRS unless the data is copied (via an action button) into the Items tab first.

This tab is displayed with the pink-red background.

Extended Item Attributes

It is possible to set a maximum of 10 attributes per product within the items tab, if this is inusfficient additional (unlimited) attributes can be set via this tab.

Tick to show the contents of the extended item attributes tab.

Untick to Hide.

Data held in this tab remains event if the tab his hidden.

This tab is optional and threfore is displayed with the light blue background.

Extended Item Options

It is possible to set a maximum of 20 first level questions per product within the items tab, if this is inusfficient additional (unlimited) options (questions) can be set via this tab.

Tick to show the contents of the extended item options tab.

Untick to Hide.

Data held in this tab remains event if the tab his hidden.

This tab is optional and threfore is displayed with the light blue background.

Extended Item Images

It is possible to set a maximum of 10 images per product within the items tab, if this is inusfficient additional (unlimited) images can be set via this tab.

Tick to show the contents of the extended item images tab.

Untick to Hide.

Data held in this tab remains event if the tab his hidden.

This tab is optional and threfore is displayed with the light blue background.

Extended Choice Links

It is possible to set a maximum of 5 linked questions per answer on the Chocies tab, if this is inusfficient additional (unlimited) number of next level questions can be set via this tab.

Tick to show the contents of the extended choice links tab.

Untick to Hide.

Data held in this tab remains event if the tab his hidden.

This tab is optional and threfore is displayed with the light blue background.

Notes

This notes pad is for operator only and is designed to allow the collection of thoughts and actions for future reference.

Tick to show the contents of the notes tab.

Untick to Hide.

Data held in this tab remains event if the tab his hidden.

This tab is not used in data export and as such is coloured red.

Help & Tips

This tab contains help, tips and advice as to what to do and not do with data including a number of control-key shorcuts.

Tick to show the contents of the help tab.

Untick to Hide.

Data Mapping

Data mappping allows raw electronic data to be ingested into the spreadsheet thus creating or updating existing records and removes the need to copy and paste multiple columns.

Default Product Group

This field must be set to use the data import mapping process.

Once the Prod Group tab has been populated, select the default product group from the pick list which will be used if this column is empty when importing mapped data.

Lead Time

This field must be set (none-zero) to use the data import mapping process.

Set to the number of days it takes to get a product from point of issuing the purchase order to receipt at the ‘warehouse’ door.

Sheet Mapping in Use

Is the quick data population method required?

  • Tick to show the contents of the notes tab

  • Untick to Hide

The import data tabs (SheetMapping and ImportData are coloured green to aid identification.

Default Range

Optionally specify a product range range to be used when the import process cannot identify a range.

Price Charming (formula 1 to 5)

Multiplier

Specify a value to multiply the ‘source’ column by to generate a base ‘selling’ price. E.g. 3.5 would increase the source price by a factor of 3.5.

Round to

Specify a value the calculated selling price (from field 1) is to be ‘moved to’.

E.g., 50 would change a selling price of 123 to 100. 10 would change a the 123 to 120.

If you do not want to use this feature set to 0.01

The calculated value is always rounded down.

Take off

Specify a value to be taken off the calculated price. E.g., 0.01 would cause all prices to end in 0.99. .25 would result in prices ending in 0.75 (both assuming a whole number was specified in field 2)

Set to 0 if you do not want to use this feature.

Name

Optionally specify a name for the specific formula calculation.

This makes selection of the appropriate formula easier when applying price charming within the Choices and Items tabs.

New Catalogue Linking

When creating a new suppliers catalogue it is essential to have a link point between SwanFRS and the Product Creator spreasheet. This is acheived via the Catalogue ID.

Menu Location: System Admin > Purchasing > Set Up Suppliers (Vendors)

Create the supplier / vendor ensuring and then click teh CATALOG ID button shown in the top level supplier listing.

Clicking the Catalog IDs button produces a screen like that shown below

Field

Comments

Catalogue ID

This unique number must match with the Supplier / Vendor Catalogue ID in the Catalogue Tab of the product creator spreadsheet.

Name

Specify the name of the catalogue used when communicating with the supplier / vendor.

Debrand

Specify the name of the catalogue which is used when conversing with customers.

Leave blank if the range / collection is not to be debranded.

Active

Tick to allow stock to be ordered, processed etc.

Data Type

This field must be set to XML if using the product creator as the sole means of product creation and maintenance.

If, once loaded, SwanFRS will be used to maintain items this field should be set to XML, Internal. This gives access to Set Up Product Pricing.

Managed Catalogue Product Group

Used when importing catalogues from third party generation systems and is used to force the product group for new items where not set in the import file.

Selling Price Format

For use where the catalog being imported does not have a selling price.

Choose from

  • None – do not use the auto calculate selling formula.

  • Calculate using a margin / mark up (set in the data entry text box at the foot of the column)

  • Percentage – uplift by a percentage of cost

  • Multiply – multiply the cost by a specified value.

Choose the price charming method from

  • None

  • Round down

  • 9 &5’s

  • Golden price rounding up within tolerance (set in the type in field)

  • Golden price rounding down

Ensure the Catalogue ID set here matches the catalogue ID set witin the spredsheet.

If these fields do not match product may be uploaded into the wrong suppliers porfolio within SwanFRS.

Refresh Lookup

When processing data in one of the ‘extra’ mapping tabs, the table within the tab displays the product description (where set), however if SKU information has been changed within the Items tab the description may be incorrect or not show at all, therefore it is recommended that the background look up tables are refreshed whenever data changes within the Items tab.

This is achieved by selecting the CLICK HERE TO REFRESH THE DESCRIPTOIN IN THIS TABLE button. Its position and narrative may vary between tabs.

When selected the background table is rebuilt thus ensuing the correct description is presented for each SKU on the line.

It is possible to enter a SKU reference which is not in the items tab this will results in the ‘not found’ message being displayed. This will not prohibit the data export; un-matched items will not be exported (except for the Sets tab which will allow component SKUs to be selected which are not present in the Items tab).

These tabs require the operator to specify a SKU code, however it is not possible to copy this column from the Items tab due to it being a protected field, therefore an alternative way of gaining access to the SKU column is required.

When rebuilding the background data, the process offers the operator the option of listing the SKU and its associated (concatenated range and description) as a separate spreadsheet. This separate spreadsheet effectively offers the ability to copy SKUs and paste them into the ‘extra’s tab.

To use this feature, select YES when prompted.

Selecting YES creates a new spreadsheet which contains a list of SKUs and descriptions. This new spreadsheet will usually be named Bookn (where n is a number). The screen will change to show the product creator on the left-hand side of the screen and the SKU pick list on the right.

This second spreadsheet does not need to be saved and can be closed at your discretion.

Having opted to use this feature the confirmation of completion message will appear like that shown below.

Clicking to confirm the process has completed replaces the ‘wait’ screen on the left-hand display with the ‘extras’ tab selected.

It is now possible to copy SKUs from the SKULookup tab and paste them into the ‘extras’ tab within the product creator spreadsheet.

Creating 2 Side by Side Panels.

The manual way of obtaining the same effect it to ensure you have two spreadsheets open.

On the main spreadsheet locate the view ribbon at the top of the page and follow the steps below.

If wish to open both spreadsheets at the same time, select the View menu option.

Then click on ‘View Side by Side’ to show both spreadsheets in the same screen.

Then select ‘Arrange All’ it is recommended you select the Vertical option.

This displays the two spreadsheets side by side for onwards processing.

To stop both screens scrolling at the same time, un-select the Synchronous Scrolling option. (This option is immediately below the View Side by Side option).

Upgrading from Previous Versions

It is possible to import data from earlier versions of the spreadsheet to do so it is necessary to open the original ‘old’ version of the spreadsheet and V7 at the same time.

From the Catalogue tab click the Import from Earlier Version button

Clicking produces the following window where the name of the ‘source’ data file (spreadsheet).

The filename keyed must match the name of the ‘old’ open spreadsheet. (Without the .xlsm suffix)

This routine will then ask if you wish to retain the ‘static data' (dark blue tabs).

Prior to confirming the action about to occur.

This version of the spreadsheet will only import version 7.x spreadsheets. If you are using an older version of the spreadsheet (Version 6 for example) you will need to import to version 7.3.2 and then import into this version.

Once the process has completed. Please ensure the data has been imported correctly prior to proceeding.

This feature will overwrite any data already present in the version 7 spreadsheet leaving the original unchanged.

Exporting from SwanFRS

It is possible to import data from SwanFRS for importing into the spreadsheet.

The process begins with a Crystal Report in SwanFRS – Product Export requires the selection of a supplier and a promotion and generates an export file.

Please ensure you’re running PRODUCT EXTRACT 7 (or later) which is designed specifically for this version of the product creator.

Once the output has been displayed on screen click the export button and then choose ‘export to excel workbook.

This will offer a series of export formats

Tip - choose the optoin containing the work ‘workbook'

The export file needs to be exported to xlsx.

Ensure you select the Microsoft Excel Workbook Data-Only option

With the export data file open, open the Product Creator Spreadsheet and click the Import from SwanFRS button.

Clicking produces the following window where the name of the ‘source’ data file (spreadsheet).

The filename keyed must match the name of the ‘export data file’ open spreadsheet. (Without the .xlsx suffix)

Clicking OK begins the process of pulling in the data from the source spreadsheet into the version 7 data format.

The program will advise once the process has completed. Please ensure the data has been imported correctly prior to proceeding.

All existing data tabs are cleared during this process.

Clearing Data

It is possible to clear the spreadsheet of product data using a single button.

From the Catalogue tab click the Empty Data Tabs (Restart) button

Clicking produces the following warning

Electing to clear the spreadsheet of data, clears all tabs except for the Catalogue and Product Groups tab.

There is no ‘undo’ feature for this process therefore it is strongly recommended that you take a copy of your existing spreadsheet before clearing data.

Notes

When turned on, via the Catalogue Tab, this page allows the entry of notes

Notes are not exported to SwanFRS or used elsewhere in the spreadsheet. They are solely used for reference purposes.

This tab is optional.

Table Columns

Field

Comments

Date

Optionally specify a date.

Comment

Optionally enter narrative.

Product Groups

This tab holds the product groups used in SwanFRS and is used as a look up for the product group choice list on the Items tab.

This tab is designed to be populated by your Swan Implementation team. Any new product groups set up within the Set Up Product Groups program should be replicated in this tab. when using manual update methods.

Variances between this tab and Set Up Product Groups may cause the Import Catalogue program to produce a load error.

When imported automatically from SwanFRS this tab becomes a look up tab - i.e., no alterations are possible.

Once your product groups have been created ask you implementation consultant to populate this tab and use if for all suppliers / vendors.

Field

Comments

Product group description

The product group description as set within SwanFRS.

PG Code

The code used within SwanFRS.

Class

Specify the type of product. Choices are –

  • 1 – Unique

  • 2 – Accessory

  • 3 – Measured

  • 4 – Service

This column is protected once data has been ‘saved for export’ or imported from SwanFRS. It is not possible to change the class within SwanFRS and to ensure consistency between the systems it is therefore not possible to change classes once exported or if imported into the spreadsheet.

Type

This field displays the type of class selected.

Sales Dept

(1 to 3)

These fields form a look up allowing the operator to see which ‘default’ sales departments the product group is linked to an where neccesary which item specific sales departments need setting within the items tab.

The spreadsheet does not create product groups in SwanFRS – product groups must exist before the spreadsheet is imported.

It is important to ensure this table is sequenced alphabetically – once the Groups have been created click the re-sequence table icon (yellow A-Z) in the header section of the page to acheive this.

Attributes

This tab holds any attributes used in SwanFRS and is used as a look up for the attribute’s choice lists on the Items tab.

When imported automatically from SwanFRS this tab becomes a look up tab - i.e., no alterations are possible.

Attributes are system wide choices and therefore this tab could be replicated in all spreadsheets, only setting the attributes applicable to the catalogue within the Items tab.

Field

Comments

Look up code

This field will produce a warning if one or more of the cells in the row would cause a problem when loaded into SwanFRS.

Duplicate indicates a repeated Attribute

Attribute Code

There are two options.

Create an attribute group name for selection within the Items tab with the value being keyed specifically for that item.

Set an attribute group and value. Separating the group and value with a pipe symbol (|).

Any value set against this code is ignored.

This option allows for speedy selection of commonly used attributes.

E.g., ‘Size | Small’ creates the attribute code of Size and a value of Small.

The import process will automatically create new attributes, where associated with a SKU.

It is suggested that the table is sequenced alphabetically, this makes picking the attribute and value easier within the Items tab

When linking to a Swan website the following attributes may be of use.

Examples of use are: -

Attribute Name

Value

Details

Promotion_Colour

Hex value

A hexadecimal value corresponding to the colour of the banner.

Consult with your web team re the correct value to use.

Promotion_Name

Free format narrative

A ‘banner’ title indicating the product is the subject of a special price – E.g., ON SALE

Note: The following list of Attributes have specific columns in the spreadsheet and do not need setting up. If used more than once per SKU, they will cause the load to fail.

Attribute Name

Value

Details

Brand

Free format narrative

Used when sending data to a linked website – used in product grouping

Commodity Code

Free format narrative

Displays on the Purchase order when importing / exporting goods (from Europe)

Is_Reorderable

1 or 0

Used when sending data to a linked website – controls whether only stock items can be sold.

PO_Label

1 or 0

Setting to 1 instructs the PO print routine to include a stock label which can be sent to the supplier for them to attach to the product prior to shipping.

Product_Upsells

SKU, SKU, SKU

A list of SKUs separated by commas instructing the web to suggest the customer purchases with their chosen item.

Promotion_Colour

Hex value

A hexadecimal value corresponding to the colour of the banner.

Consult with your web team re the correct value to use.

Shipping_Class

Free format text – no spaces

Used to instruct the website of advanced delivery rules – please discuss with your web consultant.

Ticket_Size

Integer

This attribute is designed to inform the ticket engine as to the size of label to be used for this stock item.

Price Points

This tab holds a list of preferred selling prices and is referenced by the update prices button on the Refresh, Choices, and Items tabs.

This tab is optional only required if wishing to use ‘Golden Price’ pointing.

Field

Comments

Look up code

This field will produce a warning if the start or end price point is blank or if the end price point is a less value than the start price.

Start Price

Specify the lowest price to be moved to the ‘price point’

End Price

Specify the highest price to be moved to the ‘price point’

Price Point

Specify the replacement price for all calculated prices within the start and end price range

It is suggested that the table is sequenced alphabetically, this makes picking the attribute and value easier within

If there are overlapping price points the price pointing logic will apply the first price point encountered ignoring any secondary duplicate records.

If the rounding process does not find a price range for the calculated price no rounding will take place.

Sales Departments

This tab holds a list of Sales Departments and Item Relationship look ups.

Sales Departments can be linekd to product groups and / or items.

Relationships are used within the ItemRelationships tab to design how SwanFRS will process the ‘Upsell’

When imported automatically from SwanFRS this tab becomes a look up tab - i.e., no alterations are possible.

It is possible to export the data held in this table and then to use the export as an import into SwanFRS. This process does not use the XML generation instead, click the White down arrow icon above the level 2 column data, This generates a csv output file which can be used as an import into SwanFRS.

Sales Departments

Field

Comments

Alert

This field advises if the same ‘department’ name has been entered multiple times at the same level.

It is possible to have the same name in mulitple columns.

Department Level

(1 to 3)

Specify the name of the sales department for the selected level.

Sales departments are not hierarchical each being a separate list, however when using them at product group or item level you cannot specify a level 2 if a level 1 has not been defined.

Active

Specify is the Sales Department is active within SwanFRS.

This field need only setting to N or n if the desription is in-active, the spreadsheet assumes it is active.

Relationship

Field

Comments

Description

Specify the relationship description used witin the Item Replationship tab.

Ranges

The Range / Collection tab is used to create the collection or range. This is effectively the style or design of the items within the range / collection

Field

Comments

Alert

This field will produce a warning if one or more of the cells in the row would cause a problem when loaded into SwanFRS.

The Range ID is empty

The Description or Debranded Desc exceeds 30 characters

If there is one or more alert the header row will show the red Check Alerts on a yellow background notification, useful if the issue relates to a row not currently displayed on screen.

Include

Setting this cell to ‘n’ tells the ProductXML tab to ignore this row thus omitting it from the XML generated.

All SKUs linked to this range are also excluded from the data export.

Setting to ‘n’ turns the row amber to indicate the row is ‘inactive’

Remove the ‘n’ to make the range active again.

‘n’ or ‘N’ are valid entries.

Turning off the range turns off all items within that range and will change the row on the items tab to a yellowed background with a Excluded from XML alert message.

Range / Collection ID

Unique integer within the supplier / vendor

The AUTO # button automatically populates all rows in the table with an incremental range ID number starting with the next number from the largest number already defined.

This process is non-destructive only converting ranges with descriptions that do not have Range IDs.

Cells in this column may be locked if imported from or sent to SwanFRS. To unlock click the red disk witht the pad-lock and enter the password. This will unlock the pink cells.

The password is provided during training or can be obtained by logging a support ticket.

To re-lock repeat this process.

Description

The supplier’s / vendor’s name for this range / collection.

Debranded description

Your name for this range / collection.

Set if you wish to call the range / collection something different on your sales floor.

Cells in this column may be locked if imported from or sent to SwanFRS. To unlock click the red disk witht the pad-lock and enter the password. This will unlock the pink cells.

The password is provided during training or can be obtained by logging a support ticket.

To re-lock repeat this process.

Exclude Margin

Is this Range excluded from sales order margin calculations?

Choose from Y or N.

Show debranded on PO

Is the debranded description (where set) to show on the Purchase order.

Choose from Y or N.

Image URL

Optionally specify the image file path / image URL for this range.

Embedded spaces in the image filename / path will cause the load to fail.

Where configured the spreadsheet will present a thumbnail image of the locally held URL.

Select the icon with the cursor sat in the desred Image URL cell.

Bullet (1 to 5)

Optionally specify up to 5 bullet narratives for this range.

Ticket Narrative

Optionally specify the narrative to print on display tickets when using the Style Ticket feature.

The import process will automatically create or update existing ranges / collections within SwanFRS

The spreadsheet uses a vlookup command to convert the Range Collection field (E column) on the Items tab into a Range ID (Column AF – Item tab).

Ranges can be found within Set Up Ranges / Collections within SwanFRS.

Options Tab

The Options tab lists options (questions) that may be asked by the system when defining an item.

The system will overwrite existing options or create new ones at time of catalogue import.

Do not change the description of an existing option as this will change all purchasing histories within SwanFRS. For example, changing Colour to Size will ripple across all stock items using the option thus changing their structure. If this is needed discontinue the old and generate a new.

Field

Comments

Include

Setting this cell to ‘n’ tells the ProductXML tab to ignore this row thus omitting it from the XML generated.

All options (Questions) and choices (Answers) are excluded from the data export.

Setting to ‘n’ turns the row amber to indicate the row is ‘inactive’

Remove the ‘n’ to make the range active again.

‘n’ or ‘N’ are valid entries.

Alert

This field will produce a warning if one or more of the cells in the row would cause a problem when loaded into SwanFRS.

The Type in Option is set – this is a warning only and does not trigger the alert message.

The No Choice Set is displayed if the option is set as a pick list with no ‘active’ choices available. (Active excludes options with discontinued date either historic or future)

If there is one or more alert the header row will show the Check Alerts notification, useful if the issue relates to a row not currently displayed on screen.

Description

Specify the question / option / statement.

Description MUST be unique.

Cells in this column may be locked if imported from or sent to SwanFRS. To unlock click the red disk witht the pad-lock and enter the password. This will unlock the pink cells.

The password is provided during training or can be obtained by logging a support ticket.

To re-lock repeat this process.

SwanFRS Option Description

Optionally specify an alternative name to be used within SwanFRS.

Using the same Description in this spreadsheet will cause issues therefore each row should have a unique description – this causes issues in SwanFRS where you may want to have more than one option with the same ‘operator facing’ title. E.g., Colour. – one for Grade A colours, one for Grade B colours and so on. This feature allows the same option name to be used in the software.

Cells in this column may be locked if imported from or sent to SwanFRS. To unlock click the red disk witht the pad-lock and enter the password. This will unlock the pink cells.

The password is provided during training or can be obtained by logging a support ticket.

To re-lock repeat this process.

Option ID

Specify a unique question number (within the supplier / vendor)

The AUTO # button automatically populates all rows in the table with an incremental Option ID number starting with the next number from the largest number already defined.

This process is non-destructive only converting option with descriptions that do not have Option IDs.

Cells in this column may be locked if imported from or sent to SwanFRS. To unlock click the red disk witht the pad-lock and enter the password. This will unlock the pink cells.

The password is provided during training or can be obtained by logging a support ticket.

To re-lock repeat this process.

Text option

Specify if the option can have a free format text entry.

  • N – a selection from a drop-down list is required.

Any option set to N without option choices will cause an error when the XML is generated.

  • Y – this is a text entry and therefore there are no option choices.

Affect Prices

Specify if the option will affect the item’s cost.

  • N – This option is not cost affecting.

  • P – The option will uplift the cost price by a specified percentage.

  • V – The option will uplift the cost price by a fixed value.

Prompt quantity

Specify if the option has a quantity entry.

  • N – No quantity applicable. (The letter o not a zero)

  • C – Choose a quantity from a pick list (0 to 10)

  • S – Manually set a quantity.

Auto Expand

Specify if the option is to display a list of all choices for linked questions before a choice for this question is selected.

This is of use if there are several grades with a further choice of colour within grade. Setting to Y shows all colours for all grades and auto populates the grade once the colour is selected.

Choose from Y or N.

Notes

This is a free format narrative, allowing the operator to enter useful information about the option question.

This field is not imported into SwanFRS.

Invalid or duplicated cells will be highlighted with a red or pink, respectively.

It is recommended that the table be sequenced alphabetic ascending description order.

This process not only re-sequences the data into alphabetical order it will also change any duplicated description adding the row number to the description used in the catalogue, if a SwanFRS description isn’t set it will move the original description title to this field. The Description is used in lookups within the spreadsheet. The SwanFRS Description will be sent to SwanFRS and will be used in the application.

Option Choices Tab

The Option Choices tab lists answer or choices to Options (questions) that may be asked by the system when defining an item.

Text options do not need option choices.

It is necessary to have at least one choice for each option question even if that choice is ‘not applicable’.

The system will overwrite existing options or create new ones at time of catalogue import.

Do not change the description of an existing option choice as this will change all purchasing histories within SwanFRS. For example, changing Red to Blue will ripple across all stock items using the option thus changing their structure. Discontinue the old and generate a new….

Alert – Column C

The VIEW button in the Alert column changes the display to maximize the space available. Clicking generates the following window

The page displays the type of view selected in yellow text in teh dark blue page header.

Select one of the following to change the screen display.

Choice

Action

Consolidated

This choice hides the active option, choice code and and description columns, consolidating them into a single column thus making space for more ‘active’ columns to be viewed on the right-hand side of the page.

This mode is particularly useful with the products have been identified and you’re populating the dimensions and control elements of the SKU

Choice Maintenance

This choice displays the option, choice and descriptions columns allowing them to be modified (subject to whether they are deemed known to SwanFRS where modification is restricted).

This mode consumes almost all the screen and therefore there is little space for additional control columns.

Field

Comments

Alert

This field will produce a warning if one or more of the cells in the row would cause a problem when loaded into SwanFRS.

  • The Option field is empty

  • The Description is empty

  • Loop on X – a linked question is looping back to the option question which would create an endless loop when selecting choices in SwanFRS.

  • Type in Option – appears if a selection is set against a type in option which does not have choices.

  • Value > 100% - appears when a cost or selling value is greater than 100 for an Option marked as having a percentage uplift structure.

If there is one or more alert the header row will show the Check Alerts notification, useful if the issue relates to a row not currently displayed on screen.

Option

Choose the question the answer relates to – select from the list of options in the pick list.

Cells in this column may be locked if imported from or sent to SwanFRS. To unlock click the red disk witht the pad-lock and enter the password. This will unlock the pink cells.

The password is provided during training or can be obtained by logging a support ticket.

To re-lock repeat this process.

Choice Code

Optionally specify the manufacturer’s code for this answer.

Cells in this column may be locked if imported from or sent to SwanFRS. To unlock click the red disk witht the pad-lock and enter the password. This will unlock the pink cells.

The password is provided during training or can be obtained by logging a support ticket.

To re-lock repeat this process.

Description

Specify the choice /answer text.

Cells in this column may be locked if imported from or sent to SwanFRS. To unlock click the red disk witht the pad-lock and enter the password. This will unlock the pink cells.

The password is provided during training or can be obtained by logging a support ticket.

To re-lock repeat this process.

Sequence

Specify the sequence in which answers within the Option ID are to be listed.

Cost 1

Specify the base cost of the option choice (retailers cost) of the item.

For new items this column must be set for the SKU to import – Do not set the Include Prices to N if creating new SKUs

Cost 2

Optionally specify a second cost

Ancillary Cost x

(1 to 5)

Optionally specify ancillary costs associated with the SKU.

These costs will be added to the ‘active cost’ thus forming the Cost + Uplifts value.

The row header for each ancillary cost is shown as set within the Catalogue Tab.

Landed Cost
(Cost x + uplift)

This is the ‘active’ cost column (as defined in blue cell header above the column header.

The value displayed is the chosen cost + the sum of the ancillary costs.

This field is used when ‘calculating’ selling prices but is NOT sent to SwanFRS unless the Catalogue Tab, .Export Landed instead of parameter is set.

Currency Cost

For currency suppliers / vendors where the supplier’s / vendor’s cost is fixed specify the cost price in the supplier’s / vendor’s currency.

When raising purchase orders, the system will calculate the base currency cost by dividing the currency cost by the current exchange rate.

For base currency suppliers / vendors leave this field blank.

This field is not sent to SwanFRS

Retail

Specify the RRSP / RSP / high price for the item.

This price is NEVER charged by SwanFRS and only used to show the client what the price may have been.

Sell 1

Specify the selling price 1 of the item.

It is suggested to set this as the standard or establishment price.

Sell 2

Specify the selling price 2 of the item.

It is suggested this price be the ‘in sale’ price.

Sell 3

Specify the selling price 3 of the item

It is suggested this is the special event price.

Web Price

Specify the web selling price of the item

Workzone X

(1 to 3)

These columns are provided for ‘what if’ scenarios, they allow you to experiment with costs and selling or hold calculated values which etc.

These fields are not sent to SwanFRS

Achieved Margin

This field shows the margin of the item if sold at the prices specified – the margin show relates to the ‘active selling price’ chosen in the blue control cell.

Tax is calculated using the Tax Rate values set in the Catalogue tab.

Selling Price to Achieve Margin

This column shows the ideal margin using the tax rate set in the Catalogue tab and the preferred margin percentage control cell.

This value is the raw (uncharmed) selling price required to meet the margin threshold specified.

Lead time

Specify the lead time adjustment (in days) choosing this answer has on the items overall lead time.

Disc Date

Specify the date this item becomes / became unavailable.

Option Choice Image

Specify a default image for the option choice.

This can be either an image name or a full URL to the image.

When linking to a Swan website the recommended image size is 100x100 pixels with images saved in jpg format. Quality 60% - web safe sRGB.

Where configured the spreadsheet will present a thumbnail image of the locally held URL. This can be viewed by selecting the icon in the row header, having clicked into the desired image cell first.

Linked Question X

(x in the range 1 to 5)

This optional field is used when there is a further clarification question required because of choosing the specific answer.

Up to 5 questions can be asked from a single answer in this tab. If further questions are required turn on the Extended Options Links (Choice Links) tab where it is possible to set any number of clarification questions from a specific answer.

Invalid or duplicated cells will be highlighted with a pink background.

Price Related Buttons

This section details buttons common to the Refresh, Choices, and Items tab.

This option may be restricted in which case selecting the button will present the following message.

Change Margin

Clicking this button begins the process of changing the ‘ideal margin’ (held on the catalog tab) and used to calculate the ‘selling price to achieve the stipulated margin’ column.

It also controls the background column of the ‘achieved margin’ column, showing red if the calculated value is below the target or green if above. (The brighter the green the higher the margin)

Once change the legend to the right of the button shows the new margin and the columns mentioned above will change accordingly.

Update Ancillary Costs

Selecting this button allows the setting of any of the ancillary cost columns using the following choice window.

Field

Comments

Source Column

Click to highlight the source column to be used when applying the calculation.

Destination Column

Click to highlight the destination column, the column in which the results of the calculation are to be stored.

Calculation

Choose the type of calculation to be performed

Value

Specify the value the source column is to be adjusted by.

This field requires a decimal value to be entered.

Clicking apply refreshes the destination column with the newly calculated value as defined within the window.

Convert

This button instructs the routine to convert values in the Currency Cost column to base currency using the ‘exchange rate’ set in the Catalogue tab.

Update Prices

This button allows a range of calculations to be performed on ‘price’ columns, clicking presents a two tabbed window offering basic and advanced adjustment calculations.

Basic Pricing

This tab offers a simple approach to price changes offering a series of arithmetic options.

Field

Comments

Source Column

Click to highlight the source column to be used when applying the calculation.

Destination Column

Click to highlight the destination column, the column in which the results of the calculation are to be stored.

Calculation

Choose the type of calculation to be performed

Value

Specify the value the source column is to be adjusted by.

This field requires a decimal value to be entered.

Advanced Pricing

This tab offers a series of formula options to calculate (primarily) selling prices.

Field

Comments

Source Column

Click to highlight the source column to be used when applying the calculation.

Destination Column

Click to highlight the destination column, the column in which the results of the calculation are to be stored.

Formula

Choose one of the formula options specified in the Catalogue tab to be used in partnership with the Calculation and / or charming choices relating to ‘formula’.

Where set the screen will use the name given to the formula.

Calculation

Choose one of the calculation methods.

  • Cost Uplift using Formula – this is primarily to calculating a cost price using one of the formula choices.

  • Margin – use the margin formula to calculate the result

  • Charm only – do not apply a formula – just charm the destination column.

Charming

Choose how the calculated value (in the destination column) should be ‘rounded’

Charming 0 & 1s

Where ticked the charmed price will be further adjusted if the value is X0Y or X1Y where X and Y are numbers. X0Y will have 10 taken from the value, X1Y will be reduced by a value of 20.

This applied to values over 99.

For example,

  • Any value in the range 100 to 109 will be reduced by 10 this changing 100 to 90, 101 becomes 91 etc.

  • Values in the range 1210 to 1219 would be reduced by 20, 1210 would become 1190, 1211 would become 1191 etc.

Choice Links Tab

When turned on, via the Catalogue Tab, this page allows the linking of next level questions to specific answers where the 5 default secondary level questions are not sufficient to accommodate requirements.

This tab is used if the data has been imported from a previous version of the spreadsheet and therefore should always be checked / referenced prior to sending data back to SwanFRS.

It is not possible to use the same choice link more than once at any single question level per product.

If there are linked questions set in the Choices tab, any items in the Choice Links tab both sets of linked questions are used.

The GenerateXML tab will include linked options, where set, even if this tab is hidden.

Note if the same linked question is specified the XML data will be rejected when processed in SwanFRS.

This tab is optional.

The Choice Links tab enables the creation of nested options. i.e., if you choose Answer A to question 1 the system will ask question 2.

The system will overwrite existing nested options or create new ones at time of catalogue import.

Field

Comments

Alert

This field will produce a warning if one or more of the cells in the row would cause a problem when loaded into SwanFRS.

Either the Option, Description, Choice Description or Link To field is empty

The Choice Description and Link To are the same

Option

Select the option or question which will require further definition subject to the operator choosing a choice or answer.

The pick list presented is that specified in the Options tab.

Choice Description

Specify the answer to the Option ID which will cause a nested question to be asked.

This field must be manually typed but should correspond to an answer linked to the chosen Option in the Option Choices tab.

Link To

Select the option or question which will require further definition subject to the operator choosing a choice or answer.

The pick list presented is that specified in the Options tab.

Sequence

Specify the sequence in which the nested options are to be asked.

It is possible to have more than one nested question for a single Option ID / Choice combination.

Items Tab

The Items tab is used to create the list of products. This is the base product which will be searched for by the operator / sold to the customer.

It is possible to add options to the product such as colour however it is recommended that the item be defined to a main price break / feature or spec to allow the salesperson to find the item quickly and advise of price rather than having to answer a series of questions before they can present a price to the potential customer.

It is possible to create a ‘header’ row in the table by leaving the line number blank (this shows as a red cell) – The preview tab will skip over header rows. This feature makes it easier to retain the format of the data thus matching that of the suppliers.

Flat File Export

This button creates a new spreadsheet containing the contents of the Items tab. All cells are flat values (not formulas. The following message will be displayed once the export has been created.

Please check your PC for a second occurrence of Excel. The workbook is usually named Book1.xlsx however this may be Bookn.xlsx if other workbooks are already open with the same name.

The exported workbook once opened will appear as follows.

Control Cells

This tab has three control cells identified by white text on dark blue backgrounds. These cells control the content of the XML and the auto calculated fields in this tab.

See Change Margin for directions for changing the margin percentage used.

Field

Comments

Cost

This field tells the program which cost should be used when showing the Landed Cost value.

The selected cost plus the sum of the ancillary columns is shown in the Landed Cost column.

Cost

This field tells the program which cost to use when calculating the Achieved Margin and Selling Price to Achieve Margin columns.

Sell

This field tells the program which of the selling columns is active.

The active selling is used to populate the Achieved Margin column.

Include – Column C

The CLEAR button in the Include column, clears all settings within the column and is of particular use when viewing the results of an ‘Import mapped data’ action.

Alert – Column D

The VIEW button in the Alert column changes the display to maximize the space available. Clicking generates the following window

The page displays the type of view selected in yellow text in teh dark blue page header.

Select one of the following to change the screen display.

Choice

Action

Consolidated

This choice hides the active range and description columns, consolidating them into a single column thus making space for more ‘active’ columns to be viewed on the right-hand side of the page.

This mode is particularly useful with the products have been identified and you’re populating the dimensions and control elements of the SKU

SKU Maintenance

This choice displays the SKU definitions and allows modification.

This mode consumes almost all the screen and therefore there is little space for additional control columns.

This mode is of particular use when changing descriptions / setting up the SKUs from scratch.

Admin

This mode shows the control columns used by the spreadsheet to check the consistency of the data and is of primary use by SwanFRS support.

These columns are not user amendable.

Line # - Column AI

The AUTO # button in the Line # column auto populates all rows in the table with an incremental order line number starting with the next number from the largest number already defined. This process is non-destructive only setting blank line numbers.

Line numbers will not be set for rows with missing product ranges.

Grid Seq – Column AM

The AUTO # button in the Grid Seq. column auto populates all rows in the table with an incremental order line replacing any line sequences already set.

This option, when used in partnership with the SKU display control in SwanFRS ensures the list of SKUs presented in SwanFRS’s Catalogue Search program matches the sequence in the spreadsheet.

Set From Range + D1 – Columns EL

The SET FROM RANGE + D1 button populates the Web Description (name used on the web) with the range description and the contents of description 1.

Note where the range is debranded, the debranded range name is used.

Convert – Columns EP

The CONVERT button automatically converts any numbers held in the Webshop Up Sell column which correspond to actual row numbers within the table to the row’s SKU.

For example, if a field contained 8,9,10 where the SKUs held on those rows are 10-1-2,10-1-3 and 10-4-56 the 8,9,10 would be converted to 10-1-2, 10-1-3, 10-4-56.

Row numbers must be separated with commas, any narrative which is not matched to a row number in the table will be left unchanged.

Previous Exported Data Check

Once data has been exported (saved as XML) via the GenerateXML tab, the spreadsheet generates a reference copy of the Items tab.

It is then possible to use the Control plus the ‘e’ key to ‘see’ the exported value of that cell and optionally reset it back to the exported state.

To use click in the appropriate cell in the items tab, the system will then show the previously exported value.

The window shows the cells current value, previously exported value and offers the option to revert to the previously exported value.

To reset select the YES option, to close and continue select NO .

This feature ONLY works of accessible cells within the Items Tab.

Table Columns

It is important to remember that if you empty a cell, particularly when it previously held a Y, or an N will cause the exclusion of the field from the import process. If you want to change a cell from a setting back to its default ensure you set the field, rather than making it blank. i.e., if column X has a default of Y and previously imported with an N, you must set the column to N and import it to change the setting in SwanFRS.

Field

Comments

Include

Include in export

  • N – No (exclude this row form the XML export (GenerateXML tab).

When using the SheetMapping process this column will indicate if the row has been changed by the import process. The warnings are –

  • OLD - This row was present in the Items tab prior to importing hte new data.

  • NEW - This row was created as part of the import process.

  • DISC - This original row was not mapped to a new data import tow and therefore has been Discontinued.

Remember discontinued lines need importing into SwanFRS to inform SwanFRS they are no longer purchase-able, once imported they can be removed from the spreadsheet however it is recommended they are retained to ensure the SKU isn’t reused for another product.

  • UPD - This row has been updated as a result of the import. i.e. the mapped index was already ‘known' and therefore the original data has been updated with the new imported data.

These indicators cannot be set by the user however they can be cleared via the CLEAR button in the header row.

It is possible to use the filter option to focus in on specific items.

If the range has been set to exclude, changing the contents of this column has no impact on the output status.

Rows with excluded ranges or set to N will not be included in the XML output.

Alert

This field will produce a warning if one or more of the cells in the row would cause a problem when loaded into SwanFRS.

  • SKU if the SKU hasn’t been set

  • DESCx if any of the descriptions exceeds 80 characters. (x indicates the description number).

  • Qx – if there is an invalid description set (x represents the Question number)

  • GROUP – if the product group is incorrectly set

  • CLASS – if the class isn’t set

  • RANGE – if the collection / range isn’t mapped correctly.

  • PROD-GRP – if the product group isn’t mapped correctly.

  • DAYS – if the lead time is incorrect.

  • DEFAULT – shown is the range matches the default range set in the Catalogue tab.

If there is one or more alert the header row will show the Check Alerts notification, useful if the issue relates to a row not currently displayed on screen.

SKU

This will be automatically built from the Catalogue ID, Range ID, and Line #.

Range / Collection Description

The drop-down pick list allows the operator to choose a range / collection the item is linked to – this field must be set for the item to be loaded into SwanFRS..

Cells in this column may be locked if imported from or sent to SwanFRS. To unlock click the red disk witht the pad-lock and enter the password. This will unlock the pink cells.

The password is provided during training or can be obtained by logging a support ticket.

To re-lock repeat this process.

Line #

Specify a line number for the stock item.

This field is used for the last element of the SKU.

This field must be set for the item to be loaded into SwanFRS – a blank indicates a header row which is ignored by the Preview tab – blank fields will show a red background..

Cells in this column may be locked if imported from or sent to SwanFRS. To unlock click the red disk witht the pad-lock and enter the password. This will unlock the pink cells.

The password is provided during training or can be obtained by logging a support ticket.

To re-lock repeat this process.

Retailer description 1

Specify a description defining the item.

Description field will show an amber background if a ‘restricted’ character is used – the load process will fail if the &, “ or | symbol is used.

When linking to a Swan website and using the Group_Product option this field should contain the item purpose/design placing ancillary data in the Description 2 and 3 columns. This field acts as a collation point for the web to group like product – E.g., If you have a 3-seater sofa which comes in 4 grades – description 1 should contain the ‘3-Seater Sofa’ with Description 2 and / or 3 holding the grade and other information about the product.

Retailer description 2

Additional description

Retailer description 3

Additional description

Grid Seq.

Optionally set a sequence number which will be used by SwanFRS to force the order in which matching items are displayed in the Search Catalogue results table.

Legacy System Code

This is an optional field. This field may be used to hold a legacy system code thus allowing searching by the pre-SwanFRS reference.

This code is unique and cannot be used on any other SKU (system wide). An error will be produced at time of import if this code has been used against a variant SKU.

sending legacy codes requires a separate export XML. After importing the products XML, generate the legacy XML and import.

The legacy XML should only be sent after a change of legacy code or SKU the code is linked to.

Supplier’s / Vendor’s Model Number

Specify the code used by the supplier / vendor to identify this item.

This code will be used when raising purchase orders for this item with the supplier / vendor.

Barcode

Where available specify the barcode used by the supplier / vendor to identify the item.

Cost 1

Specify the base cost (retailers cost) of the item.

For new items this column must be set for the SKU to import – Do not set the Include Prices to N if creating new SKUs

Cost 2 (Alt Cost)

Optionally specify a second cost

Ancillary Cost x

(1 to 5)

Optionally specify ancillary costs associated with the SKU.

These costs will be added to the ‘active cost’ thus forming the Cost + Uplifts value.

The row header for each ancillary cost is shown as set within the Catalogue Tab.

Landed Cost
(Cost x + uplift)

This is the ‘active’ cost column (as defined in yellow cell header above the Cost 1 column.

The value is the chosen cost * ancillary cost percentage set in the Catalogue Tab plus either the tariff uplift value or tariff uplift percentage on cost dependent upon settings in the Catalogue tab.

This field is used when ‘calculating’ selling prices but is NOT sent to SwanFRS unless directed by the setting on the Catalgoue tab.

Currency Cost

For currency suppliers / vendors where the supplier’s / vendor’s cost is fixed specify the cost price in the supplier’s / vendor’s currency.

When raising purchase orders, the system will calculate the base currency cost by dividing the currency cost by the current exchange rate.

For base currency suppliers / vendors leave this field blank.

Retail

Specify the RRSP / RSP / high price for the item.

This price is NEVER charged by SwanFRS and only used to show the client what the price may have been.

Sell 1

Specify the selling price 1 of the item.

It is suggested to set this as the standard or establishment price.

Sell 2

Specify the selling price 2 of the item.

It is suggested this price be the ‘in sale’ price.

Sell 3

Specify the selling price 3 of the item

It is suggested this is the special event price.

Web Price

Specify the web selling price of the item

Workzone X

(1 to 3)

These columns are provided to ‘what if’ scenarios and do not form part of the data sent to SwanFRS.

Achieved Margin

This field shows the margin of the item if sold at the prices specified – the margin show relates to the ‘active selling price’ chosen in the blue control cell.

Tax is calculated using the Tax Rate values set in the Catalogue tab.

Selling Price to Achieve Margin

This column shows the ideal margin using the tax rate set in the Catalogue tab and the preferred margin percentage control cell.

This value is the raw (uncharmed) selling price required to meet the margin threshold specified.

Lead time

Specify the number of days it takes from point of notifying the supplier / vendor to receipt into the warehouse.

Product group

Select the product group for this item from the pick list

Question X

(1 to 20)

If this item has a choice or question related to it select the question from the pick list – this list is populated from the Options Tab.

The spreadsheet has been constructed with a maximum of 20 questions per item – if you need more, please contact Swan support to have the range extended.

Ticket Size

Not currently used within SwanFRS however it generates an Attribute of Ticket_Size which can be used in reporting as a filter to ensure the correct size label is produced for the SKU.

This feature will require a specific label to be generated and is not part of the standard offering.

Spare

Not currently used.

Reorderable

Specify if the item is to form part of the reordering algorithm –

  • N – do not reorder (default if not set).

  • Y – Reorderable.

B2B PO

Specify if the item can be special ordered –

  • N – This item can be special ordered – back-to-back ordering allowed. (Default if not set).

  • Y – This item needs supervisory permission to special order. This restriction does not apply to internal ordering.

Minimum stock level

Optionally specify the minimum stock level for reordering.

Maximum stock level

Optionally specify the maximum stock level for reordering.

Reorder quantity

Optionally specify the number of units to reorder at any one time. (Pack size).

Home delivery

Can this item be shipped directly from the supplier / vendor to the customer?

  • N – Goods must be shipped to the retailer’s premises.

  • Y – Goods can be shipped directly to the customer home.

Number of pieces

How many pieces is the item shipped in.

Unpackaged Width

How wide is the item?

Unpackaged Height

How high is the item?

Unpackaged Depth

How deep / tall is the item.

Unpackaged Weight

How much does the item weight?

Packaged Cubic

What is the cubic space consumed by this item when packaged? (Outer box cubic).

# Of seats

Like cubics this field holds the number of seats used by some suppliers when building containers / may be used to calculate the number of ‘warranty’ units each item incurs.

Commodity Code

Used to hold the commodity code for an item when importing / exporting goods to / from Europe.

PO_Label

Indicate if the purchase order is to generate a stock tracking label for the supplier to attach to their goods prior to shipping to the retailer.

  • N – Stock labels are not required.

  • Y – Produce the label for the manufacturer and include with the PDF sent to them when ordering.

This field auto generates an PO_Label Attribute which is held against the SKU instructing the ‘Banner PO’ print to include a stock label in the output.

Commission group

Specify the commission group for this item.

Product groups used are held in the Set Up Commission Groups routine in SwanFRS

Spiff Margin Controlled

This feature is a custom column, not in general use and can be ignored.

Where set, and used it tells the commission system to pay a ‘spiff’ commission against a specific SKU if the margin of sale meets the target set within the system settings.

  • N – Do not use the system setting margin control

  • Y – Spiff margin % commission control is to be used for this SKU.

Spiff Payment Value

This feature is only used with the commission export to file routines (Export Salesperson Commission and Export Branch Statistics)

Specify the value of spiff payment for this SKU which is paid subject to the spiff system control settings when generating salesperson and branch specific commission exports via the accounts menu.

Uplift Percentage

This feature is only used with the commission export to file routines (Export Salesperson Commission and Export Branch Statistics)

Specify the uplift percentage to be applied to the items cost price when calculating a margin. For example 50 would double the cost price.

TV indicator

Specify if the item is a TV.

  • N – No (default if not set).

  • Y – This item is a TV.

Linked 1 / Custom 1

Specify a produce code this item is linked to.

This field may be used to hold the image name for a thumbnail used in catalogue search in partnership with the application parameter CATALOGUE_PICTURES is set to Feature1; however, this is advised to use Feature 2 or 3 – Please contact your Swan consultants if you need to use this field for images.

Linked 2 Custom 2

Specify a produce code this item is linked to.

This field may be used to hold the name of a thumbnail image file (held in the application parameter IMAGE_FOLDER_PATH) where the application parameter CATALOGUE_PICTURES is set to Feature2

Linked 3 / Custom 3

Specify a produce code this item is linked to.

This field may be used to hold the name of a thumbnail image file (held in the application parameter IMAGE_FOLDER_PATH) where the application parameter CATALOGUE_PICTURES is set to Feature3.

Mask separator

Specify character used as a separator when generating supplier / vendor model references that change with the selection of options.

SwanFRS-UNIX use only.

Hierarchy x

(1 to 6)

Specify the product hierarchy group for this item.

Product groups used are held in the Set Up Product Hierarchy routine in SwanFRS

Part

Is this item a part rather than a full retail item?

  • N – No (default if not set).

  • Y – This item is a part and therefore requires special permission to order.

Serial Number

Does this part have a manufacturer’s serial number?

  • N – No (default if not set).

  • Y – These items have serial numbers which must be captured before the goods can be ‘delivery confirmed’.

Allocation on Despatch

Can this SKU have stock allocated just before despatching to the customer.

  • N – No (default if not set) - allocate stock at time or order.

  • Y – Leave the item on ‘demand’ until just before delivery (for example underlay which is allocated at time of collection prior to fitting).

Disc Date

Specify the date this item became discontinued or will become discontinued. i.e., not able to order new items.

Pack Size

This relates to the number of units in the supplier’s / vendor’s standard purchase pack.

E.g., 4 indicates the supplier / vendor sells the items in packs of 4.

Pack Description

This field holds the purchase pack description (singular).

To be imported to SwanFRS both the Pack Size and Pack Description must be set.

Is Till Product

Specify if this product is to be sent to an EPOS till

  • N – Item not availabel within the till.

  • Y – This item needs to be sent to the till for EPOS operation.

Sales Department X

(1 to 3)

Optionally specify the item specific sales department for this item.

If not set sales departments will be taken from the product group and if not set at this level, a default of ‘not set’ will be used in SwanFRS report.

Image X

(1 to 10)

Optionally specify the file name or full URL path of any images linked to the SKU.

The spreadsheet has been constructed with a maximum of 10 questions per item – if you need more, please contact Swan support to have the range extended.

The optimum size for images is 540 across by 460 pixels depth.

If the image is to be located within a sub folder use the / to indicate the folder – E.g., Pictures/image.jpg

Embedded spaces in the image filename / path will cause the load to fail.

When using images on a website avoid using PNG and BMP images – JPG / JPEG are the preferred image formats for web.

When linking to a Swan website with Web Features

  • All SKUs within the roll up should have the same first image which will be used as the main feature image on the product page when it first loads.

  • The second image on each SKU will replace the main featured image, but only when the corresponding options are selected to match the SKU.

  • Any additional images will be shown as thumbnails underneath the main featured image, but only when the corresponding options are selected.

If not using Web Features

  • The first image becomes the main featured image on the product page.

  • Any additional images will be shown as thumbnails underneath the main featured image.

Where configured the spreadsheet will present a thumbnail image of the locally held URL. Click the icon in the column header, having previously clicked into the cell holding the image filename you wish to view.

Brand (web)

Specify if the item is to have a brand attribute created. Select from –

N – Do not create a brand attribute.

Y – Create a brand attribute

The brand attribute is used for groupings within the Swan linked website and is essential for the effective operation of a website. (This field is set to the Vendor name set within the Catalog Tab).

Any existing Attributes linked to the SKU will be removed and new ones added if one or more Attribute code is set.

Group_Product (web)

Specify if the item is to have a Group_Product attribute created. Select from

N – Do not create a group product attribute.

Y – Create a group product attribute

The group product attribute is used in partnership with Web Features to provide a way of grouping SKUs in to pick lists.

(This field is set to the Range/Collection suffixed by the Retailer Description 1 field).

If the length of these two fields exceeds 100 characters issues can be experienced when viewing the detail on the web. An error will be produced if this occurs – in this instance it is suggested that you reduce the number of characters in these fields perhaps moving characters from Description 1 into Description 2.

Any existing Attributes linked to the SKU will be removed and new ones added if one or more Attribute code is set.

Show Prices on Web

When linked to a web system is the item to show a selling price (on the web).

  • N – No do not show a web price

  • Y – show prices on the web (default if not set).

Web orders

Specify if the item can be sold via the web site –

  • N – This item is not visible on a linked website (default if not set).

  • Y – this item is to be sent to the website.

Webshop Search Engine Optimisation

Optionally specify a list of SEO words used for web systems to allow searching.

Webshop Description

Optionally specify an alternative name for this item when shown on the web.

Webshop Start Date

Optionally specify the first date this SKU will display on the web.

Webshop End Date

Optionally specify the last date this item is visible on the web.

Web Shipping Class

Setting this field, when used in partnership with a linked website advises the site how the product is ‘shipped’ to the customer.

Please ensure codes used are recognised by your web provider.

When used do not use the Shipping_Class attribute elsewhere in the spreadsheet.

Web Upsells

This field is designed to hold a list of SKUs which are auto suggested by a linked website as potential accompaniments to the item being viewed /purchased.

SKUs should be separated by commas.

As the SKU column cannot be copied.

Enter the row number of the SKU to be added, separated by a comma then press the CONVERT button, this action will convert the row number (of the table as seen in the left-hand column) to a SKU.

When used do not use the Product_Upsell attribute.

Attribute X code

(1 to 10)

Optionally choose an attribute group name.

Any existing Attributes linked to the SKU will be removed and new ones added if one or more Attribute code is set

Attribute X value

(1 to 10)

Optionally specify a value for the selected attribute.

Long Description

This field holds an extended description of the product and may contain HTML formatting controls.

f the application parameter CATALOGUE_PICTURES is set to ImageURL, SwanFRS will display image # 1 as the thumbnail within the Catalogue Search program.

Item Attributes

When turned on, via the Catalogue Tab, this page allows the allocation of attributes to a SKU.

It may be used if the list of attributes is in a single column in the source data (thus removing the need to swap the column orientation when pasting into the Items Tab or if there are more than ten attributes required for a single SKU.

This tab is optional.

Refresh Lookup Data – Column G

It is strongly recommended the CLICK HERE TO REFRESH THE DESCRIPTIONS IN THIS TABLE button is used whenever SKUs are added / removed or have their descriptions changed. Clicking rebuilds an internal lookup which is used to present the operator with the details of the selected SKU in column H of this tab.

Table Columns

Field

Comments

Alert

This field will produce a warning if one or more of the cells in the row would cause a problem when loaded into SwanFRS.

  • The SKU column is blank

  • The Attribute column is blank

SKU

Specify the SKU code

If the SKU code is identified in the Items tab the SKU description will populate.

Attribute

Specify the name of the attribute to be associated within the SKU

Value

Specify the value this attribute will have.

SKU Description

This field will show the range and description held against the specific SKU (assuming a match is found in the Items tab and no changes have been made since the last ‘refresh’ (see section above).

Attributes set in this tab are appended to attributes set in the Items tab when generating the XML load data.

Item Relationships

When turned on, via the Catalogue Tab, this page allows the linking of SKUs to form an ‘upsell’ scenario at point of sale within SwanFRS sales order creation suite.

Where used SwanFRS will recommend the customer purchases product Y is they purchase product X.

Relationships must be built in SwanFRS first and must exactly match the description keyed into the spreadsheet for the import to be successful.

This tab is optional.

Refresh Lookup Data – Column G

It is strongly recommended the CLICK HERE TO REFRESH THE DESCRIPTIONS IN THIS TABLE button is used whenever SKUs are added / removed or have their descriptions changed. Clicking rebuilds an internal lookup which is used to present the operator with the details of the selected SKU in column H of this tab.

Table Columns

Field

Comments

SKU

Specify the SKU code

If the SKU code is identified in the Items tab the SKU description will populate.

Relationship Description

Pick a relationship from the list provided.

This list is populated from the Sales Departments Tab.

Data set must correspond with relationships set in the Set Up Relationships program for the import into SwanFRS to be successful.

Upsell (SKU)

Specify the SKU code of the auto suggested sales item.

This SKU will be prompted to the salesperson if the SKU (column 1) is sold.

SwanFRS will offer this as an optional or mandatory upsell dependent upon the relationship type set.

This SKU need not exist in the Items tab, it only needs to be a known / valid SKU within SwanFRS for the import / relationship to be successful.

Description

This field will show the range and description held against the specific SKU (assuming a match is found in the Items tab and no changes have been made since the last ‘refresh’ (see section above).

This is the description of the selling item.

Upsell Description

This column will show the upsell product description, subject to is being present in the Items tab.

The ‘No Match Found’ message is for information and will not cause the load to fail.

Attributes set in this tab are appended to attributes set in the Items tab when generating the XML load data.

Item Options

When turned on, via the Catalogue Tab, this page allows the allocation of additional options to a SKU.

The Items tab offers a maximum of twenty options (or questions) per SKU however if this is not enough or it is easier to key them in / paste them as a column rather than a row this tab may be used.

This tab is optional.

Refresh Lookup Data – Column G

It is strongly recommended the CLICK HERE TO REFRESH THE DESCRIPTIONS IN THIS TABLE button is used whenever SKUs are added / removed or have their descriptions changed. Clicking rebuilds an internal lookup which is used to present the operator with the details of the selected SKU in column H of this tab.

Table Columns

Field

Comments

Alert

This field will produce a warning if one or more of the cells in the row would cause a problem when loaded into SwanFRS.

  • The SKU column is blank

  • The Option choice is blank

  • The Option choice isn’t matched to a Description in the Options tab.

SKU

Specify the SKU code

If the SKU code is identified in the Items tab the SKU description will populate.

Option

Choose an option (question) from the pick list.

SKU Description

This field will show the range and description held against the specific SKU (assuming a match is found in the Items tab and no changes have been made since the last ‘refresh’ (see section above).

Any options set in this tab are appended to the options (questions) set in the Items tab when importing.

Item Images

When turned on, via the Catalogue Tab, this page allows the allocation of additional images for a SKU.

The Items tab offers a maximum of ten images per SKU however if this is not enough or it is easier to key them in / paste them as a column rather than a row this tab may be used.

This tab is optional.

Refresh Lookup Data – Column G

It is strongly recommended the CLICK HERE TO REFRESH THE DESCRIPTIONS IN THIS TABLE button is used whenever SKUs are added / removed or have their descriptions changed. Clicking rebuilds an internal lookup which is used to present the operator with the details of the selected SKU in column H of this tab.

Table Columns

Field

Comments

Alert

This field will produce a warning if one or more of the cells in the row would cause a problem when loaded into SwanFRS.

  • The SKU column is blank

  • The Option choice is blank

  • The Option choice isn’t matched to a Description in the Options tab.

SKU

Specify the SKU code

If the SKU code is identified in the Items tab the SKU description will populate.

Image

Specify either the full URL to an image or the local image filename.

Where configured the spreadsheet will present a thumbnail image of the locally held URL. This image will auto-hide when a non-image field is selected.

SKU Description

This field will show the range and description held against the specific SKU (assuming a match is found in the Items tab and no changes have been made since the last ‘refresh’ (see section above).

Any options set in this tab are appended to the options (questions) set in the Items tab when importing.

GenerateXML Tab

The GenerateXML tab processes the data in the tabs generating a data load file for importing into SwanFRS.

Generating the XML

To generate an output file, click the Generate XML button. This will present a series of output formats to choose from.

This button will only operate if there are no alert notices on the Range, Options, Choices and Items tab. If one or more of these tabs has an alert the following message will be displayed and the process aborted.

If there are no alerts the operator will be allowed to choose the XML contents.

To select, double click the appropriate choice.

Choose from

Ranges, Products, Options and Pricing

It is essential to load SwanFRS with changes to the range, product, or options before loading any of the other choices.

Attempting to load data without first importing the reference data will result in the load file being rejected.

It is possible to send price changes only via this export dependant on the ‘Mode’ setting within the spreadsheet.

Alternative Retailer Codes

When the ‘’legacy System Code’ is used this XML should be used.

Legacy data is not included in the products and pricing XML output.

Blocked Option Choices

Use this option to inform SwanFRS there are blocked options in-use.

Default Option Choices

Use this option to inform SwanFRS there are default options in-use.

Product Sets

Use this option to inform SwanFRS there are product sets available.

Cancel request.

Select this option to abort the output process.

Ranges, Products, Options and Pricing

Selecting this option produces an additional confirmation prompt

The prompt window identifies the active cost and selling price to be used when importing to populate the promotion. (Or will indicate no costs are being included in the XML)

If creating new SKUs, you MUST specify a cost and selling price – the import process will reject the import if these fields are missing from the load file.

For data updates it is possible to exclude cost and or selling prices.

Including the selling price will also include the MSRP / MRP (Retail price)

These options allow the retailer to decide whether to send prices into SwanFRS.

The macro within the spreadsheet will process generating a series of rows. Wait for this process to finish before pressing any buttons.


Completing the XML Generation

Once completed a notification will be presented to screen.

If the process has discovered a data consistency issue a warning message like that shown below will be presented.

Once acknowledged the screen will redisplay showing the first error generated.

Please note there may be more errors in the xml

All lines containing an error are highlighted in yellow, the line will detail the problem.

It is not possible to export the XML if it contains an error.

More errors can be located by scrolling down the data or by using the Find & Select option as per the example below.

If the system reports a match this indicates a data error not the line it relates to and find the corresponding items in the data file and adjust the data.

Re process the data file creation and test again.

Successful generation of data will produce the following message.

Save to File

To save the generated catalogue, click the save to file Save To File button.

The button will produce a warning and block the output if there is an ‘error’ in the generated XML output. Generation of the XML is prohibited is there is an issue in the XML data set.

A series of prompts guides the operator thought this process.

The process will automatically sense the type of XML generated and suffix the file name with the XML data format. This will be visible in the header of the window.

Clicking allows the operator to select a folder/filename for the contents of the Preview tab to save to.

For Swan hosted companies it is suggested that the operator uses the Dropbox folder (<company name>\XML) to save the data file – this enables it to be loaded into SwanFRS without having to move the saved xml data file across the network.

Please ask your implementation consultant for details.

If you pick a data filename containing the XML format name, the format name is automatically removed.

When naming your file avoid using the full stop in the narrative. E.g., Supplier.Range.xml will cause a load issue.

Clicking produces an abort confirmation message.

Entering a filename / selecting a save folder path produces the following confirmation window

Note the type of file is automatically added in brackets to the specified filename.

Once saved a successful save message is displayed.

The file saved will have a .xml suffix.

Once saved this file can be used as an import file for the Import Catalogues program with SwanFRS

The type of XML file being saved will be appended to the chosen name immediately before the .xml suffix – this is to make selection easier when importing into SwanFRS. If you choose to overwrite a file with a file type in brackets already in the filename the process will automatically strip this out and replace it with the file type corresponding to the data set saved.

Save to Clipboard

To save the generated catalogue to your clipboard, click the Copy to Clipboard button.

Sets

When turned on, via the Catalogue Tab, this page allows the definition of groups or set codes.

The Sets tab is optional and enables the creation of basic sets. i.e., A set allows you to enter a single code which is expanded into a series of ‘component’ codes. E.g., you could define a table, 2 carver and 4 standard chairs as a set. Selling the set code auto expands the order into 1 set code, 1 table code, 2 x carver chair codes and 4 x standard chairs.

It is not possible to create fixed options sets using this method. To create these sets, use the Set Up Product Sets program

To use this feature, you must know the ‘parent’ or set code and its ‘children’ or component pieces.

Refresh Lookup Data – Column G

It is strongly recommended the CLICK HERE TO REFRESH THE DESCRIPTIONS IN THIS TABLE button is used whenever SKUs are added / removed or have their descriptions changed. Clicking rebuilds an internal lookup which is used to present the operator with the details of the selected SKU in column H of this tab.

Update Parent Selling Prices – Column V - Y

The UPDATE PARENT SKUS ON ITEM TAB WITH THE SUM OF HTE SELLING PRICES & COSTS button allows the operator to rebuild the selling price of the parent SKU from the sum of its components and should only be used once the data has been ‘refreshed.’

This process replaces the prices for the parent item and there are no rewind options.

Process windows

Table Columns

Field

Comments

Parent (SKU)

Specify the parent or set SKU

This field in not checked against the Items tab but will produce an error on loading if not know to SwanFRS.

The parent SKU is not used in SwanFRS other than a place holder for selling groups of items. The stock levels are held at the component level.

This code should be repeated for each component within the set.

Fixed Price

If SwanFRS is to charge the price of the Parent SKU, choose Y.

If SwanFRS is to charge the sum of the component pieces, choose N.

Component (SKU)

Specify the components SKU

Quantity

Specify the number of times this component is used within this set.

Parent Description

This is an information cell presenting the range and description of the parent SKU.

Component Description

This is an information cell presenting the range and description of the component SKU

Costs & Sellings

This information is presented to allow the operator to view the costs of the component items.

Descriptions

If the description displays as ‘No Match Found’ and there is a valid description set in the Items tab, click the CLICK HERE TO REFRESH THE DESCRIPTIONSbutton.

This runs a script (in the background) which rebuilds the index table for SKUs and Descriptions.

Defaults

When turned on, via the Catalogue Tab, this page allows the definition of default option choices for specific SKUs.

The Defaults tab is optional and enables option questions within specific SKUs to have default options pre-selected. E.g., The most common colour sold is White therefore making this the default options speeds ups the sales process in that the salesperson doesn’t have to find white in the list. It is possible to choose other colours from the pick list.

The table will not check to ensure the option selected exists in the SKU.

Refresh Lookup Data – Column G

It is strongly recommended the CLICK HERE TO REFRESH THE DESCRIPTIONS IN THIS TABLE button is used whenever SKUs are added / removed or have their descriptions changed. Clicking rebuilds an internal lookup which is used to present the operator with the details of the selected SKU in column H of this tab.

Table Columns

Field

Comments

Alert

This field will produce a warning if one or more of the cells in the row would cause a problem when loaded into SwanFRS.

The SKU column is blank

The Option & Choice isn’t mapped correctly.

SKU

Specify the SKU

This field in not checked against the Items tab but will produce an error on loading if not know to SwanFRS.

Once the SKU is selected the table will display the description linked to the SKU.

This code should be repeated for each default option to be set for the SKU.

Option & Choice

Choose an option question and answer (pick lists only) to mark this as the default offering for the specified SKU.

SKU Description

This is an information cell presenting the range and description of the SKU.

Descriptions

If the description displays as ‘No Match Found’ and there is a valid description set in the Items tab, click the CLICK HERE TO REFRESH THE DESCRIPTIONSbutton.

This runs a script (in the background) which rebuilds the index table for SKUs and Descriptions.

Blocked

When turned on, via the Catalogue Tab, this page allows the setting of blocked option choices for specific SKUs.

The Blocked tab is optional and enables option questions within specific SKUs to have options blocked. E.g., the entire range of items share the same fabric selections except the chair which is not available in the sunflower design in that its pattern doesn’t work on the item. Rather than having to have 2 separate colour options blocking the sunflower from the chair is required.

The table will not check to ensure the option selected exists in the SKU.

Refresh Lookup Data – Column J

It is strongly recommended the CLICK HERE TO REFRESH THE DESCRIPTIONS IN THIS TABLE button is used whenever SKUs are added / removed or have their descriptions changed. Clicking rebuilds an internal lookup which is used to present the operator with the details of the selected SKU in column H of this tab.

Table Columns

Field

Comments

Alert

This field will produce a warning if one or more of the cells in the row would cause a problem when loaded into SwanFRS.

The SKU column is blank

The Option & Choice isn’t mapped correctly.

SKU

Specify the SKU

This field in not checked against the Items tab but will produce an error on loading if not know to SwanFRS.

Once the SKU is selected the table will display the description linked to the SKU.

This code should be repeated for each default option to be set for the SKU.

Option & Choice

Choose an option question and answer (pick lists only) to mark this as the default offering for the specified SKU.

Blocked

  • Specify Y to block the selected Option – choice for this SKU

  • Specify N to clear a previously block option – choice for the SKU.

Web Blocked

  • Specify Y to block the selected Option on a linked website – choice for this SKU

  • Specify N to clear a previously block option on a linked website – choice for the SKU.

SKU Description

This is an information cell presenting the range and description of the SKU.

Descriptions

If the description displays as ‘No Match Found’ and there is a valid description set in the Items tab, click the CLICK HERE TO REFRESH THE DESCRIPTIONS IN THIS TABLE button

This runs a script (in the background) which rebuilds the index table for SKUs and Descriptions.

Advanced Features

PG X Ref

When turned on, via the Catalogue Tab, this page allows the definition of alternative codes for products groups and is of particular use if importing data from a third-party spreadsheet.

PG X Ref is a cross reference facility allowing you to link the third party’s product group with one used in SwanFRS.

This tab allows the creation of a list of product groups and the replacement code which is applied to the Items tab when via a ‘process’ button.

This allows the mass changing of product groups from X to Y as defined within the Items Tab.

Note: This process does not check the class of the product group therefore it is possible within the spreadsheet to change PG 1 which is an accessory classed group to PG 2 which is a measured classed group. The spreadsheet will allow the change however when importing into SwanFRS, SwanFRS will reject the load advising that changing product group classes is not possible.

This tab is of particular use when using the SheetMapping process, allowing the operator to move the vendors product groups to the retailers’ groups. This process is run as part of the import data process however on first processing the cross reference will require manual population.

Field

Comments

From PG

Specify the product group to be changed.

For example, in the source data the manufacturer has used code ‘Bedroom Cabinets’ – this code does not exist in SwanFRS and therefore needs to be replaced.

To PG

Specify the product to be change to (as set within the Product Group tab)

Using the example above ‘Bedroom Cabinets’ is ‘B011- Lockers’ in SwanFRS therefore the relationship between the two codes is formed.

Once formed any future imports of ‘Bedroom Cabinets’ will be automatically converted into ‘B011 – Lockers’

ID Check

Shows the SwanFRS product group of the To PG Code and acts as a warning should the code previously used in the Prod Groups tab no longer exist.

This icon appears only in this tab and optionally allows the extraction of ‘mismatched’ product groups set within the Items tab to be pulled into the PG X Ref tab and then for duplicates to be removed thus making the process of mapping 3rd party (or old product groups) to those currently used in SwanFRS.

Clicking APPLY PG REMAPPING works down the list of conversions applying them to the Items tab.

Remapping product groups within the Items tab is not reversible and therefore it is important to ensure the mapping is correct before ‘applying’


Web Features

When turned on, via the Catalogue Tab, this page allows the grouping of SKUs which can be used on a website as a collation or roll-up thus improving the user experience on the web.

A web feature is effectively group of individual SKUs with a common element. As the operator chooses from these groups the web distills the offering down to an individual SKU which is then offered on screen.

The process of importing web features differs from the XML imports and requires a separate load process.

This tab is optional.

Refresh Lookup Data – Column F

It is strongly recommended the CLICK HERE TO REFRESH THE DESCRIPTIONS IN THIS TABLE button is used whenever SKUs are added / removed or have their descriptions changed. Clicking rebuilds an internal lookup which is used to present the operator with the details of the selected SKU in column H of this tab.

Table Columns

Field

Comments

Alert

This field will produce a warning if one or more of the cells in the row would cause a problem when loaded into SwanFRS.

Duplicate – Duplicate tiering for different SKUs is not allowed

Range / Collection Description

This field displays, where found, the range and description of the SKU.

It is possible to toggle the ‘word wrapping’ to see more of the description if required.

SKU

Specify the SKU code

If the SKU code is identified in the Items tab the SKU description will populate.

Tier X Name

(1 to 10)

Optionally set the group name for the collection.

When using SwanFRS web any characters from the | onwards (including the |) are ignored – this allows for the same group name to exist over several ranges / suppliers etc.

Tier X Desc

(1 to 10)

Specify a choice which will form a pick list when the Tier name is presented within the web.

Start Date

Specify the first date this collation group is to be visible on the web.

End Date

Specify the last date this collation grouping is to show on the web.

Whilst this data is imported into SwanFRS it is not used within the application.

The file will be rejected at import if Tier X is set where Tier X-1 is blank.

Exporting the Data

When ready for importing into SwanFRS, click the

Clicking this icon presents the following window

Clicking OK offers the ability to specify a filename and folder.

The filename specified will be automatically prefixed with ‘WebFeatures- ‘which is a requirement of the import process.

There is no requirement to specify the .csv suffix as this will be automatically added.

When operating on the hosted platform is it recommended that the file be save in the \tmp\In folder as this is the location the file must be placed to import into SwanFRS.

Having specified the filename and folder this will be echoed back to screen to confirm the details are correct.

The prefix and suffix are applied to the filename at this time.

Clicking the YES button confirms the process and saves the file in the specified folder.

Once completed, the file will be present in the designated folder.

An example of the output file generated is shown below

Importing into SwanFRS

Menu Location: System Admin > Set Up Product Web Features

Within the program the IMPORT FEATURES button may be used to import the csv file into SwanFRS.

The import process will automatically generate any Tier descriptions not already known to SwanFRS.

More details of web processing can be found in the Web Integration Guide

Price Refreshing

This tab is turned on via the ‘Assisted Cost Refresh in Use’ option within Tab Usage.

Setting this option to ticked displays the Refresh tab, unticked hides the tab.

This tab is designed to allow the entry of a series of ‘model numbers and revised prices in the left-hand side blue headed columns.

Once ready clicking the CLICK HERE TO REFRESH THE DESCRIPTIONS IN THIS TABLE button processes the data showing the movement of products and prices cross referencing the model numbers specified with those in the Items tab.

Table 1 Columns

Field

Comments

Mapping Control

This field allows the entry of the supplier’s reference data and is used to match codes already present in the Items tab.

Click the CHANGE MAPPING button to change the mapping. Select from …

Changing the mapping resorts the right-hand side table presenting data in the chosen mapping column sequence and relinks codes where possible.

To preserve cost mapped ensure these are Locked before re-sorting

Suppliers New Cost

Specify the new selling price for this item.

Found it?

This column will show the word ‘Matched’ with a green background if the Mapped control data element has been matched to a SKU present on the Items tab.

a red ‘No Match’ informs the operator that a match hasn’t been found and therefore the item is potentially a new offering from the supplier.

If the data isn’t showing as expected, click the CLICK HERE TO CHECK FOR COST CHANGES button which will rebuild the backstage cross reference data.

Table 2 Columns

This table is replicating the pricing functionality within the Items tab.

Field

Comments

New Price?

This column will show a green ‘Matched’ if the Mapped control data element has been matched to a SKU present on the Items tab.

The red ‘No Match’ informs the operator that a match hasn’t been found and therefore the item is potentially a discontinued item.

Mapped Field

The reference held on the Items tab.

The contents of this field are controlled via the CHANGE MAPPING button in the Mapping Control column.

The tab header also indicates which field is the ‘mapping field’ and will change as the mapping is altered.

Range / Collection Description

This field shows the concatenated description (1,2 & 3) from the Items tab.

SKU

The Item SKU number.

New Cost

This is the cost specified for matched items in table 1.

Matched costs are highlighted in yellow to highlight mapped rows.

If the cost changes are to be adopted and used for price manipulation the REPLACE COSTS button should be clicked.

This produces a cost replacement window, whereby it is possible to specify which cost column is to be REPLACED by the values in the New Cost column.

Note: There is no ‘undo’ feature.

Click the LOCK COSTS button to lock the mapped costs. This allows you to enter different mapping criteria in the left-hand column whilst preserving new cost process for onwards processing in the right-hand table.

Select from …

Once locked rows appear with an orange background, the mapping in the left-hand column can now change without clearing costs.

Prices are now locked even if a new map is specified the locked price will not change unless unlocked first.

Change

This is the movement in cost from that held in the Items tab.

  • Green backgrounds indicate a price reduction

  • Red indicates a price increase

  • Yellow will show for no price changes.

Cell P2 – controls whether Cost 1, Cost 2, or Cost + Uplift column is to be used as the comparison.

Cost 1

Specify the base cost (retailers cost) of the item.

For new items this column must be set for the SKU to import – Do not set the Include Prices to N if creating new SKUs

Cost 2

Optionally specify a second cost

Ancillary Cost x

(1 to 5)

Optionally specify ancillary costs associated with the SKU.

These costs will be added to the ‘active cost’ thus forming the Cost + Uplifts value.

The row header for each ancillary cost is shown as set within the Catalogue Tab.

Cost + uplift

This is the ‘active’ cost column (as defined in yellow cell header above the Cost 1 column.

The value is the chosen cost * ancillary cost percentage set in the Catalogue Tab plus either the tariff uplift value or tariff uplift percentage on cost dependent upon settings in the Catalogue tab.

This field is used when ‘calculating’ selling prices but is NOT sent to SwanFRS.

Currency Cost

For currency suppliers / vendors where the supplier’s / vendor’s cost is fixed specify the cost price in the supplier’s / vendor’s currency.

When raising purchase orders, the system will calculate the base currency cost by dividing the currency cost by the current exchange rate.

For base currency suppliers / vendors leave this field blank.

Retail

Specify the RRSP / RSP / high price for the item.

This price is NEVER charged by SwanFRS and only used to show the client what the price may have been.

Sell 1

Specify the selling price 1 of the item.

It is suggested to set this as the standard or establishment price.

Sell 2

Specify the selling price 2 of the item.

It is suggested this price be the ‘in sale’ price.

Sell 3

Specify the selling price 3 of the item

It is suggested this is the special event price.

Web Price

Specify the web selling price of the item

Workzone X

(1 to 3)

These columns are provided to ‘what if’ scenarios and do not form part of the data sent to SwanFRS.

Achieved Margin

This field shows the margin of the item if sold at the prices specified – the margin show relates to the ‘active selling price’ chosen in the blue control cell.

Tax is calculated using the Tax Rate values set in the Catalogue tab.

Selling Price to Achieve Margin

This column shows the ideal margin using the tax rate set in the Catalogue tab and the preferred margin percentage control cell.

This value is the raw (uncharmed) selling price required to meet the margin threshold specified.

Any values changed in table 2 are NOT replicated onto the Items tab until the UPDATE ITEMS TAB WITH NEW PRICES button is selected.

Sending Data to the Items Tab

Once any price changes are finalized, and if these prices are to be ‘saved’ / to be imported into SwanFRS, they need copying to the Items tab.

Clicking the the UPDATE ITEMS TAB WITH NEW PRICES button offers the ability to send items with ‘mapped’ costs only or to refresh the entire table.

If refreshing the entire table, the process will ask if ‘non-matched’ items are to be discontinued.

If non-mapped items are to be discontinued, specify the date the item can no longer be ‘ordered.’

If the item has already been discontinued the discontinue date will not be changed.

Leaving this date empty will continue to update the prices linked to the un-mapped item but will not mark it as discontinued.

Prior to copying prices to the Items tab the prompt below will detail the actions.

  • Clicking NO aborts, the process.

  • Clicking YES copies prices from Table 2 to the items tab.

Importing Mapped Data

This feature turned on via the ‘Assisted Cost Refresh in Use’ option within Data Mapping.

Setting this option to ticked displays the SheetMapping and ImportData tabs, unticked hides these tabs.

When using the import feature, it is necessary to set a default product group (from a pick list of data obtained from the Prod Group tab) and a default lead time in days.

If either of these control fields are not set the import process will cease.

These fields will only be used if the mapped data does not contain lead time or product group information.

Import Data

This tab has no controls or formatting and is designed to allow the pasting of data directly from the supplier’s catalogue in any format.

The only stipulation for this tab is to ensure there are no ‘merged’ columns in the source data as this will cause the import process to fail.

It is important to ensure your data is pasted into the top left cell (A1) otherwise the import will fail.

It is recommended that this tab be populated to allow the data mapping to occur.

Data Mapping

This tab is designed to allow the operator to map columns in the ImportData tab to columns in the spreadsheet.

  • Green indicates the column has been found in the ‘Import Data’ or that a mapping has been created for that data column.

  • Yellow indicates the mapped column has not been found in the ‘Import Data.’

This tab is designed to allow data held in the ImportData tab to be ‘mapped’ to columns in Prod Group, Range, and Items tabs.

There is an option to clear all data from previous runs therefore no need to clear all tabs before processing.

There are four mapping, two are preloaded with specific formats mapping to standard output formats.

  • Free format

  • ‘Wondersign’ pre-set (but amendable)

  • ‘AIS’ Format-B pre-set (but amendable)

  • Unix pre-set (but amendable)

Table Columns

Field

Comments

Import Data Column

This column contains a list of column IDs (as used by Excel) to identify a column.

Import Data Header

This field is auto populated by the ‘header’ row (row 1) of the ImportData Sheet.

Misc Mapping

This column allows the mapping of a specific column in the ImportData sheet to a field used in the Items tab.

Where set the cell turns green, yellow if not used (no mapping – ignore the column).

Wondersign Columns

This is a pre-set list of columns issued by ‘Wondersign’ in their data extract.

It is possible to add mapping columns at the bottom of the pre-set list.

Wondersign Map

Although prebuilt it is possible to ‘tweak’ the settings as required.

This mapping will be used during the import where the import button is used.

AIS (Format-B) Column

This is a pre-set list of columns issued by ‘AIS (Format B) in their data extract.

It is possible to add mapping columns at the bottom of the pre-set list.

AIS Map

Although prebuilt it is possible to ‘tweak’ the settings as required.

This mapping will be used during the import where the import button is used.

UNIX Column

This is a pre-set list of columns issued by ‘FRS-UNIX’) in their data extract.

It is possible to add mapping columns at the bottom of the pre-set list.

UNIX Map

Although prebuilt it is possible to ‘tweak’ the settings as required.

This mapping will be used during the import where the import button is used.

Note colours and options are not imported.

Once the mapping has been completed and the source data has been copied into the ImportData tab clicking the PROCESS MAPPED DATA button begins the process of populating the spreadsheet with the data held in the load file.

Processing the Mapped Data

Clicking the PROCESS MAPPED DATA button produces the following window.

There are two tabs select the appropriate tab for the data set being imported.

Miscellaneous Mapping
Pre-Set Mapping

This tab should be used for importing Wondersign, AIS and Unix data sets.

Specify the data controls for importing data (some mapping types do not require all fields to be set – the process will not be allowed to continue until all appropriate fields have been selected.

preset processing is designed to append to existing data therefore if you are wishing to restart with i.e., starting a new import ensure you clear the tabs via the red clear button on the Catalogue Tab before importing.

Note: If you wish to begin with empty data tables select the Red ‘Empty Data tabs (Restart) button on the Catalogue tab as this process adds to existing data in the ranges and items tabs.

All but the last Action Required option merge new data rows with original advising of the change.

The Update Existing - Ignore New option can be used as a quick form of price update - specify the index mapping in 1 column, suppliers model number for example, then list the change columns - cost price, MRP for example, importing effectively replaces the cost with the new cost. Note: This process overwrites existing rows and IGNORES new data.

Having instructed the system how to act on the data a final screen confirms the choices made prior to processing the data.

Once process the sequence will …

  • Create new ranges will be added to the Range Tab whilst retaining those already set.

  • Create new Items will be added to the Items tab and marked accordingly

  • Update current items.

  • Where set mark original items are not present in the new load file as discontinued.

This process may take some time to complete.

Once completed, the Include column on the Items tab may display one of the following identifiers (Include Column).

OLD

This row was present in the Items tab before the import mapped data process was processed.

NEW

This is a newly created row, generated as part of the import of mapped data.

DISC

This is an original row not present in the import mapped data and has been discontinued.

Remember discontinued lines need importing once into SwanFRS to ‘discontinue’ them in SwanFRS, after this they can be deleted from the spreadsheet.

UPD

This row has been updated because of the import. i.e., the mapped index was in the items tab before the import and present in the import data. The original row has been ‘replaced’ with the new data.

N UPD indicates a row marked not to be sent to SwanFRS which has been updated via the Update Existing - Ignore new feature.

These indicators cannot be set by the user however they can be cleared via the CLEAR button in the header row.

When process Wondersign® data, any data marked as a ‘fixed’ or ‘bundle’ will be converted into a set with the Sets tab being populated with the relevant data.

The process will automatically generate missing ‘components’ used in the set, this data will not have cost or selling prices and holds the minimum data required to process a set order in SwanFRS.

If this situation arises, please contact your Wondersign® agent and advise them of the data inconsistency in the vendor data.

It is possible to continue processing and to order goods with the ‘component’ codes within SwanFRS.

Loading Data (Generate XML tab)

This is an alternative to the the save to file Save To File button.

Once the data in the Preview tab has been refreshed (via the Generate XML button) and has been checked f or errors, the next step is to create the import file.

Ensue the Correct load path has been defined within Set Up Application Parameters. The CATALOGUE_PATH should be set the folder on your server where XML load files are the processed. Typical paths are listed below.

  • Hosted systems: \\fs-01\importexport\<Retailername>\<Retailername><Live>\XML\

Please ask your implementation consultant for your <Retailername>

  • On Prem system: c:\inetpub\wwwroot\FRS<LIVE>\XML

If using your test system replace <Live> with TEST

The < > should be omitted and are used to indicate names (words) which will vary on your system.

Open the folder using File Explorer.

Right click in an area of white space, selected New, then Text Document.

This will create a file called New Text Document.txt – select (highlight) this file, right click, and select Rename.

Change the name to <Name>.xml where <Name> is your filename – it is suggested this is the supplier / vendor’s name.

If you do not see the .txt suffix tick the File Name Extension box in the View Tab. If this does not resolve the problem, please consult your system administrator to have your PC’s settings changed

If you do not see the .txt suffix tick the File Name Extension box in the View Tab. If this does not resolve the problem, please consult your system administrator to have your PC’s settings changed

Changing the filename and suffix will produce a warning like that shown below – click YES when prompted.

Having changed the filename, right click again, and select Open With, then click Notepad.

Having opened the file with Notepad return to the Excel® document.

Within the Excel – click on any cell in the Preview Tab, then press and hold the Control Key followed by the A key (all cells should be highlighted). This is the control for selecting All cells.

Then press and hold the Control key and then the C buttons – this is the instruction to copy all cells the clipboard - nothing changes on screen.

Now return to your Notepad window, clicking anywhere in the white space within the window.

Now hold the Control key and press the V button.

This will paste the contents of the Preview tab into the notepad. The contents will be like that shown below.

Finally, Close (and save) the window.

The product load file in now ready for processing within SwanFRS.

Apple / IOS directions

How get the XML data from the Preview sheet in the product creator spreadsheet in to SwanFRS on a Mac -

1. Open the Mac app called “TextEdit” (it is a standard OS app, like notepad on Windows) - this app can be found in the “Applications” folder or hit CMD + SPACE on the keyboard and type “TextEdit"

2. Create a "New Document", then from the top menu go “Format > Make Plain Text”

3. Next copy the XML data from the preview sheet and paste it into the text document.

4. From the Text Edit menu again, click "File > Save” then name the file and change the file extension from .TXT to .XML and save it.

5. This file can then be copied in to the shared SwanFRS Syncthing folder ready for import.

Importing XML Data into SwanFRS

To Import the file within SwanFRS, use the Import Catalogue routine.

For more details of this routine please refer to the XML Product Import Guide

Check the bracketed element of the load file name to ensure you’ve chosen the correct ‘upload type’ from the pick list. Mismatched data sets will cause the import to reject the data.

  • When importing products choose the Collections, Products, Options and Prices option.

  • When importing Alternative SKUs select the Alternative Retailer Codes option

  • When importing sets choose the Product Sets.

  • When import Blocked items selected Blocked Option Choices.

  • When importing Default options select Default Option Choices.

I

Files from the Confluence page

Attachments (135)

Was this article helpful?

That’s Great!

Thank you for your feedback

Sorry! We couldn't be helpful

Thank you for your feedback

Let us know how can we improve this article!

Select at least one of the reasons
CAPTCHA verification is required.

Feedback sent

We appreciate your effort and will try to fix the article