Hey ​@MikeMitch,

Under field MTOX (linked record field) you need to link whatever records need to be rolled up (first three in your case!)

Hope this helps! 

Thanks for the reply Mike…..but not so sure I understand ...when you say link the first 3 records in this case...link how, link where ??  Put a PRIMARY ID value ifrom MTOX) in the MTOX column itself just on those 3 records ??

Big Thanks Mike - seen your name in the community...real big help….if I get to persuade a customer to pickup on this system when finished are you up for paid assistance to (principally) write automation scripts ?? hourly rate ???

Thanks

Mike

OK - I see ..in the MTOX table - under the airtable added column….I have to add the ID value into the column…..that is a bit of a faff to do manually…..hmm…..how can I automate that so it is done automatically….as far as I can see I cannot do it in the new airtable added column as a copy formula (can I?)…...so….basically…...can I add an automation on a record insert that pokes a copy of the ID Primary value into that column ?  (and a heads up on that script would be wonderfully appreciated!!!!)

Yeah Airtable doesn’t use ID associations in this way, you establish links between tables manually (or with automations).

An easy manner for batch processing to do this would be to simply copy/paste the ID in the MTOX table (in your first screenshot) in the linked record field in that table, which would associate it with same named records in the other field or create new records with that ID name if none exist. 

You could also set an automation to run nightly copying ID field name into the linked record field, there are a bunch of different solutions. For an automation version you need to be aware that if you trigger on field update it’ll trigger as you type so unless the full ID number is always being input at once (i.e. copy/paste, API, that kind of thing) it can be less than ideal. 

Hi ​@MikeMitch,

You could set up automations but it would probably be better to adapt your table structure to suit how Airtable prefers to work, otherwise you’ll be fighting it the whole way.

So in your case in that first table you have multiple records with the same ID. So this could be say a Purchases table where a number of Purchases have the same Customer ID. Rather than printing the Customer ID in a text field you’d create another table called Customers and add one record there with the ID e.g. 12345 and then a linked field to the Purchases table. You could then add your 3 purchases and see that each one would contain the linked Customer ID.

Now that you have the 2 tables linked you can add lookups and rollups to summarise data. For example in the Customers table you could add a field called Total Spent and roll up the Purchases > Amount field to get a total value of all purchases from the Purchases table that have that customer ID.

Apologies if you know all this or I’ve misunderstood, but it will be much better to focus on the data structure than have to hack a solution with an automation! 
 

Hope that’s useful mate,

J.

Hi J…….Thanks for the reply...let me amplify - here is what I am trying to do :

In one table I have a 100 records - basically 10 drawings with 10 line items each - each record has one material code of 1,000’s available…..so lets say 5 drawings mention Material Code A and each has a different QTY code…..what I want is the TOTAL QTY of Material Code A across all drawings…..so I can go buy…..

So - I set up a new table - Material Summary - have a primary ID (only 1 record) is Material Code A…..and what I am looking for is to to see the total quantity against Material Code A...and B, C etc

As far as I can see - no different data structure is possible.

The stupid thing is Airtable gives me the total I want in GRID VIEW on to of the groups (sorted and grouped by Material Code)…..so I see the total but cannot make use of it - it does not even get copied out as a csv export.

So that is the task - how do I get an automated, dynamic total by Material Code…..and I have another 10 tables where I need this total…...i.e Material Code A has 123 needed on all the drawings, I have bought 95 of them so far, I have delivered 46 of them and used in construction 15 of them so far…..

Hi ​@MikeMitch ,

For some reason I only got a notification about your reply above this morning although it says on here it was a month ago!!? Did you get this sorted?

J.

Hi....thanks for getting back to me.

I gave up on roll up .....I imagined that once you had built the relationship you could sum a column of figures in the other table....but....I learned that for rollup to work you have to add manually every single record .....it adds up only those records that are forced and stored...awful.  I wrote a JavaScript batch in the end to sum that column. I could see no way to create a formula that would copy every record into the rollup list....hmmm.Big thanks.

Mike....your friend in SW London UK.

Ah no worries ​@MikeMitch - SW London! We used to live in Ealing but moved out to Bath which is where we’re based. Drop me an email at j@minnow.tech - be good to connect.

Hi,
Actually, it’s very easy. 
Every linked field to other table and each link you set in it, will have a respective link field (auto-created) with all links - it’s like a ‘portal’ between two tables seen from both sides.
That’s how it works:

Just create new linked field (to a new table), then copy-paste whole ID field into that linked field
In the new created table you will have a primary field of all unique IDs, and now you can set lookups/rollups etc.
The only thing you need to to afterwards - to provide auto-linking for every new record you enter in your table. 
You can do it by automation, for example ‘when record matches condition’. Important that it should trigger not too early, I mean when ID is entered in full. For that, you should include other column in it,
Like ‘When
- ID is not empty
- {Link Field} is empty
- QTY is not empty
then action ‘Update record’ will launch to put ID into {Link Field}
(without QTY condition, automation will launch right after you enter first characted into ID field, which is not what you want)