Jump to content

Lookup Fields through relationships


This topic is 1989 days old. Please don't post here. Open a new topic instead.

Recommended Posts

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.

Link to comment
Share on other sites

This topic is 1989 days old. Please don't post here. Open a new topic instead.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.