Skip to Content
Former Member
Sep 08, 2016 at 04:35 PM

How do I display specific data based on a field with multiple values



First post, looking for some guidance and could not find my solution in the archives.

I have created a report that brings back data based on a specific field called the Adjustment Control Code (ACNT) which defines the value($) I need displayed. The ACNT field can contain up to 5 different values (Null,1,2,3,and 4). (side note: The field comes back as a string value so I have this option to use as well as a variable I created that converts these values to numbers 0,1,2,3,and 4). What I am trying to return is a single row for each account, based on what code is listed in this ACNT field. The problem is the ACNT field can come back with just a single value, or it can come back with a combination of values such as 2 and 4 or 0,1,2, and 4 as examples. Each time a different ACNT code is presented, it creates a new row to display. The problem is if the ACNT combination contains a 2, I only need to see the ($) value associated with that row. So with the situation where the account brings back ACNT codes Null,1,2,and 4, I only want the row in relation to the ACNT 2.

This is the variable I created in an attempt to bring back the $ value needed based on specific combinations of possible ACNT codes that could be presented:

=If([Adjustment Control Code])=("2") Then ([THPB List (Calc_-6)])

ElseIf ([Adjustment Control Code]) InList("2";"4") Then ([THPB List (Calc_-6)]) ElseIf ([Adjustment Control Code]) InList("2";"1") Then ([THPB List (Calc_-6)]) ElseIf ([Adjustment Control Code]) InList("2";"4";"1") Then ([THPB List (Calc_-6)]) Else ([Unit Price (Calc_65)])

It seems as if a filter would do the trick, but it has to be built with the conditional requirements above, not to exclude the other values if a 2 is not present.

Please advise.