Chapter 2: Writing Logic

Chapter 2: Writing Logic

Chapter 2 Checklist

  • Learn About:
  • Types of Logic Canvases
  • Tools of the Logic Canvas and Service Canvas
  • The differences between an Inquire and a RunQuery service
  • Project Tasks:
  • Create “GetJDEOrders” method
  • Input:
  • SoldToNumber, OrderNumber, and PONumber Filter
  • Sorting Parameters
  • Paging Parameters
  • Output:
  • Orders Array
  • Success
  • Message
  • Paging Outputs
  • Sorting Outputs
  • Fully test the method in DV

Intro to Logic in EASYProcess

Logic in EASYProcess is performed in a Logic Canvas. It is an execution flow of services and logic defined by the user. The flow is directed from Start to Terminate by connecting directional lines to services along with other process flow elements which may cause the logic to branch off into a different flow based on some criteria. The end result looks like a flowchart and it visually represents the logic to be used in various scenarios.

There are different types of Logic Canvases in EASYProcess. The main difference between the types are:

  1. What triggers the logic to run
  2. How/When the logic is used

Types of Logic Canvases

  • Stand-Alone Method

  • We will create this type of method in the current chapter
  • It can be called by referencing the method name
  • It accepts a defined input and produces an output and is built in a modular way that it could be used by a variety of different areas of the application - View, Batch Job, API, etc.
  • Batch Jobs

  • Triggered by EASYProcess on a predefined schedule
  • Ex: Every day, a batch job runs that sends the admin an email with details on who has completed registration requests for the portal
  • APIs

  • REST and SOAP APIs are available for creation in EASYProcess
  • They can be called by other systems in cases where logic must run
  • Ex: For customers too large to visit a Distributor Portal to place their order, they configure their system to call an API daily that sends their order information to the Distributor Portal. This API performs the same logic as if a user had placed an order on the Portal.
  • Views (WebPages and Widgets)

  • Views run logic when they first load and when triggered by user interaction with elements on the page.

Creating a New Method and the Logic Canvas

Create a New Method

Navigate to the Developer Workbench. We will be creating a new method. From the right, click “Add New” and select “Method”.

Enter the below information to create a method.

Name: GetJDEOrders

Description: Find orders in JDE for a given Sold To Number (AN8)

With the method added to the developer workbench, you will be able to click it to open the Method Canvas.

Logic Canvas and Tools

When arriving on the Logic Canvas after opening the method, you can see various tools across the screen. We will go through the various areas of the screen and highlight some tools that will be useful for your development.

Left Panel

The left panel of the Logic Canvas contains many tabs for development tools.

Services search

The Logic Canvas allows developers to write logic using Services. The Logic Canvas resembles a flowchart with each of the Services resembling a step in the flowchart.

Services perform a specific function and are dragged onto the working area from the left panel from the “Services” tab.

Assistant Tab

The assistant tab will be one of the most used tabs in the left panel. This allows users to:

  • Define the Parameters a Logic Canvas can accept as input
  • Shows the other services that exist in the Logic Canvas (used from a Service Canvas that will be explained later)

Configs

Configuration Variables can be referenced in a Logic Canvas to incorporate the Variable Values into the logic that is being built. The “Configs” tab shows all the System and Application Configuration Variables and can be dragged into a service when in a Service Canvas. This will create the reference so the Variable Value is used at Run Time by the Logic

Dragging in a Configuration Variable from the “Configs” tab in a Service Canvas

User Tab

User Properties, like the one we added for “AddressBookNumber” and already existing properties, like name and email, are available here for reference. Using these references is how we will query JDE and limit the results to only show the orders for the currently logged in account.

Functions

Functions are available within the “Functions” tab. There are XSLT functions for the ones supported within EASYProcess, as well as built-in EASYProcess functions which can be used to perform the same action as a Service.

EASYProcess ToUpper function in use in a CreateServiceNode

Defining Method Parameters

When any method is created, the developer should have in mind its basic input and output. The output is typically used to determine the name of the method. Once the method is created, we use the known input to define the method parameters.

Throughout the development process, this may change, but this is what we know about our method.

Input: SoldToNumber

Action: Use the input to query JDE

Output: The orders for the given AN8

At the top left of the left panel in “Assistant” tab, click the “+” to add a parameter and name it “SoldToNumber”.

Method parameters shown in the left panel “Assistant” tab from the Logic Canvas. The parameter “SoldToNumber” has been created and it has been given value “4242” which is expected to have records in F4201 matching on SHAN8 to show in a sales order inquiry page.

Once created, when it is selected, you can add a value. Set the value to an AN8 that you know will return records.

Outside of testing in the Logic Canvas, this value will not be used.

NOTE: If adding parameters one by one is difficult because of the number of parameters you expect your method will have, there is a faster option. Click the “Edit” button by the parameters and it will open a “Parameter Editor” window. The window has a toggle to switch between JSON and XML format.

Parameter Editor opened from Logic Canvas in JSON format

Parameter Editor opened from Logic Canvas in XML format

Configuring a RunQuery

In the Services tab in the left panel of the “Logic Canvas”, search for “Run Query”. Drag the service onto the canvas and name the service “QueryJDEOrders”.

Connect the start service to the created RunQuery and double click the service to open it. This will display the Service Canvas.

From within the service canvas, there are 3 panels.

