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.