Sorting data in datatool depending on argument

Question

I'm pretty new on latex programming (I used quite a lot for writing text). I'm trying to insert data on a datatool database and then sort the data according to one of the fields. However, if this field is filled through a macro argument, it works only if the argument itself in not the result of another macro. Here the code:

\documentclass{article}

 \usepackage{datatool}

\newcommand{\sortitem}[3]{%
   \DTLnewrow{list}%
   \DTLnewdbentry{list}{value}{#1}%
   \DTLnewdbentry{list}{label}{#2}%
   \DTLnewdbentry{list}{description}{#3}%
}

\newcommand{\aaa}{a}
\newcommand{\fff}{f}
\newcommand{\bbb}{b}
\newcommand{\zzz}{z}
\newcommand{\ccc}{c}

\newenvironment{sortedlist}%
{%
   \DTLifdbexists{list}{\DTLcleardb{list}}{\DTLnewdb{list}}%
}%
{%
   \DTLsort*{value}{list}%
   \begin{enumerate}
     \DTLforeach*{list}{\theLabel=label,\theDesc=description, \theValue=value}{%
       \item \theValue~\theLabel
     }%

     \end{enumerate}%
} 
\begin{document}

Sorted:

\begin{sortedlist}
   \sortitem{a}{item}{opt}
   \sortitem{f}{item}{opt}
   \sortitem{b}{item}{opt}
   \sortitem{z}{item}{opt}
   \sortitem{c}{item}{opt}
\end{sortedlist}

Not sorted:

 \begin{sortedlist}
   \sortitem{\aaa}{item}{opt}
   \sortitem{\fff}{item}{opt}
   \sortitem{\bbb}{item}{opt}
   \sortitem{\zzz}{item}{opt}
   \sortitem{\ccc}{item}{opt}
\end{sortedlist}

\end{document}

The ouput is:

Sorted:

1. a item
2. b item
3. c item
4. f item
5. z item

Not sorted (wrong):

1. a item
2. f item
3. b item
4. z item
5. c item

that is, as expected in the first case but not in the second case. I suspect it is due to the order of macro expansion thus I tried to insert \expandafter but with no luck. Can anyone shed some light on the problem to give me a clue on what to investigate?

EDIT: koleygr solution works indeed. However if I introduce a more complex macro like for example:

\usepackage[first=1, last=9]{lcg}
\newcommand{\random}{\rand\arabic{rand}}
...
 \begin{sortedlist}
   \sortitem{\random}{item}{opt}
   \sortitem{\random}{item}{opt}
   \sortitem{\random}{item}{opt}
   \sortitem{\random}{item}{opt}
   \sortitem{\random}{item}{opt}
\end{sortedlist}

Latex does not compile anymore (TeX capacity exceeded, sorry [input stack size=5000]). I tried to change the input stack size but it didn't help. On the other hand if I define \random as robust command, the situation goes back to the previous situation (the db is not ordered as expected). Any other clue?

Answer 1

Changed for Edit's request

\documentclass{article}
\usepackage{datatool}
\usepackage{tikz}
 %\def\random#1{\pgfmathparse{random(#1)}\pgfmathresult} %<-This or any 
 % simmilar I tied doesn't work

\newcommand{\sortitem}[3]{%
   \DTLnewrow{list}%
   \dtlexpandnewvalue
   \DTLnewdbentry{list}{value}{#1}%
   \DTLnewdbentry{list}{label}{#2}%
   \DTLnewdbentry{list}{description}{#3}%
}

\newcommand{\aaa}{a}
\newcommand{\fff}{f}
\newcommand{\bbb}{b}
\newcommand{\zzz}{z}
\newcommand{\ccc}{c}

\newenvironment{sortedlist}%
{%
   \DTLifdbexists{list}{\DTLcleardb{list}}{\DTLnewdb{list}}%
}%
{%
   \DTLsort*{value}{list}%
   \begin{enumerate}

     \DTLforeach*{list}{\theLabel=label,\theDesc=description, \theValue=value}{%
       \item \theValue~\theLabel
     }%

     \end{enumerate}%
} 
\begin{document}

Sorted:

\begin{sortedlist}
   \sortitem{a}{item}{opt}
   \sortitem{f}{item}{opt}
   \sortitem{b}{item}{opt}
   \sortitem{z}{item}{opt}
   \sortitem{c}{item}{opt}
\end{sortedlist}

Not sorted:

\foreach \i in {1,...,5}{
\pgfmathparse{random(9)}
\expandafter\xdef\csname MyItem\i\endcsname{\pgfmathresult}

Item\i=\pgfmathresult\par
}

Sorted:
\begin{sortedlist}
\foreach \i in {1,...,5}{
  \sortitem{\csname MyItem\i\endcsname}{item}{opt} 
}
\end{sortedlist}\vspace*{10pt}

\begin{sortedlist}
\foreach \i in {\aaa,\bbb,\ccc,\zzz,\fff}{
  \sortitem{\i}{item}{opt} 
}
\end{sortedlist}

\end{document}

The above code gives the following result:

You can find this way how to use random numbers with the help of tikz package. Using the commented \random command or any similar did not gave me the wanted result. The numbers was changing every time and inside the Sorting procedure too. But I think you can do your work like above.

Old Answer

You have to expand values with \dtlexpandvalue:

\documentclass{article}

 \usepackage{datatool}

\newcommand{\sortitem}[3]{%
   \DTLnewrow{list}%
   \dtlexpandnewvalue
   \DTLnewdbentry{list}{value}{#1}%
   \DTLnewdbentry{list}{label}{#2}%
   \DTLnewdbentry{list}{description}{#3}%
}

\newcommand{\aaa}{a}
\newcommand{\fff}{f}
\newcommand{\bbb}{b}
\newcommand{\zzz}{z}
\newcommand{\ccc}{c}

\newenvironment{sortedlist}%
{%
   \DTLifdbexists{list}{\DTLcleardb{list}}{\DTLnewdb{list}}%
}%
{%
   \DTLsort*{value}{list}%
   \begin{enumerate}
     \DTLforeach*{list}{\theLabel=label,\theDesc=description, \theValue=value}{%
       \item \theValue~\theLabel
     }%

     \end{enumerate}%
} 
\begin{document}

Sorted:

\begin{sortedlist}
   \sortitem{a}{item}{opt}
   \sortitem{f}{item}{opt}
   \sortitem{b}{item}{opt}
   \sortitem{z}{item}{opt}
   \sortitem{c}{item}{opt}
\end{sortedlist}

Not sorted:

 \begin{sortedlist}
   \sortitem{\aaa}{item}{opt}
   \sortitem{\fff}{item}{opt}
   \sortitem{\bbb}{item}{opt}
   \sortitem{\zzz}{item}{opt}
   \sortitem{\ccc}{item}{opt}
\end{sortedlist}

\end{document}

Found that here: Datatool \DTLnewdbentry does not take value for key column but that answer is for more than one expansions (May be needed for you or someone else with similar problems)