Creating base Product Import sheet from existing products and variants [Zapier]

[Kirsten]

Hi people

I am working on a zap to get active product/variants into a google sheet in the correct format to be used as a base for the product importer, enabling existing users with lots of products to start using this as their base product handling.

Given my technical limitations, my current plan is to create this zap, and then just turn it on for an hour or so at an agreed time, while they go and edit the variants that they want to get picked up. then the sheet becomes their ‘source of truth’ and doesn’t get updated again from OFN

[NB. If I can wrangle someone to write me a query that just fetches all the variants we need that we can trigger whenever we want that would obviously be better as then I could just run it] but for now working with this

So i’m almost there, but missing some critical fields - particularly PRICE and ideally on_hand and on_demand - can anyone help me find these?

This is what I have so far

I get the Spree_Variants table for edited variant

Then I use the Product_ID returned from that to lookup the Products table

Then I use the Supplier_ID to look up the Enterprises table

Then the Primary Taxon ID to get the Product Category

and I get the Shipping Category

and then perhaps tax - will come back to that

I am getting the option_value via the variant_ID and have two further lookups to get me 1kg. This isn’t quite right but I think I’m on the right track . .

So then I am setting up my ‘Create Spreadsheet Row’ which is all good and working (needs more fiddling with different variant types get everything in the right place)

Where can I find values for these critical fields? They are not in any of the above tables that I can see?

i.e. where it actually says g or kg, price, on_hand or on_demand

The most important missing data here is the price … I think we can work with everything else (as this user mostly uses on demand), but it would be really annoying to have to cross-check to get all the current prices.

Any advice on where I can get the price on a variant pretty please!! @maikel @luisramos0 @sauloperez @Matt-Yorkley

FYI @tschumilas @lin_d_hop @Rachel as people who might be interested in trying to do something like this

NB. This is a lot of tasks and will be quite zap expensive. Preferably only ever run it once for each user and likely need to charge them to do so

[Kirsten]

hmmm, outside of test zone this isn’t working - which makes me wonder if spree_variants is totally the wrong table, as in when I make a change to a variant it doesn’t seem to be picking up at all . . hmmm

[tschumilas]

This would be SOOOOOO amazing! As I understand it (correct if I don’t get this) - a user could/would decide to manage their products via spreadsheet using the import tool. I (super admin) could get them started by doing this zap to get them their source of truth spreadsheet to start. Then, as long as they don’t do any editing on-line in their product list, this will always be their source to make changes. People will love this. Frankly - I would probably learn to run it, and then I’d do it for users and just send them the spreadsheet (easier that way, I’m guessing - unless this is really easy to run.,) I’m just not confident in my ability to replicate it here - but maybe its something that can be transferred in some way? Big kisses on this one!!!

[tschumilas]

One question - what products would it pull? Would it pull all products the user has permission to access (so for a hub - it would pull all supplier’s products) Or would it only pull products that have had stock assigned? (Several bigger users here with 300 plus products, have ALL their season’s products entered at the beginning of the season, and then they just set stock to control what shows up in shops. They would want the full product lists offline to manage, not just currently active products with stock.)

[Kirsten]

I think all . . given my limited / non-existent tech skills I’m working with a pretty clumsy idea of how to trigger it!

[maikel]

Is it a producer or a shop enterprise? Do they use inventory?

By default, prices are in spree_prices but they can also be in variant_overrides. You can find a code representation of the database structure at https://github.com/openfoodfoundation/openfoodnetwork/blob/master/db/schema.rb.

I’m worried that this is an example where Zappiness is more expensive than building in OFN.

[Kirsten]

I am very confident that this is a case where zappiness is more expensive. But I have ability to work with zappiness!

[luisramos0]

hello!
This is a good example of why we want to rollback the overrides concept in OFN! Currently there’s no way we can build a simple query to fetch a list of an enterprise products with price and stock levels because to do that you need to consider “overrides” and doing that is never straight forward…

I dont think you want to go this way… but I can guide:

• prices - join spree_variants with spree_prices done, right? wrong, if there is an override in variant_override table, then price is variant_override.price if present, otherwise, use spree_prices
• stock - join spree_stock_items with spree_variants done, right? wrong, if there is an override in variant_override table, then stock is variant_override.on_hand and variant_override.on_demand, right? wrong! If variant_override.on_demand is nil than the stock is the one in spree_stock_items.

This is THE main core problem in the OFN data model and I think we should fix this when we build the Network Feature by simply making overrides become normal variants (spree_variants) with a reference to “source” which would be another variant. As we build this we can deprecate and delete variant_overrides as it exists. This was discussed in the last gathering and everyone agreed in general with the approach.

[tschumilas]

Hesitant to contribute here - not totally sure I understand, but I just want to make sure we consider in this that there will always be the need to get a report with both the price set by the producer AND the price (with overrides) the product was sold for. The second prices isn’t simply the first price plus fees. (Because in over-riding it, a hub, essentially, sets a new base price. But we still need to be able to grab the producer-set price in order for suppliers to create invoices to the hub, and for the hub to calcuate and track their surplus.

[luisramos0]

yes @tschumilas that makes sense and it should be possible to do that both currently and in the new structure.

[Permakai]

We use a pivot table - easy, quick and customisable. But maybe we are at cross purposes?

[Rachel]

@Kirsten in your experimentation did you see if it was possible to extract the tax_category easily?

[Kirsten]

I’m sorry I don’t remember - I have been a bit slack on tax analysis because it is so rare for a food product in aus to have to deal with it. I can’t imagine it would be easily - I think perhaps the Xero report should have it?

[Kirsten]

yep @Rachel just confirmed that tax is correct in the Xero invoices report - but i don’t recall getting that far in my zapier exploration. I think it would be on the product . . Just occurred to me from my learning curve (slow but sure) it is entirely likely that this info can be extracted using the current api. I just checked out https://openfoodnetwork.org.au/api/products/bulk_products - try being logged in as the enterprise you’re interested in and have a look at this. It seems to have tax information in it . . I haven’t tested with a product that actually has tax @Rachel

Could we perhaps build a product import thing relatively easily with this and a couple of lookup tables for taxonomy and producer from ID?

[Rachel]

my learning curve is super slow as well

@Kirsten

yep @Rachel just confirmed that tax is correct in the Xero invoices report

Well actually not on the French instance, that’s why I came here and asked you. I don’t know exactly what the Xero report is showing: it’s says “taxtype” in the title. But this is not the tax category…

[Kirsten]

hmmmm ok, bugger . .

[tschumilas]

I just looked at the zero report in OFN_CAN, and the "tax type’ column is populated with the phrase - Tax Free Income - regardless of the tax category of the product. so - yes… bugger.