Skip to main content

Notifications

Announcements

No record found.

News and Announcements icon
Community site session details

Community site session details

Session Id :
Dynamics 365 Community / Forums / Microsoft Dynamics AX (Archived) / CustAgingReport Insert...
Microsoft Dynamics AX (Archived)

CustAgingReport Insert_recordset with method and join

(0) ShareShare
ReportReport
Posted on by HAIRUL HAZRI 1,214

Hi. I'm adding extra information on CustAgingReport and it's using insert_recordset to copy data into temporary table. I added fields from CustTable and it's working fine.

The problem is getting a value from method. I tried assigning the value to variable first but obviously it will return empty value. 

Please help correct my approach. Thank You. 

CustTable tmpCustTable;

Phone contactNo;

contactNo = tmpCustTable.phone();

INSERT_RECORDSET custAgingReportTmp
(AccountNum, Name, CustAccount, Txt, Currency, CustGroup, BillingClassification, Balance, ContactNo, SortOrder, SalesGroup, CreditMax, CreditMaxCust, SegmentId, PaymTermId) 
SELECT
AccountNum, Name, AccountNum, Txt, CurrencyCode, GroupId, BillingClassification, balance, contactNo 
FROM tmpAccountSum
JOIN SortOrder
FROM custVendTransAging
WHERE tmpAccountSum.AccountNum == custVendTransAging.AccountNum
JOIN SalesGroup, CreditMax, CreditMaxCust, SegmentId, PaymTermId
FROM tmpCustTable
WHERE tmpCustTable.AccountNum == tmpAccountSum.AccountNum;

*This post is locked for comments

  • Verified answer
    HAIRUL HAZRI Profile Picture
    HAIRUL HAZRI 1,214 on at
    RE: CustAgingReport Insert_recordset with method and join

    My solution is to manually join the related tables on CustTable.phone() method into the insert_recordset query and get the field directly.

    INSERT_RECORDSET custAgingReportTmp
    (AccountNum, Name, CustAccount, Txt, Currency, CustGroup, BillingClassification, Balance, SortOrder, SalesGroup, CreditMax, CreditMaxCust, SegmentId, PaymTermId, ContactNo) 
    SELECT
    AccountNum, Name, AccountNum, Txt, CurrencyCode, GroupId, BillingClassification, balance
    FROM tmpAccountSum
    JOIN SortOrder
    FROM custVendTransAging
    WHERE tmpAccountSum.AccountNum == custVendTransAging.AccountNum
    JOIN SalesGroup, CreditMax, CreditMaxCust, SegmentId, PaymTermId
    FROM tmpCustTable
    WHERE tmpCustTable.AccountNum == tmpAccountSum.AccountNum

    join dirPartyTable

    where tmpCustTable.Party == dirPartyTable.RecId

    join Locator

    from logisticsElectronicAddress

    where logisticsElectronicAddress.RecId == dirPartyTable.PrimaryContactPhone;

  • HAIRUL HAZRI Profile Picture
    HAIRUL HAZRI 1,214 on at
    RE: CustAgingReport Insert_recordset with method and join

    CustAgingReport is related to many classes and using a lot of temptable. All insert method for those temptables are using insert_recordset just like this. So I don't think it will work.

  • HAIRUL HAZRI Profile Picture
    HAIRUL HAZRI 1,214 on at
    RE: CustAgingReport Insert_recordset with method and join

    Hi Brandon.

    Thank you the clarification.

    But, this CustAgingReport is using RDP. From what I understand, display methods can only be used in query based report. Right?

    Can you suggest any other suitable approach?

    Thank You.

  • Suggested answer
    Brandon Wiese Profile Picture
    Brandon Wiese 17,788 on at
    RE: CustAgingReport Insert_recordset with method and join

    You cannot use a method in an INSERT_RECORDSET.  The kernel attempts to construct a single set based SQL statement from the INSERT_RECORDSET, and SQL Server will have no idea what AX methods are, for starters.  You could break the INSERT_RECORDSET into a standard WHILE SELECT { } statement, but there will be a heavy performance penalty for a record loop vs a set based SQL statement.

    A better approach would be to use a display method on your report that takes the AccountNum and returns the .phone() method from that CustTable record.

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

Quick Links

🌸 Community Spring Festival 2025 Challenge Winners! 🌸

Congratulations to all our community participants!

Adis Hodzic – Community Spotlight

We are honored to recognize Adis Hodzic as our May 2025 Community…

Kudos to the April Top 10 Community Stars!

Thanks for all your good work in the Community!

Leaderboard > Microsoft Dynamics AX (Archived)

#1
Mohamed Amine Mahmoudi Profile Picture

Mohamed Amine Mahmoudi 100 Super User 2025 Season 1

#2
Community Member Profile Picture

Community Member 48

#3
shanawaz davood basha Profile Picture

shanawaz davood basha 6

Overall leaderboard

Featured topics

Product updates

Dynamics 365 release plans