2 Replies Latest reply on Jul 22, 2013 7:18 AM by deathrobot

    Updating List based on portal filtering and using results in conditional format

    deathrobot

      Title

      Updating List based on portal filtering and using results in conditional format

      Post

           I have a portal with a list of companies, sorted alphabetically by name. I use some conditional formatting to show a single letter at the top and to the left of each letter group (e.g. an "A" at the first company that begins with "A", a "B" at the first company that begins with "B", etc.). I want to have each of these letter sections alternate between two colors, so that all the "A's" have the first color, all the "B's" have the second color, etc. I tried using a conditional calc like this:

           PatternCount (
                #BDFHJLNPRTVXZ;
                Table::FirstLetterCalc
           )

           // when true, it changes the fill color

           This works great…unless there are no companies that begin with a certain letter. In that case, I get two consecutive sections with the same color. I realize I need to do something like create a list which is a summary of all the first letters used and then replace my formula with one that looks at the number where the letter occurs in that list, but am unsure of how to best go about this. And, to make things even tricker, this needs to update based on the filtering of the list at any given time, meaning the value list would need to update based on the current portal filtering. Any ideas?

           Thanks,
           Michael

            

           UPDATE: I'm now able to create a list that does not include missing first letters using a custom function from Brian Dunning's site (Value_Dedupe, http://www.briandunning.com/cf/1109 ). Still having trouble filtering it so it matches the filtered portal set.

        • 1. Re: Updating List based on portal filtering and using results in conditional format
          shilpas@metasyssoftware.com

               Hi Michael,

               I assume the Filemaker database file has two table with parent child relationship and child table is being displayed as portal on parent table layout.

               Lets say, 'States' is parent table and 'companies' is child table. On layout based on 'states' table, you are showing portal of companies in that state. So in 'Companies' tabel you have fields 'CompanyName ' and 'FirstLetterCalc'. In order to have all A's in first color, B's in second color, then C's agin in first color, please make following updates to your database

               Add Cutom function - DeduplicateList ( input ; output ) (Ref Link- http://www.briandunning.com/cf/1109)

               Set the relationship between states & companies sorted by 'Company Name' ascending.
               Now add following fields to the database

               1. In 'States' table (parent table), create calculation field - 'UniqueFirstLetterList' with formula -
               Substitute(DeduplicateList (List ( Companies::FirstLetterCalc ) ; "" );"¶";"")

               2.  In 'Companies' table add calculation field - 'UniqueListPosition'
               Position(  States::UniqueFirstLetterList ;CompanyFirstLetter ; 1; 1)

               3. Apply conditional formatting to 'CompanyName' field of portal. set these two conditions
               a. Mod(Companies::UniqueListPosition;2) ≠ 0 - set text to be shown in 'first color' (color of your choice')
               b. Mod(Contacts 2::UniqueListPosition;2) = 0 - set text to be shown in 'second color' (color of your choice')

               I have attached screenshot of output - screen visible to applciation user.

               Hope this helps you. If you are looking for different format, I would suggest you to provide screenshot.

                

               Thanks,
               MetaSys team

          • 2. Re: Updating List based on portal filtering and using results in conditional format
            deathrobot

                 Hi MetaSys,

                 Thanks very much for this. This solved the first of my two issues perfectly; using the position calc and the mod function makes it easy to conditionally format for every other first letter, no matter how many first letters there are. Great!

                 My second issue is that I'm having trouble getting 'UniqueFirstLetterList' to dynamically change based on my portal filtering. In my case, the parent and child TO's are both based on the same Companies table using a self/cartesian join ( Companies --X--< CompaniesPortal ). I have a global field in the Companies layout that dynamically filters the portal (using a calc in the filter section of the portal dialog). This means that at any given time, the portal has a different collection of first letters. I've tried a bunch of things, including inserting a case function within the list that matches my portal filtering:

            Substitute(DeduplicateList (List ( Case (PortalFilterCalc ; Companies::FirstLetterCalc)  ) ; "" );"¶";"")

            but only get a '?'. Do you know of a way to do this?

                 Michael