6 Replies Latest reply on Nov 20, 2012 8:59 AM by philmodjunk

    Portal Filter Records - Unique Only

    Terri

      Title

      Portal Filter Records - Unique Only

      Post

           I am hoping to be able to filter a portal record for unique only. 
            
           Here is an example of the records that are in the table that the portal is showing:
            
           Style: #12345
           Color: WHI
           Season: SS
           Season Year: 2013
            
           Style: #12345
           Color: WHI
           Season: FH
           Season Year: 2012
            
           Style: #12345
           Color: BLA
           Season: FH
           Season Year: 2012
            
           The layout I'm using is related to the other table by style number. The portal shows all the colors for that style. But I want WHI to only show up once. I'm not sure which type of calculation I could use to ensure it only shows up once.
            
           Any brainstorming ideas?

        • 1. Re: Portal Filter Records - Unique Only
          philmodjunk

               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.

          • 2. Re: Portal Filter Records - Unique Only
            Terri

                 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?

            • 3. Re: Portal Filter Records - Unique Only
              Terri

                   Any thoughts as to whether creating another table and importing records is the best option? Or is there another solution I can consider?

              • 4. Re: Portal Filter Records - Unique Only
                philmodjunk

                     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.

                • 5. Re: Portal Filter Records - Unique Only
                  Terri

                       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?
                        
                  • 6. Re: Portal Filter Records - Unique Only
                    philmodjunk

                         What I described originally will still work as long as you modify it to combine all three values instead of two.