How to get sales Responsible field in the Excel sheet ?

Finance and Operations/AX Developer Forum
1

Hi experts ,

I am trying to create csv file …i have around 31 fields …in that first row should be header and second row should be sub header .I am useing container I am getting values but I am unable to get the field called sales responsible …value showing some value like recid…but I want print sales responsible person name …can u some one suggest how to get name ?

One more thing in the 31 fields last 7 fields will be come under sub header and if it has lines …that many lines should come in sub header fields.

Please suggest me how to achieve this one please

2

Do you mean the WorkerSalesResposible on salesTable?
If so, you can get the name by using Tables\HcmWorker\Methods\name
Example - HcmWorker::find(salesTable.WorkerSalesResposible ).name();

Can you explain the other requirement in detail?

3

Thanks for the reply …

no kranthi this 2 fields in salesquotationtable “Wokersalesresponsible” and “WorkerSalesTaker”… i want this 2 fields in person names in the excel sheet we can find this field quotation header level in the form level…(in the excel sheet u can refer the what i am getting the value at 1st field called Person )

coming to other requirement we have 31 fields in that 1 row should be main header and 2 row should be sub header …

Sub header fields coming from 2 customization tables which we added in salesquotaionline level tab level.

2 tables are “Bom info table” and another one is “Operation info table” . whenever we will create the quotation, that time we will enter the lines in the “bom info” tab and “operation info table” (but this 2 tables don’t have any link or relation this are independent table)…

i am sharing excel sheet for reference …

Sample Dev1.PNG
Sample Dev1.PNG1338×275 23.5 KB

thanks kranthi once again for your valuable time

4

You can the same approach(use HCMWorker.name method).

What issue do you have?

5

workersalesresponsible field now coming with person name thanks a lot.
but sub header level we need get multipule lines which we enter in the Bom Info and Operation info Tab at Quotationline level. presently i am getting one line .

6

Can you show us your code?

7

this i wrote on the run method

