Hi there! I hope I can be clear about my quandary:
I'm in the process of creating a report consisting of transactions between clients. My table columns consist of SENDER NAME, SENDER ADDRESS, RECEIVER NAME, RECEIVER ADDRESS, DATE and TRANSACTION AMT.
My final report needs to consist of those entries that contain certain keywords or partial words (for example, any field that has the word, but not necessarily ONLY the word, "ignition"). I need to search all 4 of the name / address fields.
There are about 100 keywords. :eek:
Is there an easier way to create a filter that would search all 4 of the columns rather than creating 400 expressions of
If not, can the filter handle that much criteria?
Thanks for your help!
I don't if a lookup field can contain 100 elements, but you might want to try it.
Create a calculated field and select lookup.
Click the lookup tab. Select your input field (you're using 4 fields. we'll call them A, B, C and D). eg: Field_A. On the input value line enter one of you criteria ("ignition"). Output value is set to "x" or whatever you like.
Match is set to input field contains input value. "Output value when no match is found" is set to null.
To reduce typing just duplicate the calculated field and give it a new name(lookup_B) and change the input field (FIELD_B).
Then you should be able to set a filter like this
lookup_A = "x" .or.
lookup_B= "x" .or.
lookup_C = "x" .or.
lookup_D = "x"
I've used this technique and it's worked for me, but I've only had to input 10 - 15 criteria.
If it doesn't accept 100 entries, you may need to create more that one lookup table for each field.
I was wondering if, since you are working with transaction data, there was a transaction number in your report that would make each transaction unique? Capture the fields you need, including transaction number without filtering the data. Then you could create a summary based on the transaction # column that captures all records. Then under the Key Field properties matching tab, specify the values you are looking for. Not sure if this will work for you or not. Good luck.