[ic] Product Table Import development- Automated maitence of product data - Price Updates ?

[Sean Benton]

Hello fello Interchange users and developers ,

I am still very new to using interchange but I am finding my way around 
it slowly ... and i am happy to use the development of my own site to 
give me daily exersises in expanding my knolage and programing skills 
with interchange as i believe its a fantastic base to work on.

There may be others out there that have been thinking along these same 
lines and have implemented something already or having these same 
issues, may be there is already a solution for this, im hoping that we 
will hear from you in this thread and get some ideas to form a standard, 
so the inhancements will benifit all and hopefuly be included in new 
versions.

I hope someone is able to point me in the right direction as I would 
like to know if there are any enhancements already available for the UI 
Product Table Import or development of a maintence program, even 
something that runs from a cron as an examble and manupulates the 
Product data would be interesting to look at , I guess you will get a 
general idea from my notes below.

An Overview of what im looking to implement and acheive - please let me 
know if this is dificult to interperate what im saying.

1. To be able to configure a maitence table that would be able to update 
data in differnet tables and fields at sceduled times, being able to 
implement mathamatical functions such as price updates from a percentage 
field within the product item data tables.
2. To save Import settings for different suppliers, ( UI Product Import 
Utility ) to be able to import product data from external file and cross 
reference field names with field names that may be different in the 
supplied external csv file that also may be on row 3 not row 1 without 
having to modify the source csv file.
3. To add a maitence function that would alow automated downloading from 
a remote url of a csv or txt  product data file to be imported into the 
product database tables.
4. To add a maitence function to automate saving of a csv data file for 
export with the ability to rename fields , include or exclude data from 
several different tables or add fields on the result of combining data 
fields in matmatical functions.

A bit of a run down of my situation ....
I currently get csv or txt data from 4 or 5 different suppliers ( 
distrubutors ) and its a real big exersise updating the catalog products 
and prices as they can change quickly by large amounts from week to week 
and even become unavailable thus not being up to date causes a real lack 
of competitive pricing on that product and an issue each time a product 
is ordered that is no longer available.

Different suppliers have different sku codes for the same products, and 
offen differnt prices most of these products can be referenced by a 
manafacturers Part number which is one of the many additional fields I 
have added to the foundation demo product item fields.

I have thought about a new cross refrence supplier price table called 
"suppliersprice" the table containing the following fields:
 "supplier_code,  suplier_price_current, supplier_price_previous, 
our_sku, their_sku, product_ref,  last_updated_date "

Defined as :
supplier_code = The code we use to reference the supplier just a 
refernce number eg. Bills Wholsale ID: "2230" 
suplier_price_current = The new price for the item ( Our wholesale Cost 
Ex Tax )
supplier_price_previous  =  The previous price for the Item ( Our 
wholsale Cost Ex Tax )
our_sku = Our sku code for this Item
their_sku = The suppliers sku code for ordering from them this item
product_ref = Just for safety a minimal short description added from the 
update to identify that the data for the right product
last_updated_date = the Date our automated program last updated this 
price ( can also be used as a indication to when a product expires from 
a supplier catalog subsuquently a maintence program could check this 
table for very old dates every month and scedule a item to be checked by 
an administrator )

One could simpliy then add a select table in the product item for a 
prefered supplier or have a automated task to select the best source 
price for the item.

A maitence program would then get the latest data files from remote 
supplier servers on a daily or weekly basis via url save the file localy 
with a datestamp .
Then another process or the same process doesnt realy matter would look 
for datestamp change on theses files open the new data file update the 
prices for that supplier adding the new date to the last_updated_date 
field and moving the suppiler_current_price to the supplier_previous_ 
price, checking for new items and also for items no longer listed 
against the previous data for that supplier, new or removed items would 
be flaged  for Admin attention pending their inclusion or removal from 
the live product database.

Administrator could then simpliy review the new " Admin Item Attention 
Table " on a daily or weekly basis and approve or reject the changes 
which would then set the item for a seceduled process .

Ok now you can tell me that this is the wrong way to do this , or its a 
great idea , or even that you cant understand a word of it ....
Maybe there is some thing that already does all of this, or maybe you 
just dont care to comment ....
Well ive only jotted down a few details and thoughts as they have come 
to me, if this is helpful or interesting to anyone else please feel free 
to comment or email me directly  ... Im just looking for some direction 
so as not to do it in a way that is usless to future versions

Regards,
Sean Benton

Neurometrics Australia
http://www.neurometrics.net/


**** Some other Notes  ****
UI product Import - Some new options featurues that would be helpful 
would be  :

1. Adding new items if present but not updating any exsisting records . 
I cant get it to do this at present .
2. Save setup with a name for using again latter including filter sets .
3. Add ability to add conditional syntax to import such as :
   a.  if supplier_name  = "bobs products"  then update else skip
   b.  if new item set inactive = true  inactive_flag = "new product" 
admin_flag = "true"
        ( set new items to inactive and set a reason to new product for 
using as a select field latter )   

***********************