Batch Update from File

This function allows you to maintain standard Buy Prices or Sell Prices in some other database or spreadsheet and import these into Ostendo.  This feature requires that the other database is capable of exporting to .csv or .xls format

 

The Price Update process uses a standard Ostendo routine to carry out this process and comprises the following Steps

•

Create a Price Update Batch and define the update criteria

•

Point the import function to the .csv or .xls file

•

Match the import file to the fields in the .csv or .xls file

•

Run the import function to a temporary file

•

View the results and re-run if necessary

•

Update the prices

 

You should note that because this uses a common Ostendo Import function some of the fields are not applicable to Price Update from File.  This will be explained when you reach the appropriate step.

 

List Tab

 

Display fields

 

The displayed fields are taken from the Update Batch information entered into the ‘Detail’ tab.   The selection of which fields to display is at the user’s discretion.   To select the fields to display simply ‘right mouse’ in the main panel and select ‘Customize List Fields’.   On the displayed panel you can:

•

Click on the ‘Show field’ checkbox to display the field

•

Amend the column heading by changing the content of ‘Display Label’

•

Define the sort sequence of the records by going to the lower panel and dragging the field from ‘Available Fields’ to ‘Sort By’.

•

Click the ‘Save’ Button when done.

 

Data Display Options

 

Field Position - On the List screen you can move the field position by dragging the column heading left or right to the position where you want it to appear.

 

Filtering and Sorting - If you ‘check’ the checkbox then the displayed data is available for filtering and sorting:

•

To sort the data in a selected column simply click on the column heading.  Clicking against will sort in descending order.

•

Click on the black ‘down arrow’ to the right of your selected column heading and you can either select a specific entry to display all records containing that entry, or you can select (Custom…) to enter detailed selection criteria.

 

Include Updated Status - By default the display will only show Batches that have not been updated.   If you wish to see all batches including those that have been updated then ‘check’ this checkbox.

 

Searching - If you enter some text into the ‘Search’ field and click on the ‘binoculars’ Icon then all records will be interrogated and those records that contain the entered text will be displayed.  To revert back to displaying all records click on the ‘Eraser’ Icon

 

Buttons

 

Close: This will close the Update screen.  If you have any unsaved data then you will be asked if you wish to save it before the screen is closed.

 

Add: This will bring up the ‘Detail’ tab for entry of a new Update Batch.

 

Save: Not applicable to the List panel

 

Cancel: Not applicable to the List panel

 

Delete: This only becomes ‘active’ when linked to a Update Batch with status ‘In Progress’.  In this instance it enables you to delete the selected Batch.

 

Related: This will bring up a list of functions that are related to Update.  You may open and maintain information in those screens whilst still remaining in the Update screen.

 

Reports: This will bring up a list of Reports that are related to Update.  You can immediately run the report whilst still remaining in the Update screen.

 

 

Detail Tab

 

This tab enables you to create and maintain a import Price Update Batch Header.  

 

Entry and Display fields

 

Update No: This is a unique number automatically allocated by the program.  It cannot be amended.

 

Status: This is the status of the Update Batch and is maintained by the program.   The displayed variants are:

Planned - The Update Batch is still being worked on

Updated - The Update Batch has been ‘Posted’ and the records updated against the included Items

 

Reference: Enter a brief description of the Update Batch.  No validation checks will be carried out on this field.

 

Pricing Date: This is the date the Batch was created.  It is prefilled with the system date and cannot be amended.

 

Pricing Update Options

 

In this section you define what you want to update

 

Items or Descriptors: Click on the appropriate ‘Radio’ button to select whether this Update batch covers Items or Descriptors.

 

Matched to Item Code or Supplier Code: Click on the appropriate ‘Radio’ button to select whether you are matching imported records against your own Item or Descriptor identity or are using the Supplier’s reference.   In the case of the second option you also need to enter a Supplier in the next field.

 

Supplier: This is only required if the prices being imported use the Supplier’s reference as the main key.  In this instance select the Supplier from the drop-down list

 

Update Batch Pricing: Click on the appropriate ‘Radio’ button to select whether you are going to update the Buy Price or the Std Sell Price against your Item or Descriptor.   This enables the program to evaluate the % increase or decrease of the new price to the old price

 

Apply update on the above settings: Clicking on this button will perform an update of the Price against all the records in the Update file.  This is performed after the Import Routine described below.

 

Notes: Extended Notes for your own information can be attached to this Update Batch.  Whenever you click on this field an icon will appear in the top-right.  By clicking on this Icon you can bring up the Frequently Used Text table.  Select the text to be copied and either press the OK Button in that screen or double-click the selected record.

 

Import Routine

 

Import Data from file and generate price update lines: Clicking on this button will bring up a sub-routine where you can match the .csv or .xls file to that required for the import process.  The following steps are carried out.   You should note that this uses a standard data Importing function that is used throughout Ostendo; therefore not all entry fields are required.

 

Step 1: Click on the appropriate ‘Radio’ button to select whether the data being imported is in a .csv or a xls. File format then click the ‘Next’ button.  Note: The ‘Load Specification’ button is described in Step 5

 

Step 2: Point to where the source file resides and inform the import routine as to the following conditions in that file:

Start at Row - From which row in the import file is the importing to commence

Field Names - tell the program which row in the import file contains headings

Record Separator - unless you have specifically defined otherwise leave this as CRLF

Text Qualifier - unless you have specifically defined otherwise leave this as “

Click the ‘Next’ button. 

 

Step 3: You should now match two fields in your .csv or .xls file with the two fields required in Ostendo.  These are:

Item Code - The Item Number, Descriptor Code, or Supplier’s Reference Number

New Price - The new price to be imported

Click the ‘Next’ button. 

 

Step 4: Define the format of the fields in the Import File.   This allows the import routine to convert it from your remote data source format to the format defined in the Regional Settings of you computer if different.

 

Step 5: This screen confirms your settings.  Ensure that all the settings are correct. 

 

At this point you will see that the ‘Load Specification’ button has changed to ‘Save Specification’.  If you click on this button then you can save the results of Steps 1 through 5 and use this in future rather than go through each step in turn.

 

Click the ‘Execute’ button to perform the import.  You will then be sent to the Lines Panel in Ostendo

 

Lines Tab

 

This tab shows you the result of the Import.  I.e

Code - The Code of the Item or Descriptor

Description - description of the Item or Descriptor

Unit - base unit of the Item or Descriptor

Old Price - the current price of the Item or Descriptor

New Price - the new (imported) price

% Change - the percentage change

 

You should note that this is an interim file where you can assess the results before actually posting the update.  No changes can be made here.  If you require changes then amend the source .csv or .xls file and re-import

 

To complete the update, go back to the ‘Detail’ Tab an click on the following button:

 

Apply update on the above settings:  Clicking on this button will perform an update of the Price against all the records in the Update file

 

Buttons

 

Close: This will close the Pricing Update screen.  If you have any unsaved data then you will be asked if you wish to save it before the screen is closed.

 

Add: This will show a blank screen for entry of a new Price Update Batch.

 

Save: This will save the current data without exiting the screen

 

Cancel: Any changes made to the current Pricing Update Batch Header record or the last time the ‘Save’ Button was pressed will be lost.

 

Delete: If the Pricing Update Batch status is ‘Planned’ then it can be deleted at any time.  ‘Updated’ batched cannot be deleted.

 

Related: This will bring up a list of functions that are related to Pricing Update.  You may open and maintain information in those screens whilst still remaining in the Pricing Update screen.

 

Reports: This will bring up a list of Reports that are related to Pricing Update.  You can immediately run the report whilst still remaining in the Pricing Update screen.