Solved

What is the best practice for adding an inventory attribute as a read-only column to order entry?

  • 5 April 2021
  • 5 replies
  • 990 views

Userlevel 7
Badge +5

I’ve seen how I can add an inventory extension to dynamically create fields on the InventoryItem DAC for one or more attributes and that seems nice for Generic Inquiries.

In my case I want to add an inventory item attribute to the SO Order Entry Document Details tab as a read-only field.

I’m not sure of the best way to make that field in the SOLine DAC.

 

icon

Best answer by Django 5 April 2021, 23:31

View original

5 replies

Userlevel 5
Badge +2

The best practice is to create a DAC extension with a custom unbound field.

SO Order Entry Document Details tab grid is bound to SOLine DAC so you want to create a DAC extension on SOLine DAC to add your custom field.

 

Below I’m using PXDBScalar attribute on the custom field to fetch the value. If you can do it with PXFormula attribute instead of PXDBScalar this is more likely to be considered ‘best practice’. However I wasn’t able to get the value with PXFormula so I choose PXDBScalar.
 

[PXString(1, IsFixed = true)]
[INItemTypes.List()]
[PXDBScalar(typeof(Search<InventoryItem.itemType, 
                                 Where<InventoryItem.inventoryID, Equal<SOLine.inventoryID>>>))]
[PXUIField(DisplayName="Item Type")]

 

Below is the complete DAC extension as generated by Acumatica Customization Editor wizard. Since the custom field is unbound (PXString instead of PXDBString) you don’t need to create a matching DB field in the database table.


namespace PX.Objects.SO
{
  public class SOLineExt : PXCacheExtension<PX.Objects.SO.SOLine>
  {
    #region UsrInventoryItemType
    [PXString(1, IsFixed = true)]
    [INItemTypes.List()]
    [PXDBScalar(typeof(Search<InventoryItem.itemType,
                       Where<InventoryItem.inventoryID, Equal<SOLine.inventoryID>>>))]
    [PXUIField(DisplayName="Item Type")]
    public virtual string UsrInventoryItemType { get; set; }
    public abstract class usrInventoryItemType : PX.Data.BQL.BqlString.Field<usrInventoryItemType>

    { 

    }
    #endregion
  }
}

If the field you want to display is a custom field in InventoryItem, the syntax is similar:
[PXDBScalar(typeof(Search<InventoryItemExt.usrMyCustomField
                                 Where<InventoryItem.inventoryID, Equal<SOLine.inventoryID>>>))]

Userlevel 7
Badge +5

Hi @Hughes Beausejour,

  Thank you for your reply.  I’m trying to get the PXDBScalar to cooperate but I think that my formula is incorrect

 

[PXDBScalar(typeof(Search2<CSAnswers.value                   ,InnerJoin<InventoryItem, On<InventoryItem.noteID, Equal<CSAnswers.refNoteID>>>                   ,Where<InventoryItem.inventoryID, Equal<Required<SOLine.inventoryID>>      ,And<CSAnswers.attributeID, Equal<string_MyAttribute>>>> ))] 

 

  I’m getting “Conversion failed when converting the varchar value 'SO' to data type int.” If the order type is CS then the message says ‘CS’ in the message.

  I’m not sure what’s wrong with the joins/query.

Userlevel 5
Badge +2

The Required clause in the BQL query is not valid in the PXDBScalar context. I’m pretty sure the error is related to the BQL query. I had the same message when testing invalid queries. To make sure you could try the query I provided.

Userlevel 5
Badge +2

Also, the complete solution is more than the PXDBScalar attribute. The root cause of the error might be elsewhere in the solution.

Userlevel 7
Badge +5

Thank you!  I’ve be staring at this query for too long.  Required was not required.  In fact, should not be there at all.  Updated query for future me for when I forget this:

[PXDBScalar(typeof(Search2<CSAnswers.value                   ,InnerJoin<InventoryItem, On<InventoryItem.noteID, Equal<CSAnswers.refNoteID>>>                   ,Where<InventoryItem.inventoryID, Equal<SOLine.inventoryID>      ,And<CSAnswers.attributeID, Equal<string_MyAttribute>>>> ))] 

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