Microsoft Dynamics AX (Archived)

Row_Number() in insert_recordset

(0) Share

Report

Posted on by XSpy

209

Hello Everyone!

X++ newbie here.

I want to put a Row Number Field in my AX Table but there is no ROW_NUMBER() function in X++ (i think)

only in SQL Server, here is the code in SQL SERVER:

----------------------------

GO
SELECT FirstName, LastName, TerritoryName, ROUND(SalesYTD,2,1),
ROW_NUMBER() OVER(PARTITION BY TerritoryName ORDER BY SalesYTD DESC) AS RowNumber
FROM Sales.vSalesPerson
WHERE TerritoryName IS NOT NULL AND SalesYTD <> 0
ORDER BY TerritoryName;

--------------------

Output:

FirstName  LastName             TerritoryName        SalesYTD      RowNumber
---------  -------------------- ------------------   ------------  ---
Lynn       Tsoflias             Canada               1421810.92    1
José       Saraiva              Canada               2604540.71    2
Garrett    Vargas               Canada               1453719.46    3
Jillian    Carson               Central              3189418.36    1
Ranjit     Varkey Chudukatil    France               3121616.32    1
Rachel     Valdez               Germany              1827066.71    1
Michael    Blythe               Northeast            3763178.17    1
Tete       Mensa-Annan          Northwest            1576562.19    1
David      Campbell             Northwest            1573012.93    2
Pamela     Ansman-Wolfe         Northwest            1352577.13    3
Tsvi       Reiter               Southeast            2315185.61    1
Linda      Mitchell             Southwest            4251368.54    1
Shu        Ito                  Southwest            2458535.61    2
Jae        Pak                  United Kingdom       4116871.22    1

-------------------

This is my code(which is not working):

insert_recordset mytmptable (LoadId, OrderNum, WorkId, RowNumber)
select LoadId, OrderNum, WorkId,

ROW_NUMBER() OVER(PARTITION BY WorkId ORDER BY LoadId DESC) AS RowNumber

from whsWorkLine join whsWorkTable
where whsWorkLine.WorkId == whsWorkTable.WorkId;

-------------------------

Can you translate the code in X++ version?

Please help me, thanks.

*This post is locked for comments

I have the same question (0)

All responses (13)

Answers (2)

Community Member on at

Like (0)

Report

I don't think this function is implemented in AX. I also don't think you can select data from other partitions as long as the table is SaveDataPerPartition = Yes. Since you will have only data of one partition, you might as well just fill your RowNumber column with a increasing counter. If you want a unique identifier of a row in AX use the ReciId.

What exactly do you want to do with this number? What is it supposed to represent?

Was this reply helpful? Yes No
XSpy 209 on at

Like (0)

Report

Hello Jens,

I want to add that column to make a Multi-Column Report

with output of:

ex.

Column1 Column2 Column3

Data1 Data2 Data3

Data4 Data5

if you can, can you please give me a sample of the increasing counter that you're saying?

or any other suggestions of how to make my desired report.

Thanks.

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

I'm didn't do much reporting yet so I can't help you with that. But I don't understand how a column filled with a rowNumber helps you with creating multiple columns. Maybe somebody else has an idea.

Was this reply helpful? Yes No
XSpy 209 on at

Like (0)

Report

I just need the ROW_NUMBER() function or the Increasing Counter that you're saying,

after getting the data that i needed, i think i can easily make the report.

just to give you my idea:

Column1 Column2 Column3

(RowNumbers = 1) (RowNumbers = 2) (RowNumbers = 3)

data1 data2 data3

data4 data5

now i only need the incrementing data / ROW_NUMBER / Increasing Counter Code.

please help.

Thanks.

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

Change your statement to while select and declare a variable that always count to 3 and is then reset.

int i;

while select...

{

i++;

mytmptable.RowNumber = i;

mytmptable.field1 = ....

mytmptable.insert();

if(i == 3)

{

i = 0;

}

}

But thats not a good solution but more like a hack. I'm sure there is a easier way to do this in the report itself. But as I said, I can't help you with the report.

Was this reply helpful? Yes No
XSpy 209 on at

Like (0)

Report

thanks for the suggestion, but my data is grouped (like the first data example)

while your suggestion is just looping data 1 to 3.

thanks tho.

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

But then the number does not make sense. Why would you want all with number 1 in the first column, if 1 contains records from different territoties. And if you just want to group by territory, why would you need the number?

If you want the same territory on one line, then use my code, but order by territory and reset the counter if the territory changes and not when it reaches 3.

Was this reply helpful? Yes No
XSpy 209 on at

Like (0)

Report

when i use your code,

the data will be:

ex.

Column1 Column2 RowNum

00001 111 1

00001 112 2

00001 113 3

00001 114 1

00001 115 2

00002 116 3

00002 117 1

00002 118 2

00003 119 3

00003 120 1

so when i filter 00002

the output will be

Column1 Column2 RowNum

00002 116 3

00002 117 1

00002 118 2

data will be mixed up ( in report )

i want something like this:

Column1 Column2 RowNum

00001 111 1

00001 112 2

00001 113 3

00001 114 1

00001 115 2

00002 116 1 (RowNum resets here bec. Column1 changed)

00002 117 2

00002 118 3

00003 119 1

00003 120 2

Was this reply helpful? Yes No
Verified answer

Community Member on at

Like (0)

Report

As I said, you have to adapt the logic of course.

int i = 1;

str column1edt;

while select table

ORDER BY Column1

{

if(!column1edt)

{

column1edt = table.Column1;

}

if(i == 4 || column1edt != table.Column1)

{

i = 1;

}

mytmptable.RowNumber = i;

mytmptable.field1 = ....

mytmptable.insert();

i++;

column1edt = table.Column1

}

Was this reply helpful? Yes No
XSpy 209 on at

Like (0)

Report

thanks for the answer, i'm trying this code, but still not getting the correct values.

here is my code, please take a look if you have time.

----------------------------------------------------------

int rowNumb;

while select whsWorkLine join whsWorkTable

order by whsWorkLine.WorkId

where whsWorkLine.WorkId == whsWorkTable.WorkId

&& whsWorkTable.WorkStatus == contract.parmWorkStatus()

&& whsWorkTable.LoadId == contract.parmLoadId()

if(!whsWorkId)

{

whsWorkId = whsWorkLine.WorkId;

}

if(rowNumb == 4 || whsWorkId != whsWorkLine.WorkId)

{

rowNumb = 1;

}

fefwmspicklistTmp.RowNum = rowNumb;

fefwmspicklistTmp.LoadId = whsWorkLine.LoadId;

fefwmspicklistTmp.OrderNum = whsWorkLine.OrderNum;

fefwmspicklistTmp.WorkId = whsWorkLine.WorkId;

fefwmspicklistTmp.WMSLocationId = whsWorkLine.WMSLocationId;

fefwmspicklistTmp.ItemId = whsWorkLine.ItemId;

fefwmspicklistTmp.UnitId = whsWorkLine.UnitId;

fefwmspicklistTmp.QtyWork = whsWorkLine.QtyWork;

fefwmspicklistTmp.ShipmentId = whsWorkLine.ShipmentId;

fefwmspicklistTmp.Notes = whsWorkLine.Notes;

fefwmspicklistTmp.WorkTransType = whsWorkTable.WorkTransType;

fefwmspicklistTmp.MainNotes = whsWorkTable.MainNotes;

fefwmspicklistTmp.insert();

rowNumb++;

im a newbie so i can't debug. thanks for the help.

Was this reply helpful? Yes No