Portal Conditional Formatting by Related Record

[higleyjp]

Good Morning,

I'm working on a layout which requires conditional formatting based on the value of a related record in a one-to-one relationship. When I add the conditional formatting in the layout which the portal is based on it works without a problem, but when I add it in the portal directly it doesn't work at all. The calculation dialog when setting up the formatting states "This calculation will be evaluated based on context determined at runtime." Is conditional formatting on portal fields evaluated from the context of the portal, or from the parent layout? If it is from the parent layout, is there any way to force it to evaluate from a different context?

Thanks!

Justin

[comment]

conditional formatting based on the value of a related record in a one-to-one relationship.

This part is not clear. You mention a portal - which implies a one-to-many relationship. What exactly is the condition you want to use?

[higleyjp]

Currently I'm working on an inspection solution for the iPad. The inspection is performed from a tabbed layout called "Inspection." The first tab allows you to select the location and area being inspected. When you initiate an inspection it calls a script that goes to the "Master" table, finds the appropriate equipment for the entered location and area, and copies the ID for each found record to a "Temporary" inspection table. The Master table has a set of fields defining what categories a certain piece of equipment is evaluated on, so I would like to conditionally gray out the categories which are not valid for that equipment.

For example:

Master table has fields ID, Area, Equipment, Cat1, Cat2, and Cat3, where Cat1, 2, & 3 contain an X if that piece of equipment is evaluated on that criteria. Temporary inspection table has ID, Cat1, Cat2, and Cat3 fields, where Cat1, 2, & 3 store the letter grade assigned to the category during the inspection from a pop-up menu. The portal on the Inspection layout refers to the Temporary table. The conditional formatting in place sets the field background for Cat1 in the portal to gray if Master::Cat1 = "". I plan on using a script trigger to prevent entry in that field if it is inactive.

The conditional formatting above works when implemented on the Temporary layout, but not when used on the Inspection layout.

I'm just trying to do a proof of concept initially. The final implementation will be tidier with a checkbox in each category on the Master table to indicate whether the category is active. I know that I could simply create additional fields in the Temporary table for "Cat1 Active, Cat2 Active, etc", however, I don't understand why the conditional formatting will not evaluate properly through the existing relationship.

[comment]

Sorry, I got lost in your explanation. This may be unrelated to your question, but I would suggest you review your data structure - esp. the part where you have fields named Cat1, Cat2, Cat3, etc. This is almost always a sign you are using fields where you should be using records in a related table.

[higleyjp]

Sorry. Not enough sleep & not enough coffee here. The basic premise of my question is that I would like to conditionally format a portal field based on the value of a field in a related table. If I do this from the table's layout it works, but when I try to implement it in a portal based on the same table it doesn't. Is conditional formatting on portal fields evaluated from the context of the parent layout's table or from the context of the portal table?

Quick example attached. You can see that the same conditional formatting has been applied to the Temporary layout as to the portal in the Presentation layout. It works on the Temporary layout but not on Presentation.

Portal Conditional Formatting.zip

[comment]

Is conditional formatting on portal fields evaluated from the context of the parent layout's table or from the context of the portal table?

I am reluctant to answer this in general terms, because the answer is not as clear-cut as your question would have it. Perhaps you'll see that in the attached file.

In any case, I suggest again you reconsider your entire approach. You should have a table for each entity in your system, e.g. Inspections, Locations, Areas, Categories, etc.

"Master", "Presentation" and Temporary" are not entities.

I was about to point you to this thread:

http://fmforums.com/forum/topic/77339-equipment-inspection-schema/page__p__362919

but I see that you were the original poster there.

Portal Conditional Formatting2.zip

[higleyjp]

Thank you for your help. In my actual database I do have tables for all of the entities you mentioned above. I just threw together a VERY simplified mock-up to try and illustrate my question, and it worked. Thanks again!

[higleyjp]

Okay, so I have the conditional formatting working (pretty easy with your help), and now I'm trying to count the number of A's, B's, and C's. Any pointers on how to quickly aggregate this info without having to count the found set for each Category for each grade?

Thanks!

[higleyjp]

Nevermind. I used a Calculated field to aggregate each field into a list, then used ValueCount and FilterValues to count the occurence of each letter grade, with a total summary.