The 'Advanced Collection Manager'

Huw

Prompted on by recent discussions here, I've started work on the ACM again and should have something for you to test soon.

Use of the ACM will be optional, and if you don't opt to use it Brickset will behave exactly as it does now. If you do, additional facilities will be available on the site.

It will manifest itself in three ways. There will be an extra icon next to the 'notes' one in the search results which, when clicked, will pop up a window showing additional fields. There'll also be some form of 'spreadsheet' view of your entire collection to enable all the data to be viewed and edited. FInally, there'll be a means of exporting the data as a CSV.

I've been working on the pop-up window part and have this working at home, and almost ready for uploading for you to test.

Back in the summer last year, we discussed in the news comments what fields to include in the ACM, and some good suggestions were made. I'm going to make it possible to pick and choose which fields you see in your individual ACM, so that the thing doesn't become overwhelming for those that just want to record price paid or something. (that bit's not working yet)

The fields I am planning on making available are as follows. All fields will be text (and not number or date) to allow you to enter '2010' 'July 2010', '7/7/10' or whatever.

-Date acquired
-Price paid (with currency selected from a drop down list)
-Acquired from
-Condition when acquired (drop down list: MISB, As new, used complete, used incomplete)
-Condition now (drop down list: MISB, assembled, bagged up, parted out)
-Location (for you to record 'on display', 'in shed', 'round granny's or whatever)
-Notes
-For trade (check box) [*]

Check boxes to indicate the parts of the set you own, which hopefully will be linked to the condition and default to all ticked if you pick MISB, complete etc.
- parts
- minifigs
- instructions
- box

[*] This has been suggested here recently and could form the basis of some inter-member trading system in the future.

How does that sound as a start? I can add more fields later but it'll be easier to do so now, so if you have any suggestions let me know.

More info to follow when it's ready for you to test...

Huw

atkinsar

@Huw, I'm feeling unusual levels of affection for you right now ;-)

How about, Current Estimated Value as a field?

drdavewatford

This is great, @Huw - thanks ! While I'm inwardly shuddering at the mental images conjured up by @atkinsar's declarations of affection, I (kind of) know where he's coming from...!

I generally capture the amount I paid for postage separate from what I actually paid for the set itself, so in an ideal world I'd like to do that on the Brickset ACM too.

Other thoughts :

- it should be possible to hide fields that the user is not going to populate with data in order to cut down on screen clutter, e.g. I wouldn't personally use the 'condition when acquired' field

- it should be possible to hide selected fields from public view while still sharing other collection details (I'm not entirely sure whether I want the amount of money that I've invested in my collection to be a matter for public scrutiny.....!)

- perhaps the user could add 'custom' fields, e.g. the postal cost field that I suggested earlier, rather than you having to define them as defaults ?

- the problem with having all fields as text is that it presumably won't be possible for the database to automatically calcualte the total amounts paid, total value etc.., and I think these 'grand totals' will be of interest to all of us. Better therefore to allow the user to specify fields to be numeric so that totals can be calculated.

atkinsar

^ Yes great point DrD, make the value fields numeric. I think I made an assumption that this data would not be publicly viewable, and I agree with DrD that it should not be.

drdavewatford

One final request (for now....!) - provide option to export in Excel (.xlsx) format

atkinsar

^ CSV files will open just fine in Excel so you won't have any issues.

drdavewatford

^ Excellent - thanks !

Huw

> it should be possible to hide fields that the user is not going to populate with data in order to cut down on screen clutter

That is exactly the plan.

> the problem with having all fields as text is that it presumably won't be possible for the database to automatically calcualte the total amounts paid, total value etc

I wondered that, but it should be possible to have the best of both worlds in terms of allowing entry of anything you like but then doing a isnumeric() test and if the field passes, using it to calculate totals.

The date is more problematic given people might want to sort by date acquired, but without forcing dd/mm/yy or mm/dd/yy when many people won't know or care about the exact day, it won't be possible. Do you foresee that as a problem?

All data entered will be private, but if and when we come up with some trading system the 'trade' checkbox will be used to facilitate that.

I'll add a postage and Current Estimated Value field

Do some of you have this data already? Would you want to import it?

atkinsar

^ I have the data but it's all in the notes field in Brickset, so I've got a job on my hands to transfer it all (but one that I'll enjoy immensely!). An import wouldn't therefore do much for me personally.

drdavewatford

^^ I currently store my data within a Word Table, and I'm just in the process of transferring it over to an Excel spreadsheet. Given my likely non-standard ways of storing and sorting the data I'm not sure it'll be possible to import to the Brickset ACM, although I'd certainly give it a go should the facility be available.

I have no interest in sorting by date aquired, so for me it's a moot point ! Not sure if others would like this facility, however. I've always ordered my collection by set number and will continue to do so.

Probably worth initiating an ACM Beta when you're ready to test it; I'd be happy to be a guinea pig for that.

Huw

^ OK, well, you'll be able to open both pop-up windows at once to do it which should make it a bit easier.

The pop-ups now use 'highslide', the javascript library that I use for the images and other popups so they are much nicer...

jgadget

I have a spreadsheet with multiple worksheets:
The "full" setlist, which I try to keep up to date, but mainly when I add new sets to my collection.
My overall owned list, which looks up in the setlist.
A separate sheet for each year, from 2000 to 2011, again looking up in the setlist.
These latter sheets have date and cost info in them. (Prior to 2000, I have no information on dates or prices paid.)
I add the postage in to the cost, before putting it into the sheet, so I don't have that info separately.

However, I'll probably find it easier to manually add the data to the ACM db, although it will likely take some time.

I don't see much use for the Current Estimated Value field, if I have to enter it myself.
If it was automatically populated from some intelligent market tracking system, then I'd definitely like it.
As far as I'm aware, the closest thing we have to one of these is bricklink's average selling price. It doesn't allow for location or set completeness, but it's a reasonable indicator.

Is the ACM going to be able to handle multiples of sets, bought on different dates, at different prices, some of which were MISB and now open, others still MISB, and yet more that were bought used?

Seems like you've taken on a big job here.

Huw

^ I did wonder that myself about the CEV but I'll add the field and people can use it if they want to, or not display it if they prefer.

> Is the ACM going to be able to handle multiples of sets, bought on different dates, at different prices, some of which were MISB and now open, others still MISB, and yet more that were bought used?

Yes it certainly is. It was a bit of a headache to get it to, but I did that work in the database last year. As you increase/decrease the quantity in the normal way, rows are added/deleted [*] from the ACM table.

[*] Rows are only deleted if you haven't edited them. If you have, they are marked as deleted and greyed out, and can be deleted manually. If you subsequently increase the qty any rows marked as deleted are automatically undeleted. So no data is inadvertly lost from the ACM if you accidentally click on the 'I own' box to clear it. Clever, eh: -)

atkinsar

I forget to reply about the date. It's not that important, it would be nice to sort by date acquired but I'll get over it pretty quickly.

brickmatic

[*] Rows are only deleted if you haven't edited them. If you have, they are marked as deleted and greyed out, and can be deleted manually. If you subsequently increase the qty any rows marked as deleted are automatically undeleted. So no data is inadvertly lost from the ACM if you accidentally click on the 'I own' box to clear it. Clever, eh: -)

Yes it is :)

As for comments:

I think that for dates it should be stored as a date, not as a text field. If you're worried about people who don't want to enter in the day portion of a date, you could have a radio button that selects to hide or display that portion of the date. In the database, you can default the day to the 1st for people who don't use days. Also, I wouldn't worry about people fretting over the format of the date. If they really want they can change it in Excel when they export the list. As for the website, just use YYYY-MM-DD as 1) it avoids the whole American/European thing with MM/DD/YY versus DD/MM/YY 2) I find everybody intuitively gets this date when they look at it written out that way 3) it's ISO standard http://en.wikipedia.org/wiki/ISO_8601#Dates.

