Dynamics Community Forum Thread Details

Get records from tempDB

I want to implement this functionality but vendBalanceProvisionalTmpProcesing is always empty, i know it is empty because it is tempDB :

 while select AccountNum,PostingProfile from vendProvisionalBalanceTmpProcessing
            {
                select sum(AmountCur) from vendTrans
                    where vendTrans.AccountNum == vendProvisionalBalanceTmpProcessing.AccountNum
                    && vendTrans.PostingProfile == vendProvisionalBalanceTmpProcessing.PostingProfile
                    && vendTrans.TransDate >= _fromDate
                    && vendTrans.TransDate <= toDate;
            
                    tempSum += vendTrans.AmountCur;
            
                select sum(AmountMST) from vendTrans
                    where vendTrans.AccountNum == vendProvisionalBalanceTmpProcessing.AccountNum
                    && vendTrans.PostingProfile == vendProvisionalBalanceTmpProcessing.PostingProfile
                    && vendTrans.TransDate >= _fromDate
                    && vendTrans.TransDate <=toDate;
            
                    tempSum+= vendTrans.AmountMST*ledgerParameters.EonExchangeRate;
            
                    tmpValue.Amount = tempSum;
                    tmpValue.AccountNum = vendTrans.AccountNum;
                    tmpValue.PostingProfile = vendTrans.PostingProfile;
                    tmpValue.doInsert();    
            
            }

But there are 2 scenarios where i can access to vendProvisionalBalanceTmpProcessing.AccountNum :

insert_recordset tmpValue
        (AccountNum, PostingProfile, Amount)
        select AccountNum
        from vendProvisionalBalanceTmpProcessing
            group by AccountNum
        join PostingProfile, sum(AmountMST) from vendTrans
            group by PostingProfile
            where vendTrans.AccountNum == vendProvisionalBalanceTmpProcessing.AccountNum
                && vendTrans.PostingProfile == vendProvisionalBalanceTmpProcessing.PostingProfile
                && vendTrans.TransDate < _fromDate;

    update_recordset vendProvisionalBalanceTmpProcessing
        setting OpeningBalance = tmpValue.Amount
        join tmpValue
            where tmpValue.AccountNum == vendProvisionalBalanceTmpProcessing.AccountNum
                && tmpValue.PostingProfile == vendProvisionalBalanceTmpProcessing.PostingProfile;

Quick Links