Help

Re: Linked Record Field Sorting Issue - Data From Zapier

457 0
cancel
Showing results for 
Search instead for 
Did you mean: 
AlliAlosa
10 - Mercury
10 - Mercury

Hi there!

I’m going to do my best to describe this issue I’m running into… It’s certainly a strange one and pretty technical. I tried contacting both Airtable and Zapier, and neither were able to figure this out.

I have a Base from which I generate emailed reports via Zapier. The reports are grouped by company, and i want the companies to be in alphabetical order when the report is generated.

For any given linked record field in Airtable, the order of display values it returns is inherently reversed. See below example:

Ex1

I have a zap with a purpose of keeping a linked record field sorted in reverse alphabetical order, so that my reports are always sorted correctly. One of the steps of the zap is a python code snippet I managed to piece together, which takes the display values ({Display Values} in the above screenshot), sorts them in reverse alphabetical order, and inserts them back into the field {Linked Values}.

This worked perfectly up until a few months ago. The data from Zapier has not changed - it’s still in reverse order when it leaves Zapier, i.e. “C, B, A”. Previously, when it hit Airtable, it would retain the reverse order in the linked record field as expected, and display “A, B, C” in the rollup field which populates my report. Now, when this same string hits Airtable - it’s somehow switched again; populating the linked record field as “A, B, C” and displaying as “C, B, A”. Even stranger, if I copy and paste this data into the same field in Airtable, I get the result I’m looking for.

To better explain, the below screenshot is labeled with how the data was populated:

Ex2

Anyone have any thoughts? Trying not to tear my hair out over here :slightly_smiling_face:

2 Replies 2

Hi @AlliAlosa - I don’t have an answer to your specific problem, but does the display field need to be a rollup or is that just a means to an end? If it doesn’t need to be a rollup could you do this:

Screenshot 2020-01-11 at 21.25.49

Create a string field from your linked field ({Linked Field} & '') and then use your python code to sort this string value. This has the advantage of correctly sorting the linked records whether they are in order, reverse order or random order.

My script for this is:

import json
import requests

app_id = 'YOUR APP ID'
api_key = 'YOUR API KEY'

get_url = 'https://api.airtable.com/v0/' + app_id + '/Table?maxRecords=100&view=Grid%20view'


get_headers = {
    'Authorization': 'Bearer ' + api_key
    }
patch_headers = {
    'Authorization': 'Bearer ' + api_key,
    'Content-Type': 'application/json'
    }

response = requests.get(get_url, headers=get_headers)
data = response.json()

for x in data['records']:
    my_string = x['fields']['Linked Field String']
    print(my_string)
    no_spaces = my_string.replace(" ", "")
    print(no_spaces)
    my_list = no_spaces.split(",")
    my_list.sort()
    print(my_list)
    sorted = ', '.join(my_list)
    print(sorted)

    id = x['id']
    print(id)
    data = {
        "fields": {
            "Linked Field Ordered": sorted
        }
    }
    patch_url = 'https://api.airtable.com/v0/' + app_id + '/Table/' + id
    patch_response = requests.patch(patch_url, headers=patch_headers, json=data)
    print(patch_response.text)

Take out the “prints” for Zapier, this was just for debugging purposes.

Hope this helps!

JB

Thanks for the input @JonathanBowen! For the purpose of this post I dramatically simplified the structure of the base… sadly, I need the display field to be a rollup as the results and associated linked record field drive the rest of the report.

For example, let’s say I’m preparing a report for Purchaser A. The report lists purchases made by Purchaser A in Companies A, B, and C. The linked record field that I’m trying to keep sorted, links to a junction table, called [Relationships] where each record symbolizes all purchases made by Purchaser A in a particular Company (so for this report, it would be three records, “Purchaser A in Company A”, “Purchaser A in Company B”, and “Purchaser A in Company C”). On the [Relationships] table I have rollups of each purchase made, and those rollup “groups”, per-say, are the actual values I’m trying to display alphabetically in the report.

The reports can be sent at any time, so I need a way to maintain the sort order of that field without having to manually drag the fields around. :frowning: