Conditional Formatting of a tab based on the records in the tab.

[Sweet_apollo]

Hello.  

I may be aiming to do something that isnt possible and hence not able to do it, but thought I would ask before I gave up.

 

I have a set of tabs in a layout, that shows multiple records below (based on a portal record filter.)

 

It is cell tower information on signal strength. I have a field that for each record shows if the Quality of service is good or poor etc. I want to be able to make a conditional format, so that if there are say 5 records, if 3 are excellent, one is good and one is poor, the tab is marked with a colour to indicate poor (Being the lowest quality on that tab.

 

I have gone around this by using a calc that marks a field as 1-6 numeric value with 1 being best and 6 being worst. My aim in this was to make it easier than words for the coding, but I just cant get it to work. My process was to take the number and try and use a Max() for that field and then if that number equaled 6 say, it was red and the same code but if the value was 1 it would be green.

 

Currently my formatting is as such as a test as to any formatting of the tab by a field at all. I cant even get this to work on the tab.

 

Hole Information::Quality_index = "1" 

 

But originally it was this with the max qualifications.

 

Max ( Hole Information::Quality_index) = "1"

 

 

I hope that makes sense.

[Ocean West]

Since tabs could be Named using a calculation you could add an EMOJI into the name of the tab to indicate quality level alternatively you could use Button Bar and Slider Panel to navigate between panels. You may have more options to alter the buttons with color or icons.

Since you are filtering your portals it makes it hard to get an evaluation based on the same filter logic. Any calculation based on the relationship will ignore filtering options for a portal. 

You could duplicate each portal make it show only one row and include the calculation in the portal that should still respect the filtering logic you could place them above the tabs so you can see the data

 

 

 

[Sweet_apollo]

A Smiley would be a good idea too. I still don't really know how I would get the logic to work to calculate the value though. 

Basically I have a table called Hole Information, the tab filters by value in Field 1 which is say Hole 01 or Hole 02 for example. So each tab is Hole 01 and Hole 02 data. I then need it to take that search and match all records with that Hole number and the quality_index of 1-6. There could be say 20 records for that hole and I just need to find the Max or MIN.

 

I did have the thought of having another table that has a field for each hole that was based on the calculation and produced the min number for that hole, which I could then easily look up for either smileys or formatting I think. but again how I write the min or max for all record within that filter is beyond me at this point.

 

 

[Sweet_apollo]

I have a feeling from googling, that the answer may be similar to this post:

 

But as the answer is behind a dead link, I cannot find it. I assume reporting (which is something I know nothing about may hold the key)

 

[Ocean West]

perhaps this is the link:

(sorry we have some broken links from site conversion years ago - I have a conversion table can manually lookup ) 

 

[Sweet_apollo]

15 minutes ago, Ocean West said:

perhaps this is the link:

(sorry we have some broken links from site conversion years ago - I have a conversion table can manually lookup ) 

 

Maybe. I don't think it helped me fix my problem with min max of a field for multiple records. I am still very much stuck.

[Ocean West]

Any way you can make a sample file and post it for us to see? 

 

[comment]

38 minutes ago, Sweet_apollo said:

my problem with min max of a field for multiple records

The main problem with your request is the fact that your portal is filtered. Without this, it would be quite simple to use an expression like:

Max ( YourPortalTable::SomeField ) 

in your conditional formatting formula. But portal filtering works at the layout level and it's not easy to aggregate the values in a filtered portal and get the result back to the data level - which is where conditional formatting operates.

There are two ways you could approach this: one is to replicate the portal filtering in your calculation. Since I don't know how exactly your portal is filtered, I will skip this option and move directly to the other one (which is probably preferable in your situation anyway): 

Define a summary field in your portal table as Max of SomeField. Place this field in your filtered portal, and give it an object name, e.g. "maxQ". Hide the field by placing it behind another field or by using the Hide option in the Inspector. Now you will be able to use the expression:

GetLayoutObjectAttribute ( "maxQ" ; "content" )

in your conditional formatting formula to refer to the maximum value shown in the filtered portal.

 

[Sweet_apollo]

Here is a sample file. I would like the Quality field to max() and then have a way to make that into something that will allow my conditional formatting.

 

Thanks for all your help

Sample_Database 2.fmp12

[Sweet_apollo]

Anyone had a chance to look at the sample database? Still struggling with this. I appreciate it.

[comment]

I did look at your file. I did not see an attempt to implement my suggestion. In fact, I struggled even to find a portal. I am afraid you overestimate the amount of time and effort I am willing to put into this.

Edited September 13, 2021 by comment

[Sweet_apollo]

1 minute ago, comment said:

I did look at your file. I did not see an attempt to implement my suggestion. In fact, I struggled even to find a portal. I am afraid you overestimate the amount of time and effort I am willing to put into this.

I created this before your comments, as it was requested before. I appreciate your frankness & honesty.

[comment]

1 minute ago, Sweet_apollo said:

I created this before your comments, as it was requested before.

You are right, it wasn't me that asked to see your file. So if you're willing to wait and see if anyone else has the time and patience that I lack, all the better for you.