How do I set an id field based on result of other field matching

October 14, 200520 yr

Newbies

I'm new to FM so correct me if I am wrong with any statements or assumptions:

I have 2 tables.

product

cat_id

tmpCat

Category

Categories has unqiue values in 'Category' cloumn.

Products has multiple rows with the same value for 'tmpCat'.

I am trying to set for each row:

'Products::cat_id' = 'Categories::id' where Products::tmpCat = Categories::Category

I want to do this for all rows in Products.

The reason:

yes Products is not normalised as 'tmpCat' should not exist but this is due to an import from a non normalised table that combined both Products and Categories (and others tables not mentioned) together.

I could only import to one table at a time so I imported from the existing table twice ( once for Products and again for Categories ).

I then removed all duplicates from Categories.

I have created a realtionship between Products::cat_id and Categories::id.

In SQL it would be simply a matter of :

UPDATE Products p SET p.cat_id = (SELECT c.id FROM categories c WHERE c.category = p.tmpCat )

thank you in advance for your help.

Edited October 14, 200520 yr by Guest

October 14, 200520 yr

You just need to do a "switcheroo." This is pretty common when you are building from flat-file data. Just as you have a tmpCat field, with the full Category name, you need a temporary relationship, from tmpCat to the Category name in Categories. Assuming that spelling is consistent you should be able to do a Replace (on all Product records), with Calculation, using that temporary relationship, putting the id field from Categories into the Products cat_id field. Then you can delete the temporary field and relationship.

(P.S. In case you don't know, to do a Replace put your cursor in the cat_id field in Products. Be sure to backup first, as Replace cannot be "undone," and it's possible to mess up the field in the entire table.)