How to use Excel Client in Pricefx

This is a quick run through how to use Excel Client to upload and manage data in Pricefx.

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).

⚠ℹ⚠⚠

Good to know

• 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.