Modify formula in select statement
Hi, I need some help modifying a formula in the select statement. There are other parameters involved (like CaseSense) but the main one I'm focused on now is "Keyword". I want to add a second parameter (Keyword2 and maybe even Keyword3) to this. I want these to be using the "OR" operator. Can someone provide the change for this?
Just a little background on this report we are using. This basically searches one of the fields for specific keywords. You can use ? or * as wildcards currently but it doesn't support searching multiple keywords throughout the report. It will only search for consecutive keywords (which is good also and we want to keep that). I want to give the end users the option to search for multiple keywords throughout the report also though.
I have one more additional request. How much more difficult is it to put logic in so it will take symbols (like || or &&) instead of using the OR operator? So instead of using multiple "Keyword" parameters, I want to keep it currently at one but add some logic into it so if the user puts in specific characters or symbols, it will pick it up as an AND or OR operator. For example, if they put in "apples||oranges" it will search for apples or oranges...or "apples&&oranges" will search for reports that contain both words.
Attached is a sample text of the formula currently used in the select expert.
Thanks.
Select-Statement.txt
Just a little background on this report we are using. This basically searches one of the fields for specific keywords. You can use ? or * as wildcards currently but it doesn't support searching multiple keywords throughout the report. It will only search for consecutive keywords (which is good also and we want to keep that). I want to give the end users the option to search for multiple keywords throughout the report also though.
I have one more additional request. How much more difficult is it to put logic in so it will take symbols (like || or &&) instead of using the OR operator? So instead of using multiple "Keyword" parameters, I want to keep it currently at one but add some logic into it so if the user puts in specific characters or symbols, it will pick it up as an AND or OR operator. For example, if they put in "apples||oranges" it will search for apples or oranges...or "apples&&oranges" will search for reports that contain both words.
Attached is a sample text of the formula currently used in the select expert.
Thanks.
Select-Statement.txt
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
The parsing of something like that is probably beyond the capability of a Crystal formula
If you mix && and || you have an issue as to the order of operations.
In your quesiton you mention consecutive keywords. Does that mean you want apple && orange to match apple ......orange or do you need it to match orange ...... apple
Is it a full word match or will substring matches work? For instance does Apple match Apples?
mlmcc
If you mix && and || you have an issue as to the order of operations.
In your quesiton you mention consecutive keywords. Does that mean you want apple && orange to match apple ......orange or do you need it to match orange ...... apple
Is it a full word match or will substring matches work? For instance does Apple match Apples?
mlmcc
ASKER
Right now, if we input "one apple" for example, it will only return results that have those two words together (which is fine). We just want to give the users more options, so if they want to search for say "one" or "apple" separately, it will return those results.
Currently it should match the entire word only unless a wildcard is used. Unless Apple* or Apple? is used, I don't think Apples will show up.
If you can come up with just using && or ||, that would be great also. I guess I can just create two separate reports if they really need both options.
Thanks.
Currently it should match the entire word only unless a wildcard is used. Unless Apple* or Apple? is used, I don't think Apples will show up.
If you can come up with just using && or ||, that would be great also. I guess I can just create two separate reports if they really need both options.
Thanks.
If you have a lot of records, it would probably be much better if you could handle this on the server (assuming that your datasource is a db), like in a stored procedure, or in a query entered in a CR Command. I'm guessing that a record selection formula that complex (and even more complex with the changes that you're trying to make) will not be passed to the server, so the server will send all of the records to the report, and the report will do the filtering. If you're talking about a lot of records, that's going to take a while and tie up the server and network bandwidth.
Having said that, one idea for doing this in CR would be two multi-value parameters, one for "and" and one for "or". For example, ?all_of_these_words and ?any_of_these_words. The record selection formula could theoretically loop through the values in each parameter and test each value (like the checks that you're doing now), and combine the results based on which parameter each value was in. That seems easier than trying to parse a string like "apples||oranges&&plums". You could treat the two parameters as separate (include all records that match _either_ parameter), or combined (include only the records that match _both_ parameters).
That's off the top of my head. It seems like it could be done, but it's not the kind of thing I would _want_ to do in a record selection formula.
Or, for a somewhat simpler approach, you could just have some separate "and" and "or" parameters (instead of multi-value parameters). Like ?and1, ?and2, ?or1 and ?or2. Those are terrible names, but hopefully you get the idea. :-) That would be simpler than trying to go through the values in some multi-value parameters.
FWIW, I think it would be possible to parse a string like "apples||oranges&&plums". It wouldn't be my first choice, but I think it could be done, although, like mlmcc said, there is a question of how to handle conditions that include "and" and "or". Normally, "and" has precedence, so (apples || oranges && plums) would equate to (apples or (oranges and plums)), meaning records that contain "apples", or contain both "oranges" and "plums". How would you want something like "apples||oranges&&plums" interpreted?
FWIW, I think that could be one potential benefit to using separate "and" and "or" parameters. You set the report up to include the records that match both sets of values, or either set, and let the users know which way it works, as opposed to them entering a string using "||" and "&&" and not getting the records they were looking for, because the report is not interpreting the string the same way they do.
James
Having said that, one idea for doing this in CR would be two multi-value parameters, one for "and" and one for "or". For example, ?all_of_these_words and ?any_of_these_words. The record selection formula could theoretically loop through the values in each parameter and test each value (like the checks that you're doing now), and combine the results based on which parameter each value was in. That seems easier than trying to parse a string like "apples||oranges&&plums". You could treat the two parameters as separate (include all records that match _either_ parameter), or combined (include only the records that match _both_ parameters).
That's off the top of my head. It seems like it could be done, but it's not the kind of thing I would _want_ to do in a record selection formula.
Or, for a somewhat simpler approach, you could just have some separate "and" and "or" parameters (instead of multi-value parameters). Like ?and1, ?and2, ?or1 and ?or2. Those are terrible names, but hopefully you get the idea. :-) That would be simpler than trying to go through the values in some multi-value parameters.
FWIW, I think it would be possible to parse a string like "apples||oranges&&plums". It wouldn't be my first choice, but I think it could be done, although, like mlmcc said, there is a question of how to handle conditions that include "and" and "or". Normally, "and" has precedence, so (apples || oranges && plums) would equate to (apples or (oranges and plums)), meaning records that contain "apples", or contain both "oranges" and "plums". How would you want something like "apples||oranges&&plums" interpreted?
FWIW, I think that could be one potential benefit to using separate "and" and "or" parameters. You set the report up to include the records that match both sets of values, or either set, and let the users know which way it works, as opposed to them entering a string using "||" and "&&" and not getting the records they were looking for, because the report is not interpreting the string the same way they do.
James
I think that is a good idea. It would be easier to code.
mlmcc
mlmcc
ASKER
Regarding the "apples||oranges&&plums" interpretation, it would be in order or precedence so it will return only those records that have "apples" or "oranges...plums" (orange and plums).
Yes, I just brought up the more complicated && and || to see if it was possible. The additional parameters were definitely the first choice I wanted but I'm not sure how to do that either. Can someone post the modifications to include two extra OR parameters? If possible, add 3 AND parameters to it also.
Thanks.
Yes, I just brought up the more complicated && and || to see if it was possible. The additional parameters were definitely the first choice I wanted but I'm not sure how to do that either. Can someone post the modifications to include two extra OR parameters? If possible, add 3 AND parameters to it also.
Thanks.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks for the details James. It seems pretty straight forward. I'll give it a try.
ASKER
I made all the changes for the OR keywords only to see how it would work out. It looks like the report will pull everything if I leave any of the keyword parameters blank. I think I followed everything you mentioned including adding the check for blanks in the select expert. How do I make it so if one of the parameters is empty or blank to ignore it?
Thanks.
Thanks.
What version of Crystal?
Versions before CR2008 require a value for each password. CR2008 and CR2011 allow optional parameters
mlmcc
Versions before CR2008 require a value for each password. CR2008 and CR2011 allow optional parameters
mlmcc
ASKER
Crystal Reports XI
All parameters will require a value.
mlmcc
mlmcc
The checks for "" were supposed to "ignore" the "empty" parameters, by having the tests for a parameter return True if the parameter was blank, but you're right - If you don't enter anything for any parameters, you get everything.
Maybe you could just add a separate check for the case when all of the parameters are blank?
({?OrWord1} <> "" or {?OrWord2} <> "" or {?OrWord3} <> "" or
{?AndWord1} <> "" or {?AndWord2} <> "" or {?AndWord3} <> "") and
(
({?OrWord1} = "" or {@check_OrWord1}) or ({?OrWord2} = "" or {@check_OrWord2}) or
({?OrWord3} = "" or {@check_OrWord3})
)
and
(
({?AndWord1} = "" or {@check_AndWord1}) and ({?AndWord2} = "" or {@check_AndWord2}) and
({?AndWord3} = "" or {@check_AndWord3})
)
If all of the parameters are blank, the first part will be False, so the record selection formula will be False.
For the record, I'm talking about the parameters getting a blank/empty value (""), as opposed to no value at all. mlmcc is talking about not passing any value at all to a parameter. I'm assuming that you're just talking about a parameter that has been given a default value of an empty string and the user hasn't entered a different value for it, so it's just "".
James
Maybe you could just add a separate check for the case when all of the parameters are blank?
({?OrWord1} <> "" or {?OrWord2} <> "" or {?OrWord3} <> "" or
{?AndWord1} <> "" or {?AndWord2} <> "" or {?AndWord3} <> "") and
(
({?OrWord1} = "" or {@check_OrWord1}) or ({?OrWord2} = "" or {@check_OrWord2}) or
({?OrWord3} = "" or {@check_OrWord3})
)
and
(
({?AndWord1} = "" or {@check_AndWord1}) and ({?AndWord2} = "" or {@check_AndWord2}) and
({?AndWord3} = "" or {@check_AndWord3})
)
If all of the parameters are blank, the first part will be False, so the record selection formula will be False.
For the record, I'm talking about the parameters getting a blank/empty value (""), as opposed to no value at all. mlmcc is talking about not passing any value at all to a parameter. I'm assuming that you're just talking about a parameter that has been given a default value of an empty string and the user hasn't entered a different value for it, so it's just "".
James
ASKER
Thanks. I'll give that a try also.
ASKER
Thanks for all the help. Still seem to be running into some problems with it. It seems to be accepting "blank" as a criteria also and returning everything basically. I'll try to figure this out unless anyone else has other ideas that I can try. If I only use Keyword1 for example and leave Keyword2 and Keyword3 blank, it returns everything.
Accepting solution now as it has dragged on longer than necessary.
Accepting solution now as it has dragged on longer than necessary.
I don't know what your formula looks like now, but I see now that the section for the "or words" (if you're using that) would do that. I swear, it seemed to make sense when I wrote it. :-)
No guarantees (I've thought I had it figured out before), but if you don't want an empty "or word" parameter to affect the results, try changing the "or" section to something like this:
(
({?OrWord1} <> "" and {@check_OrWord1}) or ({?OrWord2} <> "" and {@check_OrWord2}) or
({?OrWord3} <> "" and {@check_OrWord3})
)
James
No guarantees (I've thought I had it figured out before), but if you don't want an empty "or word" parameter to affect the results, try changing the "or" section to something like this:
(
({?OrWord1} <> "" and {@check_OrWord1}) or ({?OrWord2} <> "" and {@check_OrWord2}) or
({?OrWord3} <> "" and {@check_OrWord3})
)
James
ASKER
Thanks for all your help on this James. I'll give this another try.
<sigh> Just noticed another potential issue.
I had a set of checks for the "or words" and a set of checks for the "and words", joined by AND. That made sense at the time, because I set the formula up so that an empty parameter meant "true". So, for example, if you didn't enter any "or words", that whole section would evaluate to True, so you'd end up with
True and ("and word" tests)
But with the new version of the "or word" tests, empty parameters mean "false", so if you don't enter any "or words", that section comes out False, so you'd end up with
False and ("and word" tests)
so, you wouldn't get any records.
I think you need to change the AND in between the "or word" and "and word" tests to OR.
And if you're using a separate set of tests before those to see if all of the parameters are empty (as shown in one of my previous posts), then I think you should put all of the "or word" and "and word" tests, combined, in () too. Without those, in pseudo-code, you would have:
(<empty param tests>)
and
(<"or word" tests>)
or
(<"and word" tests>)
CR would do the AND first, which is not what you want. What you want is:
(<empty param tests>)
and
(
(<"or word" tests>)
or
(<"and word" tests>)
)
If you still have any problems or questions, can you post your formula? I'm just making guesses about what it looks like now.
Also, FWIW, I think the empty parameter tests could be simplified a bit to
({?OrWord1} + {?OrWord2} + {?OrWord3} +
{?AndWord1} + {?AndWord2} + {?AndWord3} <> "")
Just concatenate all of the parameters together and if you don't get an empty string, they're not all empty. Or you could test them separately, as in my previous post. This is just a bit shorter.
James
I had a set of checks for the "or words" and a set of checks for the "and words", joined by AND. That made sense at the time, because I set the formula up so that an empty parameter meant "true". So, for example, if you didn't enter any "or words", that whole section would evaluate to True, so you'd end up with
True and ("and word" tests)
But with the new version of the "or word" tests, empty parameters mean "false", so if you don't enter any "or words", that section comes out False, so you'd end up with
False and ("and word" tests)
so, you wouldn't get any records.
I think you need to change the AND in between the "or word" and "and word" tests to OR.
And if you're using a separate set of tests before those to see if all of the parameters are empty (as shown in one of my previous posts), then I think you should put all of the "or word" and "and word" tests, combined, in () too. Without those, in pseudo-code, you would have:
(<empty param tests>)
and
(<"or word" tests>)
or
(<"and word" tests>)
CR would do the AND first, which is not what you want. What you want is:
(<empty param tests>)
and
(
(<"or word" tests>)
or
(<"and word" tests>)
)
If you still have any problems or questions, can you post your formula? I'm just making guesses about what it looks like now.
Also, FWIW, I think the empty parameter tests could be simplified a bit to
({?OrWord1} + {?OrWord2} + {?OrWord3} +
{?AndWord1} + {?AndWord2} + {?AndWord3} <> "")
Just concatenate all of the parameters together and if you don't get an empty string, they're not all empty. Or you could test them separately, as in my previous post. This is just a bit shorter.
James
ASKER
I didn't get much time to work on this lately. The last thing I had was the select statement you had previously on 4/9:
({?OrWord1} <> "" or {?OrWord2} <> "" or {?OrWord3} <> "" or
{?AndWord1} <> "" or {?AndWord2} <> "" or {?AndWord3} <> "") and
(
({?OrWord1} = "" or {@check_OrWord1}) or ({?OrWord2} = "" or {@check_OrWord2}) or
({?OrWord3} = "" or {@check_OrWord3})
)
I'm only using OR for now to see how it works.
Unfortunately, the timing is not right. My contract just ended and my access is removed. I will have to work on this on the next contract (which should be using a similar application) and connect to that database to continue working on this.
If I still have problems, I will post back or create a new post and link to this one when the time comes. Thanks for your continued help on this problem.
({?OrWord1} <> "" or {?OrWord2} <> "" or {?OrWord3} <> "" or
{?AndWord1} <> "" or {?AndWord2} <> "" or {?AndWord3} <> "") and
(
({?OrWord1} = "" or {@check_OrWord1}) or ({?OrWord2} = "" or {@check_OrWord2}) or
({?OrWord3} = "" or {@check_OrWord3})
)
I'm only using OR for now to see how it works.
Unfortunately, the timing is not right. My contract just ended and my access is removed. I will have to work on this on the next contract (which should be using a similar application) and connect to that database to continue working on this.
If I still have problems, I will post back or create a new post and link to this one when the time comes. Thanks for your continued help on this problem.
OK, this is really getting annoying now (and kind of embarrassing). :-) I've spotted another issue.
If you change the operator between the "or word" tests and "and word" tests to OR, as I suggested in my last post, you'd need to change the "and word" section too. As it is, if you use OR and the user doesn't enter any "and words", you would get every record, regardless of the "or words".
Here we go again. :-)
// Make sure at least one parameter is not empty
(
{?OrWord1} <> "" or {?OrWord2} <> "" or {?OrWord3} <> "" or
{?AndWord1} <> "" or {?AndWord2} <> "" or {?AndWord3} <> ""
)
and
(
// Check the "or words"
(
// One or more "or words" is not empty and matches
({?OrWord1} <> "" and {@check_OrWord1}) or
({?OrWord2} <> "" and {@check_OrWord2}) or
({?OrWord3} <> "" and {@check_OrWord3})
)
or
// Check the "and words"
(
// Make sure at least one "and word" is not empty
({?AndWord1} <> "" or {?AndWord2} <> "" or {?AndWord3} <> "") and
(
// All of the non-empty "and words" match
({?AndWord1} = "" or {@check_AndWord1}) and
({?AndWord2} = "" or {@check_AndWord2}) and
({?AndWord3} = "" or {@check_AndWord3})
)
)
)
I realize that you may not be able to use this right now, but I wanted to get this latest version worked out while it was still fresh on my mind.
James
If you change the operator between the "or word" tests and "and word" tests to OR, as I suggested in my last post, you'd need to change the "and word" section too. As it is, if you use OR and the user doesn't enter any "and words", you would get every record, regardless of the "or words".
Here we go again. :-)
// Make sure at least one parameter is not empty
(
{?OrWord1} <> "" or {?OrWord2} <> "" or {?OrWord3} <> "" or
{?AndWord1} <> "" or {?AndWord2} <> "" or {?AndWord3} <> ""
)
and
(
// Check the "or words"
(
// One or more "or words" is not empty and matches
({?OrWord1} <> "" and {@check_OrWord1}) or
({?OrWord2} <> "" and {@check_OrWord2}) or
({?OrWord3} <> "" and {@check_OrWord3})
)
or
// Check the "and words"
(
// Make sure at least one "and word" is not empty
({?AndWord1} <> "" or {?AndWord2} <> "" or {?AndWord3} <> "") and
(
// All of the non-empty "and words" match
({?AndWord1} = "" or {@check_AndWord1}) and
({?AndWord2} = "" or {@check_AndWord2}) and
({?AndWord3} = "" or {@check_AndWord3})
)
)
)
I realize that you may not be able to use this right now, but I wanted to get this latest version worked out while it was still fresh on my mind.
James
ASKER
Thanks James. I do appreciate all the follow-ups on this when you notice other problems. Will definitely give this a try when I'm able to.
ASKER
The sample input you mentioned above is perfect for what we need it for. We just need to have one or two more keyword parameters to use (either OR or AND). Definitely want to have the wildcards available as well.
So, either two &&, two || or one of each.