Finance | Project Operations, Human Resources, ...

Filter inquires form in the D365FnO

(0) Share

Report

Posted on by Riyas ahamed F

1,215

Hi all,

i have a custom inquires and that inquires i filter each month

Jan : 01/01/2020 - 31/01/2020

Feb : 01/02/2020 - 28/02/2020

As shown in the picture above, I highlight the name column, but how do I show it.

Now, i have a value in the "Basic salary" jan(2000) , Feb(400)

if i have jan to May or sometimes jan to Dec so based on a 12 month i want to filter in the executeQuery.

so that I am wondering how to make that happen in the executeQuery.?

but i tried in the job below my code,

        TransDate               currdate = prevYr(today());
        TransDate               Startdate, EndDate;
        int                             i=0, j=12;
        MPPayCycles                     payCycles;
        MPPayCycleWorkers           payCycleWorkers;
        MPPayCycleLines             PayCycleLines;



        for( i =1; i<= j; i  )
        {
            Startdate = dateMthFwd(currdate, j);
            Startdate = DateStartMth(currdate);
            EndDate = dateEndMth(currdate);
            info(strFmt("%1, %2", startDate, endDate));


        select * from  payCycleWorkers
            join  payCycles 
                where payCycles.MPPayCycleID == payCycleWorkers.PayCycleID
                    && payCycles.FromDate >= startDate
                        && payCycles.ToDate <= endDate
                    join PayCycleLines 
                        where PayCycleLines.RefPaycycleWorkerLine == payCycleWorkers.RecId
                            && PayCycleLines.Description like "Basic Salary*";

        Info(strFmt("%1", PayCycleLines.AmountCur));

        }
    }

same code how can i achieve in the executeQuery ? give me example for that?

Thanks

I have the same question (0)

All responses (8)

Answers (0)

Suggested answer

Anton Venter 20,656 Super User 2026 Season 1 on at

Like (1)

Report
Copy link

Link copied!

Hi Riyas,

You have to add code to the executeQuery of the datasource to modify the query of the datasource before it is executed. Have a look in the standard application for examples.

Was this reply helpful? Yes No
Riyas ahamed F 1,215 on at

Like (0)

Report
Copy link

Link copied!

Thanks for your replay Mr.Anton Venter,

I'm stuck same code how to do in the executeQuery please give me some example for that?

Was this reply helpful? Yes No
Anton Venter 20,656 Super User 2026 Season 1 on at

Like (0)

Report
Copy link

Link copied!

Have a look in the RouteInventProdMixedRealityManufacturingInstructionEntityAssociation_Extension class. It wraps the executeQuery method and changes the query before the next executeQuery call.

Was this reply helpful? Yes No
Riyas ahamed F 1,215 on at

Like (0)

Report
Copy link

Link copied!

above u mentioned the class there is no for me.

PU24 my version.

Was this reply helpful? Yes No
Suggested answer

Anton Venter 20,656 Super User 2026 Season 1 on at

Like (0)

Report
Copy link

Link copied!
See below, this is an example of what I mean. You will have to do the rest :-).

///
/// The RouteInventProdMixedRealityManufacturingInstructionEntityAssociation_Extension class extends /// the InstructionEntityAssociation datasource in RouteInventProd form extension. ///
[ExtensionOf(formDataSourceStr(RouteInventProd, InstructionEntityAssociation))] internal final class RouteInventProdMixedRealityManufacturingInstructionEntityAssociation_Extension { ///
/// Extends InstructionEntityAssociation datasource executeQuery. ///
public void executeQuery() { Query query = this.query(); QueryBuildDataSource qbs = query.dataSourceName(this.name()); qbs.clearDynalinks(); qbs.clearRanges(); FormDataSource prodRoute_ds = element.dataSource(formDataSourceStr(RouteInventProd, ProdRoute)) as FormDataSource; RouteOpr routeOpr = element.routeInventForm.findRouteOpr(prodRoute_ds.cursor()); qbs.addRange(fieldNum(InstructionEntityAssociation, EntityRecId)).value(queryValue(routeOpr.RecId)); qbs.addRange(fieldNum(InstructionEntityAssociation, EntityType)).value(queryValue(InstructionEntityType::RouteOpr)); next executeQuery(); } }

Was this reply helpful? Yes No
WillWU 22,363 on at

Like (0)

Report
Copy link

Link copied!
Hi Riyas,

I think you need a complex SQL statement to achieve your requirement. You can't do it in conventional methods.

Try to use Statement.executeQuery to run T-SQL directly from X :community.dynamics.com/.../executing-x-sql-statement-with-datetime-fields-using-sqlbuilder-class-in-d365fo

Just use "case when than" to write a sql statement:

select name,case when month(createtime)='1' then salary else 0 end as 'Jan', case when month(createtime)='2' then salary else 0 end as 'Feb' .... .... from Table

I think the best way is to find a more appropriate solution.

Was this reply helpful? Yes No
Riyas ahamed F 1,215 on at

Like (0)

Report
Copy link

Link copied!

Thanks for your replay MR.Will,

Was this reply helpful? Yes No

Riyas ahamed F

1,215 on at

Like (0)

Report

Copy link

Link copied!

You're correct same i did it but how to get 12 months that SQL,

