Solved

Display Attribute Values from Stock Items to Report

  • 17 August 2021
  • 10 replies
  • 1079 views

Userlevel 3
Badge +1

I need to get some attribute values from the stock items form to a report (Modifying Sales Order Report SO641010.rpx). What are the tables to be joined in order to show the necessary value from the Attributes table to the report?

icon

Best answer by Gabriel Michaud 17 August 2021, 16:35

View original

10 replies

Userlevel 7
Badge +17

Hi @TharidhiP  To display the Stock Item attribute values, please join the below tables..

SOOrder (Order Type, OrderNbr) →  SOLine (Order Type, OrderNbr)

SOLine (InventoryID) →  Inventory Item ( InventoryID)

InventoryItem (NoteID) →  CSAnswers ( RefNoteID)

 

Sample Code for your reference → you can use the same in reports as well

PXSelectJoin<InventoryItem,
InnerJoin<CSAnswers, On<InventoryItem.noteID, Equal<CSAnswers.refNoteID>>>,
Where<InventoryItem.inventoryID, Equal<Required<InventoryItem.inventoryID>>,
And<Where<CSAnswers.attributeID, Equal<BQLConstants.PRDTYPE>
>>>>.Select(Base, objSOLine.InventoryID)

 

 

Userlevel 3
Badge +1

Hi @Naveen B thanks for replying, this is possible in the Acumatica Report Designer?

Userlevel 7
Badge +17

Yes, this is possible with Acumatica report designer

Userlevel 7
Badge +10

A little-known fact is that the attributes are added as virtual fields to the related table (InventoryItem in your case) which means you can simply add it to the report or generic inquiry without having to add extra joins to CSAnswers. The attributes are named using the Attribute ID and suffixed wit _Attributes, for example COLOR_Attributes:

 

Note that SO641010 does not reference the InventoryItem table, so you will have to add it to the report by joining SOLine.InventoryID to InventoryItem.InventoryID.

Userlevel 7
Badge +17

Hi @Gabriel Michaud   Actually, I forgot  :( and thanks a lot for sharing this.

 

@TharidhiP  We can avoid the CSAnswer table join, directly you can get the attribute values from the InventoryItem table (Attribute name will be AttributeIDName_Attributes) in Acumatica Report designer.

Userlevel 3
Badge +1

Thank you for the replies, @Gabriel Michaud and @Naveen B!

Userlevel 7
Badge +4

One common issue when looking for these attribute values is that if they have been recently added then they may not appear as an option to be able to select, which can be confusing.

To get around it make sure to refresh the schema of the report, this will ensure that the Attributes, however recently added, will be visible.

Open the build schema and on Tables tab > Load Schema > Refresh All:

 

Userlevel 1
Badge

Hello All!

I’m trying to add Vendor_Lot# and MfgDate to a report. it is added to PO receipt as attributes in CSAnswer. however, cant pull them in schema.

 i tried to follow steps provided here, but i dont get to see attributes to add to GI or in report designer.

Thanks!

Arjan

Userlevel 7
Badge +4

Hi @arjan ! You have added the right table and the red box consists of the AttributeID, IsRequired, and Value seen in the CSAnswers table.

Once you add the needed fields (likely just AttributeID and Value) to the report you will be able to see these.

Make sure to join the CSAnswers table in also but if you get stuck I would recommend to make a new posting as this one is already answered, if you still need assistance we’ll help you in the new post :).

Userlevel 1
Badge

Thanks @BenjaminCrisman !.

 

i just created a new thread to discuss this further here:
Add VendorLot#(attribute&value) and MfgDate(attribute&value) to a PO Receipt report. | Community (acumatica.com)

Reply


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