Left Panel

Middle Panel

Right Panel

Stores tools the developer will need to configure the service

Shows the entire layout of the service and all the parameters it accepts

XML Preview/Text Editor. When clicked into a specific parameter in the middle panel, the right panel will change from a XML preview to a text editor.

For the RunQuery service, as with all the database workshop services, it will require a Connection Instance.

NOTE: In the Service Canvas, when a service parameter only accepts specific values, it will often have a recommended value from the left panel.

If the Connection for JDE is set up, it should show in the Static Values.

Drag in the “JDEdwards”. This will set up the query to look in JDE tables when suggesting recommended values for the query.

Next click the “Query” parameter. Paste the following query in the right panel:

SELECT *

FROM

<xsl:value-of select="$EP_Connection_Database_JDEdwards_TablePath_Data"/>.F4201

A RunQuery allows the developer to write out their own queries. However, you will need to use a table path before the table. These can be found in the left panel under “Static Values” when you are clicked into the “Query” parameter of the service.

The RunQuery is the best way to use an existing SQL query and run it in EASYProcess, like when the training gives you a query to copy and paste. However, if you are not copying from some other source material, there are tools in EASYProcess to create your own SQL queries without needing to know the syntax for the SQL statement.

All database workshop services have a Query Builder tool that is opened when clicking the “QB” button when Query is selected.

This guides the user through creating a simple query by showing the available table paths, then the available tables. The table joins and unions are displayed visually in the center of the screen and the actual SQL query is written below.

The Query Builder opens in a new tab in the Logic Canvas. Close it to return to the Service Canvas for the RunQuery.

From the Service Canvas, there is a ▶❚ button above the middle panel to run the service. Click this and the left panel will change to select “Output” and show the logs of the service when it runs.

RunQuery was run in step from the Service Canvas. The left panel is shown loading the results.

RunQuery was run in step from the Service Canvas. The left panel shows the loaded results as they would appear in the logs.

NOTE: When a service is run from the Logic Canvas, its output is also recorded in the overall logs of the Logic Canvas and can be seen from the log tab at the top of the screen.

Notice all the results are returned because we are performing a * query.

NOTE: Performing a query to select all columns is NOT recommended as it is inefficient. The inefficiency can grow to the point where a query stops functioning as expected. Whenever possible, add filters for only the columns that are needed.

Change the query to the below:

SELECT SHDOCO, SHDCTO, SHKCOO, SHVR01, SHAN8, SHSHAN, SHDRQJ, SHTRDJ, SHMCU, SHOTOT

FROM

<xsl:value-of select="$EP_Connection_Database_JDEdwards_TablePath_Data"/>.F4201

See below the Result node in the logs. Each Result now has only the columns specified in the SELECT clause.

Configuring an Inquire

So far we have been configuring a RunQuery because we can start with a query and adapt EASYProcess to use it. However, if you want to use the EASYProcess tools to create the query and don’t have a query to start from, the Inquire guides the developer through the process.

The benefit of using an Inquire to a RunQuery, is that you can enter the desired select clause, where clause, and tables and EASYProcess will build the SQL query for you. This does not require that the developer be very familiar with the SQL query syntax.

Inquires build the query for you given the select clause and where clause, but it will generally follow the pattern:

SELECT [SelectClause] FROM [Table] WHERE [WhereClause][GroupByClause] [OrderByClause]

The drawback of an Inquire is that EASYProcess will always use this pattern. Sometimes the developer may have a particular query structure in mind that does not follow this structure. When you want to break from this pattern, a RunQuery can be more useful.

Drag in an Inquire and replace the RunQuery with the Inquire service.

Working from the RunQuery, configure the Inquire to create the same query, but using the required parameters.

Here is the full XML of the Inquire once it is properly configured to match the RunQuery.

<Inquire>

    <ConnectionInstance>JDEdwards</ConnectionInstance>

    <TableName>

        <xsl:value-of select="$EP_Connection_Database_JDEdwards_TablePath_Data"/>

    .F4201</TableName>

    <Keys></Keys>

    <WhereClause>

        <Data/>

    </WhereClause>

    <OrderBy></OrderBy>

    <AdditionalWhereClause></AdditionalWhereClause>

    <Parameters />

    <ParametersXml></ParametersXml>

    <Results>

        <Result IsArray="True" Index="1">

            <Column>SHKCOO</Column>

            <Expr></Expr>

        </Result>

        <Result IsArray="True" Index="2">

            <Column>SHDOCO</Column>

            <Expr></Expr>

        </Result>

        <Result IsArray="True" Index="3">

            <Column>SHDCTO</Column>

            <Expr></Expr>

        </Result>

        <Result IsArray="True" Index="4">

            <Column>SHMCU</Column>

            <Expr></Expr>

        </Result>

        <Result IsArray="True" Index="5">

            <Column>SHAN8</Column>

            <Expr></Expr>

        </Result>

        <Result IsArray="True" Index="6">

            <Column>SHSHAN</Column>

            <Expr></Expr>

        </Result>

        <Result IsArray="True" Index="7">

            <Column>SHDRQJ</Column>

            <Expr></Expr>

        </Result>

        <Result IsArray="True" Index="8">

            <Column>SHTRDJ</Column>

            <Expr></Expr>

        </Result>

        <Result IsArray="True" Index="9">

            <Column>SHVR01</Column>

            <Expr></Expr>

        </Result>

        <Result IsArray="True" Index="10">

            <Column>SHOTOT</Column>

            <Expr></Expr>

        </Result>

    </Results>

    <GroupByClause></GroupByClause>

    <AdditonalSelectClause></AdditonalSelectClause>

    <CommandTimeout></CommandTimeout>

    <Paging>True</Paging>

    <StartIndex>

        <xsl:value-of select="WorkData/Variables/Output/Result/PageFromRecord"/>

    </StartIndex>

    <RowCount>50</RowCount>

    <ReturnTotalCount>False</ReturnTotalCount>

    <Download>False</Download>

    <DownloadLimit></DownloadLimit>

    <DownloadFormat></DownloadFormat>

    <DownloadFields></DownloadFields>

</Inquire>

Using Results with a RunQuery

The drawback of a RunQuery is that EASYProcess isn’t aware of the pattern you are following when writing your query. Without this, EASYProcess is not able to perform steps for you, and the developer is responsible for the extra tasks.

Even though the RunQuery gives the developer full control over the SQL statement that will execute, the RunQuery EASYProcess service will not be able to anticipate the results that would return from your entered SQL statement. By entering Results, you tell EASYProcess what you expect back from the query so it can be available in the recommended values.

In certain database types, if you write a query:

SELECT SHDOCO AS OrderNumber, SHDCTO as OrderType, SHKCOO as OrderCompany

FROM

<xsl:value-of select="$EP_Connection_Database_JDEdwards_TablePath_Data"/>.F4201

It may return as:

<QueryJDEOrders Output="False">

        <Output>

                <Result SelectOutput="False">

                        <ORDERNUMBER>121</ORDERNUMBER>                                                                <ORDERTYPE>SQ</ORDERTYPE>

                        <ORDERCOMPANY>00001</ORDERCOMPANY>

                </Result>

        </Output>

</QueryJDEOrders>

This may not be an issue, but if the following service in the Logic Canvas is expecting

WorkData/QueryJDEOrders/Output/Result/OrderNumber

Then it will cause issues with the case sensitive XML.

See below the RunQuery with the Results specified.

Making a Local Reference for Variables

When working with outside variables, it is best practice to create a local reference that the rest of your Logic Canvas will use. This is useful in case the name or path for the variables changes. Once it has changed, you will need to change the reference in the rest of the Logic Canvas. If you have a local reference at the beginning, you will just have to change it in one location and the rest of the canvas will remain with the local reference.

Drag in a “CreateServiceNode” and connect it right after the “Start” service. Name the service “Variables”. This will be our local reference.

These services are typically called Variables, Inputs, or Prevalues, depending on the original developer who built the app.

Adding a Where Clause

Our goal for this method is to use the passed in SoldToNumber to filter the query results. To achieve this, we need to add a filter.

For now, we only have one parameter for the method. Add the parameter in the Variables service.

Name: SoldToNumber

Value: <xsl:value-of select="WorkData/_Parameters/SoldToNumber"/>

Configured Variables Service

This will be enough for our testing, but when used on the app, we want the SoldToNumber to be pulled from the user and will not come from the Web Page.

From the left panel, select the SoldToNumber from the parameters again. This will bring up the XSLT Expression Helper at the bottom of the right panel. Click the “Choose” button to place an xsl:choose in the Value of the Result.

The When will be used in the case when the passed in parameter is blank. This is going to be our main use for this method. The case where it accepts a passed in value will be used mainly in testing so we can run the method in the Logic Canvas and produce an output. Then, since the functionality is built, in the future it could be used in a way that accepts the passed in SoldToNumber.

The value chosen for that When test will need to come from the user. To retrieve this value, go to the “User” tab in the left panel. Find the “AddressBookNumber” User Property we added in the previous chapter.

In the Otherwise, we will use the passed in parameter.

Your end result should look like this:

<xsl:choose>

    <xsl:when test="WorkData/_Parameters/SoldToNumber=''">

        <xsl:value-of select="$EP_EPUser_AddressBookNumber"/>

    </xsl:when>

    <xsl:otherwise>

        <xsl:value-of select="WorkData/_Parameters/SoldToNumber"/>

    </xsl:otherwise>

</xsl:choose>

Adding Additional Filters

Before we get any farther, we should consider how this method will be used in the inquiry page. The user we are logged in as will have an associated SoldToNumber, and that is why we need that as a filter. However, that user is going to expect some basic filtering on that page to find the record they are looking for.

For this example, we can say the users are going to have their order number or their PO number with them when they go to search. This means we will have to add a filter for these fields.

This step of considering how the source will be used in the end product is valuable to do early on to prevent redoing any steps. As you build your own projects, it is a good idea to plan out all your sources and their use before you start developing them.

Edit the Method Parameters to add two more filters: OrderNumber and PONumber. By the time we are done with this method, it will have many parameters for different uses, so it is a good idea to start organizing them into grouping XML nodes.

<SoldToNumber>4242</SoldToNumber>

<Filters>

        <OrderNumber></OrderNumber>

        <PONumber></PONumber>

</Filters>

