Mobile Architecture

 

query.findQueryFilter to find parameter

In controller class:

protected container preRunValidate()
{
    container ret;
    QueryRun                queryRun;
    int cnt,i;
    Query                   query = this.getFirstQuery();
    QueryBuildRange range;
    QueryBuildDataSource    qbds;
    str filter = "";
   
    ret = super();

    qbds = query.dataSourceTable(tablenum(SFA_MembershipTrans));
    queryRun = new QueryRun(query);
 
    if(query.findQueryFilter(qbds,"PaymMode"))      
    {
        ret = [SrsReportPreRunState::Error, "Payment mode is not able to filter in this report"];
    }
   
    if(query.findQueryFilter(qbds,"AmountCur"))      
    {
        ret = [SrsReportPreRunState::Error, "Amount Currency is not able to filter in this report"];
    }
   
    if(query.findQueryFilter(qbds,"AmountMST"))      
    {
        ret = [SrsReportPreRunState::Error, "Amount is not able to filter in this report"];
    }
   
    return ret;
}

Best way inserting query to temporary table to improve performance

Use Query::insert_recordset in R2 for report best practice

insert_recordset and update_recordset will expedite the report performance, and to insert data from query to temp table, have to use Query::insert_recordset.

Understand that from forum it is only available in R3. However I have tried in R2 and surprised it is working.
My code show as below:


