Groups | Search | Server Info | Login | Register

Groups > comp.databases.filemaker > #438

Re: Import File.....

Show all headers | View raw

On 20/10/11 8:42 AM, JayBee wrote:
> Hi
>
> I have imported several Excel files (one per manufacturer) into one
> product database.  Example fields are: manufacturer, Product Code,
> Description and Price.  Several important sales records in a sales
> table now refer to this information e.g. product code is put in to one
> field on the sales record and description and price are looked up and
> inserted automatically on each line of a sales record.  As these are
> all individual fields the look up is done by 'n' table ocurrences for
> 'n' number of invoice lines.
>
> The predicament I now have is that the prices from one of the
> manufacturers has now changed, but not by any set rule.
>
> Without deleting the whole product table and re-importing everything
> (because this would affect the sales records already created) how can
> I import the new price column and ensure that each price will be
> matched to the correct record?!  The thing that makes me nervous about
> this is that the newly imported prices could be mis-matched between
> records.
>
> This is obviously a very important maintenance question from the point
> of view importing pricelists to a Filemaker database; prices change
> frequently and it would be good to know the best way of handling these
> price changes this from other developers' point of view.
>
> Many Thanks in Advance
> JayBee


manufacturer prices lists in Excel are the biggest pita I have ever 
encountered. I have one ongoing project for Air Tool brands ( 20 or so 
lists) ... 50,000 parts from 176 brands.


1. backup your working data file

2. Design
<< As these are all individual fields the look up is done by 'n' table 
ocurrences for 'n' number of invoice lines.>>

This does not sound right. Iy sounds like you have a non-normalised 
structure, and that you  have a REL/TOC for each invoice line item. Any 
time there are 'multiple' fields, these should be in a related line 
items table.




2. HISTORICAL INTEGRITY

 > Without deleting the whole product table and re-importing everything
 > (because this would affect the sales records already created) how can

This is why the price at sale time etc should be hard coded into the 
invoice record, and not as a related field. If it is not, i.e. it is 
related then the relationship has to include a parameter for the price 
list edition, AND you can NEVER delete item prices.

I include a price_list_edition field in the parts table.
Invoice lines etc are hard coded ( scripted) with the price...

By taking this approach, any items not in te current price list are 
redundant; If the manufacturer does not provide a price, they are 
presumptively not available; so they could conceivably be delete.

eg, prior to import of the current price list, delete all item records 
for the particular brand.

However, there may still be an inventory of the redundant/superceeded 
parts. And here is another layer - superceeded, a mechanism is also 
needed to deal with these.

If there is an inventory of non-current price list parts, then the item 
records must be maintained. This requirement can vary between 
brand/source.

Here again a price list edition field is invaluable. Import matching 
against existing part number and BRAND ( the same vendor/manufacturer 
item number CAN exist between brands); particularly where a supplier 
will supply a major brand item, plus a clone brand knock-off of the 
major brand item.

Thus the import will update the existing matched part number records, 
and add the new items. Use 'replace' to update the price_list_edition 
value on the import result set. This way the historical items are 
maintained, with there earlier price list editions. These can be found 
if required, and the sell prices updated by an applied percentage, if 
required ) old stock on hand is arguably more valuable, as it is 1) nla, 
and 2) it has added cost attributed to holding over time ( the same 
money to but the stock could have been in the bank earning interest) .




<<
Without deleting the whole product table and re-importing everything
 > (because this would affect the sales records already created)
 >>

This is telling you you have an historical integrity issue, and that 
your current structure/methods is/are not correct.

The other thing to watch is that price lists can vary from edition to 
edition, in terms of structure, so in my experience scripting imports 
even for a particular brand, is futile.

Excel is hammer and chisel technology, and never forget the level of 
computer skill of the database virgins who assemble said price lists. 
Expect typos and duplications in the part number - which will be acting 
as the defacto import key.  Check and scrub.

Do not expect the column that holds the cost price in the initial spread 
sheet rows, to hold the same data all the way down... If ever there was 
a market niche that really needed database, it is those who assemble 
price lists.

Back to comp.databases.filemaker | Previous | Next — Previous in thread | Next in thread | Find similar

Thread

Import File..... JayBee <Jennifer_Beecroft@hotmail.com> - 2011-10-19 15:12 -0700
  Re: Import File..... yourname@yourisp.com (Your Name) - 2011-10-20 13:21 +1300
  Re: Import File..... cortical <cb@corticaldata.com.au> - 2011-10-20 11:46 +1030
    Re: Import File..... yourname@yourisp.com (Your Name) - 2011-10-20 16:10 +1300
      Re: Import File..... cortical <cb@corticaldata.com.au> - 2011-10-20 22:17 +1030
        Re: Import File..... yourname@yourisp.com (Your Name) - 2011-10-21 09:14 +1300
          Re: Import File..... JayBee <Jennifer_Beecroft@hotmail.com> - 2011-10-24 15:07 -0700
  Re: Import File..... Martin Τrautmann <t-usenet@gmx.net> - 2011-10-20 04:41 +0000

csiph-web