To maintain this grouping in the Variables service, you can add a prefix to the variable name:

  • Result
  • Name: FiltersOrderNumber
  • Value: <xsl:value-of select="WorkData/_Parameters/Filters/OrderNumber"/>

  • Result
  • Name: FiltersPONumber
  • Value: <xsl:value-of select="WorkData/_Parameters/Filters/PONumber"/>

Inquire

Open the Inquire service and click on “Where Clause” in the Inquire parameters. This opens a different window in the 3rd panel to allow for easy SQL WHERE Clause creation.

Drag in the filter from the top and configure the “Column” with the “SHAN8” column.

For the value, pull the SoldToNumber reference in the Variables service from the recommended values in the left panel.

Now add the OrderNumber and the PONumber filter. To do this, you will need to know which column they map to in the F4201 table. You can find this by expanding the “Columns in Table” section under “Static Values” when selecting your new filter.

In case you are having trouble finding this, the answers are also shown below in the RunQuery section.

By default, the filters added in the Inquire should have set “Ignore Blanks” to “True”. This is the value we want for an optional filter because when the page first loads, those textboxes for filters will be empty and we still need the page to return values.

For the “Ignore Case” option, set it to “True” for only the PO Number. We know the OrderNumber is a numeric field, so there is no case. The PO number could contain letters, so we will need this setting to make searches easy for the users.

RunQuery

Adding the WHERE Clause in the RunQuery seems more straightforward to start, but requires additional steps for each feature that EASYProcess takes care of with the Inquire.

Open the RunQuery and add the WHERE Clause in the Query parameter of the service.

SELECT SHKCOO, SHDOCO, SHDCTO, SHMCU, SHAN8, SHSHAN, SHDRQJ, SHTRDJ, SHVR01, SHOTOT

FROM

<xsl:value-of select="$EP_Connection_Database_JDEdwards_TablePath_Data"/>.F4201

WHERE

SHAN8 = '<xsl:value-of select="WorkData/Variables/Output/Result/SoldToNumber"/>' AND

SHDOCO = '<xsl:value-of select="WorkData/Variables/Output/Result/FiltersOrderNumber"/>' AND

SHVR01 = '<xsl:value-of select="WorkData/Variables/Output/Result/FiltersPONumber"/>'

This still seems straightforward, but it doesn’t cover the IgnoreCase and IgnoreBlanks features we added in the Inquire service. We need the SHDOCO and SHVR01 filters to only be applied in the case that the value we are comparing it to is not blank. We also need the value it is comparing in SHVR01 to not be case sensitive.

To do this we will use the Functions tab.

Ignoring Case

To ignore case, we need both the left and right side of the comparison to be in the same state. We can accomplish this with the ToUpper function. However the left side of the comparison is in SQL and the right side is in XSLT. This means each side needs to use its own language to perform the function.

Search the Functions tab for “Upper” to find the ToUpper EASYProcess function.

Drag in this function the Query.

<xsl:value-of select="EASYProcess:ToUpper('input')"/>

Change the SHVR01 filter to use this function, removing the single quotes since we will be performing the function against an xpath instead of a string as in the example above.

For the SQL side, you can use the UPPER() SQL function.

The result should look like this:

UPPER(SHVR01) = <xsl:value-of select="EASYProcess:ToUpper(WorkData/Variables/Output/Result/FiltersPONumber)"/>

Ignoring Blanks

To ignore blanks, we need to check the value of the filter we are about to apply and if it is blank, to instead remove the SQL filters. This can be accomplished with the XSLT Expression Helper.

From the left panel, find the output from the Variables service for the FiltersOrderNumber. Click it and the bottom of the right panel in the Service Canvas will have some new buttons.

 

Click the “If” to place it in the Query area. You will see the following:

<xsl:if test = "WorkData/Variables/Output/Result/FiltersOrderNumber=''"></xsl:if>

Move the SHDOCO filter inside the if tags and change the test to be !='' so the logic inside is only placed in case the filter has a value.

The result should look like this:

<xsl:if test = "WorkData/Variables/Output/Result/FiltersOrderNumber!=''">SHDOCO = '<xsl:value-of select="WorkData/Variables/Output/Result/FiltersOrderNumber"/>'</xsl:if>

Next apply this to the SHVR01 (PONumber) filter. This means the PONumber filter will have both the IgnoreBlanks and IgnoreCase logic.

We will not apply the IgnoreBlanks logic to the SHAN8 filter because if a SoldToNumber were not provided, we would not want to return all the orders. Instead, it would be more desirable to return no orders, which is what would happen if the SoldToNumber were not given.

When you think you have it, run the service in step and take a look at the placement of the ANDs to see if they need some changes to work with the new logic.

The result should look like this:

SELECT SHKCOO, SHDOCO, SHDCTO, SHMCU, SHAN8, SHSHAN, SHDRQJ, SHTRDJ, SHVR01, SHOTOT

FROM

<xsl:value-of select="$EP_Connection_Database_JDEdwards_TablePath_Data"/>.F4201

WHERE

SHAN8 = '<xsl:value-of select="WorkData/Variables/Output/Result/SoldToNumber"/>'

<xsl:if test = "WorkData/Variables/Output/Result/FiltersOrderNumber!=''">

AND SHDOCO = '<xsl:value-of select="WorkData/Variables/Output/Result/FiltersOrderNumber"/>'

</xsl:if>

<xsl:if test = "WorkData/Variables/Output/Result/FiltersPONumber!=''">

