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 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?