Set a flag using SQL through a portal

[Rich S]

Happy holidays, everybody!

Simple request: I want to set a calculated flag in a parent table's field so if any of that parent record's children has the word, Yes, in a specific text field it would set a value in the flag field. I tried the following but the syntax is getting the better of me; as always, your help is greatly appreciated! The flag calc is easy; the ExecuteSQL syntax is hard. Of course, if there's a native way in FileMaker Pro to do it I'm all for it!

Parent table name: TEST
Child table name: STANDARD_1
Fieldname in TEST: YesFlag__lxt
Fieldname in RESULT: PassedYN__lxt

ExecuteSQL ( "SELECT COUNT ( STANDARD_1::PassedYN__lxt ) FROM RESULT WHERE STANDARD_1::PassedYN__lxt = 'Yes' " ; "" ; "" )

[bcooney]

On 11/22/2017 at 6:27 PM, WF7A said:

I want to set a calculated flag in a parent table's field

Not sure of the terms that you're using here. A calculated flag field would be one that doesn't get set by script.

ESQL does not use fully qualified field references. That is, do not use table_occurrence::field_name, but rather field_name. Here's the template  from FileMakerStandards.org that I typically start with:

Let ( [ ~sql = "
    SELECT ~field
    FROM t1.~table1
    JOIN t2.~table2
    ON t1.~field = t2.~field
    WHERE ~field=?
    ORDER BY ~field
    ";
    $sqlQuery = Substitute ( ~sql ;
        [ "~table1" ; SQLTableName ( Table1::fieldName ) ];
        [ "~table2" ; SQLTableName ( Table2::fieldName ) ];
        [ "~field" ; SQLFieldName ( Table1::fieldName ) ]
    )
    $sqlResult = SQLDebugResult (
        ExecuteSQL ( $sqlQuery ; "" ; "" ;
            $value ;
            $value[2] ;
            $value[$n]
        )
    )
];
    If ( $sqlResult = "?" ;
        False ;
        True
    )
)

If you're looking to have a script set a number field in a parent, then I would suggest using a OnRecordCommit trigger. Not sure I'd even bother using SQL, since a simple calc will let you know if any children = 1. Btw, the TEST: YesFlag__lxt field should be a number. It's either 1 or null, correct? It can be formatted to show the word "yes" using the inspector.

If ( list ( parent_to_child::PassedYN))

[Rich S]

Thanks for the reply! I took your lead and constructed the equation as: 

Case ( PatternCount ( List ( STANDARD_1::PassedYN__lxt ) ; "Yes" ) ; "Yes"  ; "No" )

On second thought, the result of that can be the flag so I don't need a second field to act as one.