I did a search of the forum but didn't find an answer that fixes this for me.
I have a program that uses variables to know what category to run a report for. It uses SQL to get the data and I have SET statements to help create the SQL.
I get the following error whether I run it from the HTML page or stand alone from App Studio:

(FOC295) A VALUE IS MISSING FOR: &MPFIELD

Here's the code that sets the variables:

-SET &MPFIELD='Ministry_Partner_Number';
-SET &MPFIELD=IF &SUMMARY_CATEGORY CONTAINS 'Ministry' THEN IF &SUMMARY_VALUE EQ 123 OR 120 OR 100 OR 122 OR 243 THEN 'National_Ministry_Partner_Number' ELSE 'Ministry_Partner_Number';

-SET &Summ_Where = IF &SUMMARY_CATEGORY EQ 'Countrywide' THEN '' ELSE IF &SUMMARY_CATEGORY EQ 'Region' THEN ' and Policy_Region = ''&SUMMARY_VALUE.EVAL''' ELSE IF &SUMMARY_CATEGORY EQ 'State' THEN ' and Policy_State = ''&SUMMARY_VALUE.EVAL'''
-ELSE IF &SUMMARY_CATEGORY EQ 'Agency' THEN ' and Agency_Number = ''&SUMMARY_VALUE.EVAL''' ELSE IF &SUMMARY_CATEGORY CONTAINS 'Ministry' THEN ' and exists (select 1 from biprod.dbo.MST_CRM_ACCOUNT act where act.CRM_Unique_ID = mth.CRM_Unique_ID and act.&MPFIELD.EVAL = &SUMMARY_VALUE.EVAL)' ELSE '';


I ran it with ECHO=ALL and if the Summary Category is anything other than Ministry, &MPFIELD is blank, as you can see here:

-ELSE IF Agency EQ 'Agency' THEN ' and Agency_Number = ''231''' ELSE IF Agency CONTAINS 'Ministry' THEN ' and exists (select 1 from biprod.dbo.MST_CRM_ACCOUNT act where act.CRM_Unique_ID = mth.CRM_Unique_ID and act. = 231)' ELSE '';


The variables get set as needed and the report runs, but the error prevents me from checking &LINES after the SQL code because the error resets &LINES.

Any thoughts?

Thanks.

This message has been edited. Last edited by: DWaybright,

Deb

Try putting a -RUN after your -SET's and before the code.

quote:

-SET &MPFIELD=IF &SUMMARY_CATEGORY CONTAINS 'Ministry' THEN IF &SUMMARY_VALUE EQ 123 OR 120 OR 100 OR 122 OR 243 THEN 'National_Ministry_Partner_Number' ELSE 'Ministry_Partner_Number';

Above is not valid IF THEN ELSE
You are missing an ELSE
Let look at it with incremented position
Should it be

IF &SUMMARY_CATEGORY CONTAINS 'Ministry' THEN
   (IF &SUMMARY_VALUE EQ 123 OR 120 OR 100 OR 122 OR 243 THEN 'National_Ministry_Partner_Number' ELSE 'Ministry_Partner_Number')
ELSE ????

or

IF &SUMMARY_CATEGORY CONTAINS 'Ministry' THEN
   (IF &SUMMARY_VALUE EQ 123 OR 120 OR 100 OR 122 OR 243 THEN 'National_Ministry_Partner_Number' ELSE ????)
ELSE 'Ministry_Partner_Number'

Also, why set &MPFIELD to a specific value that you tried to change using an IF statement ?

-SET &MPFIELD='Ministry_Partner_Number';
-SET &MPFIELD=IF &SUMMARY_CATEGORY CONTAINS 'Ministry' THEN IF &SUMMARY_VALUE EQ 123 OR 120 OR 100 OR 122 OR 243 THEN 'National_Ministry_Partner_Number' ELSE 'Ministry_Partner_Number';

I suspect that the first -SET should be:

-DEFAULTH &MPFIELD='Ministry_Partner_Number'

Since Martin explained what is wrong, I would like to offer an alternative way to code this. Would this work for you?

-SET &MPFIELD=
-  IF &SUMMARY_CATEGORY CONTAINS 'Ministry' AND &SUMMARY_VALUE IN (100,120,122,123,120,243)
-    THEN 'National_Ministry_Partner_Number'
-    ELSE 'Ministry_Partner_Number';
-*
-SET &Summ_Where = DECODE &SUMMARY_CATEGORY(
-  'Region'   ' and Policy_Region = ''&SUMMARY_VALUE.EVAL'''
-  'State'    ' and Policy_State = ''&SUMMARY_VALUE.EVAL'''
-  'Agency'   ' and Agency_Number = ''&SUMMARY_VALUE.EVAL'''
-  'Ministry' ' and exists (select 1 from biprod.dbo.MST_CRM_ACCOUNT act where act.CRM_Unique_ID = mth.CRM_Unique_ID and act.&MPFIELD.EVAL = ''&SUMMARY_VALUE.EVAL'')'
-  ELSE '');

Kind of curious. In your original code, in the -SET for &Summ_Where, why is &SUMMARY_VALUE.EVAL in quotes for all values of &SUMMARY_CATEGORY except 'Ministry'?

This message has been edited. Last edited by: jgelona,

posted June 20, 2019 01:16 PM Hide Post
quote:
-SET &MPFIELD=IF &SUMMARY_CATEGORY CONTAINS 'Ministry' THEN IF &SUMMARY_VALUE EQ 123 OR 120 OR 100 OR 122 OR 243 THEN 'National_Ministry_Partner_Number' ELSE 'Ministry_Partner_Number';

Above is not valid IF THEN ELSE
You are missing an ELSE


D'oh! That was indeed the issue. Thanks Martin!

jgelona, the Ministry option was added more recently and I guess I just didn't make the effort for it to match the previous coder's style.