Difficult inventory questions

GarageSale
1

tl;dr: I’m wondering if GS 9 has the particular features in inventory/listing/report management I think I need for me to improve my workflow. So I spend a long time recording what I do now, and what I think I want to do, and end by asking questions that might help me progress.

I’m feeling the pressure to improve my inventory handling system, and if possible I’d like to stick with GarageSale 9 and possibly some self-written db systems I’ve been playing with. But (1) I’ve never really used the Inventory section of GarageSale, and (2) I’m concerned that I don’t immediately see features that would support changing what I already do, so there might be more change than I can afford to make now.

Let me summarize my workflow now, and then say what I am looking for.

  1. I sell books and other unique things, so there will never be more than one of any item, and it will probably never be “restocked”. I’ll call things “books” here, and we can assume it will almost always be sold on eBay as lots of 1 item each.
  2. I photograph a box of books at once, and then set them aside in that same box. Sometimes for months.
  3. I edit the photos, and once they’re cleaned up I create a very simple template GS listing for each book, drag the photos into those, and store those mostly-empty listings in GS big pile on a laptop that is not synced. Sometimes for months.
  4. I grab a box of books from the physical queue, and pick each one up and actually fill out its GS listing with the correct properties, category, price and so forth on the laptop. I work through a physical box over a few days, but it might not all get launched all at once. Usually I will do 5-10 books in a “bunch”; a box might have 3-50 “bunches” in it, depending on the size of thing.
  5. As I finish each bunch, I export them from GS on my laptop and import them into the “server” GS on a desktop machine which is always on. While it’s there I assign a random UUID as a SKU (see below), verify the listing and launch it.
  6. Whenever I finish listing a box, I take it back downstairs and shelve the contents. The shelving system is alphabetical by book title, so I will often have to re-shelve a lot of other inventory to make space on the bookshelves. But the books are inside (different) boxes, and those boxes are on shelves, so I can slide things around relatively easily.
  7. Every few days I “cycle” all my fixed-price listings. I stop some of them (a random selection based on SKU) and possibly change the prices, replace their SKU numbers with random UUID values and re-launch them.
  8. When items sell, I use the GS Orders to print all the packing slips first, and use those as pick lists. At this point I go to eBay infrastructure and print shipping labels and handle buyer messages there.

Problems:

  • I don’t like using random SKU values, but that was the easiest way I could find to produce a random subset of running items in a folder, back in the GS7/8 days. I can sort on the SKU column in the Main window, and select the first 100 or 200 listings, and stop those. When I re-randomize the SKU values, the only real reason is because I will want to select a different random subset next time I do that.
  • It’s difficult to manage the alphabetical order of books over ~3000 items I have listed now, and it will soon be impossible as I scale up. It is often hard to know where a particular thing is (is it small? oversized? a magazine? does it have a spine title or is it in the comic book/magazine box with the thin things? &c &c).
  • I feel like I could do some of the work in the Inventory system as it already exists, but until the recent Javascript infrastructure I didn’t think it would be possible. Now I’m not sure, and hopeful.
  • I do need to track costs, and the inventory system seems like the only correct way to do that. But my SKU-fiddling cycle (which I have not made a workaround for) means the SKU cannot be used to track the items.

What I would like to do:

  1. Photograph an item, edit the pictures, and put the pictures into a new inventory item in GS.
  2. Associate the location information (a string) with the item in inventory [Q: How can I do that, now?]
  3. Set a fixed SKU, forever, for that item, so I can track expenses for listing/re-listing.
  4. Take a folder/group of running listings, and create a selection which is a random subset (maybe something like “1/3”, or “some 100”) of those, which I can drag to a new folder. [Q: I could probably do this in JS or AppleScript, yes?]
  5. Create an Inventory item for each running listing I have now, so I can record the current locations. [Q: Is that possible, perhaps with scripting?]
  6. Change the title of the listings over time. [Q: Is the inventory item title === the listing title?]
  7. Print a pick list with item title and location when I sell things. [Q: is this feasible in a Report?]
  8. Possibly (don’t know, but suspect) add other User-defined info to the Inventory item. this would be sourcing notes, a max and min price I would sell it for, age, and so on. This would also be useful for consignment sales, which I am getting pressure to start again.

