I came across an interesting issue yesterday relating to the Dexterity sum range command. We had a SQL datetime conversion error caused by a sum range command. This was the error message:
A sum range operation on table 'SVC_Serial_Lot_Work_HIST' failed accessing SQL data.
More Info: [Microsoft][SQL Server Native Client 10.0][SQL Server]Conversion failed when converting date and/or time
When looking at the logs we could see that the error was caused by the following convert function in the resulting query. For some reason SQL got upset with a negative year value in a date!!
Further research into the source code issuing the sum range command and looking at our problem report database identified the cause.
This post is to explain how the command works, how it should be used and what can happen when it Best Practice is not followed.
This command can be used to sum numeric values in table. For SQL tables, it leverages the T-SQL SUM() function and provides far better performance than using code to loop through the table and add up values.
The way that the sum range command works is described in detail in the Dexterity help, but there are some issues I have seen which are not covered in the help file.
In summary, when the sum range command is used, the current contents of the table buffer is checked and for each of the non zero fields, all of the records in the range (exclusive) are summed.
An exclusive range is a range where each of the key fields is checked separately to see if it falls within the limits for that field. So a record is only included in the sum if all the key fields are individually within the limits. For a "well defined" range, the selected records will be the same as an inclusive range (where a first and last record are selected and all records in between are included).
Now the help file mentions that you can sum numeric fields (integer, long integer and currency) but what it does not say is that fields of other datatypes can also be summed with unpredictable results. This problem is a known issue logged in our system as Problem Report 7380. However, if instructions in the help file are followed, this issue does not occur.
Here are the correct "Best Practice" steps for using the sum range command:
If you follow these steps... great, but I have seen a number of times when step 2 has been missed and the table buffer is not cleared before setting which fields should be summed. This means that the fields used for setting the range are also included in the summing process.
The following are some examples of the results of summing additional fields:
So the bottom line is:
AFTER SETTING THE RANGE, DON'T FORGET TO CLEAR THE TABLE BUFFER BEFORE SETTING THE FIELDS TO BE SUMMED.
Got it?, Good! :-)
Posting from Mark Polino at DynamicAccounting.net
Posting from Jivtesh Singh at About Dynamics, Development and Life
David, can you provide a link the Problem Report 7380?
We are experiencing an issue with our Stock Count Entry where, according to the DEXSQL.log file, it's throwing an error with this Select Statement:
SELECT TOP 25 EXCEPTIONDATE,DATETYPE,DEX_ROW_ID FROM TWO.dbo.IV41001 WHERE EXCEPTIONDATE = '-4714.11.25' ORDER BY EXCEPTIONDATE ASC
Notice the negative date? How can I correct this issue?
No - we cannot provide such a link. The problem database isn't public.
However the problem 7380 wouldn't have anything to do with your issue here as nothing is trying to "sum" the data.
You'd need to track where that comes from via a script.log and a dexsql.log. And probably GP source to see where the values you see in the logs come from.
PLEASE READ BEFORE POSTING
Please only post comments relating to the topic of this page.
If you wish to ask a technical question, please use the links in the links section (scroll down, on right hand side) to ask on the Newsgroups or Forums. If you ask on the Newsgroups or Forums, others in the community can respond and the answers are available for everyone in the future.