Multiple fields or repeating fields?
I am writing a database to generate customer quotations.
There will be up to 20 items (occasionally more) on each quotation.
Once an item reference is input I need to lookup its cost price, list price, description, manufacturer etc I then need to make optional calculations based upon whether the user wants to apply a mark up or discount or enter a sell price and have the margins calculated etc.
My dilemma is do I use repeating fields or do I have a different fields for each line.
I think, I was told a long time ago (I've used filemaker since version 4) to avoid repeating field as referencing the individual items in them is problematic but if I use individual fields for each line item I will have to make loads of relationships.
Ie line1_item_lookup:: line1_Price …. line20_item_lookup:: line20_Price
line1_item_lookup:: line1_cost …. line20_item_lookup:: line20_cost etc
Could anyone point me in the right direction?
I don’t mind making loads of relationships if that’s the best way.