[SysEntryPointAttribute]
public void processReport()
{
    SFA_MembershipTransContract     contract;
    SFA_MembershipTrans             membershipTrans;
    RetailTransactionTable          retailTable;
    RetailTransactionSalesTrans     retailSalesTrans;
    RetailTransactionPaymentTrans   retailPaymentTrans,retailPaymentTrans2,retailPaymentTrans3,retailPaymentTrans4;
    Query                           query,q;
    QueryRun                        queryRun;
    RecordInsertList                insList;
    QueryBuildDataSource            qbds, qbds2;
    QueryBuildFieldList             fieldList;
    Map                             fieldMapping;
    SFA_RetailSalesTransTmp         salesTransTmp;
    SFA_RetailSalesTransCountTmp    STCountTmp;
    SFA_RetailPaymentTransTmp       paymTransTmp,paymTransTmp2,paymTransTmp3,paymTransTmp4;
    ;
 
    query = this.parmQuery();
 
    qbds  = query.dataSourceTable(tableNum(SFA_MembershipTrans));
    queryRun    = new QueryRun(query);


    qbds  = query.dataSourceTable(tableNum(SFA_MembershipTrans));

    q = new Query(this.parmQuery());
    fieldList = qbds.fields();
    fieldList.addField(fieldNum(SFA_MembershipTrans, RecId));
    fieldList.addField(fieldNum(SFA_MembershipTrans, Remarks));

    fieldList.dynamic(QueryFieldListDynamic::No);

    fieldMapping = new Map(Types::String, Types::Container);
    fieldMapping.insert(fieldStr(SFA_MembershipTransTmp, MemberTransRefRecId), [qbds.uniqueId(), fieldStr(SFA_MembershipTrans, RecId)]);
    fieldMapping.insert(fieldStr(SFA_MembershipTransTmp, Remarks), [qbds.uniqueId(), fieldStr(SFA_MembershipTrans, Remarks)]);

    query::insert_recordset(tmpTbl,fieldMapping,q);

    ttsBegin;
    update_recordset tmpTbl setting BatchID = membershipTrans.BatchID,BatchName = membershipTrans.BatchName,CardId = membershipTrans.CardId, CardType = membershipTrans.CardType,
    ClubHouse = membershipTrans.ClubHouse, ICNum = membershipTrans.ICNum, InvoiceId = membershipTrans.InvoiceId, MemberStatus = membershipTrans.MemberStatus,
    MemberTypeCode = membershipTrans.MemberTypeCode, ModuleType = membershipTrans.ModuleType, NumberOfMonths = membershipTrans.NumberOfMonths, NumberOfYears = membershipTrans.NumberOfYears,
    PeriodEndDate = membershipTrans.PeriodEndDate, PeriodStartDate = membershipTrans.PeriodStartDate, PlanDate = membershipTrans.PlanDate, ProcessDate = membershipTrans.ProcessDate,
    Processed = membershipTrans.Processed, PromotionId = membershipTrans.PromotionId, ReasonCode = membershipTrans.ReasonCode, ReasonId = membershipTrans.ReasonId,
    ReceiptId = membershipTrans.ReceiptId, RenewalType = membershipTrans.RenewalType, RetailLoyaltyCustId = membershipTrans.RetailLoyaltyCustId, SalesId = membershipTrans.SalesId,
    SFA_Card = membershipTrans.SFA_Card, SourceType = membershipTrans.SourceType, TransDate = membershipTrans.TransDate, Valid = membershipTrans.Valid, PaymentVoucher = membershipTrans.PaymentVoucher
    join membershipTrans where membershipTrans.RecId == tmpTbl.MemberTransRefRecId;

    insert_recordset salesTransTmp (receiptId,netAmountInclTax)
    select receiptId, sum(netAmountInclTax) from retailSalesTrans
    group by retailSalesTrans.ReceiptId
    join tmpTbl where retailSalesTrans.ReceiptId == tmpTbl.ReceiptId;  

    insert_recordset STCountTmp (receiptId,NoCount)
    select receiptId, count(RecId) from retailSalesTrans
    group by retailSalesTrans.ReceiptId
    join tmpTbl where retailSalesTrans.ReceiptId == tmpTbl.ReceiptId && retailSalesTrans.ItemId like "MER-0*";

    update_recordset salesTransTmp setting NoCount = STCountTmp.NoCount
    join STCountTmp where STCountTmp.ReceiptId == salesTransTmp.ReceiptId;

    update_recordset tmpTbl setting AmountMST = salesTransTmp.netAmountInclTax
    join salesTransTmp where salesTransTmp.ReceiptId == tmpTbl.ReceiptId;

    update_recordset tmpTbl setting  PaymReference = "Multiple"
    join salesTransTmp where salesTransTmp.ReceiptId == tmpTbl.ReceiptId && salesTransTmp.NoCount > 1;
 

    insert_recordset paymTransTmp (receiptId,amountMST)
    select receiptId, sum(amountMST) from retailPaymentTrans
    group by retailPaymentTrans.receiptId
    join tmpTbl where retailPaymentTrans.ReceiptId == tmpTbl.ReceiptId;
 
    update_recordSet paymTransTmp setting SFA_PaymMode1 = retailPaymentTrans.SFA_PaymMode, SFA_PaymMode = retailPaymentTrans.SFA_PaymMode
    join firstonly retailPaymentTrans where paymTransTmp.receiptId == retailPaymentTrans.ReceiptId;
     
    update_recordSet paymTransTmp setting SFA_PaymMode2 = retailPaymentTrans2.SFA_PaymMode, SFA_PaymMode = paymTransTmp.SFA_PaymMode +","+ retailPaymentTrans2.SFA_PaymMode
    join firstonly retailPaymentTrans2 where retailPaymentTrans2.ReceiptId == paymTransTmp.receiptId && retailPaymentTrans2.SFA_PaymMode != paymTransTmp.SFA_PaymMode1;
 
    update_recordSet paymTransTmp setting SFA_PaymMode3 = retailPaymentTrans3.SFA_PaymMode, SFA_PaymMode = paymTransTmp.SFA_PaymMode +","+ retailPaymentTrans3.SFA_PaymMode
    join firstonly retailPaymentTrans3 where retailPaymentTrans3.ReceiptId == paymTransTmp.receiptId && retailPaymentTrans3.SFA_PaymMode != paymTransTmp.SFA_PaymMode1 && retailPaymentTrans3.SFA_PaymMode != paymTransTmp.SFA_PaymMode2;
 
    update_recordSet paymTransTmp setting SFA_PaymMode4 = retailPaymentTrans4.SFA_PaymMode, SFA_PaymMode = paymTransTmp.SFA_PaymMode +","+ retailPaymentTrans4.SFA_PaymMode
    join firstonly retailPaymentTrans4 where retailPaymentTrans4.ReceiptId == paymTransTmp.receiptId && retailPaymentTrans4.SFA_PaymMode != paymTransTmp.SFA_PaymMode1 && retailPaymentTrans4.SFA_PaymMode != paymTransTmp.SFA_PaymMode2 && retailPaymentTrans4.SFA_PaymMode != paymTransTmp.SFA_PaymMode3;
 
    update_recordset tmpTbl setting  PaymMode = paymTransTmp.SFA_PaymMode, PaymAmountMST = paymTransTmp.amountMST
    join paymTransTmp where paymTransTmp.ReceiptId == tmpTbl.ReceiptId;
 
    ttsCommit;
}