AND UPPER(SHVR01) = '<xsl:value-of select="EASYProcess:ToUpper(WorkData/Variables/Output/Result/FiltersPONumber)"/>'

</xsl:if>

Parameterization

This RunQuery would work, however it is susceptible to a SQL injection attack and should NOT be left as it is. It requires an additional step for parameterization which we will cover next.

Since EASYProcess is in charge of maintaining the query pattern in an Inquire, it is able to do things for us, like parameterize the query.

A parameterized query is a query in which placeholders are used for parameters and the parameter values are supplied at execution time. The most important reason to use parameterized queries is to avoid SQL injection attacks. These can happen when a value used in the query comes from the page in user entered data.

Parameterizing a RunQuery

If using a RunQuery, when adding the WHERE clause, the values used will need to be parameterized manually by the developer. This can be done by the below steps.

  1. Place the placeholder for the parameter in the query using the correct syntax for the database type
  2. Define the parameter value one of the following ways
  1. Clicking the “+” under the Parameters section on the RunQuery
  2. Using the ParametersXml to type out all the parameters in their XML structure

A RunQuery with defined parameters using the “Parameters” section

SELECT SHKCOO, SHDOCO, SHDCTO, SHMCU, SHAN8, SHSHAN, SHDRQJ, SHTRDJ, SHVR01, SHOTOT

FROM

<xsl:value-of select="$EP_Connection_Database_JDEdwards_TablePath_Data"/>.F4201

WHERE

SHAN8 = @SoldToNumber

<xsl:if test = "WorkData/Variables/Output/Result/FiltersOrderNumber!=''">

AND SHDOCO = @OrderNumber</xsl:if>

<xsl:if test = "WorkData/Variables/Output/Result/FiltersPONumber!=''">

AND UPPER(SHVR01) = @PONumber

</xsl:if>

A RunQuery Query using a placeholder for the parameter. Here it is using @OrderNumber which will match a Parameter named OrderNumber

Now the RunQuery is ready to use safely.

Notice we even parameterized the SoldToNumber. The SoldToNumber will come from the user properties automatically, so it is not susceptible to SQL injection through this page. However, we did expose it as a parameter of the method. In the future, if this method is used in a way that does expose it to the user, then it is at risk. The parameterization of the SoldToNumber protects this method for future use.

Parameters XML

As an alternative, the RunQuery could accept the XML for the parameters instead of the individually added parameters.

This must have the following structure:

<Parameters>

        <Parameter>

                <Name></Name

                <Value></Value>

                <DataType></DataType>

        </Parameter>

</Parameters>

Another reason this is useful is if some of your parameters will not always exist. They could be present based on certain conditions. For these reasons, you might want to encase your parameters in an xsl:if or xsl:choose.

You may also want your parameters to be created based on another service’s results. In this case, you may want to create your parameters with an xsl:for-each function. See below an example of using XSLT functions in the ParametersXML.

Example Parameters XML Using an xsl:if and xsl:for-each

<Parameters>

    <xsl:if test = "WorkData/JDEOrderHeader/Output/Result/ToDateOrdered and WorkData/JDEOrderHeader/Output/Result/ToDateOrdered!=''">

        <Parameter>

            <Name>DateOrdered</Name>

            <Value><xsl:value-of select="WorkData/ToDateOrdered/Output/Result/JDEDate"/></Value>

            <DataType>Int</DataType>

        </Parameter>

    </xsl:if>

    <xsl:if test = "WorkData/JDEOrderHeader/Output/Result/Status and WorkData/JDEOrderHeader/Output/Result[Status!='']">

        <xsl:for-each select="WorkData/JDEOrderHeader[Status!='']/Status">

            <Parameter>

                <Name>Status</Name>

                <Value><xsl:value-of select="current()"/></Value>

                <DataType>String</DataType>

            </Parameter>

        </xsl:for-each>

    </xsl:if>

</Parameters>

Parameterization Syntax in Query

When using a RunQuery to query the JDE database, the database type can differ which will require a different syntax in queries. There will be actual syntax differences which will affect how you write your query (ISNULL vs IFNULL, TOP vs FETCH FIRST N ROWS ONLY, etc). Another difference you will need to be aware of, is how parameterization is affected.

Typically, parameterization uses @[ParameterName] in the query to tell EASYProcess which parameter should be used in the query. The ParameterName must then match a defined Name in the Parameters or ParameterXml section of the RunQuery. The order of the Parameters in that section does not matter since the match is performed on the name.

With DB2 (iSeries), a “?” is used in the query instead. Since all the parameters in the query will be shown with a question mark, there will be no way to distinguish between them. This makes the order the parameters are defined very important. In order, EASYProcess replaces a question mark with the next parameter in the list.

In the example below, there is a RunQuery which has a SQL query using a UNION. Each side of the UNION has 4 parameters, making a total of 8 parameters needed.

Query

SELECT SUM(t1.TotalQuantity) as TotalQuantity

