Automated table with distinct combinations of linked records

[Jonny_Longden]

I have 2 tables: table A contains groups and table b contains items. 1 record in table A can link to many in table B, but also 2 records in table a could link to the same record in table B.

I want to score the records in each of the two tables, in order to understand and prioritise which unique combination has the highest score. So, I need a third table which is automatically generated and where each row is a distinct combination of a single record from table A and a single record from table B, where they are linked according to the above.

In SQL this would be ‘full outer join’ of these 2 tables.

Is this possible?

[Mike_McLaughlin]

You can use a Lookup in A to list out all of the values from one filed in B, and a function+sum to add up all the values in that lookup.

You can’t create a 3rd table automatically, but you may be able to get the info you want anyways. My interpretation is that you want a 3rd table that has every possible combination of (Ax,Bx) - which I doubt is your actual intent. Can you mock up(at least somewhat) the tables in airtable and post what you want this to look like?

[Chad_Smith1]

Hi Mike, I am actually looking for that scenario (Ax,Bx)

For the image attached, I’d like to see 4 total records created on a new table.

[Alex_Whitton]

Hi also chasing this - can do in power query easy enough but would like it to be in a live table, so when I upate the data, each unique combination updates also - or purposefully limited in Airtable as the record count can get high (my list is 27 rows x 634 rows - final unique number of rows is 17,188)

Thanks in advance