Better than repeating fields...?

[mminer]

Happy Valentine's!

My use of FMP likes repeating fields (or some version of them) because, in an invoice, I can put one color of an item on a line and show it in all its available sizes. The layout is very readable and data entry is quick as you only need to input the color when and order is placed. The size fields appear automatically.

However, I've heard and read that repeating fields are to be avoided.

Can anyone tell me how to avoid repeating fields and still:

a) enter data quickly, without needing to define each color and size for an order item?

show all available sizes of a particular color on one line?

Thanks!

p.s.: This post is sort of a take off of my previous post: "Inventory transaction table with colors & sizes" if you'd like more background.

[dkemme]

This can be done with a few relationships. I'm imagining two tables. Stock which is the available stock to order with a field Color and then Size. If you have ten size choices for the color red, make ten records with Red and then each available size. Another table called Orders and a field called Color and then Size. Define a value list to the field Color in Stock to get the popup list for Color in Orders. Then create a relationship for the Color in Orders to the Color in Stock. Another value list to the field Size in Stock but this time select the box in the lower left hand to include only related values to the relationship you just created.

Whew... Now the user will have a pop up list with available colors in the Color field. If they choose red when they enter the Size field, only the sizes available for the color chosen will be available.

[mminer]

Thanks dkemme!

I'll have to play with this one. It's a neat idea but at first glance it seems to address more the issue of availabilty of colors & sizes. It isn't that big a deal to me to have sizes showing up that aren't available in a color--more important is quick data entry and nice presentation on the invoice, packing slip, etc. I really want to avoid having 2-3 page invoices with each line item being a design in a color. The best way to show an order is to list the design on a line and show all the sizes and quantities ordered horizontally on the same line.

It's not obvious how your solution will help this but much isn't obvious to me so I'll work on it.

Cheers!

[Barbecue]

mminer, you might want to take a look at my response to your previous post on this subject. I was away for a few days, so I didn't reply right away.

Don't make the mistake of designing your database structure based on how you want the data to appear on the screen. That's kind of like building a house to fit around your furniture.

Personally, I think an invoice that prints multiple items on a single line is a bad idea, but if you really want that, you can do it by using a calculation or script to create the line items on the fly, you don't need to create a bunch of duplicate fields.

With respect to data entry efficiency, the most important things you can do are design your form so that users enter information in the same sequence they receive it, and minimize the number of keystrokes/mouse movements needed by making good use of tab order and auto-entry. Simply having a horizontal list of fields is not inherently faster or more accurate, though it may be visually pleasing. But again, if you really want this, you can still achieve it without designing your tables around it. One way would be to create some global fields to hold the data temporarily, then use a script to commit the entries to records in the real table.