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;
