Wiki

Scale Your Enterprise

User Tools

Site Tools

Trace: • File Reader Tool • Introduction to DataBuilder • Introduction to DataBuilder
bw2:databuilder_tool

Differences

This shows you the differences between two versions of the page.

Link to this comparison view

Both sides previous revision Previous revision
Next revision 		Previous revision
bw2:databuilder_tool [2020/12/15 12:37]
akoehler 		bw2:databuilder_tool [2022/08/02 15:33] (current)
akoehler
Line 11: Line 11:
 ====== Before You Begin ====== ====== Before You Begin ======
  
-You will need to know, or have examples of , the data structures the API is expecting. This includes element (field names) and child objects within the structure. Keep in mind that names will generally require an exact match, including case.+You will need to know, or have examples of, the data structures the API is expecting. This includes an element (field names) and child objects within the structure. Keep in mind that names will generally require an exact match, including case.
  
-====For SAP Business One Service Layer==== +=====For SAP Business One Service Layer===== 
-An easy way to view the data structures used for SAP Business One is to create a small workflow that executes a GET on the object in question.  The workflow will have two WebService2 steps: a login step and a GET step.  The GET step references the target object (e.g. SalesOrders). The GET step will return the data structure for the object in the "Output String."  This is also an easy way to create a template file for the object in your environment as it will include any user-defined fields as well.+An easy way to view the data structures used for SAP Business One is to create a small workflow that executes a GET on the object in question.  The workflow will have two WebService2 steps: a login step and a GET step.  The GET step references the target object (e.g., SalesOrders). The GET step will return the data structure for the object in the "Output String."  This is also an easy way to create a template file for the object in your environment as it will include any user-defined fields as well.
  
 +More information dealing with SAP Service Layer can be found **[[bw2:sap_business_one_integration_using_service_layer|here]]**
 ====== General Notes ====== ====== General Notes ======
  
   - Input will generally be from a SQL Reader step.   - Input will generally be from a SQL Reader step.
-  - The example used in this page is for JSON.  The same general process is also used for XML.+  - The example used on this page is for JSON.  The same general process is also used for XML. 
 +  - When working with decisions, the Databuilder tool in a branch of a decision will only reference Databuilder tools in that same branch.
  
 ====== General Process ====== ====== General Process ======
Line 25: Line 27:
   - Create an input (SQL Reader) step for each dataset that will be needed. An example would be order header and order lines.   - Create an input (SQL Reader) step for each dataset that will be needed. An example would be order header and order lines.
   - Create a DataBuilder step for each dataset.   - Create a DataBuilder step for each dataset.
-  - Once all DataBuilder steps are created, add the lower-level datasets to the main dataset. The lower-level datasets are referenced by their Object Name. These are listed in the “Field Type” drop-down after they are created. +  - Once all DataBuilder steps are created, add the lower-level datasets to the main dataset. Their Object Name references the lower-level datasets. These are listed in the “Field Type” drop-down after they are created. 
-  - The “CumulativeData” variable from the main DataBuilder step in referenced in the web service step that transmits the information to the API.+  - The “CumulativeData” variable from the main DataBuilder step is referenced in the web service step that transmits the API's information.
  
-====== Sample Building JSON for SAP Business One Service Layer ======+====Databuilder==== 
 +{{ :bw2:bizweaverdatabuilder1.png |}}
  
-===== Workflow ===== +  - **Tool Name:** This is the name given to the tool. This will be displayed in the design canvas as well as the variable selector 
-This workflow builds two sections of a JSON structure for an SAP Business One sales order.  The sections are the header and the rows.  Each section consists of a SQL statement to gather the needed information and the DataBuilder to format the information.  Once the pieces are constructed, the output is sent to the SAP Service Layer API in the WebService step.  Other APIs will work in a similar manner.+  - **Description:** A space for notes about the tool. 
 +  - **DataSource Object:** This is the source of data for the Databuilder.  
 +    - This is required and is always a SQL/HANA tool. 
 +      - **Hint** the number of rows you feed into the databuilder from the subsequent SQL step determines the number of sections is output. This is more helpful when dealing with XML over JSON but keeps this in mind while generating JSON or XML the SQL statement and the database for the databuilder step. 
 +  - **Data Type:** This determines the type of file that is generated. 
 +  - **Add as an Array:** this will add an array to your JSON or XML. 
 +    - Arrays will be displayed in the Field Type drop-down  
 +    - If selected, it will insert the Array into the JSON or XML where it is located in the list. 
 +  - **Top-Level Element:** This clears the JSON or XML data if it is contained in a loop, so subsequent processes do not build on top of the previous cycle. 
 +    - This is generally used to update one thing at a time. If the API used can be updated with one large JSON or XML file, this option can be un-selected. 
 +  - **Create New Object:** The Object name of the JSON or XML structure. 
 +    - This can be used to create multiple levels of XML or JSON (see example below) 
 +  - **Preview Box:** A preview of the JSON or XML that is being built. 
 +  - **Name-Value Pair:**  
 +    - **Field Name:** The name of the name-value pair 
 +    - **Field Type:** This can either be String, Numeric, Boolean, or an Array built later in the workflow. 
 +    - **Value:** Normally a variable from the connected SQL/HANA step. These can also be hard-coded values. 
 +  - **Add Row or Delete:** {{:bw2:bizweaverdatabuilder2.png|}}  The down arrow will add a row below the current row. The "X" will delete that row.
  
-{{  :bw:databuilder1.png?600  |}} 
  
