Bulk Import of Product / Inventory

I like your plan Lynne and would appreciate you speccing phase 1 when @danielle confirms that the Aus team are OK with your plan. thanks all - this will be great

Hi all, I think @Kirsten will be in touch about bulk import, she’s had many many thoughts about how to do this over the years and is best placed to respond :slight_smile:

Fleshing out the details for phase 1:
Under an additional tab within Products -> Import Products
Compulsory fields:

  • Supplier (chosen from a drop down)
  • Product Name
  • Product Category (allow default)
  • Product Price
  • Stock level (allow default)
  • Tax Category (allow default)
    Optional Fields
  • Description
  • Units
  • Unit value
  • Display unit value as
  • Shipping Category
  • Image upload

How do we demand a user conforms to our product category?

  • We could give the option for an import to place all files that don’t match our categories to a specific category for the user to update later
    How do variants work?
  • A product being added from the specific file in which the name matches an existing product in the file is created under a variant of the product.
    Does this override existing products?
    In the case of removing items not in the input file, there could be a checkbox that asks if u want to wipe and reload all from this producer. Perhaps an option to also leave items missing from file alone or to set qty to 0.
    Option 1- delete all products from producer inventory and rewrite
    Option 2 - add and update
    a) if missing set qty to 0
    b) if missing do nothing.
    Is there a way to upload images?
    Phase one does not include image import. Images will be manually added later.
    How to handle default values?
    Before the upload default options can be selected for ‘stock level’, ‘category’ and ‘tax category’.
    What if some rows fail to import?
    The user will be able to see a list of failed imports. They will then have the opportunity to update the source file before continuing.
    Do we need to have the opportunity to check data before loading into the database?
    Eg should we allow the user to view the data on screen before updates to the database are made? I think the answer should be yes.

@Kirsten It would be great to hear your thoughts and flesh this out from your perspective. We have a crew of devs now and it would be great for us to get fleshing out bigger features as well. The team is fast getting through smaller bugs :slight_smile:

Looks great @lin_d_hop - I have a question about units. Would they have to be the same on the source spreadsheet and the shop in OFN? In Canada we are of course in metric units - but since some of our large suppliers (the very ones we would want to use a bulk import for) import product from the US, often their spreadsheets and large lists are in US metrics (lbs, ounces…). So - I’m assuming an OFN user would have to either change these on the source spreadsheet first, or we would leave those fields blank to be calculated and filled in after upload?

I think there would be many situations where the price and quantity details would have to be entered manually after uploading a list actually. For example - a wholesaler would have the price for a case (24 x 3 lb bags of apples for example) and the OFN user (for most hubs anyway) would enter the price for 1 3 lb bag. So what would happen here? (Maybe I’m missing something - I’m still new to this :slight_smile:)

My thoughts are:

Categories - Either what Lynne said or reject anything that doesn’t match.

Variants/existing products - I’d probably override and existing products and replace them with the new one. Would that be simpler? Then any variants could be added by checking the import file for product names that are the same but have different weights/units. If the name is identical, the second item will be a variant of the first. But if that’s complicated, maybe they can just be imported as separate products.

How can we check for existing products that are NOT in the import file. This may mean a product has been discontinued by the supplier or is simply not currently in season and needs to be taken off the OFN shop. The way I can see myself use bulk imports would necessitate an option to remove anything which isn’t on the import file.

In the case of removing items not in the input file, there could be a checkbox that asks if u want to wipe and reload all from this producer. Perhaps an option to also leave items missing from file alone or to set qty to 0.

Another set of cases to note is permissions and relationships to inventory vs product list.
Case 1- user is owner/manager of producer. User can choose to update product list, inventory or both.
Case 2- user is updating a product list for a producer they do not own/manage. User can only update their inventory.

hello everyone :slight_smile: here’s some thoughts from us, greatly informed by analysis from @sstead :slight_smile: and chats with @oeoeaio

NB. The top post in this thread is a wiki - I wonder if is worth editing so that the summary of where things are at sits at the top? @lin_d_hop

###Amending Products vs Amending Inventory
discussion in aus that soon we’ll need to shift the management of fluctuating product fields like price, on hand, on demand, SKU etc out of Products and into the Inventory interface. In this scenario, the products list will only store static information about products, including producer, name, category, properties, unit size?, description, image. see [here]

There’s been concern that building this product upload/update tool BEFORE making this shift, could mean that the functionality soon becomes redundant (when we move to the system of managing most fluctuating fields in Inventory). But we think we can get around this by:

  • building an update uploader that basically is/works the same on both product and inventory pages BUT
  • ignores / doesn’t write some fields depending on page (and potentially options presented to users) E.G.
  • if used on inventory page it doesn’t attempt to update info in producer, product, description etc
  • if used on product page now (e.g. either for update or create) it creates or overwrites stock, price etc . . but later could require user to have selected an inventory to do this to.

Then if information is moved later, is just a case of adjusting which fields are updated from which page

Main take home point here is that uploader should be able to use the same file from both product and inventory pages as the information is all held together, and then it just uses info appropriate to page/permissions (also as per @lin_d_hop’s cases above)

###Compulsory vs optional fields
Optional fields - Wouldn’t units/unit value be compulsory? And maybe add SKU and group buy and bulk unit size to optional fields.

###Removing / hiding / deleting products
My inclination on this is that you don’t want to delete products using an uploader without a lot of caution, because ‘deleting’ a product will have implications throughout the system, including for anyone managing it in their inventory. So I think that generally, if the ‘Update’ file is being used in:

  • Product page, the stock on-hand should be set to zero and the user notified that XX products will no longer appear in shops until stock is available
  • Inventory page, either/both stock set to zero and product hidden / removed from inventory (@oeoeaio @sstead perhaps stock already zerod when hidden? i don’t know)

