Update product

[Con]

I am new to this so be very clear in describing what direction I should go.

What I have is a database for a landscape company. One of the layouts is for "Products". To keep it simple, it has an item #, description, size, price, SKU# and supplier name.

One of my suppliers sends me a excel spreadsheet with the SKU#, name, price and size for the plants available that year. It has about 2500 plants on it.

In the spring I need to update my prices for the next year. I need to import the new prices, identify the new products, and identify the products that are not available that year. Sometimes they may not have that plant that year but will have it the next year. I identify those products with a check box field called “Active”.

Need some detailed help in how I should do this.

Thanks in advance

Con

[Fenton]

There is an option during an Import to Match Records in found set. You would therefore want to Show All Records first. Import Matching allows you to choose a match field. In this case the SKU# (which I assume is unique for each record, and consistent from year to year). If you click again on the SKU# field (after aligning up the fields, hopefully with Matching Names), the arrow "->" will change to equals "=".

Also choose the option (lower left) to import New records. So those that do not match a SKU# will come in as new records. Then Import. If you have any auto-enter fields, then leave that option in the 2nd dialog checked, otherwise not.

Your found set after Import will be your updated existing matched records, with new prices, etc., and the new records. Show Omitted Only will show the records that did not match nor are new; possibly obsolete.

Be sure to save a backup of your file before you try this. You should build the above in a script. It is the same as a manual Import dialog, but it saves your settings, and is really easier to work with.

[P.S. The above assumes your data is in the same format as the spreadsheet.]

Edited February 20, 2009 by Guest

[Con]

Thanks Feldon

Everything went great. The only thing that I don't know what to do is , the new plants that were imported didn't auto-enter the serial number for my product ID number. Is there a way to auto-enter serial numbers for the new products?

Thanks

Con

[Con]

I meant Fenton.

Con

[Fenton]

That's why I said, "If you have any auto-enter fields, then leave that option in the 2nd dialog checked, otherwise not."*

I did not know if you had your own auto-enter serial ID. But I applaud you for doing so, as it is the best practice.

All you have to do is find out what the highest one you already have is. Then Find only the new blank ones. The put your cursor in your ID field (it must be enterable and editable for this, so best to have a non-visible "developer" layout for this kind of thing).

Go to the Records menu, Replace Field Contents. Choose the option to Replace with serial numbers. Put the NEXT serial number as the starting value. Be sure the [x] Update serial number option below is checked. Then hit Replace (not Cancel, which is the default).

Always back up your file immediately before using Replace. It cannot be undone, and can quickly ruin your data (imagine setting all your serial IDs to the same value, for example).

*Sometimes you might want to leave that off even if you do have auto-enter serial IDs, as other auto-enters may retrigger and change data you have custom modified.

Edited February 21, 2009 by Guest