-===== Create Main DataBuilder Step =====+======JSON Example====== 
 +Below is an example of how to build a basic JSON File. The data is coming from SAP but this concept applies to any API being used to pass data to. 
 +{{ :bw2:bizweaverdatabuilder3.png |}}
  
-{{:bw:databuilder2.png?600|}}+  * Start by getting all the data needed and process that through a loop as we only want to process one record at a time in this example. 
 +    * **Note** Most APIs are case-sensitive. 
 +  * Once the data is sent into the loop you will limit it down to one record and add link it to the next step which is the data builder step 
 +    * Note Top-Level Element is selected. In this example, we are adding one record at a time
  
-This should always contain the first items of the data structure. +{{ :bw2:bizweaverdatabuilder4.png |}}
- +
-  - Enter a workflow step name. +
-  - Select the data source from the Datasource Object drop-down list. +
-  - Select the desired data structure type to be created (JSON or XML). +
-  - Leave the “Add as an array” checkbox cleared unless you know that the top-level object needs to be any array. This is uncommon. +
-  - Select the "Top-level element" checkbox, assuming this is the top level (header) node of the structure. +
-    - This option causes the "CumulativeData" step-variable to be cleared before starting to build a new structure. +
-  - Enter the name of the top-level object. +
-    - The requirements of the receiving API will determine this. +
-    - In some cases, a top-level object name is not used; e.g. SAP Service Layer and BigCommerce. +
-      - If a top-level object name is not used, leave the field blank. +
-  - Create a row for each value to be included in the structure. +
-  - Enter the output field name values in the right-side grid. +
-    - The Field Name value will need to exactly match (including case) the value the API is expecting. +
-  - Select the field type from the drop-down list. +
-  - Press <**F4**> to get a list of variables from the associated Datasource Object and select the appropriate value. +
- +
-Continue this process for all desired fields in the top-level object. +
- +
-The data structure being built is displayed in the left-side panel as elements are added. +
-The data structure cannot be altered in this area. The display is for information only. +
- +
- +
-===== Create the Lower-Level DataBuilder Step(s) ===== +
- +
-{{  :bw:databuilder3.png?600  |}} +
- +
-This will be the next section in the expected data structure. The process is the same as described in the “Main DataBuilder” step. +
- +
-For objects such as a set of rows, the display will only display a single entry. However, all data received as input will be included when the workflow executes.+
  
 <WRAP center round tip 80%> <WRAP center round tip 80%>
Line 79: Line 73:
 </WRAP> </WRAP>
  
 +  * Once the data is mapped add another loop to process all lines. This will be built as an array. The Create New Object is filled in as it is needed to identify the array.
  
 +{{ :bw2:bizweaverdatabuilder5.png |}}
  
-All objects other than the main one will require an Object Name. This must exactly match (including case) what is expected by the API. +  * Once the lines data builder step is built you will then return to the header data builder step and it will now appear in the field type dropdown. Select it and it will now add the array to the header data builder step once it processes.
- +
-===== Reference Lower-level Objects in the Main DataBuilder Step ===== +
- +
-Once the DataBuilder steps for the lower-level objects have been created, they can be referenced in the main DataBuilder step. This is what causes the entire data structure to be created when the workflow is executed. +
- +
-  - Click the “Field Type” drop-down in the main DataBuilder step. The names of the lower-level objects are displayed in addition to the standard field type values. +
-  - Select the desired object. +
-    - The Field Name and Value are automatically filled in. +
-    - The lower-level object is displayed in the left-side data structure. +
- +
-{{  :bw:databuilder5.png?600  |}}+
  
-Here is the resulting JSON that is sent in the API call.+{{ :bw2:bizweaverdatabuilder6.png |}}
  
-<code json> +  * Once the JSON is built it the Cumulative data variable from the Header step can be used and entered into the body section of a Web Service 2 tool to then be sent through the API.
-{ +
-"CardCode": "C20000", +
-"DocDueDate": "20190313", +
-"DocumentLines": [ +
-{ +
-"ItemCode": "A00001", +
-"Quantity": 9, +
-"LineTotal": 33.75 +
-}, +
-{ +
-"ItemCode": "A00001", +
-"Quantity": 7, +
-"LineTotal": 105 +
-} +
-], +
-"AddressExtension": [ +
-{ +
-"CItyB": "New York" +
-} +
-] +
-} +
-</code> +
-===== Reference the Output of the Main DataBuilder Step in the API Call =====+
  
-The output of the main DataBuilder step is used in the API call. This is done by using the “**CumulativeData**” variable from the Main DataBuilder step. An example is shown in the image below.+{{ :bw2:bizweaverdatabuilder7.png |}}
  
-{{  :bw:databuilder6.png?600  |}} 
  
 +====Databuilder Variables====
 +^Name         ^ Description         
 +|AddAsArry|True or False if the add as array checkbox is selected|
 +|CumulativeData|This is the collection of all data that was built in the databuilder|
 +|Data|This is the last run of data that was entered into the databuilder|
 +|DataSourceName|This indicates the name of the tool that is being used for the source data for the databuilder|
 +|DataType|This indicates the type of data your are building XML or JSON|
 +|ObjectName|Is the name of the top level of your JSON or XML|
 +|TopLevelElement|True or False if the top level element checkbox is selected|
 +| StepMessage|During a failure the reason for failure will be populated here |
 +| StepStatus        |True or False|
bw2/databuilder_tool.1608053855.txt.gz · Last modified: 2020/12/15 12:37 by akoehler

Page Tools

Except where otherwise noted, content on this wiki is licensed under the following license: CC Attribution-Noncommercial 4.0 International
CC Attribution-Noncommercial 4.0 International Valid HTML5 Valid CSS Driven by DokuWiki