Perhaps there is a special case for ‘delete’ products / variants in which you upload a file specifically to do this if someone is trying to clean things out, but doing so by omission in an uploader seems like a bad idea

The risk of assigning products to a generic category is that users may never correct this. Plus if they do correct it- this bug causes minor problems - G#1250. To help people assign their products to the correct category each instance could have a template excel sheet with the category fields in a dropdown. Makes it quick and easy to select the appropriate category for each line (see below)


from Sally

If uploader doesn’t handle images could we create a ‘fewer clicks’ way to add images- currently it’s a 3 click journey to get to the add image screen. Users using the uploader could have 100s of images.

I wonder if this could be handled relatively easily by allowing the user to put all the images in the same folder as the upload file and put the filename for each image in the csv next to the product. Then all they need to do is number them or use skus or whatever and the system can just zoop them in :wink:

If not being done, I wonder about default use of producer logo as product image, so that shop looks slightly more colourful with a lot less work to get started.


Wouldn’t it be better to ignore duplicates rather than create a copy? Could tell user ‘these items haven’t been created as they already exist’ in the upload status report

###Sally responses to @tschumilas issues

  • Theresa, yes the user would need to convert from ounces to grams in the sheet before upload. The good thing about excel is that this can be done relatively quickly and uploaded. Leaving fields blank isn’t really an option as the system should reject incomplete products.

  • Theresa, the second scenario you mention, breaking down a case of apples into 1lb bags is an example of a scenario where multiple inventory lists would be ideal. In the future, there could be a bulk invenotry lists and a retail list, the retail list will divide the bulk sizes/prices into a given fraction. This is future stuff, so yes manual for now, but at least sped up with excel, and an upload updater.

###Mapping supplier spreadsheet categories to OFN in importer
phase1 I think this is best handled by users maintaining a spreadsheet for each supplier where they can drop the suppliers list into sheet 1 and sheet 2 maps them to correct columns for ofn import. Then you save the 2nd sheet as csv and voila. You only need to map the categories once for each supplier when you set up the spreadsheet and then just drop the info in each time. So IMHO the built in category mapping can wait


How can the user denote variants? Same producer and same name could be a duplicate, not a variant? How will the system distinguish? One logic…

  • If producer name, product name, units, value are all same = duplicate
  • If producer name, product name are same BUT the units and/or value are different = variant?
  • But this won’t capture Jam - rasberry jam, strawberry jam, apricot jam as variants.
    Other option?

Thanks @Kirsten. Definitely planning to summarise everyones input into something more cohesive :slight_smile:

The more I delve into speccing this the more I realise that it makes much more sense to re-work products/inventory before progressing with import work.

We can talk about this more next UK dev call with @danielle, but it would be great to get @oeoeaio doing an estimate of an estimate (or maybe he’s thought about it enough that he can just move into estimate) of product/inventory overhaul once SO is out of the stables. UK team can definitely help with the implementation and could fund the work (figures dependent).

1 Like

ok, yes and . .

I’m not sure about that - the high level of demand for product upload and the likely tangly complexity of the product/inventory overhaul suggest to me that the best strategy is actually to get moving on the upload while maintaining this attention to possible future adaptation. If we wait to get the overhaul done before attending to the uploader I’m concerned it’ll be way too long.

Open to @oeoeaio, @sstead and @danielle disagreeing, but we’ve thought that we need a good day or two blocked out to even think through the ramifications of what we think might need to happen, let alone estimate it. I’m hoping that we might be able to find this time early next year, once standing orders is out there!

I think it is feasible to build the uploader and amend it later, and in the process of doing so will actually be informing the thinking around overhaul.

Updated at the top but probably easy to miss all the way up there…

I’ve done some work compiling my interpretation of the thinking so far into this document. Ambitiously titled Data Import SRS. Still more to do including some UX prototyping.

@MyriamBoure @oeoeaio @Kirsten @NickWeir @CynthiaReynolds @sreeharsha @Oliver @sstead @tschumilas @danielle
Please take a look, comment and suggest changes!

Looks like a good plan @lin_d_hop ! Will a CSV downloader of products/inventory be implemented at the same time? We don’t currently have a report that dumps all product data- this could be a good starting point for enterprise who want to update an existing product list.

@sstead yeah i think this would be good. Perhaps this could be the second report in the reports overhaul and we could add in P/I download to that? What do you think @Kirsten

Thanks for your comments about multiple suppliers. I was thinking that this should be a future feature but maybe it does make more sense to scope it out from the beginning…? I’ve updated the doc to capture this, would be great if you could take a look and let me know what you think.

BTW, it would be worth using the data package and related specs (see http://frictionlessdata.io/data-packages/) to define a CSV format. Its a good framework for a well-defined spec and there are tools built by Open Knowledge and the ODI we could use.

1 Like

Playing around with how the interface might look…

@oeoeaio @danielle @Kirsten
I’d like (some of) us to have a hangout to discuss this feature as soon as we can. It would be great to get proper dev input and move to a point that we can sign off on the spec so that next steps of the process can begin… issues on github… aaallriiiight

I am pulling together an agenda for this hangout here. I have linked in this discourse thread, Is there anything else I should link in? Any other thoughts on the agenda?

closing off thread. This is in development and further discussion should occur in the [FEAT] post or one of channels named there.