Skip to main content
Solved

Report Designer Additional FIltering Conditions (Does Not Start With)


Forum|alt.badge.img

Hi:

Our client is used to filter his legacy Crystal Reports using the condition [CustID] Not Equal 9*.

Crystal generates the correct SQL clause as Where [CustID] Not Like ‘9%’.

What would be the equivalent syntax in Report Designer’s Additional Filtering Conditions?

We don’t have an option like “Does Not Start With” 

I could generate the correct filter using Parameters, but the client may want to use multiple ‘Not equal’, so it would be ideal to use the Additional Filtering Conditions.

 

Thanks

Best answer by Dioris Aguilar

@gpineda64 In that case, you could add an additional parameter where users can enter the initial characters of the customer name to be excluded:

 

 

And the constraint will be as follows:
 

Notice the first two lines added with the “Or” are used to prevent filtering the records when no string has been entered to the “Not Starts With” field.

Running report from 01/01/2022 to 08/09/2022 with above constraint and Not Starts With = “ABCH”:

The only sales order excluded is the one belonging to ABCHOLDING customer.

View original
Did this topic help you find an answer to your question?

6 replies

Dioris Aguilar
Jr Varsity I
Forum|alt.badge.img+2

@gpineda64 You can try adding a new constraint in the Filters section in the report to filter out the unwanted customer names. The following example uses the Sales Order Summary report (SO610500):
 

Running report from 01/01/2022 to 08/09/2022 without any change:


Adding following constraint to Filters section:
 

 

Running report from 01/01/2022 to 08/09/2022 with above constraint:
 


Notice sales orders from ABCHOLDING and ABCVENTURE customers are no longer listed.


Forum|alt.badge.img
  • Author
  • Jr Varsity III
  • 21 replies
  • August 9, 2022

@Dioris Aguilar , thanks for you reply. Your solution with Parameters will work when the Values are known ahead of time, but the client has several sales persons who enter different value combinations for the accounts they want to exclude. In the example they provided, the user is excluding customer accounts that start with 9 or 29, but another user may want to exclude a different set of values, so it would be ideal if they can create dynamic combinations in the Additional Filtering Conditions. Thanks


Laura02
Captain II
Forum|alt.badge.img+19
  • Captain II
  • 3101 replies
  • August 9, 2022

Hello,

I am wondering what is the meaning for the digits at the front of the customer ID, and whether this meaning could be captured in another way that is more conducive to Acumatica reporting. Can Attributes or User Defined Fields, or other fields of Acumatica hold the same information, removing the need for “Starts With” and “Doesn’t Start With” in report filters?  Just an idea.


Dioris Aguilar
Jr Varsity I
Forum|alt.badge.img+2

@gpineda64 In that case, you could add an additional parameter where users can enter the initial characters of the customer name to be excluded:

 

 

And the constraint will be as follows:
 

Notice the first two lines added with the “Or” are used to prevent filtering the records when no string has been entered to the “Not Starts With” field.

Running report from 01/01/2022 to 08/09/2022 with above constraint and Not Starts With = “ABCH”:

The only sales order excluded is the one belonging to ABCHOLDING customer.


Forum|alt.badge.img
  • Author
  • Jr Varsity III
  • 21 replies
  • August 9, 2022

It seem that I’m going to have to rely on Parameters, as your example. The caveat is that I’ll have to give them at least 3 Parameters options for each field, and they are needed in more than 10 reports, so it’s going to be a lot of work.

Thank you very much for your help.


Dioris Aguilar
Jr Varsity I
Forum|alt.badge.img+2

@gpineda64 Acumatica comes with a functionality for reports that allows users to add custom filter options including one to filter fields on how they start with as shown below:

However, “Does Not Start with” option should be implemented as mentioned above in this thread.


Reply


Cookie policy

We use cookies to enhance and personalize your experience. If you accept you agree to our full cookie policy. Learn more about our cookies.

 
Cookie settings