_Tally

This system variable tells you how many records were processed by the last command that affects it. The most common use for _TALLY is to check how many records were returned by a SQL-SELECT, but it actually works with many Xbase commands and the other SQL commands.

Usage

nNumAffected = _TALLY

Here is the complete list of commands that change _TALLY (well, we think it’s complete—we tried a bunch of undocumented stuff):

Actually, JOIN, TOTAL and UPDATE are documented as changing _TALLY, too, but they’re so obsolete, we didn’t even test them. A good thumbrule is that _TALLY is updated by every command that displays a record count when TALK is ON.

Generally, _TALLY contains the number of records affected by the command. So, after DELETE, it has the number of records deleted; after REPLACE, _TALLY contains the number of records that had a field replaced.

However, there's one real oddity on this list. _TALLY after REINDEX contains the number of records for which the last tag indexed applies. If you have filtered tags (indexes using a FOR clause), _TALLY returns the number of records contained in the last index. "Last" here refers to the creation order of the tags, since they're re-created in the same order. This isn't really a big problem since we suggest you never use REINDEX anyway.

In VFP 3 and VFP 5, when TALK is OFF, INDEX and REINDEX don't update _TALLY. This bug is fixed in VFP 6.

Example

SELECT First_Name, Last_Name FROM Employee ;
   WHERE MONTH(Birth_Date)=MONTH(DATE()) ;
   INTO ARRAY aThisMonth
* Check if any records met the criteria.
IF _TALLY=0
   * If not, create the array with blank values.
   DIMENSION aThisMonth[1,2]
   aThisMonth = ""
ENDIF

This example shows perhaps the most common use of _TALLY. When you SELECT INTO an array and no records are selected, the array doesn’t get created. So, it’s usual to test _TALLY right afterwards and, if necessary, create the array.

However, the wide range of commands that affect _TALLY lends itself to all kinds of possibilities. We bet most of you have sections of code that perform one of the commands above, then use COUNT or RECCOUNT() or ALEN() to see how many records were affected (we sure do). And there was _TALLY just waiting to give you the answer. Here’s a simple example:

USE Employee
COUNT FOR MONTH(Birth_Date)=MONTH(DATE())
WAIT WINDOW ;
     LTRIM(STR(_TALLY))+" employees have birthdays this month"

Do watch out for one thing with _TALLY. Because so many commands affect it, it’s important to grab the value right away if you want to keep using it. Otherwise, you run the risk that a later command will overwrite the value.

See Also

Append From, Average, Blank, Calculate, Copy To, Copy To Array, Count, Delete, Delete-SQL, Export, Index, Join, Pack, Recall, Reindex, Replace, Replace From Array, Select-SQL, Sort, Sum, Total, Update, Update-SQL