News and Announcements icon

Dynamics 365 CommunityForums Finance | Project Operations, Human Resources, ... Setting a field in one...

Finance | Project Operations, Human Resources, ...

Answered

Setting a field in one table to a count from another table.

(1) Share

Report

Report

Posted on by Dushatar

26

I have two tables, one with just all unique account numbers, and another with lines for each sale an account has made.

I want a field in table 1 that count for each account how many sales they have done.

SQL wise it is easy:

select count(RECID) from Accounts GROUP BY AccountId;

This gives me the number of sales for each account. But how do I assign this number to the field in the right account row in table 1?

I've tried several solutions, but I cant get it to work. I am quite new to x++ and dynamics.

Table1 tab1;

Table2 tab2;

ttsbegin;

while select forUpdate table1{

table1.sales = (select count(RecID) from table2 where table1.AccountId = table2.AccountId group by AccountId;

}

ttscommit;

All responses Img

All responses (4)

Answers Img

Answers (2)

Verified answer

Gunjan Bhattachayya 35,421 on at

Like (1)

Report
RE: Setting a field in one table to a count from another table.

I was just about to reply asking you to check the DataAreaId and I forgot the update as well. Once you are comfortable with this, you can try using an update_recordeset statement for this requirement which will be faster.

Table1 tab1; Table2 tab2; update_recordset tab1 setting Sales = tab2.RecId join count(RecId) from tab2 where tab2.AccountId = tab1.AccountId;

Also, please mark the userful answer(s) as Verified to close the thread.
Dushatar 26 on at

Like (0)

Report

RE: Setting a field in one table to a count from another table.

Nevermind I (we?) forgot tab1.update();

After I added that it worked. Thank you =)
Dushatar 26 on at

Like (0)

Report

RE: Setting a field in one table to a count from another table.

Doesnt seem to work, this sets them all to 0.
Verified answer

Gunjan Bhattachayya 35,421 on at

Like (1)

Report
RE: Setting a field in one table to a count from another table.

Hi Dushatar,

Assuming that field "Sales" in Table1 is an integer field, you can try something like this statement

Table1 tab1; Table2 tab2; ttsbegin; while select forUpdate tab1 { select count(RecID) from tab2 where tab2.AccountId = tab1.AccountId; tab1.sales = tab2.RecId; } ttsCommit;

Helpful resources

Quick Links

Leaderboard

#1

André Arnaud de Cal... 292,892 Super User 2025 Season 1

#2

Martin Dráb 231,772 Most Valuable Professional

#3

nmaenpaa 101,156 Moderator

Leaderboard

Featured topics

End-of-life Announcement for Microsoft Dynamics GP

Product updates

Dynamics 365 release plans