Orders

Considerations for ORDER table using the Universal Data Model

Using the Universal Data Model for the Orders table is helpful when creating new order layouts (e.g. Work Order). Since the data structure follows the same table occurrence, an existing order layout (e.g. Sales Order) can simply be copied and everything will work.

There are, however, two caveats that the developer should know:

1. Reference Numbers have to be scripted

2. Queries must be controlled.

Reference Numbers

Every order has a reference number that is prepended with two or three letters. E.g. SO10001, PO10001, INV10001. If these were all separate tables, this value could simply be set up as an Auto-Enter serial number in the field dialog:

Creating reference number with Auto-Enter values does not work for the ORDER table

However, this doesn't work since it requires a calculation to determine the leading two or three letters (i.e. the SO, PO, INV, etc.). Instead, the next serial number is created via the script "next order reference number (orderType)". The next value is obtained via ExecuteSQL (). To achieve this, the results must be ordered. In this case, they are ordered in descending order and the last reference number is at the top.

Moreover, in order to ExecuteSQL () to order the list properly, the field type is a number rather than a text.

Every time a new order record is created (Sales Order, Purchase Order, etc.), this subscript is called and returns the next available reference number.

If an order record is deleted, this reference number is recycled. If the users do not want to recycle order numbers than the records should be canceled but not deleted.

Queries must be controlled

Searching on an order layout will natively return records from the different order types that meet that criteria. For example, searching on a reference number alone such as 10001 will return

• AO10001

• EO10001

• INV10001

• PO10001

• SO10001

To get only the order type of a particular layout, triggers are set on the order detail and order list layouts. The triggers are on both OnModeEnter and OnModeExit. The former needs to run in both Browse mode and Find mode and the latter only in Find mode.

Layout trigger OnModeEnter for order detail and list

Layout trigger OnModeExit for order detail and list

Find All

Finding all records requires another query consideration. When run natively, this will return a list view of all order types, regardless of the current layout. To show all the records of only that particular order type, the Show All command is managed by a script.

Show All script only works while using a custom menu. During development, the menu may be the [Standard FileMaker Menu], in which case the command will return all order records.