Filtering by First Value in a String?
Altair EmployeeHello,
I have a field that pulls contract numbers but we only need ones that start with a C or a V.
I am trying to use a formula filter with the formula LEFT([Field],1)="c" .or. "v" , but it is giving me an error.
Examples of the contract numbers that are pulled are B687458, C786545, G789545, and V789545. What would be a formula I could use in either a filter or calculated field to give me these results?
Thank you,
Kris
Answers
-
Forgot to mention that I am using Monarch Classic 13 and am fairly new and self taught to the program. Thank you!
0 -
Kristine,
Welcome to the Community.
Just to confirm things ....
Are you working with a "report" or something that is used as a "database"?
If a report I assume you have created a template, trapped the fields you need and the result is a populated table that you now need to filter some more.
Just to see what is going on a little more clearly try creating one filter for "C" and another for "V" and checking that both give the result you expect.
You can then create a compound filter to see what happens what you use them together.
As written your formula is not as it needs to be.
You could write it as:
LEFT([Field],1)="c" .or. Left([Field],1)="v"
Or
LEFT([Field],1).IN.("c","v")
Or simply define each filter separately as previously suggested (they may also be useful like that?) and then create a compound filter that includes both of them, getting 3 filters for the price of 2!
(I should point out the these are no the only options here but they are the ones that seem to make some sense for your immediate needs.)
HTH.
Grant
0 -
Altair Forum User said:
Kristine,
Welcome to the Community.
Just to confirm things ....
Are you working with a "report" or something that is used as a "database"?
If a report I assume you have created a template, trapped the fields you need and the result is a populated table that you now need to filter some more.
Just to see what is going on a little more clearly try creating one filter for "C" and another for "V" and checking that both give the result you expect.
You can then create a compound filter to see what happens what you use them together.
As written your formula is not as it needs to be.
You could write it as:
LEFT([Field],1)="c" .or. Left([Field],1)="v"
Or
LEFT([Field],1).IN.("c","v")
Or simply define each filter separately as previously suggested (they may also be useful like that?) and then create a compound filter that includes both of them, getting 3 filters for the price of 2!
(I should point out the these are no the only options here but they are the ones that seem to make some sense for your immediate needs.)
HTH.
Grant
Grant,
I was going to suggest Left([Field], 1) = "C" .Or. Left([Field], 1) = "V" but your formula that uses .In. is intriguing to me as I've had some struggles understanding and implementing that operator. Thanks for sharing.
-Stephen
0 -
Altair Forum User said:
Grant,
I was going to suggest Left([Field], 1) = "C" .Or. Left([Field], 1) = "V" but your formula that uses .In. is intriguing to me as I've had some struggles understanding and implementing that operator. Thanks for sharing.
-Stephen
Hi Stephen,
Glad you like it.
If you can be sure that the .IN. operator will not produce unexpected results - it is pretty much pinned down in this use need example to compare just a single character position - it can be so much nicer to use and maintain than the longer alternatives.
That said, if unsure about it, the "long hand" versions are rock solid approaches and have withstood what must be coming up to (or is it past now?) 25 years of use.
Grant
0 -
Altair Forum User said:
Kristine,
Welcome to the Community.
Just to confirm things ....
Are you working with a "report" or something that is used as a "database"?
If a report I assume you have created a template, trapped the fields you need and the result is a populated table that you now need to filter some more.
Just to see what is going on a little more clearly try creating one filter for "C" and another for "V" and checking that both give the result you expect.
You can then create a compound filter to see what happens what you use them together.
As written your formula is not as it needs to be.
You could write it as:
LEFT([Field],1)="c" .or. Left([Field],1)="v"
Or
LEFT([Field],1).IN.("c","v")
Or simply define each filter separately as previously suggested (they may also be useful like that?) and then create a compound filter that includes both of them, getting 3 filters for the price of 2!
(I should point out the these are no the only options here but they are the ones that seem to make some sense for your immediate needs.)
HTH.
Grant
I finally got back to this project and tried the first suggested formula and it worked great.
Thank you so much for your help Grant!
0 -
Altair Forum User said:
I finally got back to this project and tried the first suggested formula and it worked great.
Thank you so much for your help Grant!
It's a pleasure Kristine. Glad you got it working - you were very close in your original post.
When you come across the requirement again try the other formula so that you can gain familiarity with that too - much quicker to produce and maintain if there are several variables to list.
Grant
0