You’re offline. This is a read only version of the page.
54
Hi
While creating a new sales order, their are two fields in that form- Customer Account in Customer Tab & Invoice Account in General Tab, Initially , these two fields are lookup fields which Populate Customer Account Number from CustTable & Invoice Account field is getting initialized as soon as we choose value in Customer Account field & with the same account number we choose in Customer Account field.
I need to filter the records in Invoice Account field as per the customer group of the Account number we choose in Customer Account field.
for ex, if we choose Account no- DE-010 (let say Customer Group- 10) in Customer Account lookup field then Invoice Account lookup field should be filter & shows only records which has Customer Group 10. This functionality I need in my instance.
I have tried to write Chain of Extension for the salesTable at formDataFieldStr, I am attaching my code below-
Share
Report[ExtensionOf(formDataFieldStr(SalesTable,SalesTable,InvoiceAccount))]
final class HSHTSalesTable_FormDataFieldStr_Extension
{
public void lookup(FormControl _formControl, str _filterStr)
{
Query query = new Query();
QueryBuildDataSource queryBuildDataSource;
QueryBuildRange queryBuildRange;
SalesTable salesTable;
next lookup(_formControl, _filterStr);
SysTableLookup sysTableLookup = SysTableLookup::newParameters(tableNum(CustTable), _formControl);
queryBuildDataSource = query.addDataSource(tableNum(CustTable));
//queryBuildDataSource.addSelectionField(fieldnum(CustTable,AccountNum));
queryBuildRange = queryBuildDataSource.addRange(fieldNum(CustTable,CustGroup));
queryBuildRange.value(queryValue(CustTable::find(salesTable.CustAccount).CustGroup));
sysTableLookup.addLookupfield(fieldNum(CustTable, AccountNum));
sysTableLookup.addLookupfield(fieldNum(CustTable, CustGroup));
sysTableLookup.parmQuery(query);
sysTableLookup.performFormLookup();
}
}
I am attaching images as well for the refernce-
Hi, you can check this thread for reference
Line number 25 the method you are calling will return LogisticsPostalAddress table buffer. It won't return city, country, zip code etc. Thats why its throwing error.
If you want to add a city, country, zip code - You need to add the respective DataSource to the query.
Try joining LogisticsPostalAddressView to the query and check.
Thanks,
Girish S.
Hi Girish,
The issue gets resolved now by adding CustTable as root DataSource in query as well as in SysTablelookup.
but I want to fetch Address as well like city, country, zip code separately in Invoice Account lookup, for this I have added a method in sysTableLookup-
sysTableLookup.addLookupMethod(tableMethodStr(CustTable,postalAddress));
but after adding this method in lookup, it is throwing an error-
Error - Type Record is not supported in this type of lookups.
I am sharing my code for the reference.
All responses (
Answers (
[FormControlEventHandler(formControlStr(SalesCreateOrder, SalesTable_InvoiceAccount), FormControlEventType::Lookup)]
public static void SalesTable_InvoiceAccount_OnLookup(FormControl sender, FormControlEventArgs e)
{
FormRun element;
FormControl formCtrl;
Query query = new Query();
QueryBuildDataSource qbdsSalesTable, qbdsCustTable;
QueryBuildRange queryBuildRange;
element = sender.formRun();
formCtrl = element.design().controlName(formControlStr(SalesCreateOrder,SalesTable_CustAccount));
SysTableLookup sysTableLookup = SysTableLookup::newParameters(tableNum(CustTable), sender);
qbdsCustTable = query.addDataSource(tableNum(CustTable));
// Add the lookup columns
sysTableLookup.addLookupfield(fieldNum(CustTable,AccountNum));
sysTableLookup.addLookupMethod(tableMethodStr(CustTable,name));
sysTableLookup.addLookupMethod(tableMethodStr(CustTable,NameAlias));
sysTableLookup.addLookupfield(fieldNum(CustTable,CustGroup));
sysTableLookup.addLookupMethod(tableMethodStr(CustTable,phone));
getting error //sysTableLookup.addLookupMethod(tableMethodStr(CustTable,postalAddress));
sysTableLookup.addLookupMethod(tableMethodStr(CustTable,email));
qbdsCustTable.addRange(fieldNum(CustTable, CustGroup)).value(queryValue(CustTable::find(formCtrl.valueStr()).CustGroup));
// Run the query
sysTableLookup.parmQuery(query);
// Run the lookup
sysTableLookup.performFormLookup();
//cancel the call to super() to prevent the system from trying to show
//the lookup form twice and cause an error.
FormControlCancelableSuperEventArgs cancelableSuperEventArgs = e as FormControlCancelableSuperEventArgs;
cancelableSuperEventArgs.CancelSuperCall();
}
Thanks,
Rahul
If that is the case, then you must add SalesTable as a DataSource and as Mohit mentioned add group by for CustAccount field on SalesTable.
My suggestion will be creating a view with SalesTable and CustTable as DataSource and add the group by in the view itself.
Later you can use that view as a DataSource in the lookup query.
Thanks,
Girish S.
Hi Mohit & Girish,
The issue gets resolved now after adding CustTable as root DataSource in query as well as in SysTablelookup.
but I want to fetch Address as well like city, country, zip code separately in Invoice Account lookup, for this I have added a method in sysTableLookup-
sysTableLookup.addLookupMethod(tableMethodStr(CustTable,postalAddress));
but after adding this method in lookup, it is throwing an error-
Error - Type Record is not supported in this type of lookups.
I am sharing my code for the reference.
[FormControlEventHandler(formControlStr(SalesCreateOrder, SalesTable_InvoiceAccount), FormControlEventType::Lookup)]
public static void SalesTable_InvoiceAccount_OnLookup(FormControl sender, FormControlEventArgs e)
{
FormRun element;
FormControl formCtrl;
Query query = new Query();
QueryBuildDataSource qbdsSalesTable, qbdsCustTable;
QueryBuildRange queryBuildRange;
element = sender.formRun();
formCtrl = element.design().controlName(formControlStr(SalesCreateOrder,SalesTable_CustAccount));
SysTableLookup sysTableLookup = SysTableLookup::newParameters(tableNum(CustTable), sender);
qbdsCustTable = query.addDataSource(tableNum(CustTable));
// Add the lookup columns
sysTableLookup.addLookupfield(fieldNum(CustTable,AccountNum));
sysTableLookup.addLookupMethod(tableMethodStr(CustTable,name));
sysTableLookup.addLookupMethod(tableMethodStr(CustTable,NameAlias));
sysTableLookup.addLookupfield(fieldNum(CustTable,CustGroup));
sysTableLookup.addLookupMethod(tableMethodStr(CustTable,phone));
getting error //sysTableLookup.addLookupMethod(tableMethodStr(CustTable,postalAddress));
sysTableLookup.addLookupMethod(tableMethodStr(CustTable,email));
qbdsCustTable.addRange(fieldNum(CustTable, CustGroup)).value(queryValue(CustTable::find(formCtrl.valueStr()).CustGroup));
// Run the query
sysTableLookup.parmQuery(query);
// Run the lookup
sysTableLookup.performFormLookup();
//cancel the call to super() to prevent the system from trying to show
//the lookup form twice and cause an error.
FormControlCancelableSuperEventArgs cancelableSuperEventArgs = e as FormControlCancelableSuperEventArgs;
cancelableSuperEventArgs.CancelSuperCall();
}
Thanks,
Rahul
Hi Rahul, You can add group by on Invoice Account and you will not get duplicate records. However, that won't be a right approach to get all customer's linked to that customer group. As Girish mentioned use CustTable instead of SalesTable as datasource.
I can see your code in the question section is fine, any reason you changed it to have SalesTable as main datasource.
You have added SalesTable to the query - SalesTable will have same Invoice account for multiple SalesOrder. Thats why you are getting invoice account multiple times.
So, the query must have CustTable as a Datasource. Dont use SalesTable as a Datasource in the query.
Thanks,
Girish S.
Hi Girish & Mohit,
I have tried above task with event handlers, & records has filtered in Invoice Account lookup field based on Customer Group but same records have been fetching many times.
I am sharing my code, pls help in this.
[FormControlEventHandler(formControlStr(SalesCreateOrder, SalesTable_InvoiceAccount), FormControlEventType::Lookup)]
public static void SalesTable_InvoiceAccount_OnLookup(FormControl sender, FormControlEventArgs e)
{
FormRun element;
FormControl formCtrl;
Query query = new Query();
QueryBuildDataSource qbdsSalesTable, qbdsCustTable;
QueryBuildRange queryBuildRange;
element = sender.formRun();
// accessing CustAccount form control to use on filtering custgroup
formCtrl = element.design().controlName(formControlStr(SalesCreateOrder,SalesTable_CustAccount));
SysTableLookup sysTableLookup = SysTableLookup::newParameters(tableNum(SalesTable), sender);
qbdsSalesTable = query.addDataSource(tableNum(SalesTable));
qbdsCustTable = qbdsSalesTable.addDataSource(tableNum(CustTable));
qbdsCustTable.relations(true);
// Add the lookup columns
sysTableLookup.addLookupfield(fieldNum(SalesTable, CustAccount));
sysTableLookup.addLookupMethod(tableMethodStr(SalesTable,customerName));
sysTableLookup.addLookupfield(fieldNum(SalesTable, CustGroup));
sysTableLookup.addLookupMethod(tableMethodStr(SalesTable,customerPhone));
//sysTableLookup.addLookupMethod(tableMethodStr(SalesTable,deliveryAddress));
sysTableLookup.addLookupMethod(tableMethodStr(SalesTable,customerEmail));
qbdsCustTable.addRange(fieldNum(CustTable, CustGroup)).value(queryValue(CustTable::find(formCtrl.valueStr()).CustGroup));
// Run the query
sysTableLookup.parmQuery(query);
// Run the lookup
sysTableLookup.performFormLookup();
//cancel the call to super() to prevent the system from trying to show
//the lookup form twice and cause an error.
FormControlCancelableSuperEventArgs cancelableSuperEventArgs = e as FormControlCancelableSuperEventArgs;
cancelableSuperEventArgs.CancelSuperCall();
}
Thanks & Regards,
Rahul
Hi Rahul, As Girish mentioned it best to use event handler in this case and cancel call to super so standard lookup method is not called.
Check his article
#1
André Arnaud de Cal...
291,979
Super User 2025 Season 1
#2
Martin Dráb
230,848
Most Valuable Professional
#3
nmaenpaa
101,156
