Finance | Project Operations, Human Resources, ...

Using insert recordset in temporary table

(0) Share

Report

Posted on by Community Member Microsoft Employee

I have a Form based where I am using Insertrecord set to insert the data in to the temporary table,

But it when debugger comes to execution of this statement, AX doesn't respond and have to restart. What could be the problem...?

    InventSum                           inventSum;
    InventDim                           inventdim;
    InventSerial                        inventSerial;
    z_RentalOrderLine                   rentalLine;
    maRentalSiteReconciliation          reconciliation;
    maItemsWithNoOpenRentalContracts    temptable;

    delete_from reconciliation;
    delete_from temptable;

    insert_recordset reconciliation (ItemId,InventSizeId,InventColorId,InventStyleID,InventLocationId,WMSLocationId,InventSerialId,z_ManufacturersSerial,z_NBV)
    select ItemID from inventSum
    join InventSizeId,InventColorId,InventStyleID,InventLocationId,WMSLocationId from inventdim
    join InventSerialId,z_ManufacturersSerial,z_NBV from inventSerial
    where inventSum.InventDimId         == InventDim.inventDimId
       && inventSerial.InventSerialId   == inventdim.inventSerialId
       && inventSerial.ItemId           == inventSum.ItemId
       && inventSum.Closed              == NoYes::No
       &&(inventSum.PostedQty   inventSum.Received - inventSum.Deducted   inventSum.Registered - inventSum.Picked) - inventSum.ReservPhysical > 0
       && inventdim.wMSLocationId       == "On Rent";

All responses (5)

Answers (0)

Suggested answer

Hossein.K 6,646 on at

Like (0)

Report

RE: Using insert recordset in temporary table

Some times this issue related to the table in AX is not synchronize or cache DB or ...

First do this.
Suggested answer

Hossein.K 6,646 on at

Like (0)

Report

RE: Using insert recordset in temporary table

Hi mahiims,

I remember some year ago , I had the same issue about temporary tables and after run the form Ax Client hanged.

I solved it via set the same connection for all the tables which were used in insert_recordset queries(temporary and regular)

RegularTable.setConnection(this.parmUserConnection());

TempTable.setConnection(this.parmUserConnection());
ergun sahin 8,816 Moderator on at

Like (0)

Report
RE: Using insert recordset in temporary table

It's actually simple but I didn't explain it well. Do not do the following calculation.

&&(inventSum.PostedQty inventSum.Received - inventSum.Deducted inventSum.Registered - inventSum.Picked) - inventSum.ReservPhysical > 0

Open these fields to the temp table and fill in the values. When the records are created, redundant records will come as we remove one of the filtering conditions. Delete the redundant records (with newly opened fields) in temp table

Alternatively, you can review the select clause;
The order of the tables (you should keep the sample set as small as possible),
The order of the fields (indexes).

The order of where conditions

Run tests with smaller data, it can be written better than now.
Community Member Microsoft Employee on at

Like (0)

Report

RE: Using insert recordset in temporary table

Hi Ergun Sahin

can you please share me the sample code for the below statement.

"It may take less time if you open the fields in your temp table and perform operations after the records are created (with filtered records)"
ergun sahin 8,816 Moderator on at

Like (0)

Report

RE: Using insert recordset in temporary table

I can't see a problem in the code. It's probably taking too long because you're processing inside the select (These tables contain too many records).

It may take less time if you open the fields in your temp table and perform operations after the records are created (with filtered records). It may sound silly, but it might be faster to just insert the records and delete the redundant ones.

Community site session details

Using insert recordset in temporary table

Helpful resources

Quick Links

Announcing Our 2025 Season 1 Super Users!

Vahid Ghafarpour – Community Spotlight

Tip: Become a User Group leader!

Leaderboard

Featured topics

Product updates

Community site session details

Using insert recordset in temporary table

Helpful resources

Quick Links

Announcing Our 2025 Season 1 Super Users!

Vahid Ghafarpour – Community Spotlight

Tip: Become a User Group leader!

Subscribe to this forum!

Select categories

Leaderboard

Featured topics

Product updates