public server static void populateTempTable_Earning(MPAnnualEarningSummary _tmp, FromDate fromDate, ToDate toDate)
    {
        MPPayCycleWorkers   payCycleWorkers;
        MPPayCycles         payCycles;
        MPPayCycleLines     payCycleLine;
        UserConnection      userconn = new UserConnection();
        str                 fieldName;
        Statement           statement;
        int                 fldCnt;
        str                 tmpTableName, rangeStr, sql;

        MPAnnualEarningSummary    summaryTmp;
        Query                   query = new Query();
        QueryBuildDataSource    qbdsWorker, qbdsLine;
        QueryRun                queryRun;
        DimensionFocusNameTmp   focusTmp;

        delete_from _tmp;

        insert_recordset summaryTmp ( HcmPersonnelNumberId, Worker, FromDate, ToDate)
        select HcmPersonnelNumberId ,Worker, FromDate, ToDate from payCycleWorkers
            join payCycles
                where payCycleWorkers.paycycleId == payCycles.MPPaycycleId
                    && payCycles.FromDate >= fromDate
                    && payCycles.ToDate <= toDate;

                   && payCycleLine.DimensionDefault != 0;

        qbdsLine = query.addDataSource(tableNum(MPPayCycleLines));

        qbdsLine.addSelectionField(fieldNum(MPPayCycleLines, AmountCur), SelectionField::Sum);

        qbdsLine.addOrderByField(fieldNum(MPPayCycleLines, TransTypes));
        qbdsLine.addOrderByField(fieldNum(MPPayCycleLines, Paycode));

        qbdsLine.addGroupByField(fieldNum(MPPayCycleLines, TransTypes));
        qbdsLine.addGroupByField(fieldNum(MPPayCycleLines, Paycode));

        rangeStr = strFmt('((%1 == %2) && (%3 != %4))',
                    fieldStr(MPPayCycleLines, Accrual),
                    enum2int(NoYes::No),
                    fieldStr(MPPayCycleLines, TransTypes),
            enum2int(MPTransactionType::Basic));

        qbdsLine.addRange(fieldNum(MPPayCycleLines, TransTypes)).value(rangeStr);

        qbdsWorker = qbdsLine.addDataSource(tableNum(MPPayCycleWorkers));
        qbdsWorker.addSelectionField(fieldNum(MPPayCycleWorkers, RecId));
        qbdsWorker.addLink(fieldNum(MPPayCycleLines,RefPaycycleWorkerLine), fieldNum(MPPayCycleWorkers, RecID));

        queryRun = new QueryRun(query);
        tmpTableName = summaryTmp.getPhysicalTableName();
        
        while (queryRun.next())
        {
            payCycleLine = queryRun.get(tableNum(MPPayCycleLines));

            fldCnt  ;

            if (fldCnt <= 9)
            {
                fieldName = strFmt('Value0%1',fldCnt);
            }
            else
            {
                fieldName = strFmt('Value%1',fldCnt);
            }

            // Update using direct SQL here so that the field can be set dynamically as update_recordset function
            // in AX doesnt support dynamic fields
            sql = strFmt('UPDATE %1 ',tmpTableName);
            sql  = strFmt('SET %1 = lineTable.AmountCurSum',fieldName);

            switch (payCycleLine.TransTypes)
            {
                case MPTransactionTypeAll::Basic:
                case MPTransactionTypeAll::Allowance:
                case MPTransactionTypeAll::Increase:
                case MPTransactionTypeAll::LeaveSalary:
                case MPTransactionTypeAll::Overtime:
                case MPTransactionTypeAll::Loan:
                case MPTransactionTypeAll::TicketPaym:
                case MPTransactionTypeAll::EoS:
                case MPTransactionTypeAll::BusTrip:
                    sql  = strFmt(', TotalEarnings = (tmp.TotalEarnings   lineTable.AmountCurSum)');
                    break;

                default:
                    sql  = strFmt(', TotalDeductions = (tmp.TotalDeductions   lineTable.AmountCurSum)');
                    break;
            }

            sql  = strFmt(' From %1 tmp ', tmpTableName);
            sql  = strFmt('INNER JOIN (select sum(AmountCur) as AmountCurSum, Accrual, RefPaycycleWorkerLine, TransTypes, PayCode ');
            sql  = strFmt('from MPPayCycleLines group by TransTypes, Paycode, RefPaycycleWorkerLine, Accrual) as lineTable ');
            sql  = strFmt('on lineTable.RefPaycycleWorkerLine = tmp.PayCycleWorker ');
            sql  = strFmt('WHERE lineTable.TransTypes = %1 ',enum2int(payCycleLine.TransTypes));
            sql  = strFmt("AND lineTable.Paycode = '%1'", payCycleLine.PayCode);
            sql  = strFmt(' AND lineTable.Accrual = %1', enum2int(NoYes::No));


        
            new SqlStatementExecutePermission(sql).assert();
            statement = userConn.createStatement();
            statement.executeUpdate(sql);
            CodeAccessPermission::revertAssert();
        }
           

        insert_recordset _tmp
    (
         HcmPersonnelNumberId, Worker, Jan, Feb, Mar, Apl, May, Jun, July, Aug, Sep, Oct, Nov, Dec
      
    )
        select  HcmPersonnelNumberId, Worker, sum(Jan), sum(Feb),
            sum(Mar), sum(Apl), sum(May), sum(Jun), sum(July),
            sum(Aug), sum(Sep), sum(Oct), sum(Nov), sum(Dec)
        from summaryTmp
               group by  summaryTmp.HcmPersonnelNumberId, summaryTmp.Worker;

        update_recordSet _tmp
        setting NetSalary = _tmp.NetSalary;
    }