Query to show Field and Form rules of each template

Query to show Field and Form rules of each template

MSSQL:

SELECT rtf.templatename "Template Name",
 STUFF((SELECT ', '+ ad.org_name + char(10)   FROM  Accountdefinition ad LEFT JOIN Ruleaccountmapping ramp ON ramp.accountid=ad.org_id where rd.ruleid=ramp.ruleid FOR XML PATH ('')), 1, 1, '') 'Rule association' ,
rd.rulename "Rule Name",
rd.usertype "Applies To",
rd.formtype "Rule Execution",
ry.ruletype "Event",
rd.jscode "Script",
rff.fieldname "Condition (Criteria)",
rcm.condition_string "Condition (logic)",
rat.actionname "Action name",
rffa.fieldname "Action field name"
 FROM ruledata rd
LEFT JOIN RuleEventTypes ry ON ry.ruletypeid=rd.ruletypeid
INNER JOIN requesttemplate_list rtf ON rtf.templateid=rd.templateid
LEFT JOIN Rulecriteriamap rcm ON rd.ruleid=rcm.ruleid
LEFT JOIN RuleFormField rff ON rcm.fieldid=rff.fieldid
LEFT JOIN Rulecriteriafieldvalues rcfv ON rcm.rulecriteriaid=rcfv.rulecriteriaid
LEFT JOIN Ruleactionsmap ram ON rd.ruleid=ram.ruleid 
LEFT JOIN Ruleactionfieldsmap rafm ON ram.ruleactionid=rafm.ruleactionid
LEFT JOIN Ruleactiontypes rat ON ram.ruleactiontypeid=rat.ruleactiontypeid 
LEFT JOIN Ruleformfield rffa ON rafm.fieldid=rffa.fieldid ORDER by 1‚Äč


NOTE: Please note, the below 2 highlighted field values cannot be shown in the query because it will show only the respective IDs of it (Example: Open status shows as 1 and not as OPEN). Other then these 2 fields, all other fields are included.



          • Related Articles

          • Query to show active Field and form rules in a service template.

            Please go to Reports-New Query Report and execute the query. SELECT rtf.templatename "Template Name", rd.rulename "Rule Name", ry.ruletype "Event", rd.usertype "Applies To", rd.jscode "Script", rat.actionname "Action Name", RuleFormField.fieldname ...
          • FAFR - How to mandate field when SLA is overdue

            1. Under Admin >> Incident Template >> Edit the template in question 2. Choose field and form rules and apply the below script On Form Submit var duebyDate = $CS.getValue("DUEBYDATE"); var currentDate = new Date(); if(currentDate>duebyDate) { ...
          • Disable Default Template - Using FAFR

            1. Navigate to Admin>Incident Template>> Edit Default Template  2. Select Field & Form Rules Tab 3. Select On form Load  4. Make sure to select "Applies to Technicians" and set the action as Execute Script and paste the below script  ...
          • Query to show last updated field.

            Kindly go to Reports-New Query Report and execute the below query.  PGSQL: SELECT wo.WORKORDERID AS "Request ID",  max(wo.TITLE) AS "Subject",  max(ti.FIRST_NAME) AS "Assigned Technician",  max(aau.FIRST_NAME) AS "Requester",  ...
          • Hide templates from drop-down while using a specific template using FAFR

            The below script can be used in the respective incident template under Form Load section of Field and Form Rules. if(["Application Crashes Frequently","New incident"].indexOf(jQuery("select#template_box").select2("data").text)==-1){ ...