Automated table with distinct combinations of linked records

#1

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?

#2

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?