How to extract info about relations in SO 8.5
Hello fellow SO-users.
We are currently using 8.5 on-prem at our company, and have been very accurate with defining relations between companies and suppliers, in our database.
But, I cannot find any way to extract the data. I can only view them via the Customer card in SO.
I would like to extract the information to Excel, like the picture says.
Anybody out there that can help me?
Thanks in advance!
Peter
All Replies (3)
You can use Excel's Remote data ODATA support to get the list of relations.
SuperOffice OData queries docs
In Excel - Get Data from Other sources > From Web
Next you need to put in the URL of the relation table - this is based on where SuperOffice is installed
(site url-except default.aspx)/api/v1/archive/relation?$select=relationName,source/who,target/who&$filter=relationDefinitionId>0
The $select part specifies return fields.
The $filter specifies what relations you want. Here we want all relations.
Ok - with the help of https://theexcelclub.com/how-to-parse-custom-json-data-using-excel/ I figured out how to make Excel consume the results.
Ok - first we need to formulate the URL to the data we want.
Assuming your CRM.web is normally on https://crm.yourserver.com/so/ then the URL we want is
https://crm.yourserver.com/so/api/v1/archive/relation?$select=relationName,source/who,target/who&$filter=relationDefinitionId>0&$format=jsonTake this URL try to open it in a browser, you should get a username+password prompt from the browser, and if you type in the SuperOffice username + password, you should get JSON back - looks like this:
Now open Excel and add Data from Web
Enter the BASIC username + password when it asks:
Excel will download the data and present a data wranger window
Click the LIST next to VALUE in the central results panel.
Now click the CONVERT TO TABLE in the top.
Click OK
Click the little button with arrows in the top of COLUMN1 - this explodes the records into columns.
Uncheck the USE ORIGINAL COLUMN NAME AS PREFIX and click OK
The columns are now available in the data
Click SAVE to close the window and add the data as a new pivot table.
The data can be refreshed directly from SuperOffice
May be a selection of relations might be a nice to have...
And/or Find relation.. - just a thought!
/conrad.
