This is the documentation for Clover Club 12.0.
Documentation for the upcoming version Rampur 13.0 can be found here.

Excel Client

Excel Client is in maintenance mode with no new functionalities planned. Not every new object added to the application (such as Sellers or Seller Extensions) will be available in Excel Client. When issues in Excel Client are found, they will be scheduled for fixing through our standard prioritization process.

When it comes to uploading your XLS files to a partition, you are encouraged to use Data Upload in PlatformManager or the Import/Export functionality available for individual Pricefx tables.

The Excel Client allows you to:

  • Manipulate your Pricefx data (customers, product extensions, users etc.) using MS Excel.

  • Modify the metadata of the table columns (take special care when doing this).

The Excel Client can be accessed from the Master Data menu.

Limitations:

  • The Excel Client is only suitable for smaller data sets, up to about 25,000 lines.

  • Attributes in JSON format cannot be uploaded or downloaded using Excel Client.

  • Company Parameter tables with JSON Table Type and JSON2 Value Type cannot be updated using Excel Client.

  • When authenticating via Excel Client, the JWT token is stored in the downloaded XLSM file. This poses a security risk. To prevent injecting JWT token to the Excel file, set the application property doNotInjectJWTInEC to true. This however means that the user will have to log in when using Excel Client.

  • Excel Client supports only int32 (integer) numbers. This is due to a MS Excel limitation of 15 digits precision. Numbers larger than 15 digits will be trimmed to the floating-point number type (double type). Note that Unity UI supports int64 (long) which can have up to 19 digits. 

Important note:

  • Excel Client uses VBA (Visual Basic for Applications) macros by Microsoft. As this technology has been one of the most significant entry points for malicious code, newer Microsoft Office applications block execution of any VBA macros by default. Furthermore, Microsoft is planning to phase out support for executing VBA macros in downloaded Office documents completely.
    This situation has also contributed to the decision not to introduce any new functionality in Pricefx Excel Client and focus on our own import/export functionality (as stated at the top of this page). Further reading: Microsoft 365 roadmap.

In this section:

Open Excel Client

  1. Go to Master Data > Excel Client. The PriceFxClient.xlsm file is downloaded.

  2. Open it in MS Excel and click Enable Editing and Enable Content to be able to use the Pricefx functionality.

  3. The Excel Client will automatically try to log you in based on your web based session information. Should this fail, you will be asked to log in using your credentials.

  4. 'Pricefx' appears in the Excel menu.

Update Data

Using the Manage menu you change, delete and add data for various Pricefx objects.

  1. As a prerequisite, make sure the table you want to manage is not empty in Pricefx. If it is, it will be listed in the Excel Client but the download action will result in an empty sheet without the expected columns being visible. You need to go to Pricefx and ensure that the table has at least one row of data.

  2. Open the Excel Client. 

  3. From the Manage menu select a table you wish to work with. The following objects can be handled by the Excel Client: Users, User Groups, Products, Product Competition, Product Reference, Product BoM, Product Text, Customer, Price Record, Data Source (read only), Data Change Requests, Product Extension, Customer Extension, Company Parameter, Manual Price List, Price List, Simulation, Live Price Grid.
    When it comes to fetching the table values for the drop-down menu in Excel Client, there is a MS Excel limit of 32,767 items which can be displayed there.

    • For some of the tables you can select whether you want to download all rows or limit it by one or more filters. Note that not all operators available in Pricefx are available in the Excel Client.

    • If the downloaded data contains time fields, they are converted (just like in the application UI) to the browser's timezone. This is different from the behavior of the 'download to XLS' functionality available throughout the application (this download converts time to the UTC timezone). 

  4. Modify the data in Excel as needed. 

    • To update data – Update the values in individual cells. The row will be marked with a yellow flag "C" (changed). 
       Do not just paste over the existing lines, as the update on the server may not succeed. It is best to keep the same order of lines and update only attributes, not keys.

    • To delete data – In the 'delete' column enter Yes on the given line. This value can be copied into multiple lines. The row will be marked with a blue flag "D" (deleted). Some tables do not allow for deletion (Price List or Live Price Grid tables); where possible this has to be done through the UI.
       If there is a timestamp type of column and you have applied a filter to it (directly in the Excel sheet), this filter is not kept after the delete operation is completed (i.e., after you click 'Update Server').  

    • To add data – Create new lines in Excel. The row will be marked with a yellow flag "N" (new). Some tables do not allow for additions (Price List or Live Price Grid tables); where possible this has to be done through the UI.
       See the notes below for important information on data handling and validation.

  5. Click the Update Server option. When the data is synchronized with the Pricefx server, the change flags on each line disappear.

  6. If you want to continue editing another object, click the Clear Data option and in the Manage menu select the table to work with.
     Clear Data has no effect on data on the Pricefx server; it just clears the Excel sheet. It does not reload the meta info (unlike the Force full login option).