*Add correct index to temporary table will also improve the performance


My AX version between CU6 - CU7

AX 2012 get list of security roles from SQL with label

--List of security roles

select sr.RECID [Role Id], case when left(sr.Name, 1) = '@' then xr.Text else sr.NAME end as [Role name]

from AXPRDDB_model.dbo.SecurityRole sr with (nolock) 

left join AXPRDDB_model.dbo.ModelElementLabel xr with (nolock) on (case when left(sr.NAME, 1) = '@' then (case when cast(SUBSTRING(sr.NAME, 5, 7) as int) = xr.LabelId and SUBSTRING(sr.NAME, 2, 3) = xr.Module then 1 else 0 end) else 0 end) = 1 and xr.Language = 'en_us'

--List of users with roles select sur.USER_ [User Id], sr.AotName, case when left(sr.Name, 1) = '@' then xr.Text else sr.NAME end as [Name], sur.VALIDFROM, sur.VALIDTO from AXPRDDB.dbo.SECURITYUSERROLE sur with (nolock) inner join AXPRDDB_model.dbo.SecurityRole sr with (nolock) on sur.SECURITYROLE = sr.RecId left join AXPRDDB_model.dbo.ModelElementLabel xr with (nolock) on (case when left(sr.NAME, 1) = '@' then (case when cast(SUBSTRING(sr.NAME, 5, 7) as int) = xr.LabelId and SUBSTRING(sr.NAME, 2, 3) = xr.Module then 1 else 0 end) else 0 end) = 1 and xr.Language = 'en_us' --List of users with duties select t.AOTNAME, case when left(t.Name, 1) = '@' then xr.Text else t.NAME end as [Role name], case when left(t.DESCRIPTION, 1) = '@' then xrd.Text else t.DESCRIPTION end as [Role name] from SecurityRole s with (nolock) inner join  SecurityRoleTaskGrant g with (nolock) on s.RECID = g.SECURITYROLE inner join SecurityTask t with (nolock) on g.SECURITYTASK = t.RECID left join AXPRDDB_model.dbo.ModelElementLabel xr with (nolock) on (case when left(t.NAME, 1) = '@' then (case when cast(SUBSTRING(t.NAME, 5, 7) as int) = xr.LabelId and SUBSTRING(t.NAME, 2, 3) = xr.Module then 1 else 0 end) else 0 end) = 1 and xr.Language = 'en_us' left join AXPRDDB_model.dbo.ModelElementLabel xrd with (nolock) on (case when left(t.DESCRIPTION, 1) = '@' then (case when cast(SUBSTRING(t.DESCRIPTION, 5, 7) as int) = xrd.LabelId and SUBSTRING(t.DESCRIPTION, 2, 3) = xrd.Module then 1 else 0 end) else 0 end) = 1 and xrd.Language = 'en_us' where s.AOTNAME = 'LedgerAccountant'

Cannot Edit A LIne in SourceDocumentLine....

http://community.dynamics.com/ax/f/33/p/148665/337531#337531

http://community.dynamics.com/ax/f/33/p/94859/314786#314786

1.       As checked,  the table “PurchLine” and table “SourceDocumentLine” relation is one-to-one, but a record in SourceDocumentLine don’t have a correspondent purchase line(RecId = 6089).

update SourceDocumentLine set SourceDocumentHeader = 9999999 where RecId = 6089

2.        I tried to unbind their relation, the PO confirm is can be.

 Conclusion:

              Unbind record SourceDocumentLine relation to PurchLine, then be ok.

AX SSRS - copy report design from one VS to another VS

Purpose of such action

Scenario:

SSRS Report has alot of report design. If export/import via XPO, may cause AOT to hang, or load too long while importing. This works for situation where you only want to update one report design. Only works where you have access rights to develop in Visual Studio.

Steps:

1. Open visual studio, browse through the Application Explorer and locate the SSRS > Report, and open the report you required. 
2. Open only the desired report design, nothing else. 
3. Select any object, right click and open its properties (textbox properties). Copy the textbox name (for example, textbox123).
4. In the report designer, find or click Ctrl + f, find any object in the report design, in this case, find "textbox123". It will open the XML code of the report.
5. Copy the XML code, do the same at the destination Visual Studio, and paste the code there.

