As the title says, I'm trying to add a materials inventory to the research notes starter solution. Let me present a couple of images and try and walk you through what I'm trying to do. Should be simple enough but I'm struggling to get the relationships to work.
Here is the relationship graph (complete with some stickies and labels to explain things better)
The red box is the original starter solution and as such does what it should
The green box are a stack of layouts (included in the RG for discussion purposes) where laboratory materials (oligonucleotides, antibodies, compounds, reagents) are added to Materials Inventory::Table. This works as expected.
The blue box contains two tables; Materials Inventory::Table and Research Materials::Table.
- Materials Inventory::Table, as mentioned above, stores all information about all materials. This information comes from layouts in the green box (again these are here for illustrative purposes, I understand their presence in the RG is nonsense)
- Research Materials::Table, is almost a clone of Materials Inventory::Table. It contains all the same fields as Materials Inventory::Table, but has extra relationship fields used by Research Notes::Layout. The purpose of this table is to store materials which are related to a given Research Notes::record.
A little about the layout (as this may be part of the problem). Here is the Research Notes::Layout showing how I envisage a materials being attached to Research Notes::records.
Now that that's out of the way I can ask a few questions.
1) The drop-downs do not populate as there is no relationship between Research Notes::Table and Materials Inventory::Table. Makes sense. But...how can I get these drop-downs to fill with the respective field values from Materials Inventory
2) I am concerned about the bulk duplication of data that will occur between Materials Inventory::Table and Research Materials::Table. Is it possible (or even proper) to just store Research Notes::Research ID and Materials Inventory::pk_MaterialsInventory in Research Materials::Table and have the portal use these to pull the correct records from Materials Inventory (to display in Research Notes::LayoutPortal)
My second to last foray in this community highlighted that I think about database design in a very non-database kind of way. I will not be surprised if one of you indicates that the whole thing is a mess and there are much better ways to do what I'm trying to do. I'm open to suggestions.
Just to reiterate what I'm trying to do in 'customer speak' (maybe my lay speak makes more sense than my fmp speak): I would like a place to store all my research notes, documents and media, grouped together by 'project' (this is the original fmp starter solution). In addition to notes etc, I would also like to store details of any 'materials' that were used in a given project. I don't need to see all the 'materials' information all the time, just the main things like the category, type, name...etc (whatever is already in Results Notes::Portal.Reagents). But I will still need to be able to store and access this information (hands over a list that looks a lot like the Materials Inventory table) in case I need to order more of a material or I forget the details a material I haven't used in a while. I don't want to be jumping from page to page all the time so if we could have some drop-down menus for entering which materials were used in a project that would be great! Also.........just kidding
Hopefully this makes more sense than the last relationship related topic I opened. That was a nightmare for everyone involved . I'm on the cusp of writing work around scripts again which is a sure sign that I'm probably approaching things the wrong way (fetching and copying key fields from Materials Inventory to Research Materials).