Last time in A Place for Everything: Part 1, I introduced a script for adding an series of numeric units to a site/subsite in the Locations Authority, so in this follow-up, I’ll introduce a script that can add units that are marked by alphanumeric codes.

• Site » Subsite » Bay 1, Shelf A
• Site » Subsite » Bay 1, Shelf B
• Site » Subsite » Bay 1, Shelf C

The value of @qtybays sets the total number of units to be added to the site/subsite. The value of @qtyunits sets the total number of subunits that units should be subdivided into.

The fields @active and @public are used to set the whether the location is active or public access, respectively, and can be either 1 or 0.

/* MASS ASSIGN LOCATIONS TO AUTHORITY */
DECLARE @site varchar(128), @subsite varchar(128), @description varchar(255), @public int, @active int, @login varchar(32), @qtybays int, @qtyunits int, @counter1 int, @counter2 int, @unitType varchar(32), @shelflabel varchar(2)

/* EDIT THESE VALUES */
SET @site = 'Storage'
SET @subsite = 'Aisle C'

SET @description = 'Shelving'

SET @active = 1
SET @public = 1

SET @login = 'chad.petrovay'

SET @qtybays = 5
SET @qtyunits = 6

/* DO NOT EDIT SCRIPT BELOW */
SET NOCOUNT ON
SET @counter1 = 1

WHILE @counter1 <= @qtybays
BEGIN
	SET @counter2 = 1
	WHILE @counter2 <= @qtyunits
	BEGIN
		SET @shelflabel = CASE
			WHEN @counter2 = 1 THEN 'A' WHEN @counter2 = 2 THEN 'B' 
			WHEN @counter2 = 3 THEN 'C' WHEN @counter2 = 4 THEN 'D'
			WHEN @counter2 = 5 THEN 'E' WHEN @counter2 = 6 THEN 'F'
			WHEN @counter2 = 7 THEN 'G' WHEN @counter2 = 8 THEN 'H'
			WHEN @counter2 = 9 THEN 'I' WHEN @counter2 = 10 THEN 'J'
			WHEN @counter2 = 11 THEN 'K' WHEN @counter2 = 12 THEN 'L'
			WHEN @counter2 = 13 THEN 'M' WHEN @counter2 = 14 THEN 'N'
			WHEN @counter2 = 15 THEN 'O' WHEN @counter2 = 16 THEN 'P'
			WHEN @counter2 = 17 THEN 'Q' WHEN @counter2 = 18 THEN 'R'
			WHEN @counter2 = 19 THEN 'S' WHEN @counter2 = 20 THEN 'T'
			WHEN @counter2 = 21 THEN 'U' WHEN @counter2 = 22 THEN 'V'
			WHEN @counter2 = 23 THEN 'W' WHEN @counter2 = 24 THEN 'X'
			WHEN @counter2 = 25 THEN 'Y' WHEN @counter2 = 26 THEN 'Z'
			ELSE 'XX' END
		SET @unitType = 'Bay ' + CAST(@counter1 AS VARCHAR) + ', Shelf ' + @shelflabel
		INSERT INTO Locations (Site, Room, UnitType, Description, LoginID, Active, PublicAccess ) VALUES (@site, @subsite, @unitType, @description, @login, @active, @public )
		SET @counter2 = @counter2 + 1
		PRINT 'Added: '+@site+' >> '+@subsite+' >> '+@unitType
	END
	SET @counter1 = @counter1 + 1
END

SET NOCOUNT OFF

PRINT ''
PRINT 'COMPLETE: '+@site+' >> '+@subsite

This script does limit you to 26 subunits per unit, and only works when each unit in the subsite has the same quantity of subunits. But as a jumping off point, it can severely limit the quantity of locations you have to manually create.

Now that you have a place for everything, which is not just fulfilling an old adage, it is .