copy object name

find object name to open the XML codes

Create and implement sequence number

Scenario:

New Reservation code, ST

Step 1:

Create new sequence number into number sequence setting

Step 2:

Insert code into table layer method.

Step 3:

Implement methods into form base datasource methods.

Custom lookup based on fields in Form

Sample situation: Payment category field need to filter based on Sales Order sales type (SalesTable.AvSalesType).
 If sales type == sales contract then filter payment category where AvTurnIn == Yes.
If sales type == service invoice then filter payment category where AvServiceInvoice == Yes.

Code:
Override AvPayment.AvPaymentCategory.lookup().

public void lookup(FormControl _formControl, str _filterStr)
{
    Query query = new Query();
    QueryBuildDataSource queryBuildDataSource;
    QueryBuildRange queryBuildRange;
    SysTableLookup sysTableLookup = SysTableLookup::newParameters(tableNum(AvPaymentCategory), _formControl);
    sysTableLookup.addLookupField(fieldNum(AvPaymentCategory, AvPaymentCateogry));
    sysTableLookup.addLookupField(fieldNum(AvPaymentCategory, Description));
    queryBuildDataSource = query.addDataSource(tableNum(AvPaymentCategory));

    if (SalesTable.AvSalesType == AvSalesType::SalesContract)
    {
        queryBuildRange = queryBuildDataSource.addRange(fieldNum(AvPaymentCategory, AvTurnIn));
    }
    else
    {
        queryBuildRange = queryBuildDataSource.addRange(fieldNum(AvPaymentCategory, AvServiceInvoice));
    }

    queryBuildRange.value('1');

    sysTableLookup.parmQuery(query);
    sysTableLookup.performFormLookup();

}

How to use Code Table create Enum

1. Go to Form AvCodeTable to create the new code:

2. Create new EDT extend from AvCodeTable:

3. a) Create Table References:

    b) Create filter Value 5 (select value filter from the Form AvCodeTable field Code type ID refer to step 1) :

4. Add the field to Table:

5. Add relation 

6. Able to browse it at table now:

Adding FileNameOpen Extended Data Type to a Form

After add EDT FileNameOpen to form using New -> stringEdit, 

add some form methods:

str fileNameLookupFilename()
{
    return '';
}

str fileNameLookupTitle()
{
    return "@SYS53008";
}

str fileNameLookupInitialPath()
{
    return '';
}

container fileNameLookupFilter()
{
    return ['All files','*.*'];
}

After get the file path, call insert method:

public void AvInsertAttachment(filename file, int i)

{    

    DocuRef docuRef;

    DocuActionArchive archive;

    ;           

    

    if(file)

    {

        ttsbegin;

        docuRef.clear();

        docuRef.RefRecId = DirPartyEntity.RecId;

        docuRef.RefTableId = tableNum(CustTable);

        docuRef.RefCompanyId = DirPartyEntity.dataAreaId;

        docuRef.Name = strFmt("Consultant Attachment %1", i);

        docuRef.TypeId = 'File';

        docuRef.insert();

        archive = new DocuActionArchive();

        archive.add(docuRef, file);

        ttsCommit;

    }

}

Result:

Job to update customer financial dimension in dynamics ax 2012

static void setFinancialDimensionToCustomer(CustAccount _custAccount)  
 {  
  CustTable custTable;  
  Struct struct = new Struct();  
  container ledgerDimension;  
  DimensionDefault DimensionDefault;  
  ;  
  struct.add('BookingChannel', '30');  
  struct.add('Carrier', '01');  
  struct.add('Department', '30');  
  struct.add('Destination', '01');  
  struct.add('Division', '30');  
  struct.add('Origin', '01');  
  struct.add('Product', '30');  
  ledgerDimension += struct.fields();  
  ledgerDimension += struct.fieldName(1);  
  ledgerDimension += struct.valueIndex(1);  
  ledgerDimension += struct.fieldName(2);  
  ledgerDimension += struct.valueIndex(2);  
  ledgerDimension += struct.fieldName(3);  
  ledgerDimension += struct.valueIndex(3);  
  ledgerDimension += struct.fieldName(4);  
  ledgerDimension += struct.valueIndex(4);  
  ledgerDimension += struct.fieldName(5);  
  ledgerDimension += struct.valueIndex(5);  
  ledgerDimension += struct.fieldName(6);  
  ledgerDimension += struct.valueIndex(6);  
  ledgerDimension += struct.fieldName(7);  
  ledgerDimension += struct.valueIndex(7);  
  ttsBegin;  
  DimensionDefault = AxdDimensionUtil::getDimensionAttributeValueSetId(ledgerDimension);  
  custTable = CustTable::find(_custAccount, true);  
  custTable.DefaultDimension = DimensionDefault;  
  custTable.update();  
  ttsCommit;  
 }  

