Start Free Trial

asked on

Crystal Reports Formula

I need to create a formula which give me the result of customers who "did not" pay. That is I need to display Payment = null in the last MTD.

I have in my database - charges and payments.

I tried doing this:

{ViewTenantStatement1.TransactionDate} in MonthToDate and
{ViewTenantStatement1.IsCurrent} and
{ViewTenantStatement1.TransactionType_ID} <> 2

The result are simply displaying all the charges for that month - and ignoring payments (transaction type =2). I need something like:
{ViewTenantStatement1.TransactionDate} in MonthToDate and
{ViewTenantStatement1.IsCurrent} and
{ViewTenantStatement1.TransactionType_ID} is 2 but is NULL. . .

SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

If i understand you correctly than i guess this is what you looking for

{ViewTenantStatement1.TransactionDate} in MonthToDate and
{ViewTenantStatement1.IsCurrent} and
{ViewTenantStatement1.TransactionType_ID} = 2 AND IsNull({ViewTenantStatement1.TransactionType_ID}

Open in new window

What you need to do is use the IsNULL function to filter the records and display as you want

ASKER

I get a "("
is missing

:(

sorry missing ending bracket

{ViewTenantStatement1.TransactionDate} in MonthToDate and
{ViewTenantStatement1.IsCurrent} and
{ViewTenantStatement1.TransactionType_ID} = 2 AND IsNull({ViewTenantStatement1.TransactionType_ID})

Open in new window

{ViewTenantStatement1.TransactionType_ID} = 2
AND IsNull({ViewTenantStatement1.TransactionType_ID})

Cannot possibly be true.

Please answer the question I posted before.

ASKER

Formula worked - but generating 0 records. . .

My logic works: Give me all the tenant that are current, monthtodate AND did not pay (meaning transaction type = 2 but is null...

What am I missing?

'Formula worked -'
as I have just said , it cannot work. It is wrong. Just because it doesn't give an error doesn't mean it is correct.

I think i have 2 agree with you peter57r. it doesnt actully make any logic.

{ViewTenantStatement1.TransactionType_ID} = 2
AND IsNull({ViewTenantStatement1.TransactionType_ID})

will always return zero records.

@Starquest321, maybe if you can explain a bit better as to what you are after then we can help you out a bit better

ASKER

It's not clear to me what the data looks like.

Are you saying that there IS a record with transactiontype_id of 2 for every month but the payment amount on that record is null if there is no payment or are you saying that there is NO record with tranactiontype_id of 2 for months where there is no payment.

I am saying option 2:

"you saying that there is NO record with tranactiontype_id of 2 for months where there is no payment."

if its the second one then it will work. but u do need to have an idea as to what the data must look like.

ASKER

Asishraj:

I think peter is right . .

{ViewTenantStatement1.TransactionType_ID} = 2
AND IsNull({ViewTenantStatement1.TransactionType_ID})

Cannot possibly be true.

SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ASKER

Can I attach the report with the data? Will that help?

ASKER

What do you mean data "looks like" . . . how can I help progress the solution?

sure u can attach the report with data. i can look into it as soon as i hv a spare moment

I think what you want is SQL like this.

SELECT VT.Tenant
FROM ViewTenantStatement1 VT
WHERE
NOT ( VT.Tenant IN (Select VTS.Tenant
FROM ViewTenantStatement1 VTS
VTS.TransactionDate in MonthToDate and
VTS.IsCurrent and
VTS.TransactionType_ID = 2 ))

If so you will have to build the query in the database or in a Crystal Command. I don't believe you can do it in the select expert.

mlmcc

ASKER

Here is the report with the data. . .
tbalancenonpay-lakevillage.rpt

If I understand the report, the data you provided is only the tenant who are current. There is no data on tenant that are behind.

I see the data is a lot more complex than what I thought so my idea won't be easy to implement unless you do it in the database

To get the records you want you will have to remove the filtering then use suppression. I don't believe you can accomplish what you want through a selection filter with the current tables

mlmcc

ASKER

Let me example: The dataset is ONLY the tenants that are current. FROM those I want to PICK up the tenants who who did not make payments MTD (assuming I run the report on the 10th of each month).

The result will be: Select from the tenants that are current (not historical) the tenants who did NOT pay yet this month.

Thus tenants that ARE behind simply have a balance that is shown in the report.

How do you determine they did not pay?
Is it that there is no type 2 transaction?
If so you need some kind of subselect to choose all the tenant with a type 2 then exclude them from your result.

It could be done in Crystal using record suppression but you then need to choose all tenants who are current regardless of tranaction type value

If you are doing any opverall summaries then there will be an issue with excluding the records from the summaries.

mlmcc

ASKER

mlmcc: Exactly! I am determining that tenants did not pay if there is NO type 2 transaction. How do I put in that "subselect" statement? Can you post an example of the record supression you are speaking of?

I am not doing ANY opverall summaries. What are they?

Subselect - You can only do it in the database or use a Crystal Command. I gave you an example above

To use suppression
Remove the Type selection in the selection formula

Create a formula COuntType2
If {ViewTenantStatement1.TransactionType_ID} = 2 then
1
Else
0

You can then suppress the group header, details, group footer with a formula
Sum({@CountType2},{Group field}) > 0

mlmcc

ASKER

I am almost there:

I get an error in the formula:

Sum({@count2type},GroupName (GroupName ({ViewTenantStatement1.PropertyUnitID}) > 0)

It's saying missing )

I changed the formula name to count2type. . .

Does your report have a group?

Sum({@count2type}, GroupName (GroupName ({ViewTenantStatement1.PropertyUnitID}) > 0)

GroupName (GroupName ({ViewTenantStatement1.PropertyUnitID} need to just be the field that the report is grouped on

mlmcc

ASKER

I am a little new at this..but I think I do have a group. I attached the report above in the previous answer if you can quickly glance.

I base the group name on the PropertyUnitID. .

I am still missing a ) no matter what I do (or how many additional ones I add :(

ASKER

Please please help . . :(
I really need that report. . .
For some reason the suppression formula is missing something small. . .

ASKER CERTIFIED SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ASKER

worked! Thanks all for the help!

ASKER

Can I add one additional question (or do I have to open another one)
How can I make the Telephone numbers in the report be a href to the following:

tel://{phone}

?

Since that seems to be completely unrelated to this question, you should probably start a new question.

James

ASKER

I posted my question here - I would appreciate it if you can take a look:

https://www.experts-exchange.com/questions/27845270/Href-Phone-Field.html