12 Replies Latest reply on Dec 11, 2012 8:50 AM by philmodjunk

    filtering filtered portal

    rounakjain

      Title

      filtering filtered portal

      Post

           I have a portal which shows data from a table with unique records.

           The portal has the following fields:

           1. Product Name

           2. Prodcut Size

           3. Product Rate

           I have filtered the portal to show only those records where Product Name = "Item X".

           The product sizes for "Item X" are like:

           1/2 x 3, 1/2 x 4, 1 x 4, 1 x 12, 3/4 x 3, 3/4 x 5

           What I want to see in my portal is

           1/2 x 3 (or 1/2 x 4 whichever comes first based on sorting criteria of my portal)

           1 x 4 (or.............(as mentioned above))

           3/4 x 3 (or.....(as above)

           Thus I want only one record for each unique size "beginning" i.e. 1/2, 3/4, 1 and so on.

        • 1. Re: filtering filtered portal
          philmodjunk
               

                    The product sizes for "Item X" are like:

               

                    1/2 x 3, 1/2 x 4, 1 x 4, 1 x 12, 3/4 x 3, 3/4 x 5

               Does that mean that you have 1 record for Item X with all of this data in one field or does it mean that you have 6 records for Item X, each with a different size in the size field?

               

                    What I want to see in my portal is

               

                    1/2 x 3 (or 1/2 x 4 whichever comes first based on sorting criteria of my portal)

               And can you give an example of that "sorting criteria"?

               There are ways to construct a portal filter expression that uses data you select/enter in a text field that only matches to records where the text starts with the same characters as the text in this field:

               LayoutTable::SearchText = Left ( PortalTable::Size ; Length ( LayoutTable::SearchText ) )

          • 2. Re: filtering filtered portal
            rounakjain
                 

                      Does that mean that you have 1 record for Item X with all of this data in one field or does it mean that you have 6 records for Item X, each with a different size in the size field?

                 there are 6 records for Item X each with a different size in the size field

                 

                      And can you give an example of that "sorting criteria"?

                 modification date in descending order

            • 3. Re: filtering filtered portal
              philmodjunk

                   Ok, then the suggested filter expression:

                   LayoutTable::SearchText = Left ( PortalTable::Size ; Length ( LayoutTable::SearchText ) )

                   once you use your names in place of mine, should work for you, provided that you are using FileMaker 11 or newer.

                   If you have FileMaker 12, there are also ways to use Execute SQL to get this result.

              • 4. Re: filtering filtered portal
                rounakjain

                     I want only one record for each unique size "beginning" i.e. 1/2, 3/4, 1 and so on.

                     The expression you suggest will give me

                     1/2 x 3

                     1/2 x 4

                     when the search text is "1/2"

                     and

                     3/4 x 3

                     3/4 x 5

                     when the search text is "3/4".

                     I want to see the following in the portal:

                     1/2 x 3

                     3/4 x 3

                      

                • 5. Re: filtering filtered portal
                  philmodjunk

                       Do you have FileMaker 12?

                       Is this portal used only to display values or do you need to be able to edit one or more fields in this portal?

                       And does the sort order or the value of the second dimension determine which match size record should be visible in the portal? (your first post refers to the sort order, but your examples in your most recent post show the record with the smallest second dimension...)

                  • 6. Re: filtering filtered portal
                    rounakjain
                         

                              Do you have FileMaker 12?

                         yes

                         

                              Is this portal used only to display values or do you need to be able to edit one or more fields in this portal?

                         only display values

                         

                              And does the sort order or the value of the second dimension determine which match size record should be visible in the portal?

                         sort order

                          

                          

                    • 7. Re: filtering filtered portal
                      philmodjunk

                           Ok, there are two directions that I can take this thread.

                           It's possible to use the ExecuteSQL function in a calculation field that you then size to be many rows tall and for which you specify a scroll bar. This can look and function much like a portal as long as you don't have to edit any of the data. The SQL used can return a result matching what you describe with tab characters arranging the results into columns and each record a row of text in the field.

                           Or we can add another occurrence of your table and use the sort order you've specifed at the relationship level to come up with something that your filter expression can use to filter out the additional records that start with the same text.

                           With either approach, step 1 would be to add a field that only contains the first dimension. This could be a calculation field that uses LeftWords ( yourfield ; 1) to extract the first dimension, or you may have dimension fields already in your table with a calcualtion producing the combined dimensions you have posted in your example. Either approach works for what you are requesting and this will not need to be a visible field on your layouts.

                           So which way do you want to take this? SQL in an ExecuteSQL function or via  portal?

                      • 8. Re: filtering filtered portal
                        rounakjain

                             Thanks for the reply. Any approach is fine with me.

                        • 9. Re: filtering filtered portal
                          philmodjunk

                               Yes, but are you familiar with SQL queries? That will make a big difference as to how "steep" a learning curve would need to be climbed to understand the ExecuteSQL approach.

                          • 10. Re: filtering filtered portal
                            rounakjain

                                 yes, i am familiar. I would like some guidance though on how my problem can be solved using SQL

                            • 11. Re: filtering filtered portal
                              philmodjunk

                                   I think it can be done, but I'm going to have to experiment with a test file to make sure that the "joins" I have in mind will work for this...

                              • 12. Re: filtering filtered portal
                                philmodjunk

                                     Apologies, but after mulling this over, I went back to "tried and true" and set up an extra occurrence to make the portal approach work. I suspect that ExecuteSQL can be made to work here, but don't have the time today to mess with this more...

                                     I used these relationships:

                                     Main::ProductName X Sizes::anyfield AND
                                     Main::FirstDim X Sizes::anyfield

                                     Sizes::_fkProductID = SizesSameProductSame1stDim::_fkProductID AND
                                     Sizes::FirstDim = SizesSameProductSame1stDim::FirstDim

                                     In the relationship to SizesSameProductSame1stDim, I specified a sort order based on a date field. FirstDim is defined as LeftWords ( Size ; 1 ) with text as the return type.

                                     I then put a portal to Sizes on the Main layout with this portal filter:

                                     Main::ProductName = Sizes::ProductName and
                                     Main::FirstDim = Sizes::FirstDim and
                                     Sizes::__pkSizeID = ProductsSameProductSame1stDim::__pkSizeID

                                     How it works:

                                     The fields Main::ProductName and Main::FirstDim are included in the relationship with the cartesian join operator so that selecting values in them will automatically update the filtered portal.

                                     The expression:

                                     Sizes::__pkSizeID = ProductsSameProductSame1stDim::__pkSizeID

                                     compares the primary key of the portal record to the "first related record" of a second occurrence of the same table named ProductsSameProductSame1stDim. Since this matches records by size and product ID, then sorts them by the date field, this "first related record" will be a size record for the same product with the same first dimension as determined by the date in the date field.

                                     Here's a link to the demo file where I tested this: https://dl.dropbox.com/u/78737945/FilterPortalToFirstOfGroup.fmp12