Good day everyone!
Today I want to share with you one experience with dynamic (virtual) data in Acumatica.
Imagine that you want to have a custom virtual view in Acumatica, and use it to get and update records in the cache.
It can be a lot of different situations such as getting data from a file and putting it into view or getting data from another view, updating the records and so on.
For example, let take a popular screen Sales Order (SO301000).
On updating the Document Details (SOLine) row, I want to have a custom control Availability of selected Item.
What does it look like in the code?
I created a SOOrderEntryExt graph extension and my virtual DAC:
Snippet
[PXVirtual]
public class CustomVirtualDAC : IBqlTable
{
#region InventoryID
public abstract class inventoryID : PX.Data.BQL.BqlInt.Field<inventoryID> { }
[PXInt(IsKey = true)]
[PXDefault(PersistingCheck = PXPersistingCheck.Nothing)]
[PXUIField(DisplayName = "InventoryID")]
public virtual int? InventoryID
{
get;
set;
}
#endregion
#region SiteID
public abstract class siteID : PX.Data.BQL.BqlInt.Field<siteID> { }
[PXInt(IsKey = true)]
[PXDefault(PersistingCheck = PXPersistingCheck.Nothing)]
[PXUIField(DisplayName = "SiteID")]
public virtual int? SiteID
{
get;
set;
}
#endregion
#region LocationID
public abstract class locationID : PX.Data.BQL.BqlInt.Field<locationID> { }
[PXInt(IsKey = true)]
[PXDefault(PersistingCheck = PXPersistingCheck.Nothing)]
[PXUIField(DisplayName = "LocationID")]
public virtual int? LocationID
{
get;
set;
}
#endregion
#region AvailQty
public abstract class availQty : PX.Data.BQL.BqlDecimal.Field<availQty> { }
[PXDecimal(2)]
[PXDefault(PersistingCheck = PXPersistingCheck.Nothing)]
[PXUIField(DisplayName = "AvailQty")]
public virtual decimal? AvailQty { get; set; }
#endregion
}
After in SOOrderEntryExt I added my view and event:
public SelectFrom<CustomVirtualDAC>.View CustomVirtualView;
public virtual void _(Events.RowUpdated<SOLine> e)
{
SOLine oldRow = e.OldRow as SOLine;
SOLine row = e.Row as SOLine;
CustomVirtualDAC currVirtualRez = null;
if (row != null)
{
currVirtualRez = ReturnVirtualDACRez(row.InventoryID,
row.SiteID, row.LocationID);
if (row.OrderQty > oldRow.OrderQty)
{
//do what needed
currVirtualRez.AvailQty -= row.OrderQty - oldRow.OrderQty;
CustomVirtualView.Update(currVirtualRez);
}
}
}
And one more method where I can return the needed row or add if it does not exists:
public CustomVirtualDAC ReturnVirtualDACRez(int? inventoryID, int? siteID, int? locationID)
{
CustomVirtualDAC currVirtualRez = null;
foreach (CustomVirtualDAC mkinItemStatus in
CustomVirtualView.Select())
{
if (mkinItemStatus.InventoryID == inventoryID && mkinItemStatus.SiteID == siteID && mkinItemStatus.LocationID == locationID)
{
currVirtualRez = mkinItemStatus;
}
}
if (currVirtualRez == null)
{
InventorySummaryEnq tempGraph = PXGraph.CreateInstance<InventorySummaryEnq>();
tempGraph.Filter.Current.InventoryID = inventoryID;
tempGraph.Filter.Current.SiteID = siteID;
tempGraph.Filter.Cache.Update(tempGraph.Filter.Current);
foreach (InventorySummaryEnquiryResult record in tempGraph.ISERecords.Select())
{
CustomVirtualDAC newVirtualRez = new CustomVirtualDAC();
newVirtualRez.InventoryID = record.InventoryID;
newVirtualRez.SiteID = record?.SiteID;
newVirtualRez.LocationID = record?.LocationID;
newVirtualRez.AvailQty = record?.QtyAvail;
CustomVirtualView.Insert(newVirtualRez);
}
}
return currVirtualRez;
}
Everything seems ready and should work.But not. I receive error message Incorrect syntax near the keyword 'OPTION':
This is because Aсumatiсa is still trying to extract data from a table that does not exist. And none of the attributes such as [PXCopyPasteHiddenView] or [PXVirtualDAC] does not help.
To resolve this problem, you must implement a Dataview delegate when using a Virtual DAC.
So, in your graph extension you must add a dataview delegate to return the records that you need:
protected virtual IEnumerable AvailibilityView()
{
//fetch your records
return your records;
}
In my specific case, I must implement something like that:
public virtual IEnumerable customVirtualView()
{
List<CustomVirtualDAC> listRez = new List<CustomVirtualDAC>();
foreach (CustomVirtualDAC line in CustomVirtualView.Cache.Inserted)
{
listRez.Add(line);
}
foreach (CustomVirtualDAC line in CustomVirtualView.Cache.Updated)
{
listRez.Add(line);
}
return listRez;
}
Because I can`t call CustomVirtualView.Select() in the delegate because this will loop the code.
Now it will work.
Summary
In case if you need to read data from some 3-rd party source, and display it on the screen, then you have two ways:
1. Create useless table in Acumatica data base
2. Follow technique described in this article.