+ Start a Discussion
nksfnksf 

How can I show Field Dependency matrix in Excel

Hi I am looking for a easy way to print or export a large Field Dependency matrix in excel. I am new to saleforce please guide me with complete details.
Best Answer chosen by nksf
Sonam_SFDCSonam_SFDC
Unfortunately field dependencies are not records that we can manipulate via Data Loader or Import/Export with the Wizard that Salesforce.com has available.
 Field dependencies are part of the Metadata which cannot be manipulated via standard functionality.

I would like to suggest you to take a look to the following options:

First the link to the PDF File that contains all the necessary information to be able to handle the Metadata API that Salesforce has available:
http://www.salesforce.com/us/developer/docs/api_meta/api_meta.pdf


Also, there are other 3rd party application which allows you to export the data so it may help you to achieve what you need. An example of this application: http://appexchange.salesforce.com/listingDetail?listingId=a0N300000018leZEAQ

A last option might be to look at the Force.com Migration tool to export the Metadata, manipulate the appropriate Metadata XML files by hand and then migrate over back to your Production instance.
http://www.salesforce.com/us/developer/docs/daas/salesforce_migration_guide.pdf

All Answers

Sonam_SFDCSonam_SFDC
When you say field dependency matrix - do you mean the one you create when you set up controlling and depending custom fields in Salesforce.

Or are you trying to print a Matrix report that you have created  in Salesforce.

Please elaborate on your requirement so we can suggest accordingly.

nksfnksf
Hi Sonam. I am talking about controlling and depending custom fields. I have 3 picklist fields. Category 1, Category 2, Category 3 and have created field dependency. But there are so many values in all these 3 categories which are dependent to eachother. It will take forever to manually find that which category value is dependent to which category value. Is there any easy way to find the solution and show it in excel sheet?
Sonam_SFDCSonam_SFDC
Unfortunately field dependencies are not records that we can manipulate via Data Loader or Import/Export with the Wizard that Salesforce.com has available.
 Field dependencies are part of the Metadata which cannot be manipulated via standard functionality.

I would like to suggest you to take a look to the following options:

First the link to the PDF File that contains all the necessary information to be able to handle the Metadata API that Salesforce has available:
http://www.salesforce.com/us/developer/docs/api_meta/api_meta.pdf


Also, there are other 3rd party application which allows you to export the data so it may help you to achieve what you need. An example of this application: http://appexchange.salesforce.com/listingDetail?listingId=a0N300000018leZEAQ

A last option might be to look at the Force.com Migration tool to export the Metadata, manipulate the appropriate Metadata XML files by hand and then migrate over back to your Production instance.
http://www.salesforce.com/us/developer/docs/daas/salesforce_migration_guide.pdf
This was selected as the best answer
Kenny JacobsonKenny Jacobson
I realize I'm a little late to this, but after trying other solutions that were not really working for me, I came up with this approach.  I created some javascript code in to be used in a javascript "bookmarklet".  The idea is, you go to the "Edit Field Dependency" screen in SFDC and click on the bookmarklet and it screenscrapes the page, formatting the data into a flatten csv which you can then paste into notepad and open with Excel.

Here is the javascript that you would save to  your own webserver:
var firstColumn = 0;
        var lastColumn = 0;
        main();

        function main() {

            var curUrl = document.URL;

            if ((curUrl.indexOf('salesforce.com') != -1) || (curUrl.indexOf('localhost:') != -1)) {
                setColumnNumbers();
                var columnsName = getColumnNames();
                var FieldsWithDependencies = getFieldsWithDependencies(columnsName);
                var csvData = getCsv(FieldsWithDependencies);
                openPopup(csvData);
            }
            else {
                alert("Sorry, I don't recognize this page or its format :(");
                return;
            }
        }

        function setColumnNumbers() {
            var navElements = document.getElementsByClassName('navigationHeaderNormal');
            if (navElements[0] === undefined) {
                navElements = document.getElementsByClassName('navigationHeaderAll');
            }
            var navText = navElements[0].innerHTML;
            var startIndex = navText.indexOf('Showing Columns:') + 'Showing Columns:'.length;
            var endIndex = navText.indexOf('-', startIndex);
            var firstColumnStr = navText.substring(startIndex, endIndex);
            startIndex = endIndex + 1;
            endIndex = navText.indexOf('(of');
            var lastColumnStr = navText.substring(startIndex, endIndex);
            firstColumn = parseInt(firstColumnStr.trim()); // 11;
            lastColumn = parseInt(lastColumnStr.trim());;
            firstColumn = firstColumn - 1;
            lastColumn = lastColumn - 1;
        }

        function getCsv(fieldsWithDependencies) {
            var rtnCsv = '';
            var sepChar = ',';
            var crChar = '\r\n';
            for (i = 0; i < fieldsWithDependencies.length; i++) {
                var fieldName = fieldsWithDependencies[i][0];
                var dependencies = fieldsWithDependencies[i][1];
                for (j = 0; j < dependencies.length; j++) {
                    rtnCsv += '"' + fieldName + '"' + sepChar + '"' + dependencies[j] + '"' + crChar;
                }
            }
            return rtnCsv;
        }

        function getFieldsWithDependencies(columnsName) {
            var FieldsWithDependencies = [];
            for (i = 0; i < columnsName.length; i++) {
                var fieldValues = getEnabledRowsForColumn(i + firstColumn);
                var row = [columnsName[i], fieldValues];
                FieldsWithDependencies.push(row);
            }
            return FieldsWithDependencies;
        }

        function getColumnNames() {
            var colCount = firstColumn; // 0;
            var columnNameBase = 'th_r0c';
            var columnNames = [];
            while (colCount <= lastColumn) {
                var cellElement = document.getElementById(columnNameBase + colCount.toString());
                if (cellElement != null) {
                    var cellElementStrippedString = cellElement.innerHTML.replace('&nbsp;', '').replace('&nbsp;', '');
                    if (cellElementStrippedString.length > 0) {
                        columnNames.push(cellElementStrippedString);
                        colCount++;
                    }
                } 
            }
            return columnNames;
        }

        function getEnabledRowsForColumn(colNum) {
            var rowCount = 0;
            var fieldValues = [];
            var cellNameBase = 'te_r{0}c' + colNum;

            while (rowCount < 1000) {
                var cellId = cellNameBase.replace('{0}', rowCount.toString());
                var cellElement = document.getElementById(cellId);
                if (cellElement != null) {
                    if (cellElement.className == 'shownPickValue') {
                        var cellElementStrippedString = cellElement.innerHTML.replace('&nbsp;', '').replace('&nbsp;', '');
                        if (cellElementStrippedString.length > 0) {
                            fieldValues.push(cellElementStrippedString);
                        }
                    }
                    rowCount++;
                } else {
                    rowCount = 1001;
                }
            }
            return fieldValues;
        }

        function openPopup(csvData) {
            var popBox = document.createElement("div");
            popBox.style.backgroundColor = "#81da7a";
            popBox.style.position = "absolute";
            popBox.style.width = "700px";
            popBox.style.top = "50px";
            popBox.style.left = "200px";
            popBox.style.border = "solid 3px white";
            popBox.style.padding = "20px";
            popBox.id = "TTPopupBox";

            var closebutton = document.createElement("div");
            closebutton.id = "ttCloseButton";
            closebutton.style.backgroundColor = "white";
            closebutton.style.width = "70px";
            closebutton.onclick = removePopupBox;
            closebutton.appendChild(document.createTextNode("CLOSE"));
            popBox.appendChild(closebutton);

            var csvTextArea = document.createElement("textarea");
            csvTextArea.style.width = "500px";
            csvTextArea.style.height = "500px";
            csvTextArea.appendChild(document.createTextNode(csvData));
            popBox.appendChild(csvTextArea);

            document.body.appendChild(popBox);
        }


        function removePopupBox() {
            var popBox = document.getElementById("TTPopupBox");
            popBox.parentNode.removeChild(popBox);
        }

Here is the bookmarklet code you would use to call your hosted code:
 
javascript:void((function(){var e=document.createElement('script');e.setAttribute('type','text/javascript');e.setAttribute('charset','UTF-8');e.setAttribute('src','https://pathtoyourjscode.js?r='+Math.random()*99999999);document.body.appendChild(e)})());

Steps to use:

1. Create a bookmark in your browser with the following info (this example uses my hosted code, which you welcome to use):

Name: SFDC - Field Dep Scrape
URL: javascript:void((function(){var e=document.createElement('script');e.setAttribute('type','text/javascript');e.setAttribute('charset','UTF-8');e.setAttribute('src','https://s3.amazonaws.com/datawin-wwwroot/SFDC/fieldDepScrape.js?r='+Math.random()*99999999);document.body.appendChild(e)})());

2. Navigate to the "Edit Field Dependency" in SFDC (click on Edit in the Field Dependencies area of the picklist properties).

3. Click the bookmark you created in Step 1.

4. A Popup screen will display the flattened data with column 1 contains the master fields and column 2 the dependent fields.

This will work whether you are viewing all columns or sets of 5.

Hope this helps!

Kenny
manjunath.tjmanjunath.tj
Thanks Kenny. It was really helpful!
mbaker22mbaker22
Nice job man.  Looks like you can run it in the console as well...$$
kory108kory108
Kenny this worked great! Thank you for the post and for hosting the code!
Manjari SinghManjari Singh
This was great.. Thanks Kenny.
 
John LatimerJohn Latimer
Thanks Kenny!  Works great!
Kamyar JannatiKamyar Jannati
I know that I am being silly, but does anybody have screen grab of how to implement Kenny's workaround?

 
Bob HatcherBob Hatcher
Because Kenny was nice enough to leave his code on his AWS URL, you can invoke this by simply opening the field dependency page and, in Chrome,
  • Hamburger menu -> More Tools -> Developer Tools
  • Console tab
  • Paste the code exactly as he wrote it in his instructions under "Steps to Use" above
  • Hit enter
Matthew SoutherMatthew Souther
So rad! Thanks Kenny and Bob!
Gianluca TomatGianluca Tomat
Any way to use this same approache to also insert the values?
Doug DunfeeDoug Dunfee
Thanks to Kenny and Bob for this!  I see multple unaddressed Ideas for this going back almost a decade with no action by Salesforce.  Unfortunately this JS script is still only a partial solution to my current challenge as I have 210 columns and cannot use the View All button...
Zac  OteroZac Otero
A total life and keystroke saver! I definitely owe you a beer! 
Mko305Mko305
Thanks Kenny! Thanks Bob!
Graham VoddenGraham Vodden
I am getting the following error when I try Bob's approach:

Uncaught TypeError: Cannot read property 'innerHTML' of undefined
    at setColumnNumbers (fieldDepScrape.js?r=49219182.26176515:27)
    at main (fieldDepScrape.js?r=49219182.26176515:10)
    at fieldDepScrape.js?r=49219182.26176515:3

Any ideas how to get past this?  
Bob HatcherBob Hatcher
@Graham - you need to be on the field dependency page - the one that looks like this. http://www.salesforcetutorial.com/wp-content/uploads/2015/01/Field-Dependency2.png
Sujesh Theanchery Veettil 3Sujesh Theanchery Veettil 3
Awesome work kenny!!! Much helpful. Thank you
Andrée-Anne CouillardAndrée-Anne Couillard
Thank you Sir Keeny!
Aaron Webster 15Aaron Webster 15
I have some Field Dependencies that span multiple pages. I have updated the code so that it can support multiple pages. If you have any questions about how to implement this see Kenny Jacobson's post above.

Here is the updated Java Script: This script assumes that Salesforce is showing 5 columns per page.
 
var firstColumn = 0;

	var lastColumn = 0;

	main();



	function main() {


		var columns=prompt("How many Columns are there?",10);
	   var curUrl = document.URL;
	   var csvAll = "";



		if ((curUrl.indexOf('salesforce.com') != -1) || (curUrl.indexOf('localhost:') != -1)) {

			for (iiii = 0; iiii < Math.ceil(columns/5); iiii++) { 
			//1
			if(iiii!=0){
				picklistSetup.nextChunk();
			}
			setColumnNumbers();
			
			var columnsName = getColumnNames();

			var FieldsWithDependencies = getFieldsWithDependencies(columnsName);

			var csvData = getCsv(FieldsWithDependencies);
			csvAll+=csvData;
			}
			

			openPopup(csvAll);

		}

		else {

			alert("Sorry, I don't recognize this page or its format :(");

			return;

		}

	}



	function setColumnNumbers() {

		var navElements = document.getElementsByClassName('navigationHeaderNormal');

		if (navElements[0] === undefined) {

			navElements = document.getElementsByClassName('navigationHeaderAll');

		}

		var navText = navElements[0].innerHTML;

		var startIndex = navText.indexOf('Showing Columns:') + 'Showing Columns:'.length;

		var endIndex = navText.indexOf('-', startIndex);

		var firstColumnStr = navText.substring(startIndex, endIndex);

		startIndex = endIndex + 1;

		endIndex = navText.indexOf('(of');

		var lastColumnStr = navText.substring(startIndex, endIndex);

		firstColumn = parseInt(firstColumnStr.trim()); // 11;

		lastColumn = parseInt(lastColumnStr.trim());;

		firstColumn = firstColumn - 1;

		lastColumn = lastColumn - 1;

	}



	function getCsv(fieldsWithDependencies) {

		var rtnCsv = '';

		var sepChar = ',';

		var crChar = '\r\n';

		for (i = 0; i < fieldsWithDependencies.length; i++) {

			var fieldName = fieldsWithDependencies[i][0];

			var dependencies = fieldsWithDependencies[i][1];

			for (j = 0; j < dependencies.length; j++) {

				rtnCsv += '"' + fieldName + '"' + sepChar + '"' + dependencies[j] + '"' + crChar;

			}

		}

		return rtnCsv;

	}



	function getFieldsWithDependencies(columnsName) {

		var FieldsWithDependencies = [];

		for (i = 0; i < columnsName.length; i++) {

			var fieldValues = getEnabledRowsForColumn(i + firstColumn);

			var row = [columnsName[i], fieldValues];

			FieldsWithDependencies.push(row);

		}

		return FieldsWithDependencies;

	}



	function getColumnNames() {

		var colCount = firstColumn; // 0;

		var columnNameBase = 'th_r0c';

		var columnNames = [];

		while (colCount <= lastColumn) {

			var cellElement = document.getElementById(columnNameBase + colCount.toString());

			if (cellElement != null) {

				var cellElementStrippedString = cellElement.innerHTML.replace('&nbsp;', '').replace('&nbsp;', '');

				if (cellElementStrippedString.length > 0) {

					columnNames.push(cellElementStrippedString);

					colCount++;

				}

			}

		}

		return columnNames;

	}



	function getEnabledRowsForColumn(colNum) {

		var rowCount = 0;

		var fieldValues = [];

		var cellNameBase = 'te_r{0}c' + colNum;



		while (rowCount < 1000) {

			var cellId = cellNameBase.replace('{0}', rowCount.toString());

			var cellElement = document.getElementById(cellId);

			if (cellElement != null) {

				if (cellElement.className == 'shownPickValue') {

					var cellElementStrippedString = cellElement.innerHTML.replace('&nbsp;', '').replace('&nbsp;', '');

					if (cellElementStrippedString.length > 0) {

						fieldValues.push(cellElementStrippedString);

					}

				}

				rowCount++;

			} else {

				rowCount = 1001;

			}

		}

		return fieldValues;

	}

	function openPopup(csvData) {

		var popBox = document.createElement("div");

		popBox.style.backgroundColor = "#81da7a";

		popBox.style.position = "absolute";

		popBox.style.width = "700px";

		popBox.style.top = "50px";

		popBox.style.left = "200px";

		popBox.style.border = "solid 3px white";

		popBox.style.padding = "20px";

		popBox.id = "TTPopupBox";



		var closebutton = document.createElement("div");

		closebutton.id = "ttCloseButton";

		closebutton.style.backgroundColor = "white";

		closebutton.style.width = "70px";

		closebutton.onclick = removePopupBox;

		closebutton.appendChild(document.createTextNode("CLOSE"));

		popBox.appendChild(closebutton);



		var csvTextArea = document.createElement("textarea");

		csvTextArea.style.width = "500px";

		csvTextArea.style.height = "500px";

		csvTextArea.appendChild(document.createTextNode(csvData));

		popBox.appendChild(csvTextArea);



		document.body.appendChild(popBox);

	}





	function removePopupBox() {

		var popBox = document.getElementById("TTPopupBox");

		popBox.parentNode.removeChild(popBox);

	}

 
Anirban Saha 8Anirban Saha 8
If I want to get a large number of data more than rowcount 1000 then how can I get that
Aaron Webster 15Aaron Webster 15
Anirban Saha 8 - you should just be able to adjust line180 and 204. set 180 to be your row limit and set 204 to be your row limit + 1. for example if your new limit was 2000 it would look something like this 
while (rowCount < 2000) {
 
rowCount = 2001;



My above script is still set to only hadle 1000 rows but if anyone wants to use it they can set a bookmark on their browser and point it to this:
javascript:void((function(){var e=document.createElement('script');e.setAttribute('type','text/javascript');e.setAttribute('charset','UTF-8');e.setAttribute('src','https://rwcrmconsulting.com/FieldDependancies.js');document.body.appendChild(e)})());

Again this is a little different than the original script it is going to ask for the number of rows which can be found on the 
Number of rows
Mitch Ebin 10Mitch Ebin 10
This needs to be done in Salesforce Classic BTW. I believe the Lightning LockerServices prevent the script from running, but it could be something else. 

Regardless, it works in classic.