LUGBULK List Maker

Programming, Lego
LUGBULK List Maker

I promised myself when I started this blog that I would introduce all my GitHub project here. And the first one out is the project with the smallest target audience, a project for creating sorting lists for LUGBULK orders.

LUGBULK? Sorting lists?

To properly understand what this tools does, you’ll need to have some insight into the LEGO enthusiast world. The LEGO company is involved with the adult fan community and a LEGO organization/club can become registered as a “Recognized LEGO User Group” (RLUG) with them. Being registered as a RLUG has several benefits. One is being allow to let the members of the RLUG order a large amount of LEGO pieces directly from TLG. This is known as the LUGBULK program. But this can only be done once per year with one big order from the RLUG. The RLUG needs to compile all the individual orders from members into one big order. And once the pieces have been delivered, the RLUG then have to divide up and distribute the pieces to its members. This is where the Sorting lists come in. They are made to make it easy to divide the big order into the smaller individual orders.

To get a sense of how big the order usually is, the RLUG I belong to have the last couple of years order between 700 to 900 thousand pieces that needed to be divided up into about 150 individual orders. This is about a half ton of LEGO per year than needs to be counted and distributed.

Our first and smallest LUGBULK order. Only about 153 kg of LEGO.

The tool currently creates four types of lists.

  • A list with all the people that ordered. Useful to quickly see who got included in the big order.
  • One list for each type of piece ordered. Used for when dividing up the pieces into smaller batches. They include Element ID, TLG color name, Bricklinks description and color, everyone that ordered the piece and in what amount, and lastly sorting instructions (that needs to be updated). Element ID and TLG color name are included because that is what is printed on the bag that the pieces comes in.
  • One list of ordered pieces and amounts for each Buyer. Used to make sure each individual member get all their ordered batches
  • One big master list with all the data. Just in case someone needs the data in a different way than how it is structured in the input sheet.

The Help text for the Command line tool.

There is no official release of this tool yet. The GitHub repository contains C# code for a working command line tool and an unfinished tool with a GUI. You’ll need to compile the code to use the working command line tool.

To use the command line tool, you need to supply an excel file with all the data, define where the data is located in the file and the path to where all the new lists should be created.

For example, the tool needs the following parameters for the example sheet below.

  • The name of the Excel file to use as input
  • Name of the sheet that should be read: ExampleSheet
  • Element Id Span: B1:G1 (Blue cells)
  • BrickLink Id Span: B2:G2 (Green cells)
  • BrickLink Description Span: B3:G3 (Yellow cells)
  • BrickLink Color Span: B4:G4 (Pink cells)
  • TLG Color Span: B5:G5 (Orange cells)
  • Buyers Span: A7:A24 (Purple cells)
  • Output folder where all the new Excel files should be dumped.

ExampleFile.xlsx

The area where the order amounts are is not needed to be defined. The program figures that out by itself. Also the information about the pieces and the Buyers can switch places without problem. Meaning Buyer can be placed horizontally and pieces data can be placed vertically instead of how they are placed in the picture above.

The parameters can given directly to the tool or be placed in a settings file. The file makes it easier to modify parameters and/or rerun the tool if needed. Use command “–CreateSettingsFile” to create a file that you can edit yourself.

If executed correctly a bunch of Excel-files should be created in the specified output folder.

Known issues

  1. I’m using ClosedXML for reading and creating excel files. Unfortunately I have run into issues with it. If an excel file contains formulas that it doesn’t recognize (which can happen when a Google Spreadsheet has been saved as an Excel file) or the formulas contain the Swedish letters ÅÄÖ it will throw an Exception and the whole tool will break. I’m planing on ditching ClosedXML for this project and use something else instead.

  2. A single piece can’t be bought by more than 110 buyers. Should be possible to fix easily, I just haven’t had a reason to do it yet.

Future

Currently this tool makes it very easy for me to create the lists. I usually only need to add some missing data about the bricks before I can run it with the data from my RLUG. The only thing I want to add to it, is a nice and working GUI.