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
