This is a public Forum  publicRSS

Forum post

    Kimbakat
    Conditional Formatting - based on radio button...
    Forum post posted April 8, 2011 by Kimbakat , last edited February 9, 2012
    628 Views, 7 Comments
    Title:
    Conditional Formatting - based on radio button selections/deselections
    Post:

    So I have here an "Export" bar with working active buttons....which has various types of expenses we need to export to be imported into accounting software.

    So as an example (see image)

    The word GENERAL (button with script attached) should be dark gray and NOT BOLD. when all radio buttons in the GENERAL portal are selected.

    I've tried so many variations in the conditional formatting and they just don't work.

    Here is the current setting for the conditional formatting.

    I've also tried conditional formula (where the Radio button only means one value "Y" for yes) I've tried....

    (EXPENSES General CY::AMOUNT  ≠  0)  & ( EXPENSES General CY::ITEM PAID  = 0)

    What I need it to do is..to BE BOLD when there is an item added that has a monetary value added and NO RADIO BUTTON selected.

    This means that that Item needs to be exported. (I already have exporting scripts that export it and automatically check the radio button after export - very cool)

    Screenshot:

    Answer

     

    • PhilModJunk

      Your tests need to check an aggregate of all the records in your portal.

      To save typing, I did not try to recreate your exact table occurrence::FieldNames, substitute your names for mine:

      Here is a test that is true if all portal records have a value in the radio button field:

      Count ( Portaltable::NeverEmptyField ) = Count ( portaltable::radiobuttonfield)

      Here's a test that is true if all portal records have "paid" in the radio button field

      Count ( Portaltable::NeverEmptyField ) = PatternCount ( List ( PortalTable::RadioButtonField ) ; "Paid" )

      If the expenses field is empty, you could compare a count of that field to one of the above count methods that count your radio button fields. If it stores the value zero, you may need to add a calculation field such as If ( ExpenseAmount > 0 ; 1 ; "" ) and sum or count it.

    • raybaudi

      BTW: both the above formulas are wrong because you used the wrong operator: "&" instead of "and"

    • Kimbakat

      I also tried "and" in between..didn't work either. I've tried so many other settings and other functions.

    • PhilModJunk

      "And" instead of "&" was the least of your trouble. Your original expressions only checked the first related record. To set a conditional value list based on all the records in the portal, you need to use other expressions that use either aggregate functions like sum or count or which use List to pull all the values from the related table into a single list of values.

      Have you tried any expressions like those I suggested?

    • Kimbakat

      You would think this would be simpler..a logical process would be that on the related portal..if any records has a monetary value..and the Radio Button was "empty" = 0..then based on those two factors should result in the formatting

      Even if I omit all other projects..would it be searching OTHER projects expenses? and that is why my formula doesn't work?

    • PhilModJunk

      The expression evaluates from the context of your layout's current record.

      If you have this relationship:

      Main::PrimaryKey = Child::ForeignKey

      From Main, any reference to a single field in Child returns the value of that field from the first related record and the values of this field in the other related records are not referenced.

      In the conditional format example you posted, Expenses General CY::amount

      Refers only to the amount field in the first row of your portal which would be $4,450.00 if I am interpeting the screen shot correctly.

      That's where aggregate functions like count and sum come in. They are designed to span the entire set of related records not just the values in the first record. In similar manner, List ( RelatedTable::Field ) returns a list that combines the values of Field from all related records into a list with each record's value separated by a return character. That's why my suggestions used these functions in order to examine all the values in the portal, not just the values of the first related record.

    • Kimbakat

      SOLUTION..

      Thanks all.  This is what worked!

      Count ( EXPENSES General CY::AMOUNT )  ≠  Count ( EXPENSES General CY::ITEM PAID)

      The first item was the "amount" of the expense.

      The second item is the radio button ..if the item was clicked PAID or "Y" I use. for "yes paid".

      If the count doesn't match..the BOLD gets turned on - beautiful!!