Too many iif statements

[theresao]

I am editing a report created by a software package.&nbsp;&nbsp;I would like to substitute a phone number for a code field. I am using this iif statement: <br>iif(db.field=14,&quot;14-8880000&quot;,iif(db.field=15,&quot;15-8880001&quot;,db.field))<br>I would like to substitute 30 codes but ran out of room in the Expression Builder.&nbsp;&nbsp;How else can I get the phone numbers on the report?&nbsp;&nbsp;<br>I am not a foxpro programmer, obviously. <br>

 

[jonscott8]

theresao,<br>&nbsp;&nbsp;I assume the codes are stored in a table because of the 'db.field'. Are the phone numbers stored in a table too, are were you planning on hard coding them in?<br>&nbsp;&nbsp;If they arent stored, you could create a cross reference table with the fields: code and phone. Populate the table accordingly, code=14,phone=8880000...for all 30 codes.<br>&nbsp;&nbsp;Add your new table to the data environment of the report. Then set a relation from the original table to your new table using Code as your relational field.<br>&nbsp;&nbsp;Then your report field expression would be IIF(!EOF(newtable),newtable.phone,'')<br><br>&nbsp;&nbsp;Another alternative, although really bad design, is to add the phone field to your original table and input the phone numbers accordingly. Like I said, this is bad design because you are duplicating the phone numbers for everytime a code exists. But this is easier to a non-programmer, and if drive space and design arent issues, use this method and then all you have to do is place the phone field on your report.<br><br>Jon

 

[foxdev]

JonScott8's solutions would work, and he is right about the denormalization issues.<br><br>If you have the FoxPro database development system, an alternative would be creating a user-defined function (UDF).&nbsp;&nbsp;Though creating a new UDF program sounds like more than what you want to deal with, it would actually be a lot cleaner and clearer than cramming in a bunch of IIFs.&nbsp;&nbsp;After creating the procedure, you'd just make the single function call within the report. <p>Robert Bradley<br><a href=mailto: > </a><br><a href=

http://www.foxdev.com/>FoxDev

- Visual FoxPro Development</a><br>

 

[MikeWood]

FoxDev's point about a UDF is the correct way to go.<br><br>You need a <b>SET PROCEDURE TO someprocedure.prg</b> before running the report.<br><br>someprocedure would be best coded with a case...endcase structure like so<br><b><br>FUNCTION someprocedure<br>PARAMETERS ndbfield<br>STORE &quot;&quot; TO cValue<br>DO CASE<br>&nbsp;&nbsp;&nbsp;CASE ndbfield=14<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;cValue =&quot;14-8880000&quot;<br>&nbsp;&nbsp;&nbsp;CASE ndbfield=15<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;cValue =&quot;15-8880001&quot;<br>&nbsp;&nbsp;&nbsp;OTHERWISE<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;cValue = STR(ndbfield)<br>ENDCASE<br>RETURN cValue</b><br><br>Then the value placed on the report would be:<br><br><b>someprocedure(db.field)</b><br><br><br> <p>Mike Wood<br><a href=mailto:mwood@awod.com>mwood@awod.com</a><br><a href= > </a><br>