So the combination of style and color determine uniqueness, year and season don't matter?
Do you use FileMaker 12? If so you may want to use a multi-row calcaultion field with ExecuteSQL, using Select Distinct to pull up a list of your entries.
Doing this from earlier versions is possible, but much more complicated.
I'm using FileMaker Pro 11 Advanced.
Correct, in this case, season and year don't matter, I just want a list of all the colors this style comes in - and no duplicates.
After thinking further I wonder if my best option is to create another table and import records from the original with only unique style_color combination. Then the portal should refer to that table instead of the original.
Does that sound like the logical next step? I don't want to make things too complicated for myself, but if I have a relationship that allows creation of records via this relationship, the secondary table would always be up to date with all the styles and colors, correct?
Any thoughts as to whether creating another table and importing records is the best option? Or is there another solution I can consider?
A table where you have one record for each combination of sytle and color will work and can be set up from your existing data pretty easily. Whether or not it is the best option is impossible to say from the information avaliable to me at this point.
Here's a quick way to set up this table for your current set of records:
Define the new table with three fields, style, color and StyleColor. Define StyleColor as a text field with this auto-enter calculation:
Style & "|" & Color
Select Unique values and validate always on the validation tab.
Now use import records to import all the records from your current table into this new table. The validation settings on the StyleColor field will autmatically filter out duplicates to give you one record for each unique combination of style and color.
The main draw back--which is fairly minor, to this approach is that you will need to use scripts to keep this table updated each time you add, remove or change a record in your original table.
Sorry it is taking me so long to get back to you. I have a quick question regarding your reply.I need to modify my original post. I am using a new "key," similar to StyleColor but more detailed. It is ProductKey. It includes: Product Number & Style Number & Season Year. So it looks like 123456...ABC...FH2012. Again, i want my new table to only show one record for each product key, and then my layout will show a portal showing records from the original table that will show all colors for that particular product key by season. So only the colors for FH2012 for style ABC will be shown. In order to do this, should my relationship between these two tables be style number or the product key or does it even matter as long as there is a relationship?
What I described originally will still work as long as you modify it to combine all three values instead of two.