Report: export product/variant info for import to LettuceShare

Okey dokey - @lisahill and malcolm - here’s my thoughts and questions on the lettuce share report

Have made some comments on the google sheet, also embedded below so you can easily see and edit.

Working through this, I found the need for examples so I exported the Products and Inventory report for your EXAMPLE Winter Order Cycle, and created a category mapping with your actual data. See attached - products_and_inventory_20150701-_Lettuceshare.xlsx (82.4 KB)

Seems like it’s pretty much all there, but key questions that arise

  • ‘Quantity’ is a spare field for capturing info that you actually want back? It’s not an inventory level or anything? Is it actually needed in an export report?
  • uncertain about ‘pack size’ and ‘unit’ . .

Malcolm’s comment - “I’d just like to add that “Pack Size” is used by lettuceshare to calculate quotas, so in the example given, “6 x 300g Jars” might have a pack size of 6, and units of “each”. 5kg might be the total, but doesn’t provide the same information. Not sure if that’s the sort of thing we need to work out, but happy to discuss.”

OFN will have a line for each different variant so might have a product that can be bought in boxes of 20kg, would have a ‘group buy unit quantity’ of 20kg (captured as 20000g)
Then it might have a number of variants e.g.
Apples - box (20kg)
Apples - 5kg
Apples - 1kg

some of which (the bulk ones) are offered in FC’s order cycle and others (sub-ones) in buying groups that are running their own.

In this report, the ‘variant value’ field draws info from a couple of other fields so that it fully describes the variant, summarising as 'box (20kg); 5kg and 1kg etc (see examples for how it’s handling all the different info)

Report generated could just have every available variant line (each inheriting group buy unit quantity from the product) and buying group can delete the lines for variant sizes they don’t want to offer (e.g. in example above they only want to offer 1kg) so they delete 5kg and 20kg. So potential custom report needs:

  • fields to spit out in the order in sheet 2
  • variant value to go in the description field? or you want more detailed info such as in actual OFN description? (not sure that it’s filled in for that many at the moment)
  • unit field with stripped out ‘value’ so that we just have ‘1’ ‘kg’
  • pack size = Group Buy Unit Quantity / Unit

thanks Kirsten,

That all sounds good, look forward to seeing an export we can try.

One comment I would make is that Food Connect wholesale customers are importing the current availability list into lettuceshare on a weekly basis, they won’t want to edit it by hand at all (ie to remove variants).

thanks,
Malcolm

@mblaney I think the deleting variant part won’t be a problem because LettuceShare does all the variant work for the customer, so we would only be offering the full boxes on our list, the same as now. Therefore there would be no smaller variants on our order cycle.

@Kirsten I’m not sure how this will work with Leah’s order cycles - e.g. we will already be putting up 3 or 4 order cycles every week for sale of the full boxes - will we need to create 3/4 more with all of the possible variants? It’s making the order cycle creation process extremely complex - about to start an order cycle wishlist section… i’ll see you over there :smile:

Hi Lisa,

Food Connect currently does use a concept of variants, you offer half boxes in the availability list. Lettuceshare can deal with these because they’re on consecutive lines and have the same name. Lettuceshare offers these as “alternative” pack size and prices for the current product, but it can only deal with one per product.

Thanks to both of you for thinking through all the issues here. I’m hoping we can support the current availability spreadsheet for a while so that customers can have some confidence in the transition process. I’ve heard from a couple that they’re worried about switching, so not putting pressure on them would help.

thanks,
Malcolm

Completely agreed Malcolm - we plan on doing a slow transition to ensure smooth sailing for all. It will take customers, warehouse staff and admin staff a while to get used to the new processes, so we’re hoping to do it in waves - keen customers first (there’s even one of our buyers groups looking like using OFN before us!) Apprehensive people can definitely wait until we’ve ironed out all the kinks :smile:

@Kirsten I’m really struggling to work through this discussion & get a handle on how the fields match up. I think without knowing the back end of either system, it’s virtually impossible as the middle man to comprehend what might be required, and I don’t want to get it wrong.

Regardless of that… here is where I’m at. I’ve created a sample spreadsheet based on what we currently send out to customers, and therefore should theoretically work for LettuceShare. I’ve used data from OFN to populate it. I have questions for both programs (and their programmers!) which may have already been asked or answered… if so, sorry.

@mblaney see spreadsheet attached. When i import it to LettuceShare, the description goes into the category column. Why? It’s in exactly the same order as our current wholesale availability. Also, I can’t find anywhere that you view the description anyway… this may change what I’ve laid out below. And i remember a discussion about mapping the certification/growing method column to a particular field, but i can’t remember which field we said we’d use (and basically, i haven’t had a chance to teach myself LettuceShare or call for a lesson - SORRY!)

@Kirsten the fields used are based on the fields available to enter when creating a new product. Presumably these are available in the back end as separate fields. NB the second row shows the OFN field titles, but these are the two that i believe need to be seperate in order for LettuceShare to do it’s buyer’s club magic…

  • Value (OFN) = Pack Size (LettuceShare)
  • Unit (OFN) = Unit (LettuceShare)

For both of you, I’ve used the OFN field ‘Variant Value’ as the description, because it often concisely shows an overview of the product being offered. As already discussed and mentioned, putting the entire paragraph of description which is currently on OFN is probably unnecessary for LettuceShare and using the ‘Variant Value’ field more correctly reflects what we show in our current system.