private void run()
{
CommaIo io;
Dialog dialog;
DialogField dlgFileName;
Filename csvfileName;
FileIOPermission permission;
PurchLine_IN purchLine_IN;
int recordCount = 0;
int i=0;
String20 quotaionJourId;
int strLength;
String10 offer;
AccountNum accountNum;
SalesTaker pesron;
int j;
//str City;
//str Country;

SysOperationProgress exportprogress = new SysOperationProgress();

#File
#AviFiles

try
{
exportprogress.setCaption(“Exporting quotation lines to CSV file…”);
exportprogress.setAnimation(#AviTransfer);

// Header Column

conHeader = [“SL NO “,“Card issue date”,“Sample card”,“Version”,“Product class”,“Customer”,“End customer”,“City”,“Country”,“Person”,“Part Name”,“Part Number”,“Label SIze”,“New FACELIFT”,“Model”,“SOP”,“Qty per Annum”,“Price per set”,“Approx Annual value”,“Sample Qty”,“Cust Date”,“PD Date”,“Dispatch Date”,“Offer”,“BOM Info”,””,"","",“OPeration info”,"",“Remarks”];
conSubHeader = ["","","","","","","","","","","","","","","","","","","","","","","","",“Item Name”,“Product Name”,“Item size”,“item Qty”,“operation Name”,“Operaton Description”];
dialog = new Dialog(“Exporting quotation lines”);
dialog.filenameLookupFilter([“Excel Files”,"*.csv"]);
dlgFileName = dialog.addField(extendedTypeStr(FilePath),“File Path”);

queryRun = new QueryRun(queryStr(PwC_SampleCard));

if(dialog.run())
{

csvfileName = strFmt(@"%1%2%3_%4.csv",dlgFileName.value(), “QuotationDetails”, date2str(today(),321,2,0,2,0,4),strRem(time2Str(timeNow(), TimeSeparator::Colon, TimeFormat::Hour24), “:”));
if(csvfileName)
{
permission = new FileIOPermission(csvfileName,#io_write);
permission.assert();

io = new CommaIo(csvfileName,#io_write);

io.inFieldDelimiter(’,’);

if(!io || io.status() != IO_Status::Ok)
{
throw error(“Error in opening file!”);
}

io.writeExp(conHeader);
io.writeExp(consubheader);

if(queryRun.prompt())
{
while( queryRun.next())
{

//j = 4;
custQuotationJour = queryRun.get(tableNum(CustQuotationJour));
custQuotationTrans = queryRun.get(tableNum(CustQuotationTrans));
//pwC_AdditionalBOMItems = queryRun.get(tableNum(PwC_AdditionalBOMItems));
inventtable = queryRun.get(tableNum(InventTable));
salesQuotationLine = queryRun.get(tableNum(SalesQuotationLine));

custTable = CustTable::find(custQuotationJour.InvoiceAccount);

City = dirparty::primaryPostalAddress(custtable.Party).City;
Country = dirparty::primaryPostalAddress(custtable.Party).CountryRegionId;

select WorkerSalesTaker from salesQuotationTable
where SalesQuotationTable.QuotationId == SalesQuotationLine.QuotationId;
pesron = salesQuotationTable.WorkerSalesTaker;

inventTable = InventTable::find(custQuotationTrans.ItemId);

select ItemGroupId from inventItemGroupItem
where inventItemGroupItem.ItemId == inventTable.ItemId;
i++;
quotaionJourId = custQuotationJour.QuotationId + ‘-’;
strLength = strLen(quotaionJourId);

//select * from salesQuotationLine
// where salesQuotationLine.QuotationId == custQuotationTrans.QuotationId
// && salesQuotationLine.InventTransId == custQuotationTrans.InventTransId
// join pwC_AdditionalBOMItems
// where pwC_AdditionalBOMItems.InventTransId == salesQuotationLine.InventTransId
// join pwC_OperationInfoTable
// where pwC_OperationInfoTable.QuotationId == salesQuotationLine.QuotationId
// && pwC_OperationInfoTable.InventTransId == salesQuotationLine.InventTransId;

select * from salesQuotationTable
where salesQuotationTable.QuotationId == custQuotationJour.QuotationId;

select ExternalItemID from custVendExternalItem
where custVendExternalItem.CustVendRelation == custQuotationJour.OrderAccount
&& custVendExternalItem.ModuleType == 4
&& custVendExternalItem.ItemId == inventTable.ItemId;

if(salesQuotationTable.PwC_Offer == NoYes::Yes)
{
offer = ‘Offer’;
}
else
{
offer = ‘Sample’;
}

while select * from pwC_AdditionalBOMItems
where pwC_AdditionalBOMItems.InventTransId == custQuotationTrans.InventTransId

while select * from pwC_OperationInfoTable
where pwC_OperationInfoTable.InventTransId == custQuotationTrans.InventTransId
// conQuotationData = conNull();
conQuotationData = [i, //1 sl no custQuotationJour.QuotationDate,//2 Card issue date custQuotationTrans.quotationDocNum(),//3 sample card strDel(custQuotationTrans.quotationDocNum(),1,strLength),// 4 version inventItemGroupItem.ItemGroupId,//5 product class custQuotationJour.OrderAccount,//6 customer name inventTable.PwC_CustName,//7 End customer City,// 8 city Country,// 9 country HcmWorker::find(pesron).name(),//10 person //salesQuotationTable.WorkerSalesTaker,// 10 person inventTable.itemName(),// 11 part name custVendExternalItem.ExternalItemId,// 12 Part No salesQuotationLine.PwC_LabelDim,// 13 Label dim salesQuotationLine.PwC_NewFaceLift,// 14 new/facelift inventTable.PwC_ModelID,//15 model salesQuotationLine.PwC_Sopdate,// 16 sop salesQuotationLine.PwC_QuantityPerAnnum,// 17 Qty per annum custQuotationTrans.SalesPrice,// 18 Price per set salesquotationline.PwC_ApproxAnnualValueOfBusiness,// 19 Aprox annual value of Bussiness custQuotationTrans.Qty,// 20 sample Qty salesQuotationLine.ShippingDateRequested,// 21 Cust. Date salesquotationline.PwC_Pddate,// 22 pd date salesquotationline.ConfirmedDlv,// 23 Disp. Date offer,// 24 offer/ sample pwC_AdditionalBOMItems.ItemId,// 25 item id of bom pwC_AdditionalBOMItems.ItemName, // 26 Item name pwC_AdditionalBOMItems.ItemSize,// 27 item size pwC_AdditionalBOMItems.Qty,// 28 Item qty pwC_OperationInfoTable.OperationName,// 29 Operation Name pwC_OperationInfoTable.OperationDescription,// 3o Operation Desc salesquotationline.PwC_SpecialRemarks// 31 remarks ];

io.writeExp(conQuotationData);

// while select * from pwC_AdditionalBOMItems
// where pwC_AdditionalBOMItems.InventTransId == custQuotationTrans.InventTransId

// {

// conBOMItems = ["","","","","","","","","","","","","","","","","","","","","","","","",pwC_AdditionalBOMItems.ItemId,
//pwC_AdditionalBOMItems.ItemName,
//pwC_AdditionalBOMItems.ItemSize,
//pwC_AdditionalBOMItems.Qty

//];
//io.writeExp(conBOMItems);

//}
//while select * from pwC_OperationInfoTable
//where pwC_OperationInfoTable.InventTransId == custQuotationTrans.InventTransId
//{
//j++;
//conins(conBOMItems,j,
//}

}
}

CodeAccessPermission::revertAssert();
info(strFmt(“Report generated and saved. Path: %1”, csvfileName));
}
else
{
info(“Invalid file name specified!”);
}
}
}
catch(Exception::Error)
{
throw error(“Erorr in exporting journal lines.”);
}

}

8

You are not doing anything by using these while loops. You may have to write the data to file with in the while loop.

Example:

while select * from pwC_AdditionalBOMItems
where pwC_AdditionalBOMItems.InventTransId == custQuotationTrans.InventTransId

{

// assign values in the container and then write it to file.

}