selection formula to display records

[Former Member]

Hi,

Please find attached parameter prompt and formula:

1. the first one is Static date range parameter
2. the second one is dynamic cascading for Requisitioner code and Requisition no.
3. the last one is multivalue static parameter

For 2nd  & 3rd parameter I have set optional value = True, to display all records. Date range is mandatory, others if selected records should get filtered or else all records within date range should get displayed.

Formula used:

{PDOC_PRO_INDENT_TRACKING_A.REQUISITION_DATE} >= {?Reqdate}

and

{PDOC_PRO_INDENT_TRACKING_A.REQUISITION_DATE} <= {?Reqdate}

and

if not (hasvalue(({?ReqCode - REQUISITIONER_CODE})))

then true

else if

hasvalue({?ReqCode - REQUISITIONER_CODE})

then

{PDOC_PRO_INDENT_TRACKING_A.REQUISITIONER_CODE} = {?ReqCode - REQUISITIONER_CODE}

and

if not (hasvalue(({?ReqCode - REQUISITION_NO})))

then true

else if

hasvalue({?ReqCode - REQUISITION_NO})

then

{PDOC_PRO_INDENT_TRACKING_A.REQUISITION_NO} = {?ReqCode - REQUISITION_NO}

and

if not (hasvalue(({?PartNo})))

then true

else if

hasvalue({?PartNo}) then

{PDOC_PRO_INDENT_TRACKING_A.PART_NO}={?PartNo}

________________________________________________________________________________________

Based on the above formula records are not getting filtered correctly.

Please suggest me a formula to filter records correctly.

Thanks,

Karan Sheth

[abhilash_kumar · Accepted solution]

Hi Karan,

Please see if this works:

{PDOC_PRO_INDENT_TRACKING_A.REQUISITION_DATE} IN {?Reqdate}

AND

(

  (not (hasvalue({?ReqCode - REQUISITIONER_CODE})) OR {PDOC_PRO_INDENT_TRACKING_A.REQUISITIONER_CODE} = {?ReqCode - REQUISITIONER_CODE})

  and

  (not (hasvalue({?ReqCode - REQUISITION_NO})) OR {PDOC_PRO_INDENT_TRACKING_A.REQUISITION_NO} = {?ReqCode - REQUISITION_NO})

  and

  (not (hasvalue({?PartNo})) OR {PDOC_PRO_INDENT_TRACKING_A.PART_NO}={?PartNo})

)

-Abhilash

[Former Member · Accepted solution]

Hi Abhilash,

Thanks a lot. It works.

Can you please explain me the below code:

What is the function of OR in the below code?

  (not (hasvalue({?ReqCode - REQUISITIONER_CODE})) OR {PDOC_PRO_INDENT_TRACKING_A.REQUISITIONER_CODE} = {?ReqCode - REQUISITIONER_CODE})

  and

  (not (hasvalue({?ReqCode - REQUISITION_NO})) OR {PDOC_PRO_INDENT_TRACKING_A.REQUISITION_NO} = {?ReqCode - REQUISITION_NO})

  and

  (not (hasvalue({?PartNo})) OR {PDOC_PRO_INDENT_TRACKING_A.PART_NO}={?PartNo})

Thanks,

Karan Sheth

[abhilash_kumar · Accepted solution]

(not (hasvalue({?ReqCode - REQUISITIONER_CODE})) OR {PDOC_PRO_INDENT_TRACKING_A.REQUISITIONER_CODE} = {?ReqCode - REQUISITIONER_CODE})

IF you leave the prompt blank, this part of the statement returns TRUE:

not (hasvalue({?ReqCode - REQUISITIONER_CODE})).

and hence it returns all possible data for this field.

If you select a value in the prompt, this part of the statement executes:

{PDOC_PRO_INDENT_TRACKING_A.REQUISITIONER_CODE} = {?ReqCode - REQUISITIONER_CODE}

and hence it evaluates against each row and checks whether the value matches the prompt value chosen.

-Abhilash

[Former Member · Accepted solution]

Hi Abhilash,

Thanks a lot for your explanation.

Thanks,

Karan Sheth