Since you asked for "a better overall approach" then I'll share my preferred way of handling this. Not to say it is "better" but perhaps just "different".
I try to keep my data tied to the object where it is related directly. In the case of an attribute of an InventoryItem, I would elevate that attribute value to InventoryItem in a DAC extension so that it can be leveraged anywhere the InventoryItem is used. You seem to want to use it in conjunction with the SOLine record, but since the attribute is not tied to the SOLine, I would not extend SOLine to add it. There is nothing wrong with adding it there if your business requirement mandates it, but it keeps me more sane to know that an InventoryItem's related data comes from InventoryItem rather than trying to remember where I put it and possibly duplicate the effort elsewhere (like on a POLine) later.
Here is an example of how I've done it, pulled straight from my project but changing the Attribute references to be more generic.
public sealed class InventoryItemExt : PXCacheExtension<PX.Objects.IN.InventoryItem>
{
#region MyAttribute
[PXString]
[PXUIField(DisplayName = Messages.MyAttribDisplayName)]
[PXDBScalar(typeof(Search<CSAnswers.value,
Where<CSAnswers.refNoteID, Equal<InventoryItem.noteID>,
And<CSAnswers.attributeID, Equal<MyAttribute>>>>))]
public string MyAttribute { get; set; }
public abstract class myAttribute : PX.Data.BQL.BqlString.Field<myAttribute> { }
#endregion
public class MyAttribute : PX.Data.BQL.BqlString.Constant<MyAttribute>
{
public MyAttribute() : base("MYATTRIB") { }
}
}
Notice the constant defined to access the AttributeID "MYATTRIB" which is attached to the Item Class of the InventoryItem record.
Now that the attribute is pulled into the DAC extension on InventoryItem, I can use it on any screen related to an InventoryItem with ease as long as InventoryItem (and the associated DAC extension) have been made available to that screen.
Without testing, I may be off on this but with regards to your attempt to join 2 tables in the PXDBScalar...
When you say Search2<CSAnswers.value..., you have indicated that you want to search the CSAnswers DAC and retrieve the value field. By subsequently trying to InnerJoin back to CSAnswers, I believe you effectively have said:
Select CSAnswers.Value From CSAnswers
Inner Join InventoryItem On SOLine.InventoryID = InventoryItem.InventoryID
Inner Join CSAnswers...
I'm not sure if the SOLine reference is valid here, but it may be if you are defining this directly in the SOLine DAC extension. However, you have tried to query CSAnswers Inner Join CSAnswers. Not sure if this will fix your PXDBScalar, but if you really want to use your method to attach this to SOLine, try:
Search2<CSAnswers.value,
InnerJoin<InventoryItem,
On<InventoryItem.inventoryID, Equal<SOLine.inventoryID>>>,
Where<InventoryItem.noteID, Equal<CSAnswers.refNoteID>,
And<CSAnswers.attributeID, Equal<QHOLDAttr>>>
>))]
(Notice that I swapped the order of the relations in On clauses.)
