how to populate a parameter prompt even if it is null or empty string

[pectin232]

if isNUll({?ADDITIONAL_WORKERS})=true then {?ADDITIONAL_WORKERS} = 0 else
{?ADDITIONAL_WORKERS} = {?ADDITIONAL_WORKERS}

What I am trying to do is... in the prompt if the user or system finds no Additional workers then it will still return the default data of the person or what values is listed. As now if there is no data it will not return any data and make the section invisible. As this system is new... there is a lot of data quality issues, sometimes it can also be the system converted spaces [which we cannot see] as a value but we will not know . suggestions to above?

 

[Madawc]

It's not clear what you're after. If you want to combine ADDITIONAL_WORKERS with other data, try something like

if isNUll({?ADDITIONAL_WORKERS})
then "* No Additional Workers *"
else {?ADDITIONAL_WORKERS}

Then place @ShowAdditionalWorkers wherever you need it.

You don't need =true for an IsNull test. At least that has been true from Crystal 8.5 onwards, maybe before.

It helps to give your Crystal version - 8, 8.5, 9, 10, 11 or whatever. Methods sometimes change between versions, and higher versions have extra options. In this case, it probably makes no difference.

Madawc Williams (East Anglia, UK). Using Crystal 11.5 with SQL and Windows XP

 

[pectin232]

sorry for not being clear... if there is no value or user did not enter any values but to use the existing value that is in the listbox itself.

 

[pectin232]

I did this

if isNUll({?ADDITIONAL_WORKERS}) then {?ADDITIONAL_WORKERS} = '0'

it does not show any data... but when right click to do a browse the data.. it shows all 15 of them. How do I display it?

 

[lbass]

Please don't start multiple threads on the same topic. What version of CR are you using? Many versions don't allow a parameter to remain null.

-LB

 

[pectin232]

I am using 11.0 Thanks

 

[lbass]

What type of parameter is {?Additional_Workers}? Is it a string? A boolean? Do you want to use it in a selection formula? Or for some other reason? Please explain what you are trying to do.

In XI, you must enter a value for a parameter. So far you have not indicated a database field that you relates to this parameter. Are you just trying to use the parameter itself to format a section?

-LB

 

[Madawc]

When you replied to my earlier idea, you said you'd tried

if isNUll({?ADDITIONAL_WORKERS}) then {?ADDITIONAL_WORKERS} = '0'

I'd not expect that to work. Try

if isNUll({?ADDITIONAL_WORKERS}) then '0'
else {?ADDITIONAL_WORKERS}

Madawc Williams (East Anglia, UK). Using Crystal 11.5 with SQL and Windows XP

 

[pectin232]

It is a string. Users search for certain cases to see cases associated with the workers in charge. In most cases... the 2 fields are populated and another list box populated. Crystal will crash somehow if the 2 textbox fields and listbox fields are empty or null, etc. This rarely happens ..... only when a case is close and there are no persons listed in either of those boxes. I tried this
if isNUll({?ADDITIONAL_WORKERS}) then {?ADDITIONAL_WORKERS} = '0'

works when there is no data... but when I 'intentionally' add ID numbers to populate the fields as a test... it will not show. When I right click to browse the data it shows... the IDs I entered.. but just not displayed. or do a reset data?

 

[lbass]

I don't know what you mean by "listbox". Are you integrating CR with another application?

What do users enter when they are prompted for {?Additional_Workers} if they want to say "none"--because they have to enter something. Do they enter "0"?

How else is this parameter used? Do you reference it in another formula? What is the formula and where are you using it?

-LB

 

[pectin232]

It is stand alone. It is a front end java app which extract from the database to populate the 2 text fields which the user can select from a list of users. Sometimes when a case is close or whatever reason there is no data on these fields, Crystal report will bomb out. Is there a way for me to code in such a way that even when there is no data but to just show the default record? This parameter is used only for this reason.Based on the data on this field... crystal report will build the report based on the SQL I created

select full_name as name , contact,
decode(contact,'Not Available','NA',decode(instr(contact,'@'),0,'Work','Email')) as contacttype, address, wm_concat(role_name) as role
from (
select distinct p.person_id,(p.Last_Name||', '||p.First_Name||' '||p.Middle_Name) as full_name, r.role_name,
( CASE
WHEN a.domestic_address_line1 is null THEN 'Not Available'
ELSE a.domestic_address_line1||decode(a.city_name,null,'',', '||a.city_name)
||decode(a.state_code,null,'',', '||a.state_code)
||decode(a.zip5_code,null,'',' '||a.zip5_code)
END )as address,
decode(getPERSONCONTACT(p.person_id,'WORK'),' ', decode(getPERSONCONTACT(p.person_id,'EMAIL'),
' ','Not Available',getPERSONCONTACT(p.person_id,'EMAIL')),getPERSONCONTACT(p.person_id,'WORK')) as contact
from workload_item wi, work_assignment wa, work_assignment_role war, security_user su, person p,
role r, case_plan cp, party_address pa, address a, employee_work_history ewh
where wi.work_item_id = cp.case_id
and wa.workload_item_id = wi.workload_item_id
and wa.employee_Id = su.employee_Id
and su.person_id = p.person_id
and wa.employee_Id = ewh.employee_Id
and wa.work_assignment_id = war.work_assignment_id
and war.role_id = r.role_id
and p.person_id = pa.party_id(+)
and pa.address_id = a.address_id(+)
and ewh.current_job_flag = 1
and ewh.end_date is null
and su.end_date is null
and wi.work_item_type_code = 'CASE'
and wa.end_date is null
and pa.end_eff_date is null
and cp.case_plan_id in ({?CASE_PLAN_ID})
and p.person_id in ({?ADDITIONAL_WORKERS}))
group by full_name, contact , address
order by decode(instr(role, 'Primary Case Worker'),0,decode(instr(role, 'Supervisor'),0,3,2), 1)

 

[lbass]

Not sure, but try:

and (
p.person_id is null or
p.person_id in ({?ADDITIONAL_WORKERS}))
)

-LB

 

[pectin232]

Is that inthe Record selection formula? Thanks Lbass. For crystal I am quite new in this.

 

[lbass]

This is a suggested edit to your query as shown above.

-LB