FROM

       (

              SELECT

                     SUM(SDUORG/10) AS TotalQuantity

              FROM

                     <xsl:value-of select="$EP_Connection_Database_JDEdwards_TablePath_Data"/>.F4211

              WHERE

                     SDSHAN     = ?

                     AND SDLITM = ?

                     and SDTRDJ>= ?

                     and SDKCOO = ?

                     and SDDDOC >0

              HAVING SUM(SDUORG/10)>0

              UNION ALL

              SELECT

                     SUM(SDUORG/10) AS TotalQuantity

              FROM

                     <xsl:value-of select="$EP_Connection_Database_JDEdwards_TablePath_Data"/>.F42119

              WHERE

                     SDSHAN     = ?

                     AND SDLITM = ?

                     and SDTRDJ>= ?

                     and SDKCOO = ?

                     and SDDDOC >0

              HAVING SUM(SDUORG/10)>0

       ) t1

Parameters

<Parameters>

    <Parameter>

        <Name>SDSHAN1</Name>

        <Value><xsl:value-of select="WorkData/Variables/Output/Result/Account"/></Value>

        <DataType>Int</DataType>

    </Parameter>

    <Parameter>

        <Name>SDLITM1</Name>

        <Value><xsl:value-of select="WorkData/Variables/Output/Result/ItemNumber"/></Value>

        <DataType>String</DataType>

    </Parameter>

        <Parameter>

        <Name>Date1</Name>

        <Value><xsl:value-of select="WorkData/Variables/Output/Result/JDEDate"/></Value>

        <DataType>Int</DataType>

    </Parameter>

    <Parameter>

        <Name>SDKCOO1</Name>

        <Value><xsl:value-of select="WorkData/Variables/Output/Result/OrderCompany"/></Value>

        <DataType>String</DataType>

    </Parameter>

    <Parameter>

        <Name>SDSHAN2</Name>

        <Value><xsl:value-of select="WorkData/Variables/Output/Result/Account"/></Value>

        <DataType>Int</DataType>

    </Parameter>

    <Parameter>

        <Name>SDLITM2</Name>

        <Value><xsl:value-of select="WorkData/Variables/Output/Result/ItemNumber"/></Value>

        <DataType>String</DataType>

    </Parameter>

        <Parameter>

        <Name>Date2</Name>

        <Value><xsl:value-of select="WorkData/Variables/Output/Result/JDEDate"/></Value>

        <DataType>Int</DataType>

    </Parameter>

    <Parameter>

        <Name>SDKCOO2</Name>

        <Value><xsl:value-of select="WorkData/Variables/Output/Result/OrderCompany"/></Value>

        <DataType>String</DataType>

    </Parameter>

</Parameters>

Paging

Notice that because we are running a query with no filters, this could return a really large data set. Since this is rarely the desired outcome of the developer and if left at unlimited it could negatively impact performance, it is limited to only return 50 records by default.

In actual use on a page, we would want to limit the number or results with paging logic, but you may want to alter how many rows show on each page before more load.

This is handled in the parameters of the RunQuery/Inquire service.

Paging in a Database Workshop Service

Looking at the service, we can see the parameters that determine the paging. These parameters are standard for Database Workshop services such as RunQuery and Inquire.

  • Paging: This accepts True/False values. When set to True, it will use the RowCount to determine how many results return at a time. By default, it is set to True, but if it is turned off, it will try to retrieve all the records which is not recommended since this could cause long loading times on pages.

NOTE: When run from a logic canvas, the Paging parameter value is overridden to be True, even when it is set to False. This is to prevent writing a query that would overload the canvas with results and be hard to manage.

  • StartIndex: When Paging is True, the start index is used to determine the first record retrieved. By default, this is set to:

<xsl:value-of select="$EP_Service_Info_NextStartIndex"/>

This is a variable that lets the service look for itself when it ran previously. In our test run, we retrieved 50 records, so the NextStartIndex is 50. The next time this service runs, it will return 51 through 100.

  • RowCount: When Paging is True, the RowCount determines how many rows are retrieved at a time. The default value is 50.

  • ReturnTotalCount: When set to True, it will return the total count.

NOTE: ReturnTotalCount requires 2 queries behind the scenes to return the service output. One to run a COUNT query to get the total count and one to return the results with paging applied. This is not recommended in a production environment since it can become inefficient.

This might be useful during development to see how many records would return if you requested all of them.

It's important to remember that the data you are working with in the DV/QA environments is often far less than in the PD environment. When developing, keep in mind the type of data you will be working with and incorporate that into your designs.

Inquire/RunQuery

For our example, we need to change that hardcoded 50 results per page, but we will change it to something more flexible.

In our final page, we may want to show only 10 per page, but since this is a method that could have other uses in the future, we will build it to dynamically accept the page size. This will make the method the most powerful for other uses.

If we did not do this, today we would hardcode the method to return 10 at a time. Then, in a few months, we could reuse this method and that new use needs 100 at a time. The next developer cannot make their user return 100 without negatively affecting our page. This forces the next developer to go edit our work in the method.

NOTE: It is best practice to try to build objects that are built for growth the first time to prevent more work in the future.

To change our method to support this, we will have to make the following changes:

  1. Method Input
  2. Variables Service
  3. RunQuery/Inquire
  4. Output

These follow the paging parameters from beginning to end of the method. Notice a new step has been added: Output. We have not built this part of our method yet, but it is important to output what paging parameters were used so the Web Page knows the next set of pages it needs to pass as input.

Method Input

In the method input, add a new holder for paging and add two new parameters underneath it:

  • StartIndex
  • PageSize

<SoldToNumber>4242</SoldToNumber>