A call to the Microsoft Dynamics AX SRSFramework Service service failed. An existing connection was forcibly closed by the remote host.

Issues related to AXRDCE are often resolved by deleting the AUC files

1. Stop reporting services,
2. Goto: C:\Users\xxx\AppData\Local\ ( may in difference path like D:\ E:\)
3. Take the backup of all AUC files,
4. Remove them from there
5. Start reporting services

* Another problem may face is may have multiple ax service in multiple server, try to start all the ax first only process the element deployment (ssrs report element deploy).

Can't view the image/picture in ESS report using IE browser, but can view in Firefox

Try to change the MIME type in Visual studio image properties.

Access denied to field Partition (Partition) in table XXXX...

The problem should be no add the table in Permissions Privileges. Example:

How to save the SSRS report to PDF/HTML through code in dynamics ax 2012.

SrsReportRun srsReportRun; srsReportRun = new SrsReportRun("InventTruckTransactionReport.PrecisionDesign1"); srsReportRun.init(); srsReportRun.reportCaption("InventTruckTransactionReport.PrecisionDesign1"); srsReportRun.reportParameter("TruckTransDS_JournalId").value("000161_070"); srsReportRun.showDialog(false); // Print to a file named ReportExample in HTML/PDF format. srsReportRun.printDestinationSettings().printMediumType(SRSPrintMediumType::File); srsReportRun.printDestinationSettings().fileFormat(SRSReportFileFormat::PDF); srsReportRun.printDestinationSettings().overwriteFile(true); srsReportRun.printDestinationSettings().fileName(@"C:\InventTruckTransactionReport.pdf"); if( srsReportRun ) { srsReportRun.executeReport(); }

How to print the SSRS report in dynamics ax 2012 from code.

SrsReportRun srsReportRun; // initiate the report. srsReportRun = new SrsReportRun ("InventTruckTransactionReport.PrecisionDesign1"); srsReportRun.init(); srsReportRun.reportCaption("InventTruckTransactionReport.PrecisionDesign1"); // set parameters name, value. srsReportRun.reportParameter("TruckTransDS_JournalId").value("000161_070"); // suppress the dialog srsReportRun.showDialog(false); if( srsReportRun ) { // run the report srsReportRun.executeReport(); }

DMF Error During Import

DMF Error During Import. System.ServiceModel.ProtocolException: The remote server returned an unexpected response: (502) Bad Gateway. ---> System.Net.WebException: The remote server returned an error: (502) Bad Gateway. at System.Net.HttpWebRequest.GetResponse() at System.ServiceModel.Channels.HttpChannelFactory`1.HttpRequestChannel.HttpChannelRequest.WaitForReply(TimeSpan timeout) --- End of inner exception stack trace --- Makesure the LAN SETTING of INTERNET CONNECTION is not hijacked or override by third party software, e.g. hotspot shield.

Create Var layer Visual studio

right click edit Visual studio short cut
paste the following link, edit correct path link.

"E:\Program Files (x86)\Microsoft Visual Studio 10.0\Common7\IDE\devenv.exe" /AxConfig C:\VAR_INCEIF_STG_FINAL.axc
pause

Open Client auto clash

go to C:\Users\sj.lee(UserName)\AppData\Local

delete all file name start with ax_...

Deploy all report

Open Windows PowerShell as an administrator by following these steps:

1. Click Start > Administrative Tools.

2. Right-click the Microsoft Dynamics AX 2012 Management Shell option.

3. Click Run as administrator.

4. To deploy a specific report, enter the name of the report. For example, to deploy the CustTransList report, enter the following command:

Publish-AXReport -ReportName CustTransList

5. To deploy two or more specific reports, enter the names of the reports. For example, to deploy the CustTransList and CustTransOpenPerDate reports,
   enter the following command:

Publish-AXReport -ReportName CustTransList, CustTransOpenPerDate


6. To deploy all reports, enter the following command:

Publish-AXReport ReportName *