Assigning field type according to data type

[Former Member]

I am creating a report in which the user can select the fields they want in the report from a parameter, and the order they select the fields is the order in which they show on the report. The issue that I have is making sure the data type is exported properly. I can convert everything to a string and it exports just fine, however, my users cannot perform calculations using strings, and their excel proficiency is limited at best.

Each field on the report has a formula (that of course does not work, which is why I am posing this question) that states:

Shared DateVar array FieldValDte<br>Shared NumberVar array FieldValNbr<br>Shared StringVar array FieldVal<br><br>if isdate(FieldValDte[1]) then FieldValDte[1] else<br>if isnumeric(FieldValNbr[1]) then FieldValNbr[1] else<br>FieldVal[1])

When saving this formula, I receive an error stating "A number is required here."

I have also tried:

Shared StringVar array FieldValDte;
Shared StringVar array FieldValNbr;<br>Shared StringVar array FieldVal;<br>if isdate(FieldValDte[1])then 
    CDate (FieldValDte[1]);if isnumeric (FieldValNbr[1])then 
    ToNumber (FieldValNbr[1]);IfNot isdate(FieldValDte[1])AndNot isnumeric (FieldValNbr[1])Then 
FieldVal[1];

When executing this, whatever data type the last "if" statement refers to is the data type assigned to the filed on the report. If that data type does not match the field that I need, then it displays blank on the report.

[former_member292966]

Hi Paul,

The error is caused because isnumber should be IsNumeric.

After you fix that though, none of your conditions will work because IsDate, IsNumeric need to be evaluated against a String field. So rather than setting your parameters as Date or Number, define them as String and then you can use IsDate and IsNumeric to determine their type and convert them using CDate and ToNumber.

Once you do that though, you'll still run into an issue because the nested If will ultimately cause you problems because the result of the IF will need to be the same datatype.

I broke out the If into three separate statements to accommodate the three separate possibilities:

Shared StringVar array FieldValDte; 
Shared StringVar array FieldValNbr; 
Shared StringVar array FieldVal; 

if isdate(FieldValDte[1]) then 
    CDate (FieldValDte[1]); 

if isnumeric (FieldValNbr[1]) then 
    ToNumber (FieldValNbr[1]); 

If Not isdate(FieldValDte[1]) And 
    Not isnumeric (FieldValNbr[1]) Then 
FieldVal[1];

Give this a try and see if it works.

Brian

[Former Member]

Thank you Brian! I should have shared in my original question that I have tried splitting the If - Then out to separate statements as you have outlined, however, the last data type in the chain wins no matter what the actual data type is. So in your example above, the result of the formula is always the string value. If I change the order so that the "isNumeric" is last, the number field is what is displayed, no matter if it is a number, date, or string. This has been truly maddening and I have been working on this for almost a month now