Calculated Fields based on max(Date)

[tthayer (Trygve Thayer)]

Trying to create a calculated field to show the OpenPayable field based on the Max(Date) Problem is the query does not like Max being used with a booleen field.

I have a query with 3 records that have a date and a booleen field of Open/Closed. 2 are closed and one is open but the one that is open is the latest date. How do I show the value of the booleen field where the date is the greatest.

MyGuess which does not work… Max(APInvHead.InvoiceDate), ApInvHead.OpenPayable
And set the field type to nvarchar

[cfinley (Chance Finley)]

Hey buddy, hope you are doing well.

Sounds like you need to do a subquery to return the boolean effectively. Have 1 field in the subquery and return the subquery name in a calculated field

[JasonMcD (Jason McDermott)]

Is this like this question from yesterday?

Min Req. Date in BAQ

Is there a way to do this using table criteria or subquery criteria? There are some other fields in OrderRel I want to add, but I’d like the keep the rows limited to rows with the earliest Req Date.

[MLamkin (Marjorie Lamkin)]

Max(boolean) doesn’t work, but Max(CASE WHEN boolean = 1 THEN 1 ELSE 0 END) does. There’s ways around it

[tthayer (Trygve Thayer)]

Worked perfect Marjorie !