Sync records

Build record types to sync data into Middle

A record type is considered an input in Middle; it defines what sort of data your app will store and use. Record types should match the sort of records you wish to store and execute logic on.

Let’s talk about a simple example. A store that sells drinks and wants to send an email to anyone who buys a special drink. We would create the following record types:

  1. Customer - a record type for the people who shop at the store.

  2. Sale - a record type for each purchase at the store.

With the Sale record type we can monitor all sales. If a sale includes a special drink we can look up the Customer associated with their sale and get their email address.

Your record types will likely closely match the GET endpoints that the system you are integrating to offers.

To create a new record type, click + Create and fill out your record type name and description. It can be helpful to include links to the endpoint documentation in your description.

Attributes

Attributes define what fields you want to store in your record type. All attributes you define here are required in order for the record to be valid and accepted by Middle.

An attribute is composed of four parts:

  1. Order - the visual order of the attribute in the list. This is only for looks.

  2. Type - The type of data that will be stored in this attribute.

  3. Key - The key for the attribute. Middle will require that this key exist when your python script outputs a record. These will likely closely resemble the keys returned by the output object from the system you’re integrating with.

  4. Title/Description- The name/description of this attribute when the record is seen in workflows. People who use your app will see this name/description.

To create an attribute, click Add Attribute.

As you create attributes, you can use the insert above or below buttons on the right hand side to insert a new attribute, or use the delete button to remove attributes.

Attribute types

Primary ID

Each record type is required to have exactly one primary_id configured. The primary ID should be a consistent and unique value for a given record. When you send a record to Middle it will either:

  1. Create the record if it is a new primary ID.

  2. Update the record if it has seen this primary ID before.

For the Customer record type, you'd want to define the primary_id as customer_id.

Foreign keys

A record type can also have a foreign key to another record type. You can choose what record you are keying to from the Key to? dropdown.

In our example, a store ultimately wants to email customers for buying a special drink. A Sale at that store would need to have a foreign key to a Customer in order to understand who made the purchase.

Let’s create a new record type for Sale, define its primary_id, and create a new attribute for a foreign key to Customer.

You’ll need to provide a Related Key, which will be used for linking records to Middle workflows.

This key must be unique and any modifications to the key can affect existing workflows. You will also provide a Related Display Name, which will be visible to the user when viewing related records from an account.

Additional attributes

You can continue to add additional attributes that represent the data that you would like to be stored within this record.

  • String: A string attribute is a simple attribute that contains text.

  • Number: A number attribute is restricted to numeric values only. Including non-numeric characters will cause a validation failure and the record will be rejected.

  • Boolean: A boolean is a simple true/false value.

  • ISO Date: An ISO Date is a single date on a calendar. Middle uses ISO 8601 format. When outputting a record to Middle either an aware python datetime OR an ISO Date String is required for this type.

  • ISO Datetime: An ISO Datetime is a combination of a date and time. Middle uses ISO 8601 format. When outputting a record to Middle either an aware python datetime OR an ISO Datetime String is required for this type.

  • Object: An object is a special attribute that contains a set of other attributes. In python terms, this attribute must have a python dictionary with the keys/values of the attributes inside the Object. You can also determine whether that attribute is an array of these items using the Array toggle. Let’s look at an example for an object within the Sale record type assuming multiple items are purchased. In python terms, the returned object may look something like the below code block. Each of these items are represented by a dictionary (object) containing their sale_item_id, name, and price. We can define an object with the key items and turn on the Array toggle to indicate this will be an array of objects. We then define the keys found within the nested object.

{
“items”: [
{
“sale_item_id”:13JO9798DS”,
“name”: “Protein Smoothie”,
“Price”: 6.99
},
{
“sale_item_id”:9IUO2392US”,
“name”: “Bottled Water”,
“Price”: 2.99
}
]
}

Poll syncs

A poll sync determines how Middle retrieves and defines this record type’s data. Middle handles three main types of poll syncs:

  1. Complete Transfer - return all records

  2. Recent Record Lookup - yield records created or modified within timeframe

  3. Primary-ID Lookup - yield records that have the given primary id(s)

Poll syncs handle the initial transfer of a record to Middle, as well as any updates to a record.

Create a poll sync

To create a new poll sync, click + Create New Poll Sync.

On the next page, you will be able to configure your new poll sync for your record type. Let’s go over some of the inputs and toggles on this page.

  1. Sync active and available for consumption toggle - this can be switched to Active when your sync is configured and you are ready for Accounts to start syncing this record type.

  2. Name: The name of your poll sync.

  3. Interface Style: The type of poll sync you would like to use. Choosing this interface style will determine what further settings can be configured on this page.

Below these settings, you will see the Script Editor. This is where you will write your python code to return or yield records for this record type.

Let’s walk through some basics for each poll sync type.

Complete transfer

A complete transfer is used when you want to return all records for a record type.

When selecting Complete Transfer, these additional fields will show up when customizing your poll sync.

  1. Continuous Polling - toggle for determining if you would like to continuously sync. When Enabled, you can continuously perform complete transfers over an interval of time.

  2. Automated Related Record Lookup - toggle for determining if you would like to perform related record lookups. When Enabled, you can trigger a complete transfer when an unseen record is found.

