1 Reply Latest reply on Nov 17, 2011 2:12 PM by philmodjunk

    Report the range of numbers located on a particular shelf or in a box

    jdhelle

      Title

      Report the range of numbers located on a particular shelf or in a box

      Post

      I have a database of Diamond Disc Records that calculates where the physical record is located, Shelf # or Box #, based upon how many records fit onto one shelf and in one box by coupling number.  I have several that have been entered into the database but are waiting to be cleaned and filed.  I need a report that shows the range of coupling numbers on each shelf and each box to make it easier to file the remaining records.  Between shelves and boxes there are more than 40 possibilities.  

      Example:

      Shelf 1 - 50052 - 51125

      Shelf 2 - 51126 - 51220 

      Box 1 - 80032 - 80090

      Box 2 - 80091 - 80125

      I have attached the scripts I use for the calculations.  In the record detail display if the box # is less than 11 the word 'Shelf' displays with the number. If the box # > 11 the word 'Box' displays with the number.

       

       

      Box_Calculation.jpg

        • 1. Re: Report the range of numbers located on a particular shelf or in a box
          philmodjunk

          Why do you put the if function inside the field repetition box to return 1 or empty like this? Seems needlessly complex.

          Instead of repeatedly evaluating the same complex expression, why not perform a find that limits your found set to only records that meet this criteria?

          I can see that Owned_Music_Library::Where_Stored receives a number form $$Box, but don't see where any field gets a "box" or "shelf" label.

          And you can simplify your set variable steps by replacing $$Box + 0 with just $$Box (and likewise with $itemcount.)

          That leaves me a bit puzzled by how your script is supposed to work here...

          Speaking in general terms, if all your records have a field with "shelf" or "box" and a second field with the shelf/box number, you can define two summary fields to report the maximum and minimum of your "coupling number" field.

          Then you can set up a summary report with a sub summary part when sorted by ShelfBoxNumb and put these two summary fields, separated by a hyphen inside this sub summary part.

          Remove the body layout part from this layout.

          Now, if you sort your records by location type (box or shelf) and then also ShelfBoxNumb, you'll get a list of locations with the number ranges found in each location.