Combine 2 fields of data into 1 field that I can group and/or sort by

[Kmatlock]

I am writing a sales and commission report that will pull information for a particular salesman based on a given parameter (salesman number).<br><br>However, the salesman numbers that need to be entered as paramenters are in two seperate fields ({salesman_1}, {salesman_2}). That's because a salesman may recieve 1.5%({salesman_1}) if he is the primary rep or 1% ({salesman_2}) if he is the secondary rep. In other cases the rep may recieve the full commission, so his number will be entered in both the primary and secondary fields. <br><br>&nbsp;I need to be able to enter one parameter (salesman number) and pull the total sales and commission information for that sales rep only.

 

[Ina]

You can achieve this by adding logic to a formula.<br><br>e.g.<br>have a parmeter that prompts for a name.<br>?Name<br><br>Create a formula that determines which column salesman is in.<br>@Name:<br>if {salesman_1} = ?Name then ....;<br>if {salesman_2} = ?Name then ....;<br><br>Or if you need to do record selection, you can also create additional formula with if then logic and give the whole SQL statement to be passed to the database.&nbsp;&nbsp;Insert this formula name in record selection:<br><br>@NameSQL<br>if {table.salesman_1} = ?Name and {table.salesman_2} &lt;&gt; ?Name<br>then {table.salesman_1} = ?Name<br>else<br>...<br><br>&nbsp;

 

[Kmatlock]

Thanks Ina. I'll give it a try