8 Replies Latest reply on Apr 4, 2012 11:54 AM by ronniemilsap

# Display Consecutive numbers for field as numerical range

Hello everyone. I am not versed in scripting or calculations, but I need to figure out how to accomplish showing number values as a rang in a single entry if 2 conditions are met.

First: Show numbers as range if consecutive and Second: Only if the locality value is the same.

The setup is simple. There are 2 fields - "Catalog Number" and "Locality"

Example:

1 Oklahoma

2 Oklahoma

3 Oklahoma

4 Oklahoma

5 Texas

6 Texas

7 Texas

8 Oklahoma

I Would like the above data to display in this fashion:

1-4 Oklahoma

5-7 Texas

8 Oklahoma

I can'y imagine this is a difficult thing to accomplish.

I appreciate any and all assistance.

Thanks!

• ###### 1. Re: Display Consecutive numbers for field as numerical range

Those are 8 records in your example, right? What should the "single entry" show if you have:

1     Oklahoma

2     Oklahoma

4     Oklahoma

5     Texas

6     Texas

7     Texas

8     Oklahoma

The other question is, where will this single entry be? Obviously it does not belong to any one record in the set. Also, is this a found set we are discussing? There's a lot of possible variations to consider - and no, it's not really simple.

http://www.briandunning.com/cf/734

• ###### 2. Re: Display Consecutive numbers for field as numerical range

Yes, they are RECORDS.

Very good question and I pondered this myself.  I soon realized the answer was obvious. If there is a break in the numerical range then it simply ignores the numbers beyond the break, displays the result, and moves on to the next number.

So your example would result with:

1-2     Oklahoma

4        Oklahoma

5-7     Texas

8        Oklahoma

I suppose the answer to your question would be that since the original layout treats every entry as a unique record, then the specific layout I'm working with would calculate the above for a unique display for printing labels.  So essentially you would never see repeated locality values for consecutive records in this layout.

Does that make sense?

• ###### 3. Re: Display Consecutive numbers for field as numerical range

ronniemilsap wrote:

the specific layout I'm working with would calculate the above for a unique display for printing labels.

I think you could perhaps achieve this by sorting the records and printing only the sub-summary by State. The question then is what determines the particular order you have: IOW, what is common to the first group of Oklahomas that 8 Oklahoma does NOT share?

• ###### 4. Re: Display Consecutive numbers for field as numerical range

OK, I think you were right in the beginning.  It is fairly complicated.

Let me describe a scenario.

In this situation, we are dealing with labels printed for jars that contain specimens.

So consider a single jar needing a label that gives minimal information (Cat# and locality) for the 10 specimens within the jar.

I want to print the label for this jar, but the jar might contain specimens that are cat#s 3-8 and cat#s 15-18.

We'll assume in this case that # 3-8 are Oklahoma and 15-18 are form Texas.

In a more simplified situation where I don't need the ranges to display in a single row if they have the same loacality, I would do 2 "finds," one for each range, and the label will display all 10 records in 10 separate lines.

You can see why this might not be ideal if I have a jar with 27 Specimens.  The printed label will be very long and the specimens difficult to view without removal.

So my preference is to have the find function display the results with each range/similar locality on a single line so that labels take up minimum space in the jar.

I told the user that this function may not be possible so if this doesn't work, no big deal.

• ###### 5. Re: Display Consecutive numbers for field as numerical range

Do I understand this correctly:

• There are (up to) 10 ?? specimens in a jar;

• Each specimen has a Catalog Number (how exactly is this determined?) and a Locality; there is no corelation beteen the two (??).

What if your label would look like this:

Oklahoma: 1-4, 8

Texas: 5-7

or even:

Oklahoma: 1, 2, 3, 4, 8

Texas: 5, 6 7

• ###### 6. Re: Display Consecutive numbers for field as numerical range

No correlation between cat# and locality.

I am simplifying the key points for the sake of communication.

In theory, "Oklahoma 1-4, 8" would work, however, the catlog numbers are serveral digits long.  So this solution is not likely to work.

I appreciate your thoughts on this.

• ###### 7. Re: Display Consecutive numbers for field as numerical range

ronniemilsap wrote:

I am simplifying the key points for the sake of communication.

That could be a mistake, because at this point I am not sure which constraints of the problem are real and which are only imagined. For example:

1-4     Oklahoma

5-7     Texas

8        Oklahoma

is different from :

1-4     Oklahoma

8        Oklahoma

5-7     Texas

or:

Oklahoma 1-4

Oklahoma 8

Texas 5-7

but I don't know if the difference matters at all.

• ###### 8. Re: Display Consecutive numbers for field as numerical range

The cat# undoubtedly would come first as that is how the layout and all past labels have been designed.

The most likely scenario is your first example.  Attached is an example of how they have been manually entering the label data.

Label 1 shows that unique entries would simply occupy their own line for the print.

Label 2 shows that a range of 20 specimens occupy only one line because they are consecutive in catalog number and all have the same Locality.

Line 2 on label 2 might show another specimen somwhere outide of the numerical range or locality that the previous line contained.

(The fact that they used only the last 2 digits in the numerical range for label 2 is not important - in fact, I prefer it to read "40741-40761")