Lookup Fields through relationships

I encountered an issue today that I'm reflecting on a bit. The DB I manage has been around for a long time now and has been in hands of various developers. The original DB started as a FM Starting Point and was changed along the way until the guy left the company. Another developer started redeveloping the solution copying various structures from the original solution, and essentially making a bigger mess. Then came along me, fresh, never played with FM before and took it on board (this is now some 5 years ago, if only I had the time to write a book what I learned).

This week I started developing a Production Dashboard where production staff can see the various products that have been released for production. Customers purchase products which are listed on an invoice as lineitems and have links/relationships to the product selected.

When an invoice is created and lineitems are added the price on each lineitem is stored in a number field and only ever manually edited if needed.

The Production Dashboard highlighted an issue with the products table and the lineitems table. The ProductID in the product table was a number field, and the ProductID in the lineitem table was a text field. Some ProductID's in the lineitem table had 01, 02 etc, where the ProductID in the products table had 1 or 2. As a result, no relationship. No problems I thought, and changed the ProductID in the lineitem table to a number field. That didn't change the 01 into a 1 though, and the relationship was still broken.

Then I opted to replace field contents in the lineitem table for all items with a 01, 02 etc to a 1, 2 etc. At that point I actually created new relationships as it turned out and that had an effect on lookup fields as I found out.

Price info is copied from the product table into the lineitem table using a lookup field. Unfortunately, the price in the product table is only a guide, and the final price often varies. But when I changed the ProductID in the lineitem table and replaced it with a proper number (ie, 01 to 1, etc), I inadvertently also triggered a new lookup to occur. As a result, pricing for a lot of items (some 150.000..., there's about 900.000 lineitems...) were changed.

I managed to restore original prices from yesterdays backup so in the end it was more a learning experience than anything else.

However, I am now questioning the usability of lookup fields. The same can be done using a number field with an auto-calc, and that allows you to prevent changes once the field has a value. Unlike lookup fields.

I'd be interested in other's opinions of lookup fields vs auto-calc fields. Another approach (in this case) would be to only copy values through a script, but with the history of this DB, there's so many of them.... Most of them weren't even stuck in a folder structure. I've created a folder structure a longtime ago and am slowly going through the scripts, replacing them where needed and moving them as required.

Anyway, I hope you enjoyed the story. My saving grace was the backup. That enabled me to restore the altered info quickly.