Things I don’t know yet:

  • Can I record the arbitrary (undefined, still) “location” string in the Inventory item, but not in its description? Can I add notes? A min and max price value (could be an integer or float)? An acquisition date? A date at which I would want to discount? &c
  • I would need to be able to search for all of these in Smart Groups too. I’m pretty sure GS would need to be extended for this to work. For instance for Consignment reports, if I had recorded the Consignor, I would periodically want to generate a report for them of costs and revenue.
  • I am pretty sure I can select a bunch of listings, and programmatically deselect a random subset, so I’ll try that. This eliminates the stupid “random SKU” kludge I’ve been using for years.
  • Can I import a Listing as an Inventory item somehow? Using JS or AS, maybe?
  • Is the title of an Inventory item the same as the Listing, or are they different strings? I don’t have a sense of the Inventory/listing object models. Same with the description and images. It feels like the SKU is the key these tables are linked by?
  • Related: If I change the title of a listing, does it change the title of the associated inventory item, or is the SKU the only foreign key linking them?
  • Can I produce a list of sold inventory items with the [nonexistent] “location” string I will need? I only see five basic fields in the Inventory Smart Groups window, and this concerns me.

I know @ilja and others have in the past made it possible to programmatically read and write to User Properties, but it still looks like there is no way to associate them with the Inventory (even via a join to the Listings table). And I see that I can create a Smart Group (of Listings) that has contains constraints. So arguably I could associate my “location” string with the listings, but that would not propagate back to the Inventory record. Which is where I really need it to be, because Listings don’t have a location, and get replaced frequently and many times.

At any rate, if you’ve read this, you’re probably in the same ¯\_(ツ)_/¯ state as me.

2

I have started to rtfm (again) and have resolved at least a few of these myself.

A?: The titles of an Inventory record and the linked Listing records are independent. Same with the Description fields, sort of.

via manual:

  • Enter your product’s title in the Title field. This field is only visible in GarageSale and used to identify your item visually.

and

  • The Description field is where you can provide a detailed description of the inventory item that will not show in the actual listing, unless so desired…

I cannot see a simple way forward yet to adding typed information I need to associate with an Inventory item (Consignor, Location, various dates) and also have those values link to the associated Listings.

Again, what I would like would be to record new fields:

  • Min Price I will sell it for (numeric)
  • Max Price I will sell it for (numeric)
  • Consignor (string, at least, which I can link to an external table in my own apps I guess)
  • Location (string)

So that I can do things like:

  • Programmatically change the current (single) listing price to a new value between the Min and Max for the associated Inventory item. I do this now, manually, by just guessing what the min/max are.
  • Print a pick list of Inventory items that have sold, with their locations, so I can go get them
  • Sort Inventory by its Location into if, for example, I reorganize my storage space in the house, or if a box is damaged
  • Print a list of a Consignor’s items, with the fees and revenues associated with only that subset
  • Create a Smart Group of Listings or Inventory items from a given Consignor
  • Generate a report for taxes that has items in Inventory, and reports the original cost, total fees and revenue (if any) associated with them, in aggregate
  • &c

And all that for between 5000 and 10000 items, which is my eventual growth target.

I think maybe that the smallest change in GS that would work for me would be to associate (basic typed) User Property fields with the Inventory records, and have those propagate (or be reachable via a join in Smart Groups and Reports or even scripting) to the associated Listing records.

I might with a lot of effort be able to create a very structured Inventory item Description (string), and if the scripting interface exposes the right stuff I could possible write a script to manually parse and copy the data from that string into User Property fields in the associated Listing(s). It would be risky and onerous, and it would be dangerous to keep up to date: for instance if I have a Listing running with User Properties I’ve written via script, based on data stored in its Inventory item, and then somehow change that original Inventory item’s state (in its Description string), I would need to manually update the Listing somehow, or at least guarantee it is ended and a new one started.

That feels like a very bad idea, when I can see the database structure built into GS sitting right there out of my reach. What’s frustrating to me is that I understand these are very reachable behaviors, but also that I’m probably pretty unusual in the way I want to manage inventory.

Even if I were to write my own personal app and slot it into the Inventory system in GS via a kludge-pile of scripts, I still would be risking compatibility issues later. And I can’t imagine simple stuff like Consignments and inventory financial reporting aren’t going to be useful for some other folks.

3

But also, now that I’m looking at the GarageSale AppleScript dictionary, there is just no way for me to create and edit Inventory items via script anyway? Ouch.

after more rtfm: OK I suppose I could (1) export listings, (2) use those files to create a CSV programmatically, and (3) import that CSV into Inventory, sort of.

4

After thinking more, I believe a useful fraction of what I want to do can be done (but with difficulty) using User Properties in Listing records. If I understand the model though, these are always string type, both key and value, right?

The missing component, which increasingly seems crucial for business and taxes, is tracking total costs spent on an inventory item over many listings. Given the large number of things I have which are “just Listings”, at the minimum I would need a way of creating Inventory items for all of them “in reverse”.

5

This topic was automatically closed 10 days after the last reply. New replies are no longer allowed.