Sorting of ItemId

Finance and Operations/AX User Forum
1

Hello Everyone,

I have an issue relating to ItemId. What i have to do is that, Suppose i have several ItemId’s Like “3012-AO-08023-A, 3012-AO-08023-B, 3012-AO-08023-C”& so-on other series, Now i want to sort these Item id and want to get the last one, like if the series is “3012-AO-08023” then its last value i.e.“3012-AO-08023-C” will print on screen.

Please suggest some solution to it, i am using Ax-2012.

Thanks & Regards

Rajveer

2

You could simply combine LIKE and sorting, for example:

InventTable it;

select firstOnly it
    order by ItemId desc
    where it.ItemId like '3012-AO-0802-*';
3

Hi martin,

Thanks for reply, yes you are correct, but again this Item Id series may be changed i.e it contains several Series Like : 3012-AO-0802, 3012-AO-08019…and so-on…

So how to choose then.

Thanks

Rajveer

4

You can’t write such a query using X++. You would have to fetch all records and process them in application layer. Depending on what exactly you need to achieve, you could, for example, fetch only item ID field, find the IDs you want, save them to a TempDB table and join it with InventTable when needed.

The better approach will be to change database design - you’ve got into these troubles because you try to use non-atomic values.

5

Thank you Martin for guiding me…

6

Hi Martin,

1 more thing i would like to ask you, can you tell me how to sort the string values.

7

Where do you want to sort string values? If in database, it’s the same as with any other values (the only difference is that it depends on database collation), i.e. using order by and so on.
If you explain what’s the problem, you might get a better answer.

8

Hi Martin,

Let me explain you my whole problem, I have to attach certain images in to the SSRS report, now these images are stored in a folder in my system. They are actually ItemId Images, so as i have said in my previous post also that i just fetch the images from the folder, now i want to SORT these ItemId images so that the last version of the particular ItemId image will get. 2055.Capture.PNG

9

So now the question is not about ItemId field in AX at all, it’s rather about reading and sorting file names in a .NET language (in a SSRS report), correct? If so, why didn’t you say that before? I wouldn’t have wasted time with explaining you all the SQL stuff.

You can write the whole thing easily in C# with LINQ:

var highestIDs = from n in Directory.GetFiles(@"c:\somePath")
                    group n by n.Substring(0, 16) into g
                    select g.OrderByDescending(a => a).First();

Change the grouping condition as appropriate.

10

Thank you Martin for this solution, but i get another solution also. its as follows

str moveName, fromName, previousName;

searchFilePath = @“D:\sample\new”;
directoryInfo = new System.IO.DirectoryInfo(searchFilePath);
itemname = “._”+itemId+"-B.png";

fileList = directoryInfo.GetFiles(""+itemId+"");
fileCount = fileList.get_Length();

for (i = 0; i < fileCount; i++)
{
if(i>0)
{
previousName = fromName;
}
file = fileList.GetValue(i);
filename = file.ToString();

fromName = file.get_FullName();

if(strCmp(previousName, fromName) == -1 )
{
previousName = fromName;
}

}
/

info(previousName);

where the itemId is passed as a parameter in the method of the class in Ax…

its working for me…

11

Be warned that GetFiles() doesn’t guarantee any order of the returned files - you should sort them otherwise your code may fail.

12

Yes Martin,

I know…that’s why i use the logic of strcmp()…and i have tested it…its working fine…