Let’s think about some use cases where these toggles could be utilized:

  • If an API does not support syncing a record type based on an updated or modified time, a continuous poll can be used in some cases to continuously update records.

  • Sometimes we complete an initial complete transfer of a record type, but an additional item or type is added by the source.

Now let’s look at a simple example of a Complete Transfer script. Let’s say that we created a record type called Location and we want to return all Locations.

    # Here's sample code to get you started
    resp = requests.get('https://my api url', 
                        headers={'Authorization': f'Bearer {auth["access_token"]}'})
    # 500 errors tend to be transient. Try again later.
    if resp.status == 500:
        # throw it back at the end of the queue 
        raise Restart(msg="500 error, try again later")
    if resp.status != 200:  
        print(f"unknown error encountered, {resp.status}")
        break
    body = resp.json()
    if not body['recs']:
        print("finishing gathering records, ending now")
        break
    for rec in body['recs']:
        yield {
            'pk': rec['PrimaryId'],
            'sales': [{'price': s['Price'], 'item': s['ItemId']} for s in rec['Sales']],
            'order_dt': rec['OrderDateTime'],
        }py

We want to write a script that grabs the location data from an app's location endpoint, and we yield each location record based on the results returned.

Recent record lookup

Recent record lookup poll syncs are used when you want to yield records from within a certain timeframe. This usually functions based on the creation or update datetime of the type of record you’re trying to sync.

When creating a recent record lookup, these additional fields will populate to help configure the sync:

  1. Max page timedelta - the maximum time frame to limit a sync to. By default, a recent record sync will be chunked in 10 day spans (any time frame bigger than 10 days will be broken into 10 day invocations).

  2. Continuous max sync back - we will never sync back further than this amount of time.

  3. Sync overlap - how much should syncs overlap, ensuring all data is collected. It is recommended that this value is at least half of the continuous poll interval.

  4. Continuous Polling - how often we should sync. When enabled, we'll poll for this record type throughout the day, based on the continuous poll interval.

In many cases, you will leave these as the default values. However, let’s think about some circumstances where you might configure differently:

  • If you want to sync the last year of sales, you may consider expanding the max page timedelta. By default, Middle will chunk one year of data into 10 day spans; Middle only allows 20 invocations at once, so expanding this will allow you to reduce your number of invocations.

  • If you want to poll classes throughout the day, once an hour, you can expand the continuous poll interval to an hour.

Let’s take a look at a simple code to return Sales from in a certain timeframe:

# Here's sample code to get your started
resp = requests.get ('https://api.coolnewapp.com/v1/sales'
                     headers={'Authorization': f'Bearer {auth["access_toke"]}'},
                     params={
                         "createdDateStart": args["last_modified_from_date"],
                         "createdDateEnd": args["last_modified_to_dt"]
                     })
                     
 if resp.status != 200:
     print(f"unknown error encountered, {resp.status}")
     break
     
 body = resp.json()
 
 for sale in body.get('sales', []):
     yield sale

The “gather_records” function in Middle uses a dictionary called args. For recent-record lookups, this dictionary is represented as:

{
'last_modified_from_dt': datetime, 
'last_modified_to_dt': datetime
}

You can use the args dictionary to pass in last_modified_from_dt and last_modified_from_dt as parameters or to filter your results, depending on the API you are working with.

For example, if you are continuously syncing the last hour of Sales, with a half hour overlap, the last_modified_from_dt would represent 1.5 hours ago, and last_modified_to_dt would represent now.

Primary ID Lookup

A Primary-ID lookup is used when you want to look up a record(s) by a primary ID. This type of lookup is often used when a foreign key is encountered in another record that has not been seen before.

For example, a Sale record could have a Sale Item that has never been seen before. A primary ID lookup built on the Sale Item record type could then automatically be triggered to look up the individual sale item by its ID.

When selecting Primary-ID Lookup, these additional fields will show up when customizing your poll sync.

  1. Max number of primary keys per call - max number of primary keys that can be used to invoke a lookup

  2. Automated Related Record Lookup - toggle for determining if you would like to perform related record lookups. When Enabled, you can trigger a complete transfer when an unseen record is found.

Some API endpoints allow you to do batch lookups by IDs, while others only allow requesting one ID at a time. Within Middle, a Primary-ID lookup can be triggered by up to 100 unseen keys by default. If you are concerned about timeouts or the amount of data being returned by your function, you may consider reducing the max number of primary keys per call.

The “gather_records” function in Middle uses a dictionary called args. For primary-ID lookups, this dictionary is represented as:

{
“primary_id_list”: [str, str, str…]
}

The primary_id_list contains all the primary keys that were found, and can be used as is to invoke a GET request that handles bulk ids, or can be iterated through to perform an individual lookup as shown below.

# Here's sample code to get you started
for sale_item_id in args["primary_id_list"]:
    resp = requests.get('https://api.coolnewapp.com/v1/sale_items/{sale)_item_id}',
                        headers={'Authorization': f'Bearer {auth["access_token"]}'}
                        )
                        
    if resp.status != 200:
        print(f"unknown error encountered, {resp.status}")
        return
    
    body = resp.json()
    if not body.get("sale_item_id"):
        print(f'No sale item found for this {sale_item_id}')
    else:
        for sale_item_id in body.get('sale_item_id')
            yield sale_item_id

Last updated