CD Reference Number

Question

I have a numeric reference id from 1 to n, which I use in a view scheme to list CDs by this reference id, grouped in groups of 160 discs. Unfortunately, the group by box is 0-based, so CDs are listed as reference ids:

  0 - 159
160 - 319
...

I'd like

  1 - 160
161 - 320

Is there a way to do this?

Answer 1

One approach is to use an expression that maps the possible range of reference ids into a set of categories. Here is one such expression that creates groups of size 160 (whitespace added for clarity)

  If(IsEmpty([Ref ID],1),
      Unassigned,
      PadNumber(Math(160*(FormatNumber(Math(([Ref ID]-1)/160+0.5))-1)+1),4) - 
      PadNumber(Math(160*(FormatNumber(Math(([Ref ID]-1)/160+0.5))-1)+160),4))

The expression outputs "Unassigned" if the reference id is empty. This protects against empty reference id values in the latter portions of the expression.

The bulk of the expression maps, for example, reference id values 1 to 160 into just two numbers, 1 and 160. This allows creating the categories that will be used in selections. Ignoring the PadNumber() portion, which just adds leading 0's for alignment, the first part

  Math(160*(FormatNumber(Math(([Ref ID]-1)/160+0.5))-1)+1)

creates the first portion of the category label (eg. the "1" in "1 - 160"). It does this by first subtracting 1 from the reference id (thus making it 0-based), and dividing the result by the group size (here, 160), which provides a value from 0 to less than 1. By adding 0.5, FormatNumber helps truncate, leaving only the whole integer. Finally, multiplying by the group size of 160 creates the various steps (eg. 1 - 160, 161 - 320, etc.). The next portion of the expression

  Math(160*(FormatNumber(Math(([Ref ID]-1)/160+0.5))-1)+160)

does the same thing essentially, but outputs the end of the range (eg. the "160" in "1 - 160"). The two expressions are concatenated by the " - " to create the category label.

To change the grouping, replace all "160" values to the desired grouping size. Here is an example using a Search list, with several reference id categories in the pane, with group sizes of 160, 10, and 1.

Answer 2

A second approach is to use a search list. This approach is not maintenance free, however, requiring additional expressions as the reference id list grows. It is not quite practical for more than a handful of groupings.

You would name the search list "Reference ID" and then add searches such as:

1 - 160 [=isrange([reference id],1-160)]=1

161 - 320 [=isrange([reference id],161-320)]=1

321 - 480 [=isrange([reference id],321-480)]=1

481 - 640 [=isrange([reference id],481-640)]=1

Add additional searches as the reference id list grows beyond the last grouping.

... and that is how you use expressions in searches! [=<EXPRESSION>]=1 (use one for positive results, or zero for negative results)

From marko.

Result

Marko - fantastic thanks! This worked like a charm.

Adding a new expression every 160 CDs is trivial, so maintainence is not a problem for me.

I suppose it would be a nice feature if the Group By could have a check box which would make numeric groupings 1-based instead of 0-based, or even more general yet, and offset that could be added to numeric Group By's.

References

Forum post

Forum post