Create integration data structures in depth 编辑

When creating microapps you may find the requirement to access tables in your target System of Record that are separated by more than two levels from the parent table. Owing to limitations currently found in the HTTP integration, a solution around this is possible.

This article provides information on how to access tables in your target System of Record when this use case arises. This solution is not straight forward, but if you follow the description below, you can create deeper data structures.

Use case

You want to build a microapp that allows a user to approve a request on ServiceNow. To use this microapp, the user must be able to:

• receive and open a notification
• receive a page with a list of items to approve
• see each item’s details
• view who sent the request
• approve these requests

The details needed for building an action or page for each of these steps are stored in tables retrieved via endpoints. However, the table with the data for the approver (the table with data containing the item list) is further than two tables apart from another data locations.

Solution prerequisites

To create this workaround you need to use a combination of child API call-chaining and table merging described in Configure the integration.

Prerequisites:

• You have defined your end-to-end use case with the understanding of what must be run in your microapp and what information your end user views and actions.
• You have created the endpoint to return the table data you need from your target System of Record.

Note:

Configured tables and primary keys cannot be edited after initial set-up.

• You have familiarized yourself with the add additional API calls and merge tables features in HTTP integration.

View and build your data structure

When building your microapps, the conventional model supported by the Microapps Platform is for between tables separated only by one step away (N+1 model).

You can see this by checking your integration configuration set up during HTTP integration. For example, you can see that Ticket is one step away from tags, but neither is directly connected to comments_w_users.

Some relationships are created automatically during endpoint configuration, and you see them in the table reference of the integration. However, for this specific use case, you must create some manual definitions to create the relationships between tables.

Data Structure Merge Strategy

When designing the data structure to build your microapps in this scenario, consider the following important points:

• Choose the parent API call depending on the data structure you must achieve to build your microapp. Consider how to use incremental sync for your data set and the API that will return only the updated data structure. This API must be set as the parent.
• Where possible, configure only one-to-many rather than many-to-one. Many-to-one configurations result in repetitive API calls and will impact data sync efficiency.
• Consider the source of the notification you require and how it is configured so that your user will receive only one notification in cases where table merging is configured and data can be duplicated.
• The parent API must always be the most volatile object.

Use the following merge table methods for the specific cases:

• One-to-one - Use Merge as detail. This results in only one record stored in the database that contains all attributes from the parent and child APIs. The child values are used when the attribute is present in both parent and child API call.
• One-to-many - Use Merge as sublist. All parent attributes are stored with every child record.
• Many-to-one - Usually many-to-one is not a scenario for Child API calls. You must consider the most suitable method, whether to use table merging or manual setup of the entity relationship (no merging applied). If no merging is applied, only the first child is stored, other children are ignored due to duplicated primary key detection.

Define manual relationships

To define relationships manually, there must be a common column in both tables to use to build a relationship. You can check this in the tables and relationships section of the data integration. If two separated tables have a column in common, you can manually create a relationship between them. If there is no common column then you must create relationships in the example shown in the following procedure.

Advanced use case

If you cannot create data structures beyond n+1 using the common column relationship you can create a flattened data structure using a combination of API child calls and table merging. The general ‘advanced use case’ follows the basic principle of:

1. Set up your integration.
2. Edit you table structure.
3. Create your API call chains from your primary table to the table you want to combine to.
4. Merge tables via table merging in a top-down method (for example, parent to child).
5. When your large table is created, return to the parent table and set ignore for all table entities.

For example building a microapp with request-list>item list>item details>approver, the microapp must be able to show the request and detail for the approver - but is not able due to current limitation of only n+1 relationships. You can use the table merging feature to fix this problem.

While building your data endpoint, propagate the table structure from the parent data endpoint (request-list) to the child endpoint (approver) within the item list.

You can then set to merge everything from the parent data endpoint to this child API using a table merging strategy. The result is that everything that was in the parent table, displays in the data structure of the child API (item-list).

Configuring in this manner results in three levels of data being contained in one large database table. This new table can be used to build the page as per the use case defined when you started to build your microapps. This method can be used for as many levels are required.

API Child Call and Table Merge example

The following example illustrates the general workflow of creating a table structure to reach data beyond an n+1 relationship. Each individual use must be built based on the individual use case you want to build for your microapp. Ensure you are familiar with your target integration System of Record and you have a good understand of the outcome of your structure when using this method.

Create API call chain

1. Navigate to the Data Loading page for your integration:
2. Add as many child API calls from your root endpoint to the destination child endpoint as required: When finished, you can view your data structure on the main Data Loading page.

Merge the parent to children API calls

Now merge the root / parent table to the child endpoints in sequence until you reach the destination table:

1. Select edit from the ellipsis menu for your integration.
2. Select edit from the ellipsis menu for the child endpoint of your root integration.
3. Navigate to the bottom of the Edit Data Endpoint page and select Edit to select configure table merging: Repeat this process as many times as needed for each child table in the sequence until you reach the destination table that will enable you to build your microapp.

Ignore repeated API calls

When you have finished the merge ‘chain’ return back to the root endpoint. Follow these steps:

1. Select edit.
2. Set all tables to the Ignored status: This stops the table from loading twice into the cache and therefore improve performance. You can now use your chained / merged table to build your microapp.

Important Considerations

Always consider the following when building your data using this method:

• All parent and child API calls have their own data structure.
• These structures are different sets of data.
• If the data structure is merged (from parent to a child), all attributes show up in the child data structure.
• If the full chain is kept, the data is stored “twice” - ensure that the data structure in the parent call is deleted completely as every attribute appears in the child data structure.
• Don’t leave the parent API call with the data structure as is - delete it where possible.