Alternative to a massive nested IF?

[MTarkington]

Ok, first I'll apologize if there's been anything like this on here before, but I did not have any idea where to start looking.

I have a table that has 80 fields that correspond to each other (ex: Code_Authorized_1 - Authorized_units_1; Code_Authorized_2 - Authorized_units_2). I am creating a formula to display all 80 of these fields for the purpose of utilizing a parameter.

Now, the way Crystal is working is that if the first set of these fields is populated, but the second set is not, it will not display any of the fields, but if there are 2 sets of the fields populated, it will display them.

This is with the below formula:
trim(authorized_code_1)+'-'+trim(units_authorized_1)+';'+
trim(authorized_code_2)+'-'+trim(units_authorized_2)

I was planning on putting all 80 of these fields in a formula like the above, but it creates the problem I described. It will ignore the first string if the second string is not populated. We know for a fact that all 80 of the fields we are using are not populated, thus the above formula will not work.

My first thought is to create a massive nested if that looks like this:

if isnull(code_authorized_2) then
trim(code_authorized_1)+'-'+trim(units_authorized_1)
else
if isnull(code_authorized_3) then
trim(code_authorized_1)+'-'+trim(units_authorized_1)+';'+
trim(code_authorized_2)+'-'+trim(units_authorized_2)
else
if isnull(code_authorized_4) then
trim(code_authorized_1)+'-'+trim(units_authorized_1)+';'+
trim(code_authorized_2)+'-'+trim(units_authorized_2)+';'+
trim(code_authorized_3)+'-'+trim(units_authorized_3)
else...

And so on...

I know this code works. It is displaying the information we need, but for 40 sets of 2 corresponding fields, this will, hands down be the largest IF statement I have ever coded.

I am sure there has to be an easier way to do this, but I can't figure it out.

The database is in Cache. Using Crystal 10.

Please let me know if any of the above needs any kind of clarification. (I'll be surprised if it doesn't)

TIA,
Mark

 

[tpeters1]

Would this work?

<code>
local stringvar s;

if not isnull(code_authorized_1) then
s := s & trim(code_authorized_1) & '-' & trim(units_authorized_1);
if not isnull(code_authorized_2) then
s := s & trim(code_authorized_2) & '-' & trim(units_authorized_2);
if not isnull(code_authorized_3) then
s := s & trim(code_authorized_3) & '-' & trim(units_authorized_3);

s;
</code>

 

[lbass]

Can you explain how you plan to use this formula? You mention "I am creating a formula to display all 80 of these fields for the purpose of utilizing a parameter." How does using a parameter relate to this formula?

-LB

 

[Madawc]

You can use formula fields within formula field. So make @Trim1, which contains trim(code_authorized_1)+'-'+trim(units_authorized_1). Invoke it as @Trim1 rather than giving the full code on each line.

You can probably also 'hive off' some of the 'if' commands into separate commands, if you don't like having too many together.

Madawc Williams (East Anglia, UK)

 

[MTarkington]

Ok, I understand the concepts being explained here, so I will, more than likely, be using one of them.

Now, lbass asked about the parameter. My first thought was that I could throw all of the data from the 40/80 fields into a single formula field, w/ a delimeter of some sort, then I could set up a parameter that was search this formula for certain codes entered by the user.

Now, that I've actually tried to set it up as an example, it does not like the formula.

Does it even sound possible?

Thanks for the responses,
Mark

 

[tpeters1]

There is a maximum size for strings in formulas. Could you create an array instead?

 

[lbass]

I still don't understand what you are trying to do. If you are trying to display the two fields together for the purpose of a parameter selection so the user can see both fields at the same time, then you could create a report with the two fields, export it to a text file, remove punctuation and column labels, and then import the file into the parameter option screen in order to populate it. In the record selection formula the parameter could be set to the code field only.

-LB

 

[synapsevampire]

I liked this part "We know for a fact that all 80 of the fields we are using are not populated, thus the above formula will not work." If you know that all of them aren't populated, then you have nothing to display...

You can use an isnull check, or you can select File->Report Options->Convert null value to default, and then Crystal will use your original formula, however since some fields will now be "", you will get a series of dashes since they are hard coded to always display.

I think that the solution is to check the integrity of each field.

Modify tpeters to:

local stringvar s:="";
if not isnull(code_authorized_1) then
s := s & trim(code_authorized_1) & '-' & trim(units_authorized_1);
if not isnull(code_authorized_2) then
s := s & trim(code_authorized_2) & '-' & trim(units_authorized_2);
if not isnull(code_authorized_3) then
s := s & trim(code_authorized_3) & '-' & trim(units_authorized_3);
//...etc...
s

-k