Ranges

When working with tables, it is often efficient to limit the amount of information being accessed. You can do this by setting up a range for the table. A range is based on a key for the table, and allows you to access a specified portion of the rows in the table. The selected range will be treated as an entire table. For instance, calling the GetFirst() method returns the first row in the range. Calling the GetLast() method returns the last row in the range, and so on.

You use the RangeStart() and RangeEnd() methods to specify the range for a table. You can specify one range per table, and the range is associated with a specific key. The range will be used only when the table is accessed using the key the range is associated with. To clear the range specified for a table, use the RangeClear() method.

Example 1 - Range with simple key

In the following C# example, a range is used to limit the rows accessed to only those customers whose names begin with "A". Notice that the second key for the table, composed of the Customer Name field, is used for the RangeStart() and RangeEnd() methods. The first and last rows in the range are displayed.

// Variable for any table operation error
TableError err;

// Create a reference to the table
RmCustomerMstrTable CustomerMasterTable;
CustomerMasterTable = Dynamics.Tables.RmCustomerMstr;

// Set the key to use for the table
// Key 2 - Contains the Customer Name
CustomerMasterTable.Key = 2;

// Specify the start of the range
CustomerMasterTable.Clear();
CustomerMasterTable.CustomerName.Value = "A";
CustomerMasterTable.RangeStart();

// Specify the end of the range
CustomerMasterTable.Clear();
CustomerMasterTable.CustomerName.Value = "B";
CustomerMasterTable.RangeEnd();

// Display the first item in the range
err = CustomerMasterTable.GetFirst();

if (err == TableError.NoError)
{
    MessageBox.Show("First customer: " +
    CustomerMasterTable.CustomerName.Value);
}
else
{
    MessageBox.Show("An error occured retrieving the row: " +
    err.ToString());
}

// Display the last item in the range
err = CustomerMasterTable.GetLast();

if (err == TableError.NoError)
{
    MessageBox.Show("Last customer: " +
    CustomerMasterTable.CustomerName.Value);
}
else
{
    MessageBox.Show("An error occured retrieving the row: " +
    err.ToString());
}

// Close the table
CustomerMasterTable.Close();

Example 2 - Range with multi-segment key

If a key is composed of several segments, you can create ranges based on serveral key segments. It's important that all segments of the key be set when specifying the start and end of the range. The Clear() and Fill() methods are often used when creating a range based on a multi-segment key. They are used to set all of the fields in the table buffer to the empty value or the filled value. This ensures that all of the key segments have been specified before the RangeStart() or RangeEnd() methods are called.

For example, the following C# code creates a range for the IV_Item_MSTR table that includes only items in the COMPONENTS class. The range is created using key 3 for the Item Master table. This key has two segments: Item Class Code and Item Number.

To set the beginning of the range, the Clear() method is used to set the fields in the IV_Item_MSTR table buffer to the minimum value. The Item Class Code field in the table buffer is set to "COMPONENTS". The RangeStart() method specified this is the beginning of the range.

To set the end of the range, the Fill() method is used to set the fields in the IV_Item_MSTR table buffer to the maximum value. The Item Class Code field in the table buffer is set to "COMPONENTS". The RangeEnd() method specifies this is the end of the range.

The key values for the range are shown in the following illustration. The range will include rows where the Item Class Code value is "COMPONENT" and the Item Number can be any value.

// Variable for any table operation error
TableError err;

// Create a reference to the table
IvItemMstrTable ItemMasterTable;
ItemMasterTable = Dynamics.Tables.IvItemMstr;

// Set the key to use for the table
// Key 3 - Contains the Item Class Code and the Item Number
ItemMasterTable.Key = 3;

// Specify the start of the range
ItemMasterTable.Clear();
ItemMasterTable.ItemClassCode.Value = "COMPONENTS";
ItemMasterTable.RangeStart();

// Specify the end of the range
ItemMasterTable.Fill();
ItemMasterTable.ItemClassCode.Value = "COMPONENTS";
ItemMasterTable.RangeEnd();

// Read through the items in the range
StringBuilder itemList = new StringBuilder();

err = ItemMasterTable.GetFirst();

while(err == TableError.NoError)
{
    // Add the item to the list
    itemList.AppendLine(ItemMasterTable.ItemNumber + "   " +
    ItemMasterTable.ItemDescription);

    // Get the next item in the range
    err = ItemMasterTable.GetNext();
}

// Display the list of items
MessageBox.Show(itemList.ToString());

// Close the table
ItemMasterTable.Close();

Example 3 - Removing rows in a range

Once a range is created for a table, you can use the RangeRemove() method to remove all of the rows in that range from the table. In many cases, this will be faster than removing individual rows using the Remove() method.

The following C# example deletes all customer information for St. Patrick's Hospital. A single row must be deleted from the RM_Customer_MSTR and the RM_Customer_MSTR_SUM tables. Several rows must be deleted from the RM_Customer_MSTR_ADDR table. A range is used to remove the rows from this table.

// Variable for any table operation error
TableError err;

// Create references to the tables
RmCustomerMstrTable CustomerMasterTable;
CustomerMasterTable = Dynamics.Tables.RmCustomerMstr;

RmCustomerMstrSumTable CustomerMasterSummaryTable;
CustomerMasterSummaryTable = Dynamics.Tables.RmCustomerMstrSum;

RmCustomerMstrAddrTable CustomerMasterAddressTable;
CustomerMasterAddressTable = Dynamics.Tables.RmCustomerMstrAddr;

// Delete the row from the Customer Master Summary table
CustomerMasterSummaryTable.Key = 1;
CustomerMasterSummaryTable.CustomerNumber.Value = "STPATRIC0001";
err = CustomerMasterSummaryTable.Change();
if (err == TableError.NoError)
{
    err = CustomerMasterSummaryTable.Remove();
}

// Close the table
CustomerMasterSummaryTable.Close();

// Delete the row from the Customer Master table
CustomerMasterTable.Key = 1;
CustomerMasterTable.CustomerNumber.Value = "STPATRIC0001";
err = CustomerMasterTable.Change();
if (err == TableError.NoError)
{
    err = CustomerMasterTable.Remove();
}

// Close the table
CustomerMasterTable.Close();

// Delete the rows from the Customer Master Address table
// Key 1 has two segments (Customer Number and Address Code)
CustomerMasterAddressTable.Key = 1;

// Specify the start of the range
CustomerMasterAddressTable.Clear();
CustomerMasterAddressTable.CustomerNumber.Value = "STPATRIC0001";
CustomerMasterAddressTable.RangeStart();

// Specify the end of the range
CustomerMasterAddressTable.Fill();
CustomerMasterAddressTable.CustomerNumber.Value = "STPATRIC0001";
CustomerMasterAddressTable.RangeEnd();

// Remove the rows from the range
CustomerMasterAddressTable.RangeRemove();

// Close the table
CustomerMasterAddressTable.Close();