Difference between revisions of "Searching for Custom Field Settings Using a Report"

Revision as of 03:29, 8 April 2014

You can import the following SmartSimple report to search for custom fields in your system with certain values in the Options/Template/Default Value section of the custom field settings.

When the report is imported, it will be named "Custom Field - Search Contents" and will be located in the "Custom Fields" report category.

When run, the report will prompt you for a text string to be found in the Options/Template/Default Value section of all custom fields in your system and will allow you to view the field settings.

<reportexport>
 <cfmapping></cfmapping>
 <typemapping></typemapping>
 <customfieldlist></customfieldlist>
 <excel_start></excel_start>
 <excel_file>0</excel_file>
 <excel_exformat>1</excel_exformat>
 <excel_mstart></excel_mstart>
 <word_tablebm></word_tablebm>
 <donotrebuild>0</donotrebuild>
 <mergesubrpt>0</mergesubrpt>
 <objectids>9000,</objectids>
 <primaryobjectid>9000</primaryobjectid>
 <fieldlist>,0,844,845,846,842</fieldlist>
 <custabletype></custabletype>
 <totallist>Custom Fields</totallist>
 <totalcustomfield></totalcustomfield>
 <reportfilter></reportfilter>
 <header></header>
 <footer></footer>
 <description>Searches the contents (Options, default values, WPV content etc) for the text entered</description>
 <compiledsql>SELECT customfieldid as objpkey,0 as `~Index~`,customfieldid as 1_customfieldid,name as 2_name,caption as 3_caption,if(allowempty=1,"Yes","No") as 5_allowempty,CONCAT("<a href='/s_editcustomfield.jsp?nextlevel=1&entity=",entity,"&appid=",CASE WHEN entity in (24,23,26,18,31,28) THEN 102884 WHEN entity=78 THEN itemid ELSE "" END,"&field=",customfieldid,"&itemid=",itemid,"&title=Edit' target='_blank'>View Field</a>") as 6_customfieldid FROM SS_System.tblcustomfields
 WHERE (rootcompanyid=@rootcompanyid@) aNd (1=1) AND ((options like concat("%",*10_0_850*,"%"))) Group By customfieldid Order By 1_customfieldid ASC </compiledsql>
 <promptmsg>-Enter search term</promptmsg>
 <compiledheader>"#","Custom Field ID","Field Name","Field Caption","Allow Empty","Edit"</compiledheader>
 <htmlheader><tr><th class=Sub></th><th class=Sub><a class=SubHead href="javascript:orderreport('1_customfieldid')">Custom Field ID</a></th><th class=Sub><a class=SubHead href="javascript:orderreport('2_name')">Field Name</a></th><th class=Sub><a class=SubHead href="javascript:orderreport('3_caption')">Field Caption</a></th><th class=Sub><a class=SubHead href="javascript:orderreport('5_allowempty')">Allow Empty</a></th><th class=Sub><a class=SubHead href="javascript:orderreport('6_customfieldid')">Edit</a></th></tr></htmlheader>
 <compiledtag><tr><td class=Data1>~Index~</td><td class=Data1>~1_customfieldid~</td><td class=Data1>~2_name~</td><td class=Data1>~3_caption~</td><td class=Data1>~5_allowempty~</td><td class=Data1>~6_customfieldid~</td></tr></compiledtag>
 <compiledtemplate></compiledtemplate>
 <criteriatemplate></criteriatemplate>
 <recordlimit>0</recordlimit>
 <usenewbuilder>1</usenewbuilder>
 <buildermatrix><reportbuilder>
 <column>
 <columnindex>0</columnindex>
 <columnname>#</columnname>
 <tabletype>9000</tabletype>
 <fieldtype>9000:0:0</fieldtype>
 <fields>0 as `~Index~`::0</fields>
 <columnsql>0 as `~Index~`</columnsql>
 <visibles>1</visibles>
 <totals>0</totals>
 </column>
 <column>
 <columnindex>1</columnindex>
 <columnname>Custom Field ID</columnname>
 <tabletype>9000</tabletype>
 <fieldtype>9000:0:0</fieldtype>
 <fields>customfieldid as customfieldid::844</fields>
 <columnsql>customfieldid as customfieldid</columnsql>
 <groupby>10</groupby>
 <sortby>ASC</sortby>
 <visibles>1</visibles>
 <totals>0</totals>
 </column>
 <column>
 <columnindex>2</columnindex>
 <columnname>Field Name</columnname>
 <tabletype>9000</tabletype>
 <fieldtype>9000:0:0</fieldtype>
 <fields>name as name::845</fields>
 <columnsql>name as name</columnsql>
 <visibles>1</visibles>
 <totals>0</totals>
 </column>
 <column>
 <columnindex>3</columnindex>
 <columnname>Field Caption</columnname>
 <tabletype>9000</tabletype>
 <fieldtype>9000:0:0</fieldtype>
 <fields>caption as caption::846</fields>
 <columnsql>caption as caption</columnsql>
 <visibles>1</visibles>
 <totals>0</totals>
 </column>
 <column>
 <columnindex>4</columnindex>
 <columnname>Field Description</columnname>
 <tabletype>9000</tabletype>
 <fieldtype>9000:0:0</fieldtype>
 <fields>cusdesp as cusdesp::847</fields>
 <columnsql>cusdesp as cusdesp</columnsql>
 <visibles>0</visibles>
 <totals>0</totals>
 </column>
 <column>
 <columnindex>5</columnindex>
 <columnname>Allow Empty</columnname>
 <tabletype>9000</tabletype>
 <fieldtype>9000:0:0</fieldtype>
 <fields>if(allowempty=1,"Yes","No") as allowempty::842</fields>
 <columnsql>if(allowempty=1,"Yes","No") as allowempty</columnsql>
 <visibles>1</visibles>
 <totals>0</totals>
 </column>
 <column>
 <columnindex>6</columnindex>
 <columnname>Edit</columnname>
 <tabletype>9000</tabletype>
 <fieldtype>9000:0:0</fieldtype>
 <fields>customfieldid as customfieldid::844</fields>
 <columnsql>CONCAT("@lt@a href='/s_editcustomfield.jsp?nextlevel=1@at@entity=",entity,"@at@appid=",CASE WHEN entity in (24,23,26,18,31,28) THEN 102884 WHEN entity=78 THEN itemid ELSE "" END,"@at@field=",customfieldid,"@at@itemid=",itemid,"@at@title=Edit' target='_blank'@gt@View Field@lt@/a@gt@") as customfieldid</columnsql>
 <advcus>CONCAT("@lt@a href='/s_editcustomfield.jsp?nextlevel=1@at@entity=",[9],"@at@appid=",CASE WHEN [9] in (24,23,26,18,31,28) THEN 102884 WHEN [9]=78 THEN [8] ELSE "" END,"@at@field=",[2],"@at@itemid=",[8],"@at@title=Edit' target='_blank'@gt@View Field@lt@/a@gt@")</advcus>
 <visibles>1</visibles>
 <totals>0</totals>
 </column>
 <column>
 <columnindex>7</columnindex>
 <columnname>Item ID</columnname>
 <tabletype>9000</tabletype>
 <fieldtype>9000:0:0</fieldtype>
 <fields>itemid as itemid::839</fields>
 <columnsql>itemid as itemid</columnsql>
 <visibles>0</visibles>
 <totals>0</totals>
 </column>
 <column>
 <columnindex>8</columnindex>
 <columnname>Entity</columnname>
 <tabletype>9000</tabletype>
 <fieldtype>9000:0:0</fieldtype>
 <fields>entity as entity::840</fields>
 <columnsql>entity as entity</columnsql>
 <visibles>0</visibles>
 <totals>0</totals>
 </column>
 <column>
 <columnindex>9</columnindex>
 <columnname>Options/Template/Default Value</columnname>
 <tabletype>9000</tabletype>
 <fieldtype>9000:0:0</fieldtype>
 <fields>options as options::850</fields>
 <columnsql>options as options</columnsql>
 <visibles>0</visibles>
 <totals>0</totals>
 <criteria1>like concat("%",???,"%")</criteria1>
 </column>
 <columnmapping>#==~Index~;;Custom Field ID==~1_customfieldid~;;Field Name==~2_name~;;Field Caption==~3_caption~;;==;;Allow Empty==~5_allowempty~;;Edit==~6_customfieldid~;;==;;==;;==;;</columnmapping>
 </reportbuilder></buildermatrix>
 <issingleview>0</issingleview>
 <rinetenabled>0</rinetenabled>
 <singleviewtemplate></singleviewtemplate>
 <compiledsingleview></compiledsingleview>
 <cacheenabled>0</cacheenabled>
 <hideglobaltemplate>0</hideglobaltemplate>
 <cacheinterval>0</cacheinterval>
 <cacheuser>0</cacheuser>
 <straightjoin>0</straightjoin>
 <enabletranslate>0</enabletranslate>
 <noparas>0</noparas>
 <name>Custom Field - Search Contents</name>
 </reportexport>
 

See Also

• Importing and Exporting Reports
• Reporting on Custom Fields