I can’t work out how to attach/embed a csv in to this discussion, so there’s a link to a google sheet, which you can download and change to a csv (sorry, again) Maybe @mblaney could upload it to LettuceShare for a better analysis of if it will work, as with my limited knowledge of the program, all I could do was upload it and see vaguely how the chips fell.

AH HA! I worked out how to imbed it!!

Also… this is the link to google sheets

ok @lisahill that’s making sense to me (I think!) and have just talked it through with @RohanM and we see no big problems. @mblaney can you please check Lisa’s report as an upload into LettuceShare and let us know asap if any issues / changes - we will aim to build this either this week or next, and much cheaper to pick up problems before Rohan’s time involved . .

Key things I want to check:

  • proposing to put this as a ‘sub-report’ under Products & Inventory reports as it’s quite similar, so there will be three options: All products; Inventory (on hand) and LettuceShare import
  • Lettuceshare does the calculation to split things into the size that BG customers can buy, so all this report needs is to give the bulk amounts with a nice divisible OFN_Value and OFN_Unit - LS takes care of the rest. This means that
    • you wouldn’t necessarily need to use the Group Buy Unit Size variable in OFN (noting however that it’s likely still needed for non-LS users)
    • up to FC to make sure that you do set the bulk variants so this works e.g. ‘12 bottles’ rather than ‘1 box’

hi all,

Yes Lisa’s report works fine with lettuceshare. The problem she mentioned about the description is a new (optional) feature that shouldn’t have been applied to her group (sorry Lisa! all fixed). It can be used to import categories, but otherwise the description is not used by lettuceshare.

thanks,
Malcolm

ok, @lisahill and @mblaney - this will get built either this or next week. Lisa could you please confirm that you have seen and understand / agree with my couple of points above?

Agreed! I will keep an eye on how we are entering variants. I think mostly it is done the correct way, as it matches our current system, so we should be fine continuing with that. Only point i’ve thought of in the last week or so is the possibility that one day the report might be useful for something else, and/or LettuceShare might want to start using some kind of unique product code. Therefore i’d like to have the SKU field in this report as well - as the final column to keep it out of the way of LettuceShare for now? Does anyone see any issues with that? @mblaney @Kirsten ?

SKU added in last column, @mblaney please yell out asap if that breaks the import.

@RohanM - all yours :slight_smile:

1 Like

oh sorry I meant to reply to this one earlier!! it does break my import.

Sorry @lisahill for not replying earlier, I agree that it might be useful in the future but until then making it the last column doesn’t keep it out the way, it will just break things.

thanks @Kirsten for the reminder and sorry for the trouble.

Malcolm

@mblaney No Worries! Just an idea… all good if it won’t work :smile:

removed. yay. need 20 characters . .

@lisahill and @mblaney
Rohan has created the LettuceShare report and you can see it and have a play with it now on the testing website- https://staging2.openfood.com.au

It would be very helpful if you could login and add some of your typical wholesale products and check that the report is generating the correct information to be imported to LettuceShare.

I’ve created you both a login for this site (have emailed you the password), and created a mock enterprise called Food Connect Testing, which you are both managers of. This website is all mock- so you can create as much false data as you like.

If anyone else would like a login, let me know. Post any feedback here. Thank you!

thanks I did a quick test, it imported fine into lettuceshare so that’s good. :smile:

some feedback: the pack size and units were duplicated in the description, and after the required columns there was an empty column followed by a cloumn without a header that had the category in it. None of that affects the import but probably not expected behaviour.

thanks,
Malcolm

Alrighty - I’ve finally had a moment to throw a few test products into the staging two site.
I’ve also pulled the report across the LettuceShare (although still very rusty on any kind of skills in that system!).

I agree with Malcolm - not sure what the blank column (will be ‘J’ in a spreadsheet) between Grower & Taxons is? - see below, maybe it was meant to be the certification method?

Also Taxons doesn’t have a header (as Malcolm already noted).

@mblaney when I pull the report into lettucehsare, the stuff in column B ‘description’ doesn’t appear under ‘category’ which is where I’m guessing it should? Any ideas as to why that might be left blank / did that happen to you?

I also noticed that the growing method/certification didn’t come through at all - looking back to my sample report (a few posts up) I had shown (Columns I & J) that we needed to try and get the producer & certification into the one column ‘grower’ for lettuceshare… was there any chance of achieving this? @sstead Looking back i can now see that i didn’t specifically spell that out in my description…

In the same way that we needed to separate the details in Pack Size/Unit - ideally we need to combine the producer/certification method… is this going to be possible? I’ve had discussions with one of the Buyer’s Groups using LettuceShare and the one thing they noted whilst testing OFN was that the certification wasn’t easily viewable. So ideally we’d really like this to be in the report somewhere - and combining those two fields is the only way i can see to squeeze it in…

Also @mblaney - are you happy that there’s nowhere with a longer description? I guess people can always go to the main OFN shopfront to look at photos and longer descriptions? And we never really had a long description with the current availability list

Hi @lisahill, @mblaney,

I’ve made some changes to the report to address those problems, and you should now be able to see them by generating the report again from staging2. Could you give it a shot and let me know how it looks?

Cheers,
Rohan

thanks @RohanM, I’m happy to have a look but seem to have lost access to the Food Connect Test group? I’ll include @sstead in this response as I know she set it up originally.

thanks,
Malcolm

Hi @mblaney,

I’ve setup the Food Connect Testing enterprise again, you and @lisahill should both be able to see it, and create products for it.

Thanks!
Sally