Skip to main content

Notifications

Announcements

🌸 Community Spring Festival 2025 Challenge & Updates🌸
News and Announcements icon
Community site session details

Community site session details

Session Id :
Dynamics 365 Community / Forums / Finance | Project Operations, Human Resources, ... / Temp table issue in D3...
Finance | Project Operations, Human Resources, ...
Answered

Temp table issue in D365Fo

(0) ShareShare
ReportReport
Posted on by Riyas ahamed F 1,215

Hi guys,

i have a enquiry form and while a open form records are empty and i'm used in the enquiry form TempTable

but same while a open form in ax 2012 records showing D365Fo not how to populate in D365Fo?

Thanks 

  • Verified answer
    Martin Dráb Profile Picture
    Martin Dráb 232,994 Most Valuable Professional on at
    RE: Temp table issue in D365Fo

    First of all, you've commented out the call of populateTempTable(), therefore populateTempTable_Accrual() isn't called either.

    It seems that you didn't try to debug your code, otherwise you would have found that.

  • Riyas ahamed F Profile Picture
    Riyas ahamed F 1,215 on at
    RE: Temp table issue in D365Fo

    Sir i want to show the record without data disappears in the temptable so that below my code once open and close the form data disappears.

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

    MPPayCycleAccrualSummaryTmp    summaryTmp;

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

    delete_from _tmp;

    insert_recordset summaryTmp (PayCycleWorker, ContNetSalary, GrossSalaryCur, HcmPersonnelNumberId, MPCTC, Worker, FromDate, ToDate, PaycycleID, PayGroup)
        select recID, ContNetSalary, GrossSalaryCur, HcmPersonnelNumberId, MPCTC, Worker from payCycleWorkers
            join FromDate, ToDate, MPPaycycleID, PayGroup from payCycles
                where payCycleWorkers.paycycleId == payCycles.MPPaycycleId
                    && payCycles.FromDate >= fromDate
                    && payCycles.ToDate <= toDate;

   
    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));

    qbdsLine.addRange(fieldNum(MPPayCycleLines, Accrual)).value(SysQuery::value(NoYes::Yes));

    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));

        // 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);

        switch (payCycleLine.TransTypes)
        {
            case MPTransactionTypeAll::PensionAccrual:
                sql  = strFmt('SET PensionCompany = lineTable.AmountCurSum',fieldName);
                sql  = strFmt(', TotalPension = (tmp.TotalPension   lineTable.AmountCurSum)');
                break;

            default:
                fldCnt  ;

                if (fldCnt <= 9)
                {
                    fieldName = strFmt('Value0%1',fldCnt);
                }
                else
                {
                    fieldName = strFmt('Value%1',fldCnt);
                }
                sql  = strFmt('SET %1 = lineTable.AmountCurSum',fieldName);
                break;
        }
        sql  = strFmt(', TotalAccruals = (tmp.TotalAccruals   lineTable.AmountCurSum)');
        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::Yes));

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

    update_recordSet summaryTmp
        setting PensionEmployee = payCycleLine.AmountCur,
            TotalAccruals = summaryTmp.TotalAccruals   payCycleLine.AmountCur,
            TotalPension = summaryTmp.TotalPension   payCycleLine.AmountCur
            join payCycleLine
                where payCycleLine.RefPaycycleWorkerLine == summaryTmp.PayCycleWorker
                    && payCycleLine.TransTypes == MPTransactionTypeAll::PensionAccrual
                    && payCycleLine.Accrual == NoYes::No;

    insert_recordset _tmp
    (
        PayGroup, HcmPersonnelNumberId, Worker, TotalAccruals, TotalPension, PensionEmployee, PensionCompany, DefaultDimension,
        Value01, Value02, Value03, Value04, Value05, Value06, Value07, Value08,
        Value09, Value10, Value11, Value12, Value13, Value14, Value15, Value16,
        Value17, Value18, Value19, Value20, Value21, Value22, Value23, Value24,
        Value25, Value26, Value27, Value28, Value29, Value30, Value31, Value32,
        Value33, Value34, Value35, Value36, Value37, Value38, Value39, Value40
    )
        select PayGroup, HcmPersonnelNumberId, Worker, sum(TotalAccruals), sum(TotalPension), sum(PensionEmployee), sum(PensionCompany), DefaultDimension,
            sum(Value01), sum(Value02), sum(Value03), sum(Value04), sum(Value05),
            sum(Value06), sum(Value07), sum(Value08), sum(Value09), sum(Value10),
            sum(Value11), sum(Value12), sum(Value13), sum(Value14), sum(Value15),
            sum(Value16), sum(Value17), sum(Value18), sum(Value19), sum(Value20),
            sum(Value21), sum(Value22), sum(Value23), sum(Value24), sum(Value25),
            sum(Value26), sum(Value27), sum(Value28), sum(Value29), sum(Value30),
            sum(Value31), sum(Value32), sum(Value33), sum(Value34), sum(Value35),
            sum(Value36), sum(Value37), sum(Value38), sum(Value39), sum(Value40) from summaryTmp
                group by summaryTmp.PayGroup, summaryTmp.HcmPersonnelNumberId, summaryTmp.Worker;

    update_recordSet _tmp
        setting NetSalary = _tmp.TotalEarnings - _tmp.TotalDeductions;
}
}

    Form code below,

     public void init()
    {
        super();

        accrualsParameters = MPPaycycleAccrualsParams::construct();
        accrualsParameters.loadLastValue(formStr(MPPaycycleEarningsParams));
        //this.populateTempTable();
        this.initializeControl(accrualsParameters.getStartDate(), accrualsParameters.getEndDate());
        
    }
    public void populateTempTable()
    {
        MPPaycycleSummaryListPage::populateTempTable_Accrual(MPPayCycleAccrualSummaryTmp, accrualsParameters.getStartDate(), accrualsParameters.getEndDate());
    }

    /// 
    /// Refreshes the grid.
    /// 
    public void refresh()
    {
        this.populateTempTable();

        MPPaycycleAccrualSummaryTmp_ds.research(true);
    }

    Thanks

  • Blue Wang Profile Picture
    Blue Wang on at
    RE: Temp table issue in D365Fo

    Hi Riyas,

    Temp table populated only exists in the scope of it's execution object.  If out of scope, then you lose your temp table variable and buffer.

    docs.microsoft.com/.../temporary-tempdb-tables

    The temp table you are trying to display on the grid must be populated in scope (within your form, or passed in through Args() from a caller object), then you need to link the temp table to your form datasource.

    www.axaptapedia.com/Temporary_tables

  • Martin Dráb Profile Picture
    Martin Dráb 232,994 Most Valuable Professional on at
    RE: Temp table issue in D365Fo

    I already told you what you should do - you should either insert data to the temporary buffer when opening the form, or use a non-temporary table.

  • Riyas ahamed F Profile Picture
    Riyas ahamed F 1,215 on at
    RE: Temp table issue in D365Fo

    Thanks for your replay sir,

    i'm sure in ax 2012 its tempTable but i want to when opening the form data populate so that what should i do ? once ax 2012 server connected for me i will show the snap for the tempTable.

  • Suggested answer
    Martin Dráb Profile Picture
    Martin Dráb 232,994 Most Valuable Professional on at
    RE: Temp table issue in D365Fo

    If it works in AX 2012, then the table isn't temporary there. Temporary tables don't store data in database - when you close the form, the data disappears.

    If you want to store data in the table when you close the form, then don't use a temporary table.

    If you want to use a temporary table, you must insert some data into it when opening the form.

  • Riyas ahamed F Profile Picture
    Riyas ahamed F 1,215 on at
    RE: Temp table issue in D365Fo
    [quote user="Riyas ahamed F"]

    Hi guys,

    i have a enquiry form and while a open form records are empty and i'm used in the enquiry form TempTable

    but same while a open form in ax 2012 records showing D365Fo not how to populate in D365Fo?

    Thanks 

    [/quote]

    But in D365Fo record created at the time once form close and again open there no record. same i did it in ax 2012 its working.

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

News and Announcements

🌸 Community Spring Festival 2025 Challenge & Updates🌸

Quick Links

🌸 Community Spring Festival 2025 Challenge 🌸

WIN Power Platform Community Conference 2025 tickets!

Jonas ”Jones” Melgaard – Community Spotlight

We are honored to recognize Jonas "Jones" Melgaard as our April 2025…

Kudos to the March Top 10 Community Stars!

Thanks for all your good work in the Community!

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 294,261 Super User 2025 Season 1

#2
Martin Dráb Profile Picture

Martin Dráb 232,994 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,158 Moderator

Leaderboard

Featured topics

End-of-life Announcement for Microsoft Dynamics GP

Product updates

Dynamics 365 release plans