Good morning c1,
Thanks for the post.
Inventory is the primary use for FMP for me. After playing with different scenarios, I settled on:
Individual lot# inventory table
The product layout has a portal of individual lot#'s of that particular product, linked by product name.
The Lot# layout has a portal of the transactions for that lot, linked by hidden uniqueID.
Each relationship is a one to many relationship, and the portals help to view this.
Every product has its own record in the Product table
Every lot of every product has its own record in the lot# inventory table.
Every transaction has its own record in the transaction table.
Hope this helps to get the juices flowin'...previous prior planning prevents poor performance.
and the direct answer to your question...IMHO if the customer cares what color they get...then a different color is a different product...make them different records. It'll save you headaches doing backtracing later.
Thanks for the reply! When I was experimenting, I started making each different product, color a record of its own. That seemed easier to understand for me.
Then I was looking at the database templates and tried the one on invoicing and thats how I come to know about repeating fields. That seems suitable too, wouldn't it?
I can start off quickly using the earlier method of different records of each colour but just want to be sure and not regret not using repeating fields later.
Hope to you can provide more insights to it.
Thanks in advance.
You could make repeating fields work as long as you don't end up with something like 579 repeats in the field. I personally would recommend against it though (take this as the opinion that it is...I figure its a style thing for the most part).
As you get very comfortable with table relationships, you'll find them to be more powerful and easier to maintain over time. I personally would use a related table with a one to many relationship rather than a repeating field. Perhaps I simply understand related tables better than I understand repeating fields.
I run two main dbases for inventory (Raw materials & Finished Goods). I have ~4000 finished goods with ~6000 individual lots on record at any given time, and 2000 raw materials with ~2000 lots...total of ~30,000-60,000 transactions per year.
I do not have a single repeating field...can you imagine a repeating field with 2000 repetitions...gives me the shivers.
Again, it may be more of a style thing, and it would certainly be affected by how much info you need in the field...I can see how repeating fields might be a benefit...but I can't see how they would offer what I cannot also get by the related table.
My opinion, go with the related tables. Once you're comfortable with them, I don't think you'll regret the choice.
Please, please DO NOT use repeating fields. Even if you can get them to do what you want (which I doubt), at some time you WILL be sorry!
Ninja and Scott,
Thanks for the advice! I will go ahead without using repeating fields.
What about database for managing invoices, should I not use repeating fields as well?
Same answer. NO!
You never know how many repeats you will need. As soon as you set it to 10, you'll need 12. And there are issues with finding and sorting repeating fields.
Just make an "Invoice Lines" table with an invoice_number field that you can use to link to the Invoice table. No limitations, lots of flexibility, no regrets.
Thanks for the agreement Scott,
c1, just like each product has many lot# records in another table...
and just like each lot# has many transactions in another table
so should your invoice have many line items in another table.
Repeating fields...I wouldn't, I haven't, I won't. I'm sure they have a good use, I'll trust the FMP folks to have put them in for a good reason...but I haven't found that reason yet.