Recently I have a requirement where I have to create a custom lookup for those external items on sales line which are linked to the customer on the sales order.
- External Item Id linked with Customer:
- Create a view for adding the Product Dimensions as below:
- For Lookup of External Item Id
- Add a custom field on the saleslines and copy it's onlookup event handler from form.
- Create a new class and paste the copied event handler in the class, it will create a new method.
- Use the below code lookUp:
[FormControlEventHandler(formControlStr(SalesTable, SalesLine_ExtItemId), FormControlEventType::Lookup)]
public static void SalesLine_ExtItemId_OnLookup(FormControl sender, FormControlEventArgs e)
{
Query query = new Query();
QueryBuildDataSource queryBuildDataSource, qbds;
SysTableLookup sysTableLookup;
SalesLine salesLine = sender.dataSourceObject().cursor();
// CustVendExtItemView is a custom view showed above.
sysTableLookup = SysTableLookup::newParameters(tableNum(CustVendExtItemView), sender);
queryBuildDataSource = query.addDataSource(tableNum(CustVendExtItemView));
queryBuildDataSource.addRange(fieldNum(CustVendExtItemView, CustVendRelation)).value(salesLine.CustAccount);
sysTableLookup.addLookupField(fieldNum(CustVendExtItemView, ExternalItemId), true);
sysTableLookup.addLookupField(fieldNum(CustVendExtItemView, Description));
sysTableLookup.addLookupField(fieldNum(CustVendExtItemView, ItemId));
sysTableLookup.addLookupField(fieldNum(CustVendExtItemView, ExternalItemTxt));
sysTableLookup.addLookupField(fieldNum(CustVendExtItemView, CustVendRelation));
sysTableLookup.addLookupField(fieldNum(CustVendExtItemView, InventColorId));
sysTableLookup.addLookupField(fieldNum(CustVendExtItemView, InventSizeId));
sysTableLookup.addLookupField(fieldNum(CustVendExtItemView, InventStyleId));
sysTableLookup.addLookupField(fieldNum(CustVendExtItemView, InventVersionId));
sysTableLookup.parmQuery(query);
sysTableLookup.performFormLookup();
}
- Now create a table to hold the Product Dimension value as below:
- Copy the Modified event handler of ExternalItemId from Form DS and use the below code:
/// <summary>
///
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
[FormDataFieldEventHandler(formDataFieldStr(SalesTable, SalesLine, ExtItemId), FormDataFieldEventType::Modified)]
public static void ExtItemId_OnModified(FormDataObject sender, FormDataFieldEventArgs e)
{
//Custom View
CustVendExtItemView externalItem;
FormDataSource salesLine_ds = sender.datasource();
SalesLine salesLine = salesLine_ds.cursor();
ExtItemId isbnItemId;
//Custom Table
CustVendExtItemTempTable custVendExtTempTable;
ttsbegin;
delete_from custVendExtTempTable;
ttscommit;
select firstonly externalItem
where externalItem.ExternalItemId == salesLine.ExtItemId;
if (externalItem.RecId)
{
ttsbegin;
custVendExtTempTable.clear();
custVendExtTempTable.ItemId = externalItem.ItemId;
custVendExtTempTable.ExternalItemId = externalItem.ExternalItemId;
custVendExtTempTable.ExternalItemTxt = externalItem.ExternalItemTxt;
custVendExtTempTable.CustVendRelation = externalItem.CustVendRelation;
custVendExtTempTable.Description = externalItem.Description;
custVendExtTempTable.InventColorId = externalItem.InventColorId;
custVendExtTempTable.InventSizeId = externalItem.InventSizeId;
custVendExtTempTable.InventStyleId = externalItem.InventStyleId;
custVendExtTempTable.insert();
ttscommit;
}
salesLine.ItemId = externalItem.ItemId;
isbnItemId = salesLine.ExtItemId;
salesLine_ds.object(fieldnum(SalesLine, ItemId)).modified();
salesLine.ExtItemId = isbnItemId;
}
- Now Create a COC for SalesLineType.initFromInventTable for populating the Product Dimensions on the External Item Id:
void initFromInventTable(InventTable _inventTable, boolean _resetPrice, AgreementHeaderRecId _matchingAgreement, boolean _executeOnlyIfProductIsFullySpecified)
{
next initFromInventTable(_inventTable, _resetPrice, _matchingAgreement, _executeOnlyIfProductIsFullySpecified);
InventDim inventDim = salesLine.inventdim();
//Custom Table
CustVendExtItemTempTable custVendExtTempTable;
select firstonly custVendExtTempTable;
// Populate Item Number and update InventDim with Product Attribute when user selects External Item Number.
if (custVendExtTempTable.RecId)
{
if (custVendExtTempTable.InventColorId)
{
inventDim.InventColorId = custVendExtTempTable.InventColorId;
}
if (custVendExtTempTable.InventSizeId)
{
inventDim.InventSizeId = custVendExtTempTable.InventSizeId;
}
if (custVendExtTempTable.InventStyleId)
{
inventDim.InventStyleId = custVendExtTempTable.InventStyleId;
}
if (custVendExtTempTable.InventVersionId)
{
inventDim.InventVersionId = custVendExtTempTable.InventVersionId;
}
salesLine.InventDimId = InventDim::findOrCreate(inventDim).inventDimId;
ttsbegin;
delete_from custVendExtTempTable;
ttscommit;
}
// Populate External Item Number and update InventDim with Product Attribute when user selects ItemId.
else if (salesLine.ItemId)
{
CustVendExternalItem custVendExternalItem;
select firstonly custVendExternalItem
where custVendExternalItem.CustVendRelation == salesLine.CustAccount
&& custVendExternalItem.ModuleType == ModuleInventPurchSalesVendCustGroup::Cust
&& custVendExternalItem.ItemId == salesLine.ItemId;
if (custVendExternalItem.RecId)
{
salesLine.ExtItemId = custVendExternalItem.ExternalItemId;
// Custom View
CustVendExtItemView externalItem;
select firstonly externalItem
where externalItem.ExternalItemId == salesLine.ExtItemId;
if (externalItem.InventColorId)
{
inventDim.InventColorId = externalItem.InventColorId;
}
if (externalItem.InventSizeId)
{
inventDim.InventSizeId = externalItem.InventSizeId;
}
if (externalItem.InventStyleId)
{
inventDim.InventStyleId = externalItem.InventStyleId;
}
if (externalItem.InventVersionId)
{
inventDim.InventVersionId = externalItem.InventVersionId;
}
salesLine.InventDimId = InventDim::findOrCreate(inventDim).inventDimId;
}
}
}
- This is how it final development will look like:
External Items on the customer:
LookUp on Sales Line:
