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

In article
<3257baec-294c-48fb-909e-4545faaa7a2a@v7g2000yqf.googlegroups.com>, JayBee
<Jennifer_Beecroft@hotmail.com> 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.


ALWAYS, ALWAYS MAKE A BACK-UP FIRST!
Then if things do go wrong, you can restore the back-up.


It depends how your prices are "looked up".

If the prices in the Sales Records are normal Calculation fields, then
they will most likely copy the new prices no matter what you do (including
manually changing the product records).

If the prices in the Sales Records are normal Text or Number fields using
an auto-enter lookup or auto-enter calculation option, then when you do
the Import, there's an option to perform auto-enter calculations, lookups,
etc. Turning off that option should stop FileMaker changing the prices in
existing records (although if you're importing into a separate Table or
file, then they shouldn't re-lookup anyway).

Bearing in mind that we have no idea how your database system is set-up,
there should be no problems deleting the original manufacturer records and
importing the entire new file each time. (Of course, if the Product Codes
keep changing, then it will confuse staff, unless they're using barcode
scanners.)

Helpful Harry  :o)

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.

In article <j7nsp3$gkc$1@speranza.aioe.org>, cortical
<cb@corticaldata.com.au> wrote:
> 
<snip>
> 
> 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.

Even when price lists are done on a database, they have to be exported to
something compatible with other systems, which usually means either a TXT
or CSV file ... and often the CSV file defaults to opening with Excel so
is seen as a spreadsheet when it really isn't.

Helpful Haryy  :o)

On Wed, 19 Oct 2011 15:12:05 -0700 (PDT), JayBee wrote:
>  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.

I do this frequently.

My solution is to have a dedicated import table. There I do whatever is
needed for comparison - you could match article and price and remove
everything which remained identical. You then may change old prices and
tag them as invalid and import new prices from the import table to the
price list.

And even more important: you may have to add new article IDs.

Thus you shold have at least three tables:

1) an import table
.... where all records could be deleted after processing, or could be
kept for an import history

2) an article table

3) a price table

I recommend to keep 2 and 3 separately.

You can automate most of it, where you only have to choose the excel
source file (and even this could be automated).

- Martin

On 20/10/11 1:40 PM, Your Name wrote:
> In article<j7nsp3$gkc$1@speranza.aioe.org>, cortical
> <cb@corticaldata.com.au>  wrote:
>>
> <snip>
>>
>> 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.
>
> Even when price lists are done on a database, they have to be exported to
> something compatible with other systems, which usually means either a TXT
> or CSV file ... and often the CSV file defaults to opening with Excel so
> is seen as a spreadsheet when it really isn't.
>
> Helpful Haryy  :o)

Missing the point Harry. It is common form some sources, that in the 
same price list spreadsheet, some product rows are 5 column, some 6 
column, some 7 column

They merge different fields... the 6th column in one row may be the cost 
price, in another category of product, it might be the 5th becuse 3 and 
4 have been merged,  so as imports use the standard delimiter of tab for 
field, pilcrow for record, a compression occurs, the merged fields are 
seen as single fields


c1 c2 c3 c4 c5  >> f1 f2 f3 f4 f5
c1 (c2 c3) c4 c5  >> c2 in f2, c4 in f3, c5 in f4

In article <j7p1oi$54q$1@speranza.aioe.org>, cortical
<cb@corticaldata.com.au> wrote:
> On 20/10/11 1:40 PM, Your Name wrote:
> > In article<j7nsp3$gkc$1@speranza.aioe.org>, cortical
> > <cb@corticaldata.com.au>  wrote:
> >>
> > <snip>
> >>
> >> 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.
> >
> > Even when price lists are done on a database, they have to be exported to
> > something compatible with other systems, which usually means either a TXT
> > or CSV file ... and often the CSV file defaults to opening with Excel so
> > is seen as a spreadsheet when it really isn't.
> 
> Missing the point Harry. It is common form some sources, that in the 
> same price list spreadsheet, some product rows are 5 column, some 6 
> column, some 7 column
> 
> They merge different fields... the 6th column in one row may be the cost 
> price, in another category of product, it might be the 5th becuse 3 and 
> 4 have been merged,  so as imports use the standard delimiter of tab for 
> field, pilcrow for record, a compression occurs, the merged fields are 
> seen as single fields
> 
> c1 c2 c3 c4 c5  >> f1 f2 f3 f4 f5
> c1 (c2 c3) c4 c5  >> c2 in f2, c4 in f3, c5 in f4

Yes, spreadsheets can be a complete mess thanks to people not really
knowing how to use Excel, or the same sheet being re-used numerous times,
etc.

But you said there needed to be a database for those who assemble price
lists (maybe I misread that as being the manufacturer when you meant the
person at the FileMaker end), and my point was that databases are already
used by most big companies when making their price lists. The problem is
the databases aren't compatible with every sales company database, so the
price lists have to be exported as Text or CSV files ... but of course in
these cases there will be now merged cells.

Helpful Harry  :o)

Thanks for everybody's answers.  All very valid.  I am slowly filling
in a manufacturer column for the pricelist database in question.  Once
that's completed I will search on 'manufacturerx' , delete all of
those records, then import the file with the newly revised prices for
manufacturerx.

Bear in mind this is the first pricelist that I have imported to
Filemaker.

For the future though Harry I will probably as you say delete and re-
import the tables each time, as and when needed.

+ thanks for your tips on the recalculating malarky Harry.  The sales
records are displayed and stored in a completely different table, so
thanks for confirming that those records shouldn't be affected/
recalculated when pricelists are deleted and re-imported.  The price
is auto-entered using an auto-enter calculation option based on the
product code that the user enters.