#Dexterity Well Behaved Ranges


David Meego - Click for blog homepageWhile we are discussing ranges and in particular indexed ranges, it is worth having a discussion on what a “Well Behaved” range is.

If you have not read the previous article on Future Proof range setting, check it out below:

Definitions

Let’s start with a definition of a Well Behaved range:

A Well Behaved range is one for which the returned data for an inclusive range and an exclusive range are the same.

Don’t you love definitions that introduce new terms without defining them? 🙂

So…

An Inclusive Range is a range where you define a starting and ending values and all records between those values are included.

An Exclusive Range is a range where each field in the key is compared against the starting and ending values and if the record fails this check for any of the fields, it is excluded.

So why is this important?

When Microsoft Dynamics GP supported other database platforms such as Ctree, Btrieve and Pervasive.SQL as well as Microsoft SQL Server. This was extremely important to ensure that the product behaved the same on all platforms.

Ctree, Btrieve and Pervasive.SQL are all ISAM platforms and use inclusive ranges. Microsoft SQL Server is a Relational database platform and by default uses an exclusive range. Microsoft SQL Server can use inclusive ranges but there is a performance overhead for doing so.

When defining a range, you can use the inclusive and exclusive keywords to specify the behaviour you desire, but if you create a range as Well Behaved, it will always return the same data regardless. This is much simpler and safer.

You might say that we don’t use the ISAM platforms any more, but remember that local temporary tables and memory tables still use Ctree and so a Well Behaved range will give you the expected results every time.

Can I see an Example?

Here is an example to help explain, based on a simple transaction line table with two fields:

Transaction Number Sequence Number
1 1
1 2
1 3
2 1
2 2
3 1

Based on the following Range definition to get all the lines from invoices 1 and 2:

Range Definition Transaction Number  Sequence Number 
Start of Range 1 1
End of Range 2 2

The inclusive range will return 5 records, being (1,1) (1,2) (1,3) (2,1) & (2,2).

The exclusive range will return 4 records, being (1,1) (1,2) (2,1) & (2,2). The record (1,3) is excluded because the Sequence Number field is not between the start value of 1 and end value of 2.

The above range is not Well Behaved as it does not have the same results for both inclusive and exclusive ranges.

Another issue: If I add a new line to transaction 2 with a sequence number of 3, it will not be included in the range. My attempt to get all lines from invoices 1 and 2 has failed miserably.

How do I fix the Example?

To fix this example we need to explain Clear and Fill. Clearing a field is setting the field to is minimum or empty value. For example: an empty string or a numeric value of zero. Filling a field is setting the field to its maximum possible value. The maximum values are dependent on the datatype used. We will symbolize Clearing and Filling with C and F. Dexterity has clear and fill field and table commands for this purpose.

Note: Dexterity will automatically update a filled string to use the maximum character based on the current SQL sort order. However, if you communicate directly with SQL (pass through SQL or parameters for stored procedures), you will need to perform that mapping yourself. That would need to be a separate article.

Based on the following Range definition to get all the lines for invoices 1 and 2:

Range Definition Transaction Number  Sequence Number 
Start of Range 1 C for Clear
End of Range 2 F for Fill

The inclusive range will return 5 records, being (1,1) (1,2) (1,3) (2,1) & (2,2).

The exclusive range will also return 5 records, being (1,1) (1,2) (1,3) (2,1) & (2,2).

The above range is Well Behaved as it does have the same results for both inclusive and exclusive ranges.

Also, if other lines are added to either invoice, they will automatically be included in the range.

How do you define a range as Well Behaved?

To make it easier to define a range as Well Behaved, there are some rules. As long as the range defined meets the rules, it will be Well Behaved.

Working from the first field (key segment) in the index down to the last field, you need to obey the following three rules.

  • There can be 0 or more fields with the same start and end value.
  • There can be 0 or 1 field with different start and end values.
  • The rest of the fields must be cleared and filled.

I remember this as the shortened version: “0 or more same, 0 or 1 different, rest clear/fill”.

The first example has 0 values the same, 2 values different and no cleared/filled values. The two values different make it fail the rules, so it is not Well Behaved.

The second “fixed” example has 0 values the same, 1 value different and 1 value cleared/filled. This obeys the rules and so it is Well Behaved.

Keep developing.

David

This article was originally posted on http://www.winthropdc.com/blog.

One thought on “#Dexterity Well Behaved Ranges

Please post feedback or comments

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.