I’ve got a table with the primary field being materials. I also have a projects field that will require the use of some of these materials. Each material will be given a serial code that is universally unique and a product code that is unique to each project (materials can be used for multiple projects and the product code will change for each project)
How to set up my base to have a project view where I can view and edit the product code field but only have that record be relevant to that project.
If I’ve understood this correctly you should be able to get your result as follows:
Table 1: Materials
Have the primary field be the unique serial code, add any other fields you need.
Table 2: Projects
Have the primary field be the project name (or some other identifier).
Table 3: Project items
Have the primary field be the product code. Then you can have a secondary field which is a linked field which only accepts one record and links back to the ‘Materials’ table. Then have another linked field that accepts one record and links to the ‘Projects’ table.
You can then choose to also sync across to the ‘Project items’ table (via Lookup fields) any details you want to be universally shared across all project materials linked to the same master ‘material’ record. All the other fields can be customised for that Project specific version of the material.
Hopefully that makes sense and answers your question!