How Do I Fetch the First or Last Record of a Table in a Task? (Magic xpa 3.x)

Sometimes you may want to only fetch one record in a task, say, the first or last record of a table. For instance, if you want to update the “last login time” for a particular user, or find the greatest record number in a table.

There are a few steps in doing this, which we will go through below. The methods are different for a Main Source than a Linked Source, however, so there are two sections.

Fetching the first or last record for a Main Source

1. Define your index

The first thing to think about when fetching the first or last record, is what exactly are you meaning by “first” and “last”. That is, for any given table, there might be multiple indices. For instance, in a Customer table, the “first customer” might mean the one with the smallest customer ID, or the one with the first alphabetical Customer Name, or the one with the smallest zip code.

In Magic xpa, you define which index you are using by selecting the index in the Main Source operation.

However, if you do not have an index that will define the search order the way you want, you can use the task’s Sort window to perform a runtime sort of the data.

You do not need to enter Range or Locate values in the Data View to get the first or last record.

2. Set up the search direction

Next, you need to determine whether you are looking for the first or last record. If you are looking for the first record, you don’t need to do anything here. However, if you are looking for the last record, then you need to tell Magic xpa to start at the bottom of the table (since it would be very inefficient to search all the way through the table to find the last one!).

To get the last record, you set the Range order or Locate order in Range/Locate->Expressions to Descending.

Range Order vs. Locate Order

When you are fetching just the last record, either Range order or Locate order will work. They function slightly differently, as you can see above. In an online task the differences are obvious: in a one-cycle batch task such as this one the result will be the same.

However, it is a common practice to just set both to Descending in this sort of one-cycle task, as it seems easier to understand.

3. Check the number of cycles

When you are fetching just one record, you will be using a batch task that cycles only once.

You set this up in Task Properties (Ctrl+P), on the General tab.

1. Set Task Type to Batch.

2. Set End Task condition to Yes.

3. Set Evaluate condition to After updating record.

Fetching the first or last record for a Linked Source

1. Define your index

The first thing to think about when fetching the first or last record, is what exactly are you meaning by “first” and “last”. That is, for any given table, there might be multiple indices. For instance, in a Customer table, the “first customer” might mean the one with the smallest customer ID, or the one with the first alphabetical Customer Name, or the one with the smallest zip code.

2. Set up the search direction

Next, you need to set the search direction. If you want to get the first record, according to the index direction, set Direction to Default. If you want the last record, according to the index direction, set Direction to Reversed.

That’s all you need to do. You don’t need to enter a Range or Locate value.

See also

The Online and Rich Client Samples projects (program DV12 and RDV12)