I've got a define that includes an ampersand ("&"), but it is NOT a prompted value. Rather, it is part of a formula that I am passing out the result.

The define is:

 FLD1/A250='<td align=left x:num x:fmla="=SUMPRODUCT((E10:E1740=T(INDIRECT("A"&ROW())))*(F10:F1740=T(INDIRECT("B"&ROW()))))">0</td>';
 

So WebFOCUS thinks that the &ROW is a prompt.

How do I get WebFOCUS to NOT think that the "&" is a prompt?

Try adding the pipe | between the amper and the following letter.

HTH

Do a search on escape character.

One work around would be to define or -SET the ampersand as a variable and concatenate the variable in your second line.

  
-SET &R='&';

(now your DEFINEs will have to be.....)

FLD1/A250='<td align=left x:num x:fmla="=SUMPRODUCT((E10:E1740=T(INDIRECT("A"' || &R || 'ROW())))*(F10:F1740=T(INDIRECT("B"' || &R || 'ROW()))))">0</td>';

Sandeep Mamidenna

In the example above, make sure you use the strong concatenation symbol (||) not the weak one (|), to eliminate any unnecessary spaces.

WebFOCUS does not seem to like the "A" (the quotes).

FLD1/A250='<td align=left x:num x:fmla="=SUMPRODUCT((E10:E1740=T(INDIRECT("A"' || &R || 'ROW())))*(F10:F1740=T(INDIRECT("B"' || &R || 'ROW()))))">0</td>';

If you go that route, you need to put quotes around &R:

FLD1/A250='<td align=left x:num x:fmla="=SUMPRODUCT((E10:E1740=T(INDIRECT("A"' || '&R' || 'ROW())))*(F10:F1740=T(INDIRECT("B"' || '&R' || 'ROW()))))">0</td>';

Otherwise DEFINE will think & is the name of a Focus field.

(in fact you don't need &R at all -- '&', quotes included, is fine by itself.)


Another tack is to code a substitute character (say, ^), and then use CTRAN in a second define to transform it to &. That will be easier to code and to desk-check.

This message has been edited. Last edited by: j.gross,

That gives me an error:

HTML ERROR in Cell Formula : =SUMPRODUCT((E10:E1740=T(INDIRECT(

if I use:

FLD1/A250='<td align=left x:num x:fmla="=SUMPRODUCT((E10:E1740=T(INDIRECT(A' | '&R' | 'ROW())))*(F10:F1740=T(INDIRECT(B' | '&R' | 'ROW()))))">0</td>';

Then the whole formula gets out to Excel, but not the quotes around the "A" and "B".

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

The value stored in FLD1 is as below. Note my understores, indicating paired double-quotes and the content between them. Obviousy, there's a conflict between the outer double-quotes for x:fmla="..." and the inner ones around A and B (that's the basis of the error message, and the chunk of code quoted therein).

You need to change the outer ones to single quotes (coded in Focus as pairs of apostrophes), or escape the inner ones as \" (I think html respects that).

<td align=left x:num x:fmla="=SUMPRODUCT[[E10:E1740=T[INDIRECT["A"&ROW[))))*[F10:F1740=T[INDIRECT["B"&ROW[)))))">0</td>
                            ==================================== ================================== ============

Use this to expand on-hope it helps
DEFINE FILE CAR
AMPER/A1 = '&';
XX/A4 = 'H'||(' '|AMPER)||('H');
END
TABLE FILE CAR PRINT CAR XX BY COUNTRY
END

Ira
aix 533 WF 5.3.3(P), 5.3.7 (D) 7.6.1(T)

Assuming the formula is correct, it seems like the first suggestion by jimster06 is correct.

This

DEFINE FILE CAR
FLD1/A250='<td align=left x:num x:fmla="=SUMPRODUCT((E10:E1740=T(INDIRECT("A"&|ROW())))*(F10:F1740=T(INDIRECT("B"&|ROW()))))">0</td>';
END

Evaluates to this:

DEFINE FILE CAR
FLD1/A250='<td align=left x:num x:fmla="=SUMPRODUCT((E10:E1740=T(INDIRECT("A"&ROW())))*(F10:F1740=T(INDIRECT("B"&ROW()))))">0</td>';
END

You need three sets of nested quotes:
(1) outermost, the string indicator for Focus DEFINE (single quotes)
(2) next, in HTML, delimiting the value for xfmla (single or double quotes)
(3) innermost, in the Excel formula, within the arg of INDIRECT() (double quotes)

To avoid conflicts, use single-quotes for #2 -- escaping them (within the outer single-quotes) by coding pairs of single quotes -- thus:

FLD1/A250=
'<td align=left x:num xfmla=''=SUMPRODUCT((E10:E1740=T(INDIRECT("A"&ROW())))*(F10:F1740=T(INDIRECT("B"&ROW()))))''>0</td>'
;

That will place this value in FLD1:

 <td align=left x:num xfmla='=SUMPRODUCT((E10:E1740=T(INDIRECT("A"&ROW())))*(F10:F1740=T(INDIRECT("B"&ROW()))))'>0</td>

and that should do it.

The two single quotes fixed it.

My define:

 FLD2/A250='<td align=left x:num x:fmla=''=SUMPRODUCT((E10:E50000=T(INDIRECT( "A" ' | '&' | 'ROW())))*(F10:F50000=T(INDIRECT( "B" ' | '&' | 'ROW()))))''>0</td>';