Record Selection Formula 1

[kandikcj]

Is there a way to select records based on the result of a formula? Essentialy what I am trying to do is report off of a report. I have tried everything I can think of and haven't been able to discover a way to do it. Any assistance would be greatly appreciated.
Thanks,
Charlie

 

[synapsevampire]

Please post technical information when posting, such as:

Crystal version
Database used
Connectivity
Example data
Expected output

The answer to your question is probably.

Selecting records based on a formula is as simple as:

{table.date} < currentdate

Currentdate being a formula.

But there are many factors to consider, and a nested query in an Add Command may be all that you need, but it would serve everyone better were you to provide technical information, unless the above resolves.

-k

 

[nagornyi]

A possible solution could be to export the report output to Excel or XML and use the output as datasource for your report.

 

[dgillz]

You cannot report off of a report in crystal.

Furthermore, why would you want to? If you give some examples of your raw data and your desired output, I am sure we can help you get what you want.



Software Sales, Training, Implementation and Support for Exact Macola, eSynergy, and Crystal Reports

http://www.trianglepartners.com

 

[kandikcj]

Hello all,
Here is my problem: In our database we have people that need to have tests done at different intervals and I want to create a report that will pull only the people that are due to come in in the next month. In the database we have has a field that lists how often test needs to be done and another table that lists the tests and when they were done. What I am trying to do is look at when the last test was done, run that date through a calculation to determine when the next one is due and pull the data based on that calculated date.
So far I have created a report that will pull everyone, perform all of the calculations and list when all of them are due. What I would like to do is query that report. I know that I could run it dump it into access and query that but we would like to publish it on the web so our users could run it whenever they want. Is this possible?
Thanks,
Charlie

 

[lbass]

You might want to include all records in your report, but use a group select to display only those names with a due date in the next month. Go to report->edit selection formula->GROUP and enter:

month({@duedate}) = month(dateadd(&quot;m&quot;,1,(currentdate)))

-LB

 

[kandikcj]

I have tried that but I get the error &quot;This formula can not be used because it must be evaulated later.&quot;
Charlie

 

[lbass]

What is in the formula for {@duedate}?

-LB

 

[kandikcj]

Currently I am using three formulas. 1 is used for determining when the last test was performed:
shared yearnum as number
dim RecentYear as number
shared recentmonth as number
yearnum = year(cdate({ORDER_PROC.ORDERING_DATE}))
recentyear = 0
if recentyear < yearnum then
recentyear = yearnum
recentmonth = month({ORDER_PROC.ORDERING_DATE})
end if
formula = (recentyear)

The second is used to calculate when the next test is due:
shared nextpapyr as number
Shared nextpapmo as number
shared recentmonth as number
select case {HM_MODIFIER.MODIFIER_NUM_C}
case 11004
'11004 is Q1 Yr Screening
nextpapyr = year({ORDER_PROC.ORDERING_DATE}) + 1
nextpapmo = recentmonth
case 11005
'11005 is Q2 Yr Screening
nextpapyr = year({ORDER_PROC.ORDERING_DATE}) + 2
nextpapmo = recentmonth
case 11006
'11005 is Q6 Mo Screening
nextpapyr = year({ORDER_PROC.ORDERING_DATE})
nextpapmo = recentmonth + 6
case 11007
'11005 is Q3 Mo Screening
nextpapyr = year({ORDER_PROC.ORDERING_DATE})
nextpapmo = recentmonth + 3
case 11008
'11005 is Q5 Yr Screening
nextpapyr = year({ORDER_PROC.ORDERING_DATE}) + 5
nextpapmo = recentmonth
end select
if nextpapmo > 12 then
nextpapmo = nextpapmo - 12
nextpapyr = nextpapyr + 1
end if
formula = (nextpapyr)

The third is used to display the month that the next test is due:
shared nextpapmo as number
formula = nextpapmo

If there is a better way to do this I am more than willing to change it.
Thanks,
Charlie

 

[lbass]

You might try the following:

Create a formula {@mos} which translates all screening frequencies to months:

select {HM_MODIFIER.MODIFIER_NUM_C}
case 11004 : 12
case 11005 : 24
case 11006 : 6
case 11007 : 3
case 11008 : 60
default : 0

Then insert a group on {table.patientID} and
go to report->edit selection formula->GROUP and enter:

date(year(dateadd(&quot;m&quot;,{@mos},maximum({ORDER_PROC.ORDERING_DATE},{table.patientID}))),
month(dateadd(&quot;m&quot;,{@mos},maximum({ORDER_PROC.ORDERING_DATE},{table.patientID}))), 01) =
date(year(dateadd(&quot;m&quot;,1,currentdate)),month(dateadd(&quot;m&quot;,1,currentdate)), 01)

This tests out here.

-LB

 

[kandikcj]

You are absolutely amazing!
Thank you very much,
Charlie