Allow Alternative Order Detail Prime Index

The sales and purchase order detail files can have a prime index. A prime index is a unique key that references the order line and in SOP and POP this index consists of a combination of the order reference and the order entry number. The index for an order line though can be re-assigned if a user inserts a new line within the existing order – this updates the entry number and the resulting index for each line after the inserted detail to reflect the new entry number.

Where users are using third-party integrations – perhaps an external Warehouse Management System or a Purchase Invoice Document Scanning solution – then a changing index can cause an issue meaning that, in such circumstances, you need to prevent the option to insert a line within the order and only allow the addition of lines at the end.

This new feature maintains a ‘counter’ number in addition to the entry number and, if invoked, uses the counter number with the order number as the prime index. Unlike entry numbers the counter number is not rejigged for inserted lines resulting in a static prime index.

Here we have an existing three-line order

If we insert line between existing entries 1 and 2 then the existing lines 2 and 3 become entry no 3 and 4 and the inserted line carries the entry no 2. However, for the counter the inserted line is updated as 4.

Installation

Database Changes

To add these items, select Installation from the main menu followed by Application Manager and then Restructure a Database. Select the Sales or Purchase Order Processing application and elect to update the Live Files and then select the appropriate application file.

To insert a new item press <Enter> against a blank entry, enter the title as required and press <Enter>, select the item type (using the drop-down list if required), and depending on item type select the size required

File                     Item Name            Type            Description

SOH/POH            COUNTER-TOT         N                Holds the highest counter no in use on the order lines.

SOD/POD            COUNTER-NO          N                Holds the counter no for the order detail line.

Once the required items have been recorded press the <ESC> key and select the UPDATE button to save the database changes. When prompted respond ‘Y’ to the prompts to ‘Extract Existing Data to New Database Structure’ and, if appropriate, ‘Copy Table Entries from Old Database to New Database’. Final prompts ask to ‘Remove (.OLD) Database’ and to ‘Carry Out the Same Restructure on the BASE File as well’ – respond as required. If you select to update the base file you need to manually insert the new items at the same data item positions as on the live file.

Once you’ve added the order header item repeat the process to add the order detail item.

Set Database Profiles

The new data items for the Counter Total and Counter No need to be referenced within the Datafile Database Profiles. Select Installation from the main menu followed by Application Manager and Set Database Profiles. Choose the required application.

For Sales Order Processing the Counter Total item is referenced within the Header Optional 4 parameter screen and the Counter No item is referenced within the Detail Optional 6 parameter screen. For Purchase Order Processing the Counter Total item is referenced within the Header Mandatory screen and the Counter No item is referenced within the Detail Optional 3 parameter screen.

Update Existing Orders 

You will need to initialise the new Counter No items on the Order Header and Order Detail files. For existing orders use the ‘Entry No’ as the base value. It is worth seeking advice from your Datafile Support agent for this part of the process.

To update select Database Systems from the main menu followed by Datafiler Database Utilities and Global Updates to a Database. Select first the database to update – for this you need to enter the path/filename typically the path required would be DATA\???-SOH.DFD for the Sales Order Header (replacing ??? with the three-letter company id).

Select the item to update – Counter Total / Counter No as appropriate, set that updating with the copy of another item (C), and then select the item you’re updating from – No-Entries or Entry-No as applicable.

Select to update all records and then repeat for the order detail.