# How to check if a datatool db has an existing member

I would like to suppress duplicated entries in a list. The lists are comma separated lists defined in a macro as in my earlier question on Sorting Comma Separated Lists defined with, or without macro.

My current approach is to use the datatool package. The most obvious method to me seemed to use \DTLgetlocation. But, that yields the following error if the member does not exist (uncommenting the commented lines in the MWE to see this):

Package datatool Error: There is no element XYZ in database duplicates

Perhaps I should not be using the datatool package for this and use an entirely different approach?

## Update:

The code below has been updated to do an additional test via a \foreach loop (with and without a macro defined list). This also necessitated the addition of \dtlexpandnewvalue in the \IfIsInDB macro.

## Code:

\documentclass{article}
\usepackage[paperheight=15in]{geometry}% so output fits on "one" page
\usepackage{datatool}

\newcommand*{\DBKey}{Value}%

\newcommand*{\InitalizeDuplicatesDB}[1]{%
% #1 = name to be used for this duplicate's DB
\DTLifdbexists{#1}%
{\DTLcleardb{#1}}% DB exists, so just clear it
{\DTLnewdb{#1}}%   DB does not exist, so create it
}%
% #1 = name of DB
% #2 = member to be added to DB
\DTLnewrow{#1}%
\DTLnewdbentry{#1}{\DBKey}{#2}%
}%
\newcommand*{\IfIsInDB}[4]{%
% #1 = name to be used for this duplicate's DB
% #2 = member to check if in DB (gets added if not)
% #3 = code to execute if member is in DB
% #4 = code to execute if member is not in DB
%
%
\DTLgetlocation{\RowIndex}{\ColIndex}{#1}{#2}%
\ifnum\RowIndex>0\relax%
#3%
\else%
#4%
\fi%
}%

\begin{document}
\InitalizeDuplicatesDB{duplicates}

Current db:\par
\DTLdisplaydb{duplicates}

\medskip
\IfIsInDB{duplicates}{ABC}{ABC is a duplicate}{ABC added DB}\par
\IfIsInDB{duplicates}{DEF}{DEF is a duplicate}{DEF added DB}\par
%\IfIsInDB{duplicates}{XYZ}{XYZ is a duplicate}{XYZ added DB}\par

\medskip
Current db (before foreach - no macro):\par
\DTLdisplaydb{duplicates}

%% Following tests added in update.
%\medskip\par\noindent
%Testing with a non-macro defined list (ABC, DEF should not be added", and MMM should only be added" once):\par
%\foreach \x in {ABC, DEF, ABC, MMM, MMM, JHM}{%
%}%
%
%Current db (after foreach - no macro):\par
%\DTLdisplaydb{duplicates}
%
%\medskip\par\noindent
%Testing with a macro defined list: (ABC, DEF should not be "added", and NNN should only be added" once)\par
%\newcommand*{\ListMembers}{ABC, XXX, DEF, NNN, NNN, YYY}%
%\foreach \x in \ListMembers {%
%}
%
%Current db (after foreach - macro defn):\par
%\DTLdisplaydb{duplicates}
\end{document}

-

The \foreach loops execute their code in a group for each step, so they are useless when something must be changed "globally", as is the case for your databases.

Here's a way using the patch proposed by Werner; but I used a different command to patch, in order not to change the functionality of \DTLgetlocation:

\documentclass{article}
\usepackage[paperheight=15in]{geometry}% so output fits on "one" page
\usepackage{datatool}
\usepackage{xparse}

\ExplSyntaxOn
\NewDocumentCommand{\foreachin}{ m +m } % long arguments
{
\clist_map_inline:nn { #1 } { #2 }
}
\ExplSyntaxOff

%%% Werner's patch
\usepackage{etoolbox}
\makeatletter
% Patch \DTLgetlocation to gobble the error
\let\ErrorFreeDTLgetlocation\DTLgetlocation
\patchcmd{\ErrorFreeDTLgetlocation}% <cmd>
{\PackageError}% <search>
{\@gobbletwo}% <replace>
{}{}% <success><failure>
\def\@@dtlnovalue{\@dtlnovalue}
%%%

\newcommand*{\DBKey}{Value}

\newcommand*{\InitalizeDuplicatesDB}[1]{%
% #1 = name to be used for this duplicate's DB
\DTLifdbexists{#1}%
{\DTLcleardb{#1}}% DB exists, so just clear it
{\DTLnewdb{#1}}%   DB does not exist, so create it
}
% #1 = name of DB
% #2 = member to be added to DB
\DTLnewrow{#1}%
\DTLnewdbentry{#1}{\DBKey}{#2}%
}
\newcommand*{\IfIsInDB}[4]{% %New definition!
% #1 = name to be used for this duplicate's DB
% #2 = member to check if in DB (gets added if not)
% #3 = code to execute if member is in DB
% #4 = code to execute if member is not in DB
\ErrorFreeDTLgetlocation{\RowIndex}{\ColIndex}{#1}{#2}%
\ifx\RowIndex\@@dtlnovalue
#4%
\else
#3
\fi
}
\makeatother

\begin{document}

\InitalizeDuplicatesDB{duplicates}

Current db:\par
\DTLdisplaydb{duplicates}

\medskip
\IfIsInDB{duplicates}{ABC}{ABC is a duplicate}{ABC added DB}\par
\IfIsInDB{duplicates}{DEF}{DEF is a duplicate}{DEF added DB}\par
\IfIsInDB{duplicates}{XYZ}{XYZ is a duplicate}{XYZ added DB}\par

\medskip
Current db (before foreach - no macro):\par
\DTLdisplaydb{duplicates}

% Following tests added in update.
\medskip\par\noindent
Testing with a non-macro defined list (ABC, DEF should not be added", and MMM should only be added" once):\par
\foreachin {ABC, DEF, ABC, MMM, MMM, JHM}{%
}

Current db (after foreach - no macro):\par
\DTLdisplaydb{duplicates}

\end{document}


As you see, the syntax of \foreachin is very similar to \foreach\x in; instead of \x you use #1 in the code part.

## A variant

If you want to be able to pass the list to \foreachin as a macro, say

\newcommand{\mylist}{ABC, DEF, ABC, MMM, MMM, JHM}



then change the definition of \foreachin:

\ExplSyntaxOn
\NewDocumentCommand{\foreachin}{ m +m } % long arguments
{
\clist_map_inline:on { #1 } { #2 }
}
\cs_generate_variant:Nn \clist_map_inline:nn {o}
\ExplSyntaxOff


# A completely different approach

Instead of datatool you might use the property list data type of expl3.

\documentclass{article}
\usepackage{xparse}
\ExplSyntaxOn
\NewDocumentCommand{\InitializeDuplicatesDB}{m}
{
\prop_gclear_new:c { g_grill_#1_prop }
}
% #1 = name of DB
% #2 = member to be added to DB
{
\prop_gput:cnn { g_grill_#1_prop } { #2 } { Value }
}
\NewDocumentCommand{\IfIsInDB}{mmmm}
% #1 = name to be used for this duplicate's DB
% #2 = member to check if in DB (gets added if not)
% #3 = code to execute if member is in DB
% #4 = code to execute if member is not in DB
{
\prop_get:cnNTF { g_grill_#1_prop } { #2 } \l_tmpa_tl
{ #3 }
}
\NewDocumentCommand{\DTLdisplaydb}{m}
{
\prop_map_inline:cn { g_grill_#1_prop } { ##1\par }
}
\ExplSyntaxOff

\begin{document}
\InitializeDuplicatesDB{duplicates}

Current db:\par
\DTLdisplaydb{duplicates}

\medskip
\IfIsInDB{duplicates}{ABC}{ABC is a duplicate}{ABC added DB}\par
\IfIsInDB{duplicates}{DEF}{DEF is a duplicate}{DEF added DB}\par
\IfIsInDB{duplicates}{XYZ}{XYZ is a duplicate}{XYZ added DB}\par

\medskip
Current db (before foreach - no macro):\par
\DTLdisplaydb{duplicates}
\end{document}


The for each cycle can then be defined just as before. Of course, in a real application, you'll add an argument to \AddMemberToDB (to replace the common Value given to each key).

# And now for something completely different

If what you really need is to maintain a comma separated list, adding one element at a time unless it's already present, you can do with the clist data type of expl3.

\documentclass{article}
\usepackage{xparse}
\ExplSyntaxOn
\NewDocumentCommand{\NewList}{ m }
{
\clist_new:c { g_pg_#1_clist }
}
{
\clist_if_in:cnTF { g_pg_#1_clist } { #2 }
{ #2~is~a~duplicate~in~\texttt{#1}\par }
{
\clist_gput_right:cn { g_pg_#1_clist } { #2 }
}
}
\NewDocumentCommand{\ShowList}{ m }
{
List~\texttt{#1}~contains\par
\clist_map_inline:cn { g_pg_#1_clist } { ##1\par }
}

\NewDocumentCommand{\foreachin}{ m m } % long arguments
{
\clist_map_inline:Vn #1 { #2 }
}
\cs_generate_variant:Nn \clist_map_inline:nn {V}
\ExplSyntaxOff

\setlength{\parindent}{0pt}

\begin{document}

\NewList{duplicates} % allocate a new list

\ShowList{duplicates}

\ShowList{duplicates}

\newcommand{\mylist}{ABC, DEF, ABC, MMM, MMM, JHM}

\ShowList{duplicates}

\end{document}


The output will be

If you don't care about doing something if the added entries are duplicate and want only to have uniqueness, you can use a simpler strategy:

\documentclass{article}
\usepackage{xparse}
\ExplSyntaxOn
\NewDocumentCommand{\NewList}{ m }
{
\clist_new:c { g_pg_#1_clist }
}
{
\IfBooleanTF{#1}
{
\clist_gput_right:cV { g_pg_#2_clist } #3
}
{
\clist_gput_right:cn { g_pg_#2_clist } { #3 }
}
\clist_gremove_duplicates:c { g_pg_#2_clist }
}
\NewDocumentCommand{\ShowList}{ m }
{
List~\texttt{#1}~contains\par
\clist_map_inline:cn { g_pg_#1_clist } { ##1\par }
}
\begin{document}
\NewList{duplicates}

\ShowList{duplicates}

\ShowList{duplicates}

\newcommand{\mylist}{ABC, DEF, ABC, MMM, MMM, JHM}

\ShowList{duplicates}
\end{document}


The output will be

To be on the safe side, the macro \AddToList has a *-variant for giving a macro as the second argument.

-
I know that the \foreach groups things, but when I saw that the data was being added to the db and was still in the db after the \foreach I was not able to make sense of that. –  Peter Grill Apr 29 '12 at 1:34
Also, not able to figure out how to make the \foreachin work with a macro defined list. –  Peter Grill Apr 29 '12 at 1:36
@PeterGrill The data was added before the \foreach, I believe. For the list passed as a macro, I've added a variant. –  egreg Apr 29 '12 at 8:33

Not sure about the datatool usage, but if you just want to process a comma separated list I think that package is a lot more powerful than you need. The following plain TeX produces

\$ tex ll5
This is TeX, Version 3.1415926 (Web2C 2010)
(./ll5.tex
[ABC, DEF, ABC, MMM, MMM, JHM] -> [ABC,DEF,MMM,JHM]
[ABC] -> [ABC]
[] -> []
[xx, yyy, xx, yyy, sss] -> [xx,yyy,sss]
[xx] -> [xx]
[] -> []
)
No pages of output.
Transcript written on ll5.log.


Showing the duplicates being removed, and some edge cases tested.

\def\removedups#1#2{%take list in #1 remove duplicates into #2
\begingroup
\def\temp##1{}%
\expandafter\xrem#1,\relax
\expandafter\endgroup
\expandafter\def\expandafter#2\expandafter{\temp}}

\def\xrem#1,#2{%
\expandafter\ifx\csname!!#1\endcsname\xrem\else
\expandafter\let\csname!!#1\endcsname\xrem
\edef\temp{\temp,#1}%
\fi
\ifx\relax#2\else
\expandafter\xrem\expandafter#2%
\fi}

\def\llll{ABC, DEF, ABC, MMM, MMM, JHM}
\removedups\llll\aA
\immediate\write20{[\llll] -> [\aA]}

\def\llll{ABC}
\removedups\llll\aB
\immediate\write20{[\llll] -> [\aB]}

\def\llll{}
\removedups\llll\aC
\immediate\write20{[\llll] -> [\aC]}

\removedups{xx, yyy, xx, yyy, sss}\bA
\immediate\write20{[xx, yyy, xx, yyy, sss] -> [\bA]}

\removedups{xx}\bB
\immediate\write20{[xx] -> [\bB]}

\removedups{}\bC
\immediate\write20{[] -> [\bC]}

\bye

-

DTLgetlocation ends with

\newcommand*{\DTLgetlocation}[4]{%
% <snip>
\ifx#1\dtlnovalue
\PackageError{datatool}{There is no element #4' in
database #3'}{}%
\fi
}


which produces the error. One way would be to avoid this error by patching \DTLgetlocation, and then performing the equivalence test inside \IfIsInDB:

\documentclass{article}
\usepackage{datatool}% http://ctan.org/pkg/datatool
\usepackage{etoolbox}% http://ctan.org/pkg/etoolbox
\makeatletter
% Patch \DTLgetlocation to gobble the error
\patchcmd{\DTLgetlocation}% <cmd>
{\PackageError}% <search>
{\@gobbletwo}% <replace>
{}{}% <success><failure>
\makeatother
\newcommand*{\DBKey}{Value}%

\newcommand*{\InitalizeDuplicatesDB}[1]{%
% #1 = name to be used for this duplicate's DB
\DTLifdbexists{#1}%
{\DTLcleardb{#1}}% DB exists, so just clear it
{\DTLnewdb{#1}}%   DB does not exist, so create it
}%
% #1 = name of DB
% #2 = member to be added to DB
\DTLnewrow{#1}%
\DTLnewdbentry{#1}{\DBKey}{#2}%
}%
\makeatletter
\newcommand*{\IfIsInDB}[4]{%
% #1 = name to be used for this duplicate's DB
% #2 = member to check if in DB (gets added if not)
% #3 = code to execute if member is in DB
% #4 = code to execute if member is not in DB
\DTLgetlocation{\RowIndex}{\ColIndex}{#1}{#2}%
\expandafter\ifx\RowIndex\@dtlnovalue% No entry/duplicate found...
#4%
\else% ...a duplicate was found
%    \ifnum\RowIndex>0\relax%
#3%
%    \fi
\fi%
}%
\makeatother

\begin{document}
\InitalizeDuplicatesDB{duplicates}

Current db:
\DTLdisplaydb{duplicates}

\medskip

\IfIsInDB{duplicates}{ABC}{ABC is a duplicate}{ABC added DB}\par
\IfIsInDB{duplicates}{DEF}{DEF is a duplicate}{DEF added DB}\par
\IfIsInDB{duplicates}{XYZ}{XYZ is a duplicate}{XYZ added DB}\par

\medskip

Current db:
\DTLdisplaydb{duplicates}
\end{document}


The patch from \PackageError to \@gobbletwo (in \DTLgetlocation) eats the first two arguments. However, the final empty group {} doesn't affect the output. Additionally, \RowIndex is defined as \@dtlnovalue, which we can use in comparison to check for an existing item (or not).

-
Thanks. This worked great for the initial test. I have expanded it further using a \foreach and does not seem to work for that case. I thought it was some scoping related issue, but the db prints out just fine. Note: This necessitated the addition of \dtlexpandnewvalue in \IfIsInDB -- if you forget to add this you will get Undefined control sequence \@dtl@val ->\x error. –  Peter Grill Apr 26 '12 at 17:16
The problem with \foreach is the usual one: each step is executed inside a group, so the changes to the database disappear. Just don't use \foreach for these tasks, but a "group-free" procedure, such as those defined by etoolbox or expl3. –  egreg Apr 28 '12 at 21:29
Instead of \expandafter\ifx\RowIndex\@dtlnovalue it's better to use \ifx\RowIndex\@@dtlnovalue, with \def\@@dtlnovalue{\@dtlnovalue}; the test would fail if \RowIndex expands to a two (or more) digit number. –  egreg Apr 28 '12 at 22:47

Here is brute force solution: Modify \IfIsInDB to use a \DTLforeach to step thru the database and see if that value is found. Not very elegant, but does appear to work.

\documentclass{article}
\usepackage[paperheight=15in]{geometry}% so output fits on "one" page
\usepackage{etoolbox}
\usepackage{xstring}
\usepackage{datatool}

\newcommand*{\DBKey}{Value}%

\newcommand*{\InitalizeDuplicatesDB}[1]{%
% #1 = name to be used for this duplicate's DB
\DTLifdbexists{#1}%
{\DTLcleardb{#1}}% DB exists, so just clear it
{\DTLnewdb{#1}}%   DB does not exist, so create it
}%
% #1 = name of DB
% #2 = Key of column to add to
% #3 = member to be added to DB
\DTLnewrow{#1}%
\DTLnewdbentry{#1}{#2}{#3}%
}%

\newtoggle{foundInDB}%
\newcommand*{\IfIsInDB}[5]{%
% #1 = name to be used for this duplicate's DB
% #2 = Key of column to check
% #3 = member to check if in DB (gets added if not)
% #4 = code to execute if member is in DB
% #5 = code to execute if member is not in DB
%
%
\togglefalse{foundInDB}%
\DTLforeach{#1}{\CurrentMember=#2}{%
\IfEq{#3}{\CurrentMember}{% Found member
\global\toggletrue{foundInDB}%
\dtlbreak% Might as well break out of loop
}{% Haven't found it yet, so keep looking....
}%
}%
%\DTLgetlocation{\RowIndex}{\ColIndex}{#1}{#2}%
\iftoggle{foundInDB}{%
#4%
}{%
#5%
}%
}%

\begin{document}
\InitalizeDuplicatesDB{duplicates}

Current db:\par
\DTLdisplaydb{duplicates}

\medskip
\IfIsInDB{duplicates}{\DBKey}{ABC}{ABC is a duplicate}{ABC added DB}\par
\IfIsInDB{duplicates}{\DBKey}{DEF}{DEF is a duplicate}{DEF added DB}\par
\IfIsInDB{duplicates}{\DBKey}{XYZ}{XYZ is a duplicate}{XYZ added DB}\par

\medskip
Current db (before foreach - no macro):\par
\DTLdisplaydb{duplicates}

% Following tests added in update.
\medskip\par\noindent
Testing with a non-macro defined list (ABC, DEF should not be added", and MMM should only be added" once):\par
\foreach \x in {ABC, DEF, ABC, MMM, MMM, JHM}{%
}%

Current db (after foreach - no macro):\par
\DTLdisplaydb{duplicates}

\medskip\par\noindent
Testing with a macro defined list: (ABC, DEF should not be "added", and NNN should only be added" once)\par
\newcommand*{\ListMembers}{ABC, XXX, DEF, NNN, NNN, YYY}%
\foreach \x in \ListMembers {%