Skip to main content

SunSystems Query

Many of the data types in SunSystems can be queried using the SunSystems Connect API. This allows us to use SunSystems data to populate external dropdown lists of customers, suppliers, account code, analysis codes etc.

tip

Refer to the SunSystems online help for more information about the data available via SSC and how to use the SSC portal to help construct the payloads required to extract it. The rest of this page assumes some familiarity with SSC payloads and XML.

Set Variable​

When you use a data connector it is likely that you may want to include some dynamic information from the FinanSys Apps form that is using it. For example, if you have multiple business units you may want the user to select a business unit, this selection will then determine what business unit to extract the reference data required. To do this we can assign a variable to hold that content (which is sent automatically when the data connector is used) using a 'Set Variable' step. In this example we are creating a variable called 'temp_input' using the Javascript expression 'return input;'

alt image

Note that variables can be used to store other information, for example you can set them to store fixed values which may make re-using data connectors for different data types easier.

Getting the security token​

Your process will need to have the steps in place to obtain a security token as described in the previous section of this help. We will use that token in the next step.

Template Script​

In the authentication example we had the body of the http request as fixed text within the Send Http Request step. However, it is possible to to use a 'Template Script' step to generate that request body first. This makes it easier to dynamically change values within the body of that request (e.g. fields from the form that submitted that request such as the SunSystems business unit to be used). Using a template script can also make it easier to re-use a request over different types of data. A template script has two main values:

Input​

This is where we define values that can be used in the content section and would usually be set in Javascript. These can be something from the 'Set Variable' step we did earlier using the format <variable name>.urlParams.<field ID> so I could get the value of the field 'business_unit' on the form and assign it to a variable 'bu' as follows:

var businessUnit = temp_input.urlParams.business_unit

We might also want to use data retrieved from a previous step in the process. This can be done using the Javascript <StepName/>.Output. In the SunSystems Authentication example we used a 'Text Parser' step to get the token and named this step 'SecurityToken'. We can now get that value into a variable for use here as follows:

var token = SecurityToken.Output;

The final part of our input script is to return the values in JSON format as follows:

	return {
		"name":"value"
		"name2":"value2"
		...
		}

The values can be the variables we have defined earlier (don't use quotes around these) or fixed values. Naming fixed values as part of the input is not required but it can make it easier to re-use a connector as the parameters than need changing can be all in the same place. E.g. When sending an SSC request to SunSystems the 'Component' and 'Method' determine what data is requested and how - including these in the input makes it easier to re-use the same request for different data types as the values just need amending here.

As we are using Javascript to populate the input we can use other Javascript functionality as required. The following is an example of an input for a supplier listing. It includes fixed and variable values as described above and also uses the || ('Or' statement) in combination with 'optional chaining' to default the business unit to 'PK1' if one is not supplied from the form.

var token = SecurityToken.Output; 
var businessUnit = temp_input?.urlParams?.business_unit || "PK1"
return {
    "component": "Supplier", 
    "method": "Query", 
    "business_unit": businessUnit,
    "token":token
};

Content​

We now can set the content of the template script. This will be literal text, but we can drop in the values obtained in the input field by enclosing their names in double curly brackets. For example in a SunSystems payload the business unit is included between the XML <BusinessUnit/> tags. So to dynamically use the business unit derived using the above input we would use:

<BusinessUnit>{{business_unit}}</BusinessUnit>

Incorporating all of the above, an example content to extract a supplier listing from SunSystems would be as follows:

<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:web="http://systemsunion.com/connect/webservices/">
  <soapenv:Body>
    <web:ComponentExecutorExecuteRequest>
      <web:authentication>{{token}}</web:authentication>
      <web:component>{{component}}</web:component>
      <web:method>{{method}}</web:method>
      <web:payload><![CDATA[<SSC>
	<SunSystemsContext>
		<BusinessUnit>{{business_unit}}</BusinessUnit>
	</SunSystemsContext>
	<Payload>
		<Filter>
			<Expr operator="AND">
				<Item operator="EQU" value="0" name="/Supplier/Status"/>
				<Item operator="NEQU" name="/Supplier/AccountCode" value=""/>
			</Expr>
		</Filter>
		<Select>
			<Supplier>
				<AccountCode/>
				<SupplierName/>
			</Supplier>
		</Select>
	</Payload>
</SSC>]]></web:payload>
    </web:ComponentExecutorExecuteRequest>
  </soapenv:Body>
</soapenv:Envelope>

The SunSystems payload is contained within the CDATA block, the format of this for different types of request can be ascertained using the SunSystems Connect Portal. This example is pulling back data for the suppliers account code, name and email address and is using a filter to include only records that have a status of 'open' (zero value) and exclude those with a missing account code. This is what we will send to SunSystems.

Send Http Request​

The next step is to send the above request to SSC using a 'Send Http Request' step type. This is similar to how we did the authentication but this time our body is coming from the previous template parser stage rather than a literal value so the parameters will be as follows:

  • Url - This will be the same as the URL used for getting the authentication token in the previous step except the last part will be 'ComponentExecutor'. E.g.https://sscsubdomain.yourcompanydomain.com/sunsystems-connect/soap/ComponentExecutor

  • Method - SSC always uses the 'POST' method.

  • Content - The template script output obtained using Javascript using the format <Template script name>.Output. So if the template script step above is called 'RequestPayload' then the content would be:

    return RequestPayload.Output;

  • Request Headers - Most API request require one or more header values, in the case of SSC it needs the SOAP action. This can be provided in JSON format as follows:

{"soapAction":"http://systemsunion.com/connect/webservices/Execute"}

  • Name - We will need to refer to the response from this request by name in the next step of this process, so give it a name without spaces or special characters.

Text Parser​

As with the authentication step we will now need to parse the results. The main difference here is that we are not expecting a single result but a list of values so do not tick the single result box. Sun returns XML and the list of suppliers will be between the <SSC><Payload><Supplier> tags so in this case the query expression will be the literal XPath of:

/SSC/Payload/Supplier[*]

XML to JSON​

As FinanSys Apps works in the JSON format we need to turn the XML that Sun has returned into a JSON equivalent, for this we need a 'XML to JSON step. We can use Javascript to extablish the source content using the step name as before, so if the previous text parser step was named RequestParser we can use the following Javascript in the source content field:

return RequestParser.Output;

Finish​

Finally we need a 'Finish' step to indicate the end of the process. This will take the output of the XML to JSON step and return it as the final output of the data connector for use in the FinanSys Apps form. If the previous step was named RequestJSON we can take the output from this but we do need to parse this output and indicate where in the JSON hierarchy the data we are interested in is located, which we can do with the following Javascript in the activity output field:

return JSON.parse(RequestJSON.Output).result.Supplier;

We can now test our connector by going to the 'Output' tab near the top of the screen and clicking the 'Text Connector' button. This should output the fields requested from Sun and we can choose which ones are displayed on the form. Should the test not work as expected the 'response' field will give detailed information about the inputs and outputs for each step of the process which should assist in troubleshooting where any issues may be.