You had a CAML working great on one group of lists. You created a list template for one of the lists in the group and used it to create another set of similar lists. You then created a rollup for the new group and applied the same CAML filter. No data was returned. What happened?
Shown below is one of the “new” lists that you want to rollup:
Specifically, you want to rollup all the items assigned to the logged in user that are older than 30 days (in other words, where the status is “Open” and the Date Received is Less than or Equal to Today minus 30 days).
The latest version of List Rollup uses the Internal Column Name in the CAML query. If you use the Display Name of a column, you may not get the results that you expect because if a column name has been changed, the Internal Name is different than the Display Name.
In the example, the new lists created from the list templates had a few columns renamed. The original CAML for the List Rollup filter was changed to reflect the new column names. The revised CAML looked like this:
<Where> <And><And> <Eq><FieldRef Name="AssignedTo" /><Value Type="Integer"><UserID /></Value></Eq> <Leq><FieldRef Name="DateReceived" /> <Value Type="DateTime"><Today /><addValue value="-30" /></Value> </Leq> </And> <Eq><FieldRef Name="Status" /><Value Type="Choice">Open</Value></Eq> </And> </Where>
No results were returned when using this filter because the DateReceived column was renamed and the Display Name did not match the Internal Name. The Internal (or original) Name of the column is StartDate.
IMPORTANT: A column’s Display Name and it’s Internal Name will be the SAME unless the column was renamed. You should not run into this issue unless you rename a column.
Changing the CAML to that shown below fixes the issue. Note the changed FieldRef Name in the fourth line.
<Where> <And><And> <Eq><FieldRef Name="AssignedTo" /><Value Type="Integer"><UserID /></Value></Eq> <Leq><FieldRef Name="StartDate" /> <Value Type="DateTime"><Today /><addValue value="-30" /></Value> </Leq> </And> <Eq><FieldRef Name="Status" /><Value Type="Choice">Open</Value></Eq> </And> </Where>
Tip: To check the value of the Internal Name of a column, follow the steps in the table below:
|1.||Go to the List Settings for the list in question.|
|2.||In the Columns section of the page, click the name of the column in question.|
|3.||When the Change Column page appears, look at the URL in the browser address bar. The parameter called Field,shown at the end of the URL, is the Internal Name for the column.