Using Wildmatch to Build an Expression

Ask a Question

Over a decade ago I wrote a blog post about conditional functions. One of the functions was wildmatch. This function does a string comparison and allows the use of wildcard variables (? and *) in the comparison string. The question mark (?) matches a single character and the asterisk (*) matches a sequence of characters. I was reintroduced to the wildcard function by a colleague when I was trying to dynamically build an expression based on selections. In this blog post, I will share how I used the wildmatch function to dynamically build an expression.

I had a base expression that I wanted to expand if a selection was made in a field. The base expression looked like this:

In my app, I had a filter pane where the user could select none or as many of the list items they wanted. If no selections were made, then the base expression should be used. If selections were made, then I wanted to add additional metrics to the expression.

For example, if Climber was selected, then the expression should look like the expression below. An extension of the base expression.

I ran into a dilemma when I wanted to update the expression based on all list items that were selected because I would not know in advance which list items, if any, would be selected. A nested if statement was not an option because there are many possible combinations of selections that could be made. I tried various expressions, but none worked quite the way I liked. I reached out to a colleague (shout out to @Charles_Bannon) to get a different perspective, and he had an example of an expression that used the wildmatch to dynamically build an expression. We updated my base expression to look like this:

Now, this looks like a lot but let’s first understand the first block of code and it will all make sense. I start with my base expression and then I add an if statement for each list item in the filter pane. Using GetFieldSelections, I capture any selections that were made in the Specialty filter pane. GetFieldSelections will return all selected list items separated by a comma. For example, if all list items were selected, it would return Climber, GC, One Day, Sprint, Time Trial. I then use the Wildmatch function with the asterisk to check for each specialty/list item. I used the asterisk because the list item could be anywhere in the string that the GetFieldSelections function returned. If WildMatch returns anything greater than 0 then it was found and therefore selected. In that case, I can add the respective expression to my base expression. If it was not selected, then I simply add 0. The same pattern is repeated throughout the expression. Each if statement checks to see if the list item was selected. If it was, add to the expression otherwise add 0.

I chose to blog about the Wildmatch function again because this was not a function that I use or that comes to mind when trying to solve a problem. It was exactly what I needed to expand my expression as the user made selections. This will no longer be a function that I forgot about. 😊

Thanks,

Jennell

You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.

Comment

Subscribe by Topic