For Product Extensions, Customer Extensions and Company Parameters there is an alternative to Excel Client when it comes to adding/modifying multiple records: you can use the Export & Import functionality available for these tables.

Notes

  • Excel formulas – If you use MS Excel formulas to generate the new/updated values, these formulas will not be kept because Pricefx stores the data values only.

  • JSON in Product/Customer filters – Handle very carefully the following two fields in the Users data: Product Filter and Customer Filter. They contain a lengthy JSON string which is a result of setting up the filters in the Pricefx UI. This JSON string must remain valid and contain valid filters; otherwise Pricefx will not display the correct content for the given user.

  • Meta mode – If you enable this option, you can modify the meta information (i.e., the data type and details for the customizable attributes) for the following objects: Customers, Products, Company Parameters, Product Extensions. This can affect your pricing logic, so be careful.

  • Bulk changes and permissions – As you update the data in bulk, it can happen that you modify multiple objects in the same table that have different edit rights (e.g., product extensions within a category). On the server side, lines for which you do not possess editing rights are filtered out. If there are some lines left after filtering, those will be updated and you will receive no message. If no line passes the filter, you will get an access denied error.

  • Key fields check – Excel Client also checks if key fields of Company Parameter, Product Extension, Customer Extension and Manual Price List tables are not empty.

  • Casing and validation – Company Parameter fields are validated as case-sensitive. However, if you have a Parameter with values restricted by a Data Source whose number of values exceeds the Excel limit of 8,192 characters, the validation is handled as case-insensitive. If you enter a value manually, make sure it matches the restricted values list as defined in Pricefx.

  • Recalculation  of PL/LPG – When updating Price Lists and LPGs, all data changes are accumulated and processed in a single asynchronous final update call. Item recalculation is triggered only if an overridable value was changed. At the end of the batch, EC always sends a command to recalculate items changed during the update(s); the action can end with recalculation of 0 items.

  • Validation rules hierarchy – When you get this message "Previous data validation on one or more business key fields <their list> has been suppressed by the business key validation" it is because you have data restriction on some column(s) that are part of the business key. In that case, the Data Source validation rule is overridden by the business key validation rule (Excel can have only one validation formula per cell). Then you need to be careful about the entered data.

  • No validation on copied data – MS Excel data validation is designed to show messages and prevent invalid entries only when users type data directly in a cell. When data is copied or filled, the messages do not appear. This is because the copied content replaces the target cell, completely overwriting it, hence removing the data validation. A solution can be to press F2 to enter the edit mode and then paste the content.

  • Values as Strings – If you want Excel to treat a value as a String, put an apostrophe in the beginning of the value, e.g. '0000123. This way you can also make sure that leading zeros are kept. 

Change Login

If you need to log to a different Pricefx instance while the Excel Client is open, take these steps:

  1. Click the Show Login option.

  2. Enter the credentials. If the first four fields of this dialog are not active, enable the Override login information option at the bottom.
    You can can also use here the two-factor authentication. For details on the setup, see Two-Factor Management.

  3. Optionally, use these options:

    • I am behind a web proxy that requires authentication – Opens another dialog where you can configure the proxy with authentication.

    • Use server side user preferences when available – End users can define preferences in the Pricefx UI. The Excel Client will download only the Default preferences to display the table information in the same order as in the UI. This can be very useful if you intend to update only a subset of the data of a given table. Create a special preference and make it default. After you open the Excel Client, this preference will be used and you will see only those columns you had selected in your preference.

    • Force full login – If checked, a "full" logout and login is performed (complete wipe of data + meta info reload). This was the default behavior before the Mojito SE version.
      If the option is not checked, only a re-login is executed; the data in Excel is not cleared.

  4. Click Login.

Details about the currently logged in user and partition are displayed after you click the Show Details option.