Skip to main content

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:





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:





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

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:





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 datad'records']:


    my_string = x 'fields']d'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

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:





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 datad'records']:


    my_string = x 'fields']d'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 nRelationships] 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. 😦

Reply


Terms & Conditions