Enriching details from another table in one table

[TSteimer]

Hi there - thank you for your comments and help in advance.

My setting: I have 2 tables in one base, containing product information.

My goal: to enrich one table (main-table) with the details of the other table (detail-table).

context: The main table contains the product name (e.g. Solvent black 27). The detail table contains that name as well (Solvent black 27) - but additional details, like e.g. color index.

How can I enrich the data in the main-table with further details of the detail-table.

I tried to do a lookup, of course, but the I dont find a way how I can ensure, that the lookup field always has the same name as the name of the main-table.
To make it more understandable: In SQL I would be a "clasical" join.

Thank you for your support

[Kenneth_Raghuna]

Short answer: You can create an automation that links the records together if the product name is the same. Then you can use lookup fields in the main table to add data.

Longer answer: This seems like it might not be the most efficient base design. Is there a reason that you have two tables that reference the same information? It might be a better strategy to have all of the extra info from table 2 in table one, but use your views to strategically hide and show data as needed.

[TSteimer]

Thank you so much - great input.
You are right, the base-design is not the best - my final goal is to have 1 table after I've matched / enriched the data.

What would you recommend out of your experience - does it make sense to create your described automated lookup - then copy the data and insert it into a new table or how would you start to make one table out of these 2 tables?

Thank you for your support!