checking for consecutive records

[tkilshaw]

If object O has attribute A of type Number, assuming that a query sorts the found set ascending by that attribute, how can I check that all rcords have consecutive numbers.

Thus if there were three records numbered:

1
2
4

Would produce an error.

thanks,

Terry

[aware_support2]

Terry,

I cannot see how this can be done using the standard functionality. You would need to write a process plug-in in Java. On that level you get a full access to the query result set. You can then iterate through the result set using an algorithm that determines if any consecutive numbers are missing and throws an error.

[greg]

Terry,

I would run a process

If
SUM BO.AccessionNumber <> MIN BO.AccessionNumber +((COUNT BO – 1)*COUNT BO)/2
Then
DISPLAY_MESSAGE ‘xxxxx’
Else
DISPLAY_MESSAGE ‘yyyyy’


You do not need FIND, since SUM and MIN look for data in the database, not in the context. This process assumes that your numbers are consecutive but do not necessary start with 1. It calculates the sum of your numbers and compares it with the sum of arithmetic series with common difference equals 1.

Could aware_support please comment on the syntax of this rule.

[tkilshaw]

Greg,

thanks for your suggestion. I tried your suggestion. Tried it again with different bracketing.

I tried adding one to the COUNT expressions too, thinking that COUNT only counts existing records and might not include the record being created.

Anyway it always takes the first branch even when all numbers are consecutive.

thanks,

Terry

[greg]

Yes, I wasn't sure about using results of COUNT in an arithmetic equation and could not find anything in the help file about it. Let's wait for clarification from aware.

[tkilshaw]

Greg

could you give the expression with brackets to show explicit precedence of operators.

For example is /2 meant to divide everything?

thanks,

Terry

[aware_support2]

SUM BO.AccessionNumber <> MIN BO.AccessionNumber +((COUNT BO – 1)*COUNT BO)/2
There is nothing wrong with using COUNT. However, I checked the formula and it does not seem to work for me. Consider a sequence 1,2,3. According to the formula the result is as follows:
1+2+3 <> 1+((3-1)*3)/2
6 <> 1+(2*3)/2
6 <> 1+3
6 <> 4

If you are trying to determine the sum of arithmetic progression (that assumes consecutive numbers), I would say the formula should be this:

(MIN BO.AccessionNumber * 2 + COUNT BO – 1) * COUNT BO / 2

Alternatively, you can use another version that is based on values of the first and last elements in the series and therefore does not make assumptions about consecutive numbers:

(MIN BO.AccessionNumber + MAX BO.AccessionNumber) * COUNT BO / 2

[greg]

You are right, I missed one set of brackets, it should be

SUM BO.AccessionNumber <> (MIN BO.AccessionNumber +((COUNT BO – 1))*COUNT BO)/2

However the aware_support's version with half sum of the first and the last terms is more elegant.

greg