Best approach to make sure retrieved online data goes into correct field in Access

[peskywinnets]

I sell on Amazon & am slowly kludging together some code to use their API to pull in stock levels (I give them stock & they store it in their warehouse...so I need to know how much stock they hold for me) ...but I would like to know what the best approach is to make sure that the returned stock levels goes against the correct SKU (product code) within access, else it would be catastrophic.

So to retrieve the information, I have an access table that I step through ...Amazon give me the option to request up to 50 SKUs at a time (it's better to request as many SKUs as possible at a time, as they aren't keen on you doing lots of requests - they throttle you back if you do). So I request the data in chunks of 50 Products (I have sell about 140 products, so it'l;l be 3 separate 'chunks' requested)

Now then, if I were retrieving one SKU at a time, then it'd be easy matching the requested data against the retrieved data (it'd be 1-1), but if I'm requesting 50 SKUs, the XML is going to be returned with 50 SKU's stock levels within....so what the best way of handling that?

For example, let's say I'm requesting stock levels for...

Product A
Product B
Product C

Amazon sends me back the XML (which I'll simplify) as

Product A 50
Product B 5
Product C 1

I need to make sue that those quantities go into the right quantity field for each product in Access.....it needs to be robust.

My thought is to store the returned data into a temporary 'holding tabl'e (populating it in 3 chunks with the returned data), then afterwards run an update query to update the quantity in my permanent table (linking the temp table & permanent table by SKU name)...at least this way I can be assured that the right stock levels have gone against the correct SKU. Plausible or would there be a more standard way of doing this?

Sorry if it appears a basic question - I'm still fairly new to VBA & particularly VBA usage within Access.

 

[Gasman]

Not an expert here, but intrigued with the xml aspect as I have need of constructing some xml for a phone directory for softphones.

Seems a bit of tweaking would need to be done if importing directly to access. Using Excel as an intermediary step *might* be eaiser?

https://www.aspfree.com/c/a/xml/importing-xml-files-into-access-2007/

I would certainly be importing to a temp table first and then updating the main table after some basic checks.

HTH

 

[peskywinnets]

Thanks...your link seems to be more to do with importing an XML file (on disk) into Access ....I'm actually pulling it live from Amazon & not saving it to a file ....but regardless of where the XML is sourced from, it's the (high level) steps involved once I have the data to ensure nothing goes awry!

 

[Gasman]

Ah, I see. I'd be saving the data anyway, so I could always go back to it. Perhaps with a date in the filename.
Sorry I could not help.

 

[jdraw]

...........I'm actually pulling it live from Amazon & not saving it to a file....

You must be storing the data somewhere if you using the data to support your Inventory Control.
But please step back a little and give us some additional facts.

In overview your Access application is in an interactive mode with Amazon.
How and when do you record what you have sent to Amazon?
Do you get some confirmation that Amazon received and holds the "goods"?
When there is a sale do you get immediate notification from Amazon or do they batch the sales info and report to you on some frequency (weekly/monthly)?

I have not worked with an Amazon api, nor gave I looked at the api, but I did create some routines to interact with a Yahoo Finance api, a European currency exchange and a weather api. This was more demo than operational.

Perhaps you can show us some of what you have.

Good luck with your project.

 

[peskywinnets]

You must be storing the data somewhere if you using the data to support your Inventory Control.

MS Access 'go gets' the data live from Amazon (using a signed http request) & stores the returned XML in memory. It's how to process that 'retrieved data' & update an Access table in a robust way that I'm wondering about.

How and when do you record what you have sent to Amazon?

That's just it, rather than recording what I've sent (though I do this in a locally stored computer file - not related to Access), I'd rather just retrieve the actual live data from Amazon.

Do you get some confirmation that Amazon received and holds the "goods"?

yes but again, that's extra information that I don't need - all I require is what stock they are holding & what's on the road (on its way to their warehouse). Amazon hold this information & can be access via their API. I know how to pull it back, but it's how to handle it from an Access perspective that I'm pondering.

When there is a sale do you get immediate notification from Amazon or do they batch the sales info and report to you on some frequency (weekly/monthly)?

I handle sales in a different way...all I need to know is what stock Amazon hold for each product (fwiw, when they make a sale, the stock gets deducted at their end)

I think how I'll approach this, is going to be loosely via Access VBA....

1. Delete any existing file (from a previous retrieval)
2. Create new file (to hold new data)
3. Request the stock level data from Amazon ...pipe the returned data into the newly created file (basically SKU & quantity info)
4. Create an Access 'linked table' to the newly created file
5. Run an Access update query (linked on SKU to the main Access table) to update the latest quantity.

At least then I can be assured that nothing went awry with writing the data out of memory into the main access table....resulting in the wrong product code having another product's stock level!

 

[jdraw]

Take a look at the Yahoo finance demo I mentioned in the previous post. There may be some ideas/concepts that you could use. I get data from Yahoo into a local table.

It sounds like you are only concerned with current quantities of product at Amazon. What about sales/finances etc.? I'm not trying to be nosy --just curious.

 

[peskywinnets]

Take a look at the Yahoo finance demo I mentioned in the previous post. There may be some ideas/concepts that you could use. I get data from Yahoo into a local table.

It sounds like you are only concerned with current quantities of product at Amazon. What about sales/finances etc.? I'm not trying to be nosy --just curious.

I use a 3rd party app to handle amazon sales orders (this 3rd party app knows what stock amazon has, but doesn't know what's on a van the road to them from me... this latter part is key to keeping the stock pipeline running smooth - hence having to 'go get' from Amazon into Access)

I export all my sales data from this 3rd party App into Access (so in essence I'm using the this 3rd party App like a virtual MS Access to Amazon API!)

 

[jdraw]

Is there any way to get "your stock en route to Amazon" into the 3rd party app? It seems the 3rd party is dealing with Amazon sales transactions and stock level drops, and only allows export of info.

 

[peskywinnets]

Is there any way to get "your stock en route to Amazon" into the 3rd party app? It seems the 3rd party is dealing with Amazon sales transactions and stock level drops, and only allows export of info.

That would be the way to go, but they aren't receptive to my requests....it's the perennial problem with using 'off the shelf' solutions, hence having to roll my own workaround.

 

[jdraw]

Can you provide some of the data you get from Amazon-- just enough records to understand the format and maybe mock up some options?

Which API are you using?
I see these on their site:
Feeds-Version 2009-01-01
Reports-Version 2009-01-01
Finances-Version 2015-05-01
Fulfillment Inbound Shipment-Version 2010-10-01
Fulfillment Inventory-Version 2010-10-01
Fulfillment Outbound Shipment-Version 2010-10-01
Merchant Fulfillment-Version 2015-06-01
Off-Amazon Payments-Version 2013-01-01
Orders-Version 2013-09-01
Products-Version 2011-10-01
Recommendations-Version 2013-04-01
Sellers-Version 2011-07-01
Subscriptions-Version 2013-07-01
Push Notifications Schemas

 

[peskywinnets]

The API I'll be using is called ListInventorySupply (which is part of the Fulfillment Inventory-Version 2010-10-01 collection)

Here's some sample XML they return....

<?xml version="1.0"?>
<ListInventorySupplyResponse xmlns="http://mws.amazonaws.com/FulfillmentInventory/2010-10-01">
<ListInventorySupplyResult>
<InventorySupplyList>
<member>
<SellerSKU>SampleSKU1</SellerSKU>
<ASIN>B00000K3CQ</ASIN>
<TotalSupplyQuantity>20</TotalSupplyQuantity>
<FNSKU>X0000000FM</FNSKU>
<Condition>NewItem</Condition>
<SupplyDetail/>
<InStockSupplyQuantity>15</InStockSupplyQuantity>
<EarliestAvailability>
<TimepointType>Immediately</TimepointType>
</EarliestAvailability>
</member>
<member>
<SellerSKU>SampleSKU2</SellerSKU>
<ASIN>B00004RWQR</ASIN>
<TotalSupplyQuantity>0</TotalSupplyQuantity>
<FNSKU>X00008FZR1</FNSKU>
<Condition>UsedLikeNew</Condition>
<SupplyDetail/>
<InStockSupplyQuantity>0</InStockSupplyQuantity>
</member>
</InventorySupplyList>
</ListInventorySupplyResult>
<ResponseMetadata>
<RequestId>e8698ffa-8e59-11df-9acb-230ae7a8b736</RequestId>
</ResponseMetadata>
</ListInventorySupplyResponse>


the values of interest to me, are the ones entitled TotalSupplyQuantity & InStockSupplyQuantity, subtract the latter from the former & you then know the quantity on a UPS VAN en-route to their warehouse from me.

 

[jdraw]

Not sure where you are with this at the moment.
I copied your XML, and added 2 more <member> data records in the xml.

I used the Access XML importer since it is element centric. It appears that the table of interest is
Member. It has the info needed to determine stock level in the UPS Van.
This is the imported data and the table created by the Access Importer.
table is Member

SellerSKU ASIN TotalSupplyQuantity FNSKU Condition SupplyDetail InStockSupplyQuantity
SampleSKU1 B00000K3CQ 20 X0000000FM NewItem 15
SampleSKU2 B00004RWQR 0 X00008FZR1 UsedLikeNew 0
SampleSKU3 B00004RWZZ 10 X00008FZR6 Immaculate 3
SampleSKU4 B00004RXAA 20 X00008FZT3 Crumpled 10

You could add a date field representing the ReceivedDate of the info from Amazon.
That way you could keep the history. You could use the ReceivedDate to constrain a query.

The other tables:
EarliestAvailability
ResponseMetadata
--may be of peripheral interest.

 

[peskywinnets]

Not sure where you are with this at the moment.
I copied your XML, and added 2 more <member> data records in the xml.

I used the Access XML importer since it is element centric. It appears that the table of interest is
Member. It has the info needed to determine stock level in the UPS Van.
This is the imported data and the table created by the Access Importer.
table is Member


You could add a date field representing the ReceivedDate of the info from Amazon.
That way you could keep the history. You could use the ReceivedDate to constrain a query.

The other tables:
EarliestAvailability
ResponseMetadata
--may be of peripheral interest.

Thanks.

The project is moving quite slow at the moment ...I'm having to relearn what I learnt about 8 months ago (how to construct the request URL in a format Amazon accepts, then CSHA256 sign the URL ....each of their API call URLs needs to be constructed slightly differently!)...that said, I've now got a an Access initiated request working & pulling back XML from Amazon (next step - relearning parsing XML using Access VBA!)

I only sell new items on Amazon, so not interested in the condition field, but you make a good point about the date...I will capture that - thanks!

 

[jdraw]

Try the xml importer. you can save the steps and rerun for each new session.

 

[MrHans]

What xml reporter are you referring to?
Is this included in Access?

 

[jdraw]

Importer not reporter

Use Access XML importer since it is element centric.

See attached jpg. I am using Access 2010.

External Data-->XML....then follow the prompts.

 

[MrHans]

Ahh right ok, but then you need to have a local file and follow the prompts.
I was hoping on a solid automated XML importer, and preferably one that handles XML HTTP responses. Anyway, not related to this thread

 

[peskywinnets]

I tried the XML Importer, but it needs the XML in a file (not a big problem)... BUT also imports all the elements (even if they aren't wanted) & the only options provided is create new tables (you end up with a lot of new tables due to the way the wizard imports) OR to append to an existing table...I worry about bloat if going this way), therefore I'm pushing on doing this longhand.

1. request the XML
2. Parse the XML to a text file in CSV format
3. Create an Access linked table to the new CSV file created
4. Run an update query with my CSV filed (Linked Table) linked on SKU with my main table.

It's a bit of a bind, but at least this way I won't get database bloat & can be sure that the correct product quantities are ultimately updated in my main Access table (because everything is linked nicely on SKU name)