Help

Change order in rollup function

6699 19
cancel
Showing results for 
Search instead for 
Did you mean: 
Manuel_Gnerlich
5 - Automation Enthusiast
5 - Automation Enthusiast

Hello everybody,

at first I set one column in my table 1 so that I can link it to items of records from a different table 2 (via multi-linking to record). Then I wish to fill a different column in table 1 through rollup function with another matching item of the same records from table 2. Problem is, that the order inside the rollup column is automatically opposite to the order in the first column linking to the record. I would like to have the same order in both columns, but I cannot set anything about the order connected to the rollup function.

Can anybody help, please?

19 Replies 19
Loic_Sanchez
6 - Interface Innovator
6 - Interface Innovator

Hello @Manuel_Gnerlich,

Are you using the ARRAYJOIN(values) or CONCATENATE(values) function for the aggregation function?
I experience the same behavior, the Rollup values are ordered in the opposite order of the multi-link column. I believe we could reach out to the support helpdesk in this case.

So apparently, the batch update Block can sort links in either ascending or descending order, so this is a workaround for now.

Hello @Loic_Sanchez

yes, I am using CONCATENATE(values). It is not working.

I got a message from support team.That bug is in to-do-list. We have to wait.

Joel_Ronne
4 - Data Explorer
4 - Data Explorer

I am waiting along with everyone else.

I’ve also come across this problem and would like to add my support for a resolution.

I have a related question, what determines the order of items in a multi-link field (and hence the opposite order in a Rollup of that field)? My guess is it seems to be creation date of the linked record, is that correct? It would be great to be able to influence the ordering, with options such as alphabetic, date modified, or matching the order in a view.

Matt_Krandel
4 - Data Explorer
4 - Data Explorer

Has anyone figured out how to customize the order or a roll up (preferably by date created)… This is imperative when trying to export this field as a data input for external applications…

What am I missing, it must be possible?

Olivia_Fagan
5 - Automation Enthusiast
5 - Automation Enthusiast

I also wish this could be fixed. My use case is for contact info. For example, my linked field will have “John Doe” then “Jane Doe”. Then I’ll have either a rollup or lookup field for their phone numbers and emails. In those rollup/lookup fields, the order of the contact info is for Jane then John instead of John then Jane.

This makes it a bit confusing, especially in page designer when exporting for 3rd parties.

SergioCodes
8 - Airtable Astronomer
8 - Airtable Astronomer

Still waiting for the solution :frowning: are we on the correct channel ? shouldn´t we report this on any other channel?

There are two issues here, and both have solutions:

  • Inconsistent order of linked records in different field types (linked record, vs, formulas, vs lookups). This has been fixed as described in this support article.

  • Sorting linked records in rollups. The order in the rollup is the same as the order the linked record. You can automatically sort the order using the Batch Update block, as mentioned earlier in this thread. (The main caveat is that linked records can only be sorted one way, so all rollups for the linked record must have the same sort.)

Hi @kuovonne ,
There is still a bug with the Batch Update. I have it sorting the linked records in ascending order (smallest to biggest) but this is an example of it after Batch Update: 10082, 22354, 22355, 8534, 9295, 9296
Expected: 8534, 9295, 9296, 10082, 22354, 22355

As you can tell the first digit for each appears to be in ascending order, but the entire number set are not. I have to correct many of mine manually.

Just letting you know. Great app otherwise.

@Stephen_Joslyn It looks like the linked records are being sorted alphabetically when you want a numeric sort. Can you share a screen capture of the primary field of the linked table? It may be that Airtable thinks that your primary field is a text string instead of a number.

The linked records are auto numbers field. I assume that’s numbers, no?

Dr Steve Joslyn BSc BVMS DipECVDI MRCVS MANZCVS
Chief Executive Officer
RCVS, European & Australian Recognised Specialist – Radiology, AI Comittee Co-Chair (ACVR/ECVDI)

address 32/152 St Georges Terrace Perth WA 6000
mobile +61 411 185 530

231b82dc-bbff-47fa-b550-9f7d19aaf5b0.png

fa91274b-6613-44ed-a32b-e6d08fb2923d.pngbef9529c-3824-454c-95de-54d262e60ede.pngThis e-mail message may contain confidential or legally privileged information and is intended only for the use of the intended recipient(s). Any unauthorized disclosure, dissemination, distribution, copying or the taking of any action in reliance on the information herein is prohibited. E-mails are not secure and cannot be guaranteed to be error-free as they can be intercepted, amended, or contain viruses. Anyone who communicates with us by e-mail is deemed to have accepted these risks. VetDB is not responsible for errors or omissions in this message and denies any responsibility for any damage arising from the use of e-mail. Any opinion and other statement contained in this message and any attachment are solely those of the author and do not necessarily represent those of the company.

@Stephen_Joslyn You are right. I just did some tests and the Batch update app sorts linked records alphabetically, even if they are numbers.

I suspect that this occurs because under the hood, the primary field of a record is stored as a text string in a linked record field, and this text string is being used for the sort. The app does not go back to the original primary field to determine the field type.

I have written an app “Sort Linked Records” that will properly sort linked records. This app is currently in review with Airtable and will eventually be released on the Marketplace, although I do not know when.

So in the end, is it possible to reorder this Rollup field for example? Logically my last test, should be on top, and not on the bottom of this field.

image

Also, I want to see my latest Test result, not the very first one.

image

It is possible, but not necessarily easy.

  • The Batch Update app will only do an alphabetical sort, and only based on the primary field. This requires reworking your primary field in the linked table.

  • My app for sorting linked records is still under review with Airtable.

  • Manually sorting each individual linked record field isn’t realistic.

  • That leaves scripting. I do have an automation script that will sort linked records, but it will only work on newly triggered records, not existing records.

That Batch Update extension looks super useful, thanks for sharing @kuovonne

As its unfortunately limited by only sorting alphabetically (and not via a lookup field of the value for example) I believe it’s limited in scope.

An example -Take the colours of a rainbow :rainbow: .

Perhaps I have a Colours Table, each record consisting of the colours name and their physical order in a number field (Red (1), orange (2), yellow (3), green (4), blue (5), indigo (6), violet (7) ).

I then have a “Rainbows” table, where each record may only consist of 3 colours - however, the colours must always remain in physical order from the Colours Table.

The Batch Update Extension is limited to only allow for the sorting of the colour in each record by name; blue, green, indigo, orange, red, violet, yellow.

So records are created could be (or they could be Batch Sorted to…) ;
[green, red, violet]
[blue, indigo, orange]

But how to process them in their correct physical order of;
[Red, green, violet]
[Orange, blue, indigo]

I so very much wish that lookup core sorting functionality is added to Linked Record fields. The fact that I can manually drag and drop my linked colours so that they’re in the order of a rainbow tells me that Airtable could potentially add a sort feature to this column… it’s crazy useful, and many users would benefit from it.

Airtable has not yet added this functionality. That’s why I wrote that automation script I linked to in my previous post.

Partyborn
6 - Interface Innovator
6 - Interface Innovator

This small but useful feature is absolutely missing!

Matt_Kennedy1
6 - Interface Innovator
6 - Interface Innovator

This hack works for simple values, w/out scripting, and could possibly be adapted for more complex purposes.