Same goes for numeric fields. I think keeping data types correct makes for a better database.

If postage gets a separate field, why not tax? Depending where I buy LEGO I pay different tax rates, so I'd like to keep this separate from the actual cost of the set.

Will Trade is better than For Trade because you might have something set aside to sell, but are unwilling to trade it at the moment.

Also, I find it amusing that people are concerned others finding out how much money is spent on LEGO. If get a good idea from the size of the collection! Although I guess it really shows when you have exact figures. And yes, I agree that info should be private. :)

jgadget

@Huw - Yes, very clever.

I wouldn't need to sort by date either, but if I felt like it, I'd do it by using an 8-digit date, YYYYMMDD.

mkoesel

How about region or country of packaging? Some of my very old sets are from Europe, for example, even though I live in the US. I realize there is an "acquired from" field, but packaging is technically distinct from who the set was acquired from and where it was acquired from.

mkoesel

-

Huw

I wouldn't need to sort by date either, but if I felt like it, I'd do it by using an 8-digit date, YYYYMMDD.

That's what I figured. If I use typed data fields I also have to do input validation and it reduces flexibility. People that want to sort by date can use the format you suggest, and those that want to say 'about july 2010' can do so, but obviously won't be able to sort.

The data will be entirely private and usable only by the person entering it so it'll be up to them to use whatever standard they like.

> Depending where I buy LEGO I pay different tax rates, so I'd like to keep this separate from the actual cost of the set.

