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 (AT) v7g2000yqf (DOT) googlegroups.com>, JayBee
<Jennifer_Beecroft (AT) hotmail (DOT) com> wrote:


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 )

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

On 20/10/11 1:40 PM, Your Name wrote:
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 (AT) speranza (DOT) aioe.org>, cortical
<cb (AT) corticaldata (DOT) com.au> wrote:
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 )

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.