Universally Searchable – Attributes and Data from Other DAC’s

Acumatica’s Universal Search feature is pretty powerful for the end user. It is accessed through the NoteID field of your DAC via PXSearchableAttribute. The feature is great when your searchable data is part of your existing DAC, but what if you need to link some related data from another DAC?

The solution is pretty easy using PXDBScalarAttribute. The example below uses [PXDBScalar] to dig into the Attributes of the InventoryItem to get the Hazmat ID stored as an attribute via the Attribute ID “HAZMAT”. Since the values are stored in CSAnswers rather than InventoryItem, [PXDBScalar] lets us pull the value into an unbound field which we can then include in the Full Text index. Universal Search then searches the full-text index to display the matching results to the end user.

We will need a DAC extension for InventoryItem. Within the DAC extension, we will want to create a [PXString] field (unbound string) using [PXDBScalar] to query the database for the desired value. We also need to create a class for the AttributeID used by the Item Class assigned to the InventoryItem. When I say AttributeID, I’m referring to the data elements on the Attribute tab of the InventoryItem screen which are determined by the Item Class. ([Attribute] vs. Attribute tabs… confusing? A bit!)

After the unbound field is defined using [PXDBScalar] to retrieve the data, notice the use of the new unbound field (Hazmat) in the NoteID attribute [PXSearchable]. The first occurrence in PXSearchable tells Acumatica to include the value in the full text index for the universal search. The second occurrence tells Acumatica to display the value in the displayed results.

using PX.Data;
using PX.Objects.CS;
using PX.Objects.IN;
using PX.Objects.SM;
using System;

namespace BLOG.IN
{
    public sealed class InventoryItemExt : PXCacheExtension<InventoryItem>
    {
        #region Hazmat
        [PXString]
        [PXUIField(DisplayName = "Hazmat ID")]
        [PXDBScalar(typeof(Search<CSAnswers.value, Where<CSAnswers.refNoteID, Equal<InventoryItem.noteID>, And<CSAnswers.attributeID, Equal<AttribHazmat>>>>))]
        public string Hazmat { get; set; }
        public abstract class hazmat : PX.Data.BQL.BqlString.Field<hazmat> { }
        #endregion

        #region NoteID  
        [PXSearchable(SearchCategory.IN, "{0}: {1}",
            new Type[] { typeof(InventoryItem.itemType), typeof(InventoryItem.inventoryCD) },
            new Type[] { typeof(InventoryItem.descr), typeof(InventoryItemExt.sItem) },
            NumberFields = new Type[] { typeof(InventoryItem.inventoryCD) },
            Line1Format = "{0}{1}", Line1Fields = new Type[] { typeof(INItemClass.itemClassCD), typeof(InventoryItem.baseUnit) },
            Line2Format = "{0}{1}", Line2Fields = new Type[] { typeof(InventoryItem.descr), typeof(InventoryItemExt.sItem) },
            WhereConstraint = typeof(Where<Current<InventoryItem.itemStatus>, NotEqual<InventoryItemStatus.unknown>>)
            )]
        [PXNote]
        public Guid? NoteID { get; set; }
        #endregion


        public class AttribHazmat : PX.Data.BQL.BqlString.Constant<AttribHazmat>
        {
            public AttribHazmat() : base("HAZMAT") { }
        }
    }

}

While this example is designed to show a value from a specific ItemClass attribute for the InventoryItem, the same concept can be used to link any other 1:1 data element you may need. For instance, if you make a DAC called “MyGroceryList” that includes InventoryItem to store InventoryID’s for the grocery list, you could include a NoteID on MyGroceryList, make it PXSearchable, and add a PXDBScalar for Description to retrieve the item description. By adding that unbound field to the PXSearchable on MyGroceryList.NoteID, a universal search for “milk” would return each week’s grocery list that contains milk without having to store the actual description in the MyGroceryList database table.

Leave a Reply