<Filters>

        <OrderNumber></OrderNumber>

        <PONumber></PONumber>

</Filters>

<Paging>

        <StartIndex></StartIndex>

        <PageSize></PageSize>

</Paging>

Variables Service

Add the new parameters in the Variables service.

RunQuery/Inquire

To connect the RunQuery and Inquire service to the paging parameters saved in the Variables service, remap the StartIndex and RowCount parameters. Make these changes for both of the services.

StartIndex: <xsl:value-of select="WorkData/Variables/Output/Result/PagingStartIndex"/>

RowCount: <xsl:value-of select="WorkData/Variables/Output/Result/PagingStartIndex"/>

Method Output

All methods need a defined output and this is done with a specific service: MethodOutput. We will need to set up the Method Output in order to add the paging parameters to it.

Drag in a MethodOutput service. This is the service to use for all non-array outputs from a method. For now, we will just set up the paging parameters that we need.

Add the following Results:

PagingNextStartIndex: <xsl:value-of select="WorkData/QueryJDEOrders/Info/Result/NextStartIndex"/>

PagingPageSize: <xsl:value-of select="WorkData/Variables/Output/Result/PagingPageSize"/>

Feel free to run some tests in the Logic Canvas with the Next Start Index.

You will find that if you leave it blank, the service defaults to 0 and returns the first record. If you set it to 0, it returns the first records. If you set it to 1, it returns starting with the second record.

So the first time it runs, the NextStartIndex will be equal to the PageSize. After that it will be incremented by the PageSize each time.

This all will work because in the Web Page where we will call the method, each time we call it, we will pass in the output of the NextStartIndex from the previous time it was called.

Sorting

In our tests so far, the records have been returning in the same order. However, there is no guarantee that will always happen without a defined sort order.

NOTE: Every query should have a default sort order to ensure that each time a page is loaded, its results will show in the same order assuming no sort options have been changed.

Adding sorting functionality also gives the user another possible feature to use on the Web Page. We will make the column headers clickable which will sort all results by the selected column header.

To change our method to support this, we will have to make changes to the same places we worked on for Paging logic:

  1. Method Input
  2. Variables Service
  3. RunQuery/Inquire
  4. Output

Change the Method Input and the Variables following the below.

  • Method Input:
  • New holder node: Sort
  • New Variables:
  • Field
  • Order
  • Variables Results:
  • SortField
  • SortOrder

For the RunQuery and Inquire sorting, we will need to change the OrderBy Clause. For the Inquire, there is an OrderBy parameter. For the RunQuery, it will be added to the end of the Query parameter.

We will need it to have a default value when there is no user selection. When there is a user selection, it will need to use that.

To accomplish this, we will need to utilize an xsl:choose. Click on the SortFiled from the recommended output of the Variables service in the left panel. This will bring up the XSLT Expression Helper.

Click on the Choose to bring it into the OrderBy field.

In the Otherwise, we will put the default sort. This should be the OrderNumber field and it should be sorted so the newest records are on top.

The When should be checking for a sort field that is not blank. When that happens, it should use that sort field and the sort order separated by a space.

If you put two xsl:value-ofs right next to each other, the space between them won’t be maintained. So, there are a couple of ways to create the space between them.

Option 1: concat XSLT Function

<xsl:value-of select="concat(WorkData/Variables/Output/Result/SortField,' ',WorkData/Variables/Output/Result/SortOrder)"/>

Option 2: A Space in another xsl:value-of

<xsl:value-of select="WorkData/Variables/Output/Result/SortField"/><xsl:value-of select="' '"/><xsl:value-of select="WorkData/Variables/Output/Result/SortOrder"/>

The result should look something like this:

<xsl:choose>

    <xsl:when test="WorkData/Variables/Output/Result/SortField!=''">

        <xsl:value-of select="concat(WorkData/Variables/Output/Result/SortField,' ',WorkData/Variables/Output/Result/SortOrder)"/>

    </xsl:when>

    <xsl:otherwise>SHDOCO DESC</xsl:otherwise>

</xsl:choose>

Then last, to output the sort fields, since the query is not changing them (unlike the paging parameters), we can add the notes to the MethodOutput and just reference them from the Variables service.

  • MethodOutput Results:
  • SortField
  • SortOrder

Method Output

Now that the logic is all complete, we can think about what output from the method will be used. We have set up the Results in the MethodOutput service, but the main purpose of this method is to return the array of orders.

This highlights that there are two kinds of output. When calling a method, EASYProcess only allows reference of services if they are a MethodOutput or if they are flagged for output. For non-array outputs, they are put in the MethodOutput service. For array outputs, they can be referenced directly and flagged as an output.

Expose the Array for Output and Catch Exception

Click on the service and a “Properties” window will be exposed. Check the “Expose Service Output” property.

Since we are checking for the exception in the MethodOutput, this means we are expecting the Inquire to sometimes exception. Services that are allowed to exception and we build error handling for are given this flag. If this flag is not on and the service throws an exception, on run time, an error page will show.

Error Page at Run Time when a service threw an exception that did not have Catch Exception flag on

NOTE: The CatchException flag should be turned on for every integration point to an outside system. This includes JDE queries, BSFNs, Orchestrations, and API calls.

Developing a Method - Best Practices

Now that you have built your method, we will take a look at some other best practices that you should consider.

