[ic] Product Table Import development- Automated maitence of
product data - Price Updates ?
manager at bgsn.net
Fri Aug 27 21:25:00 EDT 2004
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
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
**** 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 )
More information about the interchange-users