There are 2 main parts: batches and bottles. I want to access the data from both these angles.
I create batches in large jars that will be placed into multiple numbered bottles once the brewing is finished. So I want to enter the new batch, include the start date/time, and include the ingredients that vary between batches (fruit and sugar type mostly).
Then when it comes time to harvest, I want to enter a variable length list of bottle numbers that now contain that batch. Thinking sub-records here.
Then later, when we are drinking the Kefir, I want to be able to go to the bottle number and see which batch, its ingredients, and the brew time that produced the drink.
I figured that I should use a bridging db that maps bottle numbers to batch numbers. And I thought that if I created a relationship between this and the batch db, I could display some fields from the parent batch db and save another click through to the batch db. Great in theory, but having trouble here.
So I have created a batch db as follows:-
- Batch number as unique field. Used for linking.
- Each batch has start and harvest date/time fields so I can track when to harvest the brew.
- A calculated field produces the time it brewed for.
- There are fields for ingredient choices and jar# implemented as pop-ups.
- There is a "bottles" button (link or relationship?) that links to BatchInBottle db.
I created a bottle db as follows:-
- Bottle number as unique field.
- Volume field as pop-up and removed field as checkbox.
- A "batches" button (link or relationship?) that links to BatchInBottle db and shows the latest batch.
Then for the bridging db BatchInBottle:-
- Batch field (linked) to connect to the batch db.
- Value fields (relationship) to grab data from the batch db.
- Bottle field (dbpopup) to connect to the bottle db.
- A value field (relationship) to grab data from the bottle db.
So here is my main problem: I have managed to get the batch and BatchInBottle child working OK. I can enter multiple bottle records for a batch OK, but I have not yet got the extra value fields from either the batch or bottle db. And when I choose batches in the bottle db, I get no matching records.
Question 1. Am I making this too complex using the bridging db?
Question 2. What relationship should I use to see all records in the batchinbottle db for a batch number as well as being able to see all records for a bottle number?
Any other ideas on how to achieve this?
I can provide extra details on fields etc if need be.