I think I'd disagree here: you still paid a certain amount to acquire a set, if 10% of it is paid as tax, is that relevant? In the UK we can't avoid paying sales tax, maybe that colours my view :-)

vynsane

I think that for dates it should be stored as a date, not as a text field. If you're worried about people who don't want to enter in the day portion of a date, you could have a radio button that selects to hide or display that portion of the date. In the database, you can default the day to the 1st for people who don't use days. Also, I wouldn't worry about people fretting over the format of the date. If they really want they can change it in Excel when they export the list. As for the website, just use YYYY-MM-DD as 1) it avoids the whole American/European thing with MM/DD/YY versus DD/MM/YY 2) I find everybody intuitively gets this date when they look at it written out that way 3) it's ISO standard http://en.wikipedia.org/wiki/ISO_8601#Dates.

Same goes for numeric fields. I think keeping data types correct makes for a better database.

I agree, data types exist for a reason. To not use data types, it will enable non-standard data anywhere, and it could become a mess. The best of both worlds would be to store the date as YYYY-MM-DD and have an option to display it as American or European style.

Huw

So is ie better to force people to enter day, month and year or allow free-form dates for those that want them?

I guess ideally it should do both, but that would need two database fields, which isn't a problem in itself, but it does add to complexity.

rocao

@Huw: I apologize if this was mentioned somewhere previously; but how will recording this information for multiple copies of the same set be handled?

Huw

Easily, as you'll see when you try it :-)

vynsane

Well, you could make it as error-proof as possible and have three form fields for dates: Year, Month and Day. Year and Day would be numeric, Month could be numeric or alphabetic. The submission process would need to cleanse the data, checking to see if it's numeric (04), the abbreviation (Apr) or the full name (April) of the Month and then converting it to the numeric value if necessary, which would then be compiled into the YYYY-MM-DD format for storage in the database.

Huw

^ Well yes I could do that, and maybe eventually that is the way to do it, but I'll get everything else working first.

Yellowcastle

I am fine forcing the user to enter a valid date for the date field, as you can always just choose the first day of that month should you not recall. As for ECV, I imagine at some point that someone will likely develop an ECV application that you might then be able to integrate with Brickset. This whole endeavor is very exciting news but I'm not yet sure where I'll find the time to enjoy it between the forum, Brickset, and actual brick time! :o)

Coder_X

Maybe for date acquired you could just use month and year... both through select boxes...
Do you really need to know the exact day you bought it? Does it matter if it was the 1st of januari 2005 or the 12th?

You can always add a general comment 'textarea' field... there people can put in whatever they want... Tax paid, exact date bought, etc, etc...

I doubt you will ever be able to satisfy everyone if you try to customize everything...

vynsane

@Coder_X - I would think it better to offer full date functionality, and make it optional to fill out all the fields - it's always better to default to the highest level of functionality and make it optional, than to offer limited functionality. If it doesn't matter what day it was to you, that's all well and good - if you're not required to fill out the day, then that's fine. I, though, would like the opportunity to fill in the day, and wouldn't like the limitation. As they say - "It's better to have a gun and not need it than to need a gun and not have it" (not that I "gun tottin' American" by any means, it's just a saying).

fox171171

Sounds interesting. I think that having lots available, but making stuff optional (and hideable to those who don't use certain fields) is a good idea.

I vote for YYYY-MM-DD or YYYYMMDD.

atkinsar

So @Huw, I read on another discussion that you said this may be ready for beta tomorrow, that is exciting news! Are you going to roll this out to everyone on Brickset during the beta?

Huw

I'll announce it here then anybody can use it but I won't announce it on the main site just yet, so we can iron out the bugs first.

drdavewatford

^ Excellent ! Have you figured out a way of importing data into it yet ?

Huw

No, that may come later. It'll probably require downloading a template CSV, then using it to enter (or copy/paste) data into before saving as a CSV and uploading it. That's a fairly difficult piece of work due to the need to handle errors/invalid/inconsistent data etc. in the file.

It should all be completed bar some form of 'big picture' view of all your ACM data. I know how I'll do that but won't have time before tomorrow.

brickmatic

@Huw

>I think I'd disagree here: you still paid a certain amount to acquire a set, if 10% of it is paid as tax, is that relevant? In the UK we can't avoid paying sales tax, maybe that colours my view :-)

In New Jersey the sales tax is 7%. In an Urban Enterprise Zone it is 3.5%. In Pennsylvania it is 6%. In New York it is between 4% and 8.875% based on which county and city you buy in. Sales tax is not included in the list price of the set. I've purchased LEGO in all of these places.

How is this different than postage? Postage is part of the amount you paid to acquire a set.

Also, for [ Condition now (drop down list: MISB, assembled, bagged up, parted out) ] I would add modified. For instance, when people take the 8043-1: Motorized Excavator and add additional motors and change out the bucket. I would still consider it a completed 8043, just modified. I've done this to 7994-1: LEGO City Harbour for ship's superstructure, which I thought looked rather small for the size of the ship.

Galactus

"Well, you could make it as error-proof as possible and have three form fields for dates: Year, Month and Day. Year and Day would be numeric, Month could be numeric or alphabetic. The submission process would need to cleanse the data, checking to see if it's numeric (04), the abbreviation (Apr) or the full name (April) of the Month and then converting it to the numeric value if necessary, which would then be compiled into the YYYY-MM-DD format for storage in the database."

Alphabetic input for months can be done in someone's own language. Therefore conversion isn't that simple, I think. I would go for YYYY-MM-DD with the DD part optional, but I don't know if that is technical possible.

^ I think this 'modified' condition is a useful option. It's also possible that you've modified (part of) a few - or all - of your sets into a MOC. In my view this is different than assembled, which insinuates that it is in the original assembly.

@Huw, how about sets that I got for free? The ACM doesn't show the amount of € 0,- so how do I know if the price is zero or unknown?

Huw

^ I'll see if I can get 0.00 back, when entered but not assume null is 0.00

FollowsClosely

I use the advance collection manager and I am looking for a feature. Can you list all sets along with the advance columns? I want to print all my sets in a clean table format that includes a picture, set number, theme, piece count and storage location. Can this be done? If not this would be a great feature.

emilewski

I understand how a set picture would be difficult to extract in a basic csv exract file, but what I would love to see in the csv extract is the RRP and the piece count information. It is a great tool, thanks!

UndeadHalfOrc

Hi!
Here's a bug:

Recvently I sold the only Harry Potter set I had in my collection.
But when I browse my collection by theme, it still shows the Harry Potter theme even though I have no sets in the theme now!

Bosstone100

@UndeadHalfOrc - did you uncheck the owned checkbox or just change the number owned to 0? Try unchecking that box. I've had this happen before.

beatlefan1966

Maybe I'm just an oddball, but I wish I could put in the number of Lego elements I have that aren't part of sets...just bulk pieces that I've bought. I'm a little obsessed with my piece count, and that way it could be accurate! Could there be a field for "miscellaneous Lego" that could be added to the piece count?

Chooch

@beatlefan1966 you could use Bricklink to do that with

beatlefan1966

Chooch said:

@beatlefan1966 you could use Bricklink to do that with

I can use Bricklink to keep track of my inventory without any intention of selling any of it? How exactly?

sidersdd

^ You can set up a store (it's free), and just not open it up to the public (in your Store Settings you set the status to 'Closed'). You can then populate it as you wish.

You can alternatively (in case you really do want a store to sell to the public), add items to your store's Stockrooms (you get three of them). These items are not shown to the public in your store.

beatlefan1966

Thanks @sidersdd!

Bosstone100

Would it be easier to create an inventory in Brickstore?

What is the theoretical limit to how many different pieces can be stores in Brickstore before overwhelming it?

What would be the advantage of doing it on Bricklink?

Is the Bricklink interface clunky to add parts, etc?

graphite

When you get the set listing or a search that has multiple results, the little icon for the advanced collection manager is there that pops up the edit entry for that particular set. Would it be possible to have that same little icon down near where the "i own" checkbox is on the full page for an individual set?

I've been updating locations as I pack and move things around and its easiest for me to just search a set and then update it but if there is only one set matching the search it goes right to it and the option isn't there as far as I can tell to get to the ACM.

atkinsar

Often if you want to do a lot of updates to your ACM information it is easier to export, edit the file and then reimport, I've done this a number of times for updating the location.

graphite

^ I'm only really doing a handful at a time. I just noticed it because I was looking up individual sets and in some cases you'd get the listing page and sometimes the full set page, but on the full set page there was no way to bring it up and to get a listing for that set you have to search again with a partial title instead of the set number so that it has multiple results.

I may start doing the export/import way, but just thought it might be convenient to be able to get to that information from the set page as well.

atkinsar

Are you saying you can't edit the ACM info on the set details page? If so, you can do so on the ACM tab (although not on an iPad). I actually prefer to use the popup version of the ACM though as it doesn't require any horizontal scrolling, so I often do as you do and use the search results listing and popup ACM to edit my data. And yes, if there's only one result then you're forced to do a partial result to achieve that. Having access to the popup ACM would be useful on the set details page.

cheapinkc

Can ACM options like missing mini-figures for a set be taken into account in when tracking minifigs or overall collection size?