Selection Formula Plz Help! 1

[kik3]

Hi. I'm trying a formula to select data parting from several conditions. Explanation:

Notes:

{@100} is a simple forumla that says: CURRENTDATE - 10.

1. OPENDATE: is a Date where a record is entered. This is a datetime field and the condition on this field is that it has to be smaller than {@100}.

2. CLOSEDATE: Is a datetime field also. This field has to be BIGGER than {@100} but can also be NULL.

By doing this I should get around 90 Records and I'm only getting 7. I do not know if there's anything wrong with my record formula so here it is.

{LOAN_TRACKING.OPEN_DATE} < {@100} and
{LOAN_TRACKING.CLOSED} > {@100} or isnull({LOAN_TRACKING.CLOSED})

Any ideas?

Thanks in advance!

 

[lbass]

You need to check for the null first. Try:

{LOAN_TRACKING.OPEN_DATE} < currentdate - 10 and
(
isnull({LOAN_TRACKING.CLOSED}) or
{LOAN_TRACKING.CLOSED} > currentdate - 10
)

I think you are more likely to get this to pass to the SQL if you use currentdate - 10 directly, rather than in a formula.

-LB

 

[lbass]

Also note that you probably want one of the clauses to be "<=" or ">="

-LB

 

[kik3]

Thanks! I think is was missusing the or by putting all on the same line and also by not using the () like you did.

Many many thanks man!

Problem solved!

 

[kik3]

One other question. I was trying to enter a parameter field (numeric) that will ask me for the number of days. and then create a formula field ({@days})that will assume (CURRENTDATE - {?days})

So that it will calculate {@days} before reading the records.

It is working and it displays the parameter I entered and even calculates perfectly the formula. But I think I'm missing the funciton BeforePringintRecords somewhere because it is giving me the same records no matter what number I enter.'

Any ideas?

Thanks! again.

 

[lbass]

Just use:

{LOAN_TRACKING.OPEN_DATE} < currentdate - {?days} and
(
isnull({LOAN_TRACKING.CLOSED}) or
{LOAN_TRACKING.CLOSED} >= currentdate - {?days}
)

I'm assuming you are using this for record selection.

-LB

 

[kik3]

Wow thanks man!. I think I'm kinda blocked today.

hehehe

Thanks again.!

Amazing how questions that seem complex have simple answers.

 

[kik3]

For not to open a new thread. I was tryin on the same report to create a formula string field that will populate depending on conditions of datetime fields. What I wanna do is populate this field depending on datetime fields on the database that are filled and if are greater or smaller than that {@days} formula I was working with.

I was trying this code but I coudlnt got it to work.

(
if {CLOSING.CLEAR_TO_CLOSE} > DateValue (0000,00 ,00) and {CLOSING.CLEAR_TO_CLOSE} < {@100 dias} then
"08 Clear to Close"
and
(
if {LOAN_TRACKING.APPROVED} > DateValue (0000,00 ,00) and {LOAN_TRACKING.APPROVED} < {@100 dias} then
"05 Approved"
)

What I want to do is to put several of this IF statements that will determine in which case it will fit for that field to be filled on each record. Depending on those conditions.

Thanks in advance. Hope anyone can help me =)

Cheers!

 

[lbass]

Are these fields sequential, with loan tracking followed by closing, so that only one status applies to each record? If so, then try:


if {CLOSING.CLEAR_TO_CLOSE} > DateValue (0000,00 ,00) and {CLOSING.CLEAR_TO_CLOSE} < {@100 dias} then
"08 Clear to Close"
else

if {LOAN_TRACKING.APPROVED} > DateValue (0000,00 ,00) and {LOAN_TRACKING.APPROVED} < {@100 dias} then
"05 Approved"

-LB

 

[kik3]

Hey! thanks again for helping me. Well yes its only 1 status per record. but the thing is that there are 8 status available including clear to close and approved. And so with 1 else yes I can use these 2 IF's as you typed the formula. But what if there are more than 2 IF? I tried with more than 2 IF's but it gives me a blank space.

Any ideas?

 

[lbass]

It depends on the order relationship of the statuses to each other. Start the formula with the last possible status moving to the first possible status.

-LB

 

[kik3]

Yes that's exactly what I'm trying to do. From the last possible to the first. But still it appears that when I use an IF and then ELSE it only takes those 2 posible answers and does not take notice for the next ones. It will only "evaluate" the first IF and what follows the ELSE. As for crystal syntax appears to miss and END IF clause that will close 1 IF and then move to the nexdt one.

I hope you guys can help me on this one. =(

Thanks!

 

[lbass]

Please copy your formula including all ifs and thens into the post.

-LB

 

[kik3]

Nevermind I finally got it. It turns out that is not the same ELSE IF than to type ELSE and in the next line start it with an IF. Code came out like this:

if not isnull({CLOSING.CLEAR_TO_CLOSE}) and {CLOSING.CLEAR_TO_CLOSE} < {@100 dias} then
"08 Clear to Close"
else if
not isnull({LOAN_TRACKING.APPROVED}) and {LOAN_TRACKING.APPROVED} < {@100 dias} then
"05 Approved"
else if
not isnull({LOAN_TRACKING.SUBMITTED}) and {LOAN_TRACKING.SUBMITTED} < {@100 dias} then
"04 Submitted"
else if
not isnull({LOAN_TRACKING.AUAPPROVED}) and {LOAN_TRACKING.AUAPPROVED} < {@100 dias} then
"03 AU Approval"
else if
not isnull({LOAN_TRACKING.INTROSTIP}) and {LOAN_TRACKING.INTROSTIP} < {@100 dias} then
"02 Intro Stip"
else if
not isnull({LOAN_TRACKING.OPEN_DATE}) and {LOAN_TRACKING.OPEN_DATE} < {@100 dias} then
"01 Turn In

 

[lbass]

Where you place the "else" and "if" by line makes absolutely no difference. One thing you changed here was to evaluate based on nulls versus date(0,0,0).

-LB