Solved

How to filter in report designer for Item Classes on a PO Receipt

K
Rank

I am trying to print labels from a PO Receipt but only when the Inventory Item is in a particular Item Class.  My attempts at filtering aren’t working.

Example: I want to include only those where the item class contains ASHR, AUTOR, and MINPL.

In report designer I have tried various ways in the filters tab - as you can see I have LLIKE and the beginning of one of the item classes.  I have tried Like, IN and none work.  When I print the report it comes up blank.  Does anyone know a way to add a filter to accommodate this requirement?

 

 

icon

Best answer by lauraj46 17 October 2022, 23:38

View original

10 replies

lauraj46
Rank
Userlevel 7
Badge +7

Hi @kim96 ,

Did you try joining the InventoryItem with the INItemClass and filtering on the ItemClassCD?

Laura

Laura - https://www.linkedin.com/in/laura-jaffe-965a411a/ ~ If a member helped you, please *Like* their post. If a member provided an answer to your question, please mark it as *Best Answer*.
K
Rank

I did not…  Let me give that a try.  However, would you use the “Like” or “IN” for the filter to go against the item class then?

BenjaminCrisman
Rank
Userlevel 7
Badge +4

Hi @kim96! Based on @lauraj46 ‘s post you wouldn’t need to use Like or In, you would just use Equal to the full ItemClassCD or ItemClassID:

 

In the Sales Demo the items in the ELECCOMP class have 100, 200, 300, 400 which would be similar to your MINPL or ASHR classes, but matching the full CD or using the ID should work pretty well.

lauraj46
Rank
Userlevel 7
Badge +7

Hi @kim96 ,

Typically if it’s just a few classes then I would use Equal with an OR statement.  Just be sure to wrap this with parentheses.  If several codes start with the same prefix then you could use LLIKE. 

I haven’t had reason to use IN in the Report Designer.  In SQL the syntax is something like:

ItemClassCD in (‘ASHR’, ‘MINPL’) -  but I’m not sure if that syntax works in the Report Designer or not.

Laura

Laura - https://www.linkedin.com/in/laura-jaffe-965a411a/ ~ If a member helped you, please *Like* their post. If a member provided an answer to your question, please mark it as *Best Answer*.
K
Rank

Hi Laura, I appreciate all of the suggestions and tried them all, unfortunately it just won’t return data. 

I have 12 classes I need to be able to filter on,  I have tried to just filter a single item class and it wouldn’t work with Like, LLike, or IN.

Looks like I can’t do it.

 

 

lauraj46
Rank
Userlevel 7
Badge +7

Hi @kim96 ,

Did you try ‘Equal’?  One way to troubleshoot this is to remove the filter and then display the ItemClassCD field on your report.  That way you’ll see if there’s something wrong in your schema, like maybe an incorrect join.

Don’t give up, it is definitely possible to do this :)

Laura

Laura - https://www.linkedin.com/in/laura-jaffe-965a411a/ ~ If a member helped you, please *Like* their post. If a member provided an answer to your question, please mark it as *Best Answer*.
K
Rank

Hi @lauraj46, I definitely have my join ok as I can put the field InventoryItem.ItemClassIDon my report successfully.

 

In my report, for example, I have tried what you see below.

(I have also tried * instead of the spaces, also tried ? instead of the spaces, I have also tried not having it wrapped in the ‘ character.  I have also tried it HVAC_-MINPL-_____-_____-_____ just as it was copied from the Item Class screen)

 

I need to be able to return all those that include the MINPL so ideally I can’t envision entering them all one by one in the filters screens as that’s just ONE of the classes.

 

Any further suggestions?  I really appreciate your help...

BenjaminCrisman
Rank
Userlevel 7
Badge +4

@kim96 I think I see what you mean, but it might make a difference which report you are editing and what the report groupings are or other relations.

I was testing with the Inventory Balance report and it does not accept filtering on the class, but I didn’t deconstruct it to find the root reason for this.

I tested in a GI and I was able to use Equal when the Class is filtered on.

Can you provide which report you are working on or what your schema looks like?

lauraj46
Rank
Userlevel 7
Badge +7

Hi @kim96 ,

You might check out the standard report AR67400.rpx as an example of how filter on the ItemClassCD.  This report uses RLIKE to select all item classes that begin with a particular string.  As you can see in this example, RLIKE adds the wildcard on the Right.

 

I also adjusted the filter to hardcode a particular string, and that seemed to worked as well.

 

Hope this helps!

Laura

Laura - https://www.linkedin.com/in/laura-jaffe-965a411a/ ~ If a member helped you, please *Like* their post. If a member provided an answer to your question, please mark it as *Best Answer*.
lauraj46
Rank
Userlevel 7
Badge +7

Hi @kim96 ,

Based on the test that I just ran, I don’t think you need any single quotes or the wildcard characters.  Try just using LIKE and the string that you are looking for.

As @BenjaminCrisman mentioned, it would be helpful to see your schema.

Laura

Laura - https://www.linkedin.com/in/laura-jaffe-965a411a/ ~ If a member helped you, please *Like* their post. If a member provided an answer to your question, please mark it as *Best Answer*.

Reply


Terms & ConditionsCookie settings
About Acumatica ERP system
Acumatica Cloud ERP provides the best business management solution for transforming your company to thrive in the new digital economy. Built on a future-proof platform with open architecture for rapid integrations, scalability, and ease of use, Acumatica delivers unparalleled value to small and midmarket organizations. Connected Business. Delivered.
© 2008 — 2024  Acumatica, Inc. All rights reserved