Can I do a subselect

[rgoldman]

I have a formula "IF {RX_TRANSACTION_DETAIL.DATE_FILLED} < {@MIN SUBMIT DATE} THEN TRUE" - I then want to group on the TRUE, or run a query against the TRUE but it doesn't show up as a field to group upon.
Is there a way to evaluate this to use in a subselect?

 

[rgoldman]

I guess you should know I am using 8.5 and I have a evaluation copy of 11

 

[lbass]

Please show the contents of your nested formula.

-LB

 

[rgoldman]

This is the formula, it produces the results true or false, I then want to select only the true results. When I go to the select tool the formula is not one of my options to select, I imagine it is because the results are the result of a formula and crystal doesn't know what to select on because it is calculating. So, back to my original question, can I select on values I bring back in a query.

 

[lbass]

I was referring to:

{@MIN SUBMIT DATE}

-LB

 

[rgoldman]

sorry,
that is the minimum date in a order date field, I want one date field to be less than the earliest date in another field

 

[lbass]

Please share the entire formula--I'm betting it is the minimum of a date within a group.

-LB

 

[lbass]

I'll guess. In 8.5, try creating a SQL expression {%mindate} as follows:

(select min(A.`Submit Date`) from Table A where
A.`groupfield` = Table.`groupfield`)

Substitute your table name for "Table" and your exact field names for submit date and groupfield. Leave "A" as is, since it is an alias table name.

Then go to the report->edit selection formula->RECORD and enter:

{RX_TRANSACTION_DETAIL.DATE_FILLED} < {%mindate}

This will return only those records per group that are earlier than the minimum submit date for that group.

-LB