Page 1 of 1

Two way relationships for brewing DB

PostPosted: Thu Feb 16, 2017 8:36 pm
by greyman56
I am having some trouble getting my kefir brewing db to work. Here is what I am trying to achieve:-

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.

Thanks, Graham

Re: Two way relationships for brewing DB

PostPosted: Fri Feb 17, 2017 1:12 pm
by mjhanna
I would have only two databases: a bottle db and a batch db. Since a bottle has a one-to-one relationship to a batch, but a batch has a one-to-many relationship to bottles, then a batch record should have links to the many bottles. With grouped relationship fields based on the link the bottle record can show fields from the batch record, and the link field on the batch record can show all the bottles.

Hope this gives you ideas,

Re: Two way relationships for brewing DB

PostPosted: Fri Feb 17, 2017 9:02 pm
by greyman56
Thanks Michael,

I tried that first up, but it did not work because there can be 1 to 4 bottles per batch. And bottles get re-used once they are drunk.

I'll keep looking. Any further ideas welcome.

Re: Two way relationships for brewing DB

PostPosted: Sat Feb 18, 2017 10:11 am
by ddhsoftwareadmin
Hi Graham,

I was thinking the same thing as @mjhanna - it seems a normal relationship field is set up for the task. Each batch has a unique number or name in a text field, let's call it batch #. Then each bottle has an associated batch number also in a text field. Then as long as you have relationship set up on each table that points to the batch # of each table, you should be able to see the batch info for a bottle, and see all of the bottles for a batch number by tapping the corresponding relationship field in each database.

In addition, if you want to add a few bottles to a batch the process should be simple- just tap the relationship field in the batch table and add new records, each should have the batch # pre-assigned in the text field that is part of that relationship.

Let me know if I'm missing something and feel free to email me your databases if you want me to take a look!

Re: Two way relationships for brewing DB

PostPosted: Sat Feb 18, 2017 5:53 pm
by greyman56
Thanks Dave & Michael,

I see what your getting at. I was trying to use a batch x bottle db so I could avoid duplicating info about the bottle (e.g Volume) and I had a "removed" checkbox to use if the bottle was broken and then it would not appear in the bottle selection popup (thinking dbpopup). However, that seems to make it overly complex and I have not been able to get it working.

So using your approach, I will just avoid this extra info for the bottle and then the bottle db will have a record in it for every batch a bottle is used. Its kind of like the batchXbottle map db but with a popup or plain text field for the bottle number. I guess I'll need some good sorting in the bottle db to see the latest batch for each bottle at the top of the list view screen.

I'll have a play with that and see if it works in practise.

Cheers, Graham

Re: Two way relationships for brewing DB

PostPosted: Sat Feb 18, 2017 5:56 pm
by greyman56
A quick question Dave,

You mentioned using a text field for the batch number. Is there any way I can get some sort of auto-increment on that field?

I tried using a calculated field that added one to the previous record but it seems to change the batch number depending on the sort criteria. This would not be good for the relationship to bottles of course.

Thanks, Graham

Re: Two way relationships for brewing DB

PostPosted: Sat Feb 18, 2017 8:18 pm
by curtterp
greyman56 wrote:A quick question Dave,

You mentioned using a text field for the batch number. Is there any way I can get some sort of auto-increment on that field?

I tried using a calculated field that added one to the previous record but it seems to change the batch number depending on the sort criteria. This would not be good for the relationship to bottles of course.

Thanks, Graham


Anytime you use a calculated value in a field that relies on the previous record, the value will change when you sort the records on a different field, changing the order of the records.

If you only start one new batch on any given date, I would use a date field as your primary key, and set the date to when the record was added.

If you create more than one batch on any given date, I would add a serial number field with a popup that has 1 through 5, then have the primary key a calculated field with the date the record was added along with the serial number.

Re: Two way relationships for brewing DB

PostPosted: Mon Feb 20, 2017 5:45 pm
by greyman56
Thanks to all those that have responded and provided ideas / help.

I have a working solution now that uses the create date as the batch key, and using a relationship back to the batch db from the bottle db, I have the batch info displayed next to the bottle info. Using the create date for the batch key also made the sorting of the bottle db easy such that the latest batch for each bottle are displayed at the top of the bottle list.

And this is working perfectly on the windows desktop client (running under wine on linux).

However, on the android client (v4.6.316) the relationship from the bottle db back to the batch is not working properly. I have told the windows client to install the db again (deleted from android beforehand).

In list view, the relationship fields show no data (mostly). If I go into a record, I get 1 or 2 of the relationship fields showing data.

Very curios. I think I'll email the dbs to dave. Or should I attach them here?

Re: Two way relationships for brewing DB

PostPosted: Thu Feb 23, 2017 1:38 am
by greyman56
curtterp wrote:If you create more than one batch on any given date, I would add a serial number field with a popup that has 1 through 5, then have the primary key a calculated field with the date the record was added along with the serial number.

I am curious to know how that is done Curt.

I have tried a popup field as you suggest, then calc field that simply does an addition of the createDate and Serial fields (F1+F9) and this gives me a number (only output options are various numbers, date, and time). This number is the same for yesterday's 2nd batch (0=1st, 1=2nd) as for today's 1st batch. Mmmmm.

I tried adding createDate and createTime and got a number but I am suspect of its uniqueness due to the fact that it is not consistently ascending or decending. Big jumps in the number are caused by time changes, not date changes.

What am I doing wrong here?

Any other ideas on how to get a unique key fairly automatically, and one that does not change once created.

Re: Two way relationships for brewing DB

PostPosted: Thu Feb 23, 2017 5:04 am
by greyman56
Its OK, I have found a good discussion on unique static key values here:-
- http://www.ddhsoftware.com/forum/viewtopic.php?f=6&t=3310

I think I should get want I need from that thread.

Thanks.