Naming Conventions

Alias of Columns

If any columns were created that you wish to alias, doing so early in the development process makes it easier so cause any duplicate steps. An alias will be required when using an expression in a SELECT clause to perform string or math operations. Aliases can however still be used to rename columns to a more readable format.

To make these changes quicker, try using the “Edit Nodes as CSV” option.

Aliases used in Inquire Service

Aliases used in RunQuery service. Notice the change in the SELECT clause and the Results.

Names of services

When naming services, try to avoid naming services after the service itself (naming a service “Inquire” or “RunQuery”). It is also good practice to name a service after the goal behind the service instead of the specific logic it is performing.

Consider a service that performs a check against a shopping cart total and returns True when the order total surpasses $100. Once the threshold is crossed, the order earns free shipping as a perk.

Possible Names:

  • Over100Check
  • FreeShippingCheck

The name for this service should be named something related to the free shipping perk as opposed to the $100 check. This example was based off a real application and the threshold amount eventually changed to $150. At that point, the service logic needed to be updated as well as the service renamed. The rename meant that all services after it needed to be updated if they referenced the old name.

If it was determined that the effort for the renaming was not worth the time, there would be a service named “Over100Check” performing a check for $150, which would mislead future developers.

Defining Method Output and Error Handling

So far the MethodOutput service has only been returning the specific outputs of our method. However, every method needs to return some basic outputs to inform the location calling it, how things went. If the location calling this method checks for its success and does not find it, that location is able to end gracefully.

If it did not have this check, it would continue on, assuming the values returned from this method could be trusted and used, when that might not be the case. The result would be incorrect values or a feature that looks broken.

Add two Results:

  • Success
  • Message

The logic within each of these results needs to be dynamically dependent on whether the service fails. When a service fails, it has an exception node in the output. If you notice in the recommended values, this is shown so developers can create their logic from the node as if it failed without having to force a failure in their logic.

Once you click on the node in the left panel under the assistant tab, buttons appear at the bottom of the right panel. Click the “Choose” button to create an xsl:choose.

XSLT Expression Helper Opened from the “Exception” Node when selected from the Assistant tab

Once the xsl:choose is created, edit it to match the below. We will be checking for exception to determine whether the method as a whole is a success.

Success

<xsl:choose>

    <xsl:when test="not(WorkData/QueryJDEOrders/Exception)">True</xsl:when>

    <xsl:otherwise>False</xsl:otherwise>

</xsl:choose>

Message

<xsl:choose>

    <xsl:when test="not(WorkData/QueryJDEOrders/Exception)">Success</xsl:when>

    <xsl:otherwise>

        <xsl:text>QueryJDEOrders Exception: </xsl:text>

        <xsl:if test="WorkData/QueryJDEOrders/Exception/Result/Message!=''">

            <xsl:value-of select="WorkData/QueryJDEOrders/Exception/Result/Message"/>

        </xsl:if>

    </xsl:otherwise>

</xsl:choose>

Unit Testing in DV

Now that the Method is complete, it is important you do all your testing of the feature.

Every time you made a change, you could have inadvertently been breaking another feature that previously passed testing. Once all changes are done, you will need to test again to sign off on the current state of the feature after your development.

Go back through all the features we added into the method and test them thoroughly, both separately and together (does filtering work while you sort?).

Once you are sure it is working as expected, continue to the next chapter.

Chapter 2 Summary

  • Logic Canvases can be seen as Stand-Alone Methods, Batch Jobs, APIs, and Within Views
  • Method Parameters are defined in the left panel and can be mass added in the Parameter Editor or done individually
  • A local reference for variables should be made at the start of a method to prevent more work in the future
  • When a Service parameter only accepts specific values, the Static Values in the left panel will offer recommended values
  • Query Builder in a Database Workshop service displays table joins manually and builds the SQL query syntax for you
  • When a service is run in step on the Logic Canvas, the logs of its run is also recorded in the Logs tab
  • A RunQuery provides more freedom to run queries that are copy and pasted, but has many drawbacks to an Inquire. A RunQuery needs to:
  • Define results to ensure the case is maintained
  • Be manually parameterized to protect from SQL injection
  • Manually apply Ignore Case logic with an UPPER function
  • Manually apply Ignore Blanks logic with an xsl:if
  • Parameterization Parameters are noted in a RunQuery by @ or ? depending on the database type
  • When adding many service parameters, use the “Edit as CSV” feature to mass add parameters
  • When running services in the Logic Canvas, the “Paging” parameter is overridden to “True” to prevent an overload that would cause the page to become unresponsive
  • It is not recommended to run SELECT * queries because they are inefficient
  • It is not recommended to set the “ReturnTotalCount” parameter to True for database workshop services in PD because it is inefficient. It will run two queries behind the scenes. One to return the count and one to return the results.
  • All queries should be given a default Sort to ensure the results will always appear in the same order if the same filtering is done.
  • MethodOutput services are used to output non-array data. They should always contain a “Success” and a “Message” Result
  • Array outputs are referenced directly and need the “Expose Service Output” property flag turned on
  • If the “CatchException” property of a service is not turned on and it throws an exception, the WebPage running it will stop loading and show the error page. The CatchException should be turned on for every integration point to an outside system.
  • Services should be named after the goal of the service rather than the specific logic used to accomplish that goal