Two way relationships for brewing DB

For HanDBase related topics that are not specific to one platform.

Two way relationships for brewing DB

Postby greyman56 » Thu Feb 16, 2017 8:36 pm

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
Cheers, Graham
greyman56
 
Posts: 36
Joined: Wed May 27, 2009 10:53 pm
Location: Veteran, Australia

Re: Two way relationships for brewing DB

Postby mjhanna » Fri Feb 17, 2017 1:12 pm

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,
Michael
(Only a user and not affiliated with DDH Software, but working with computer databases for over fifty years.)
mjhanna
 
Posts: 156
Joined: Tue Oct 15, 2013 10:56 am
Location: New Mexico

Re: Two way relationships for brewing DB

Postby greyman56 » Fri Feb 17, 2017 9:02 pm

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.
Cheers, Graham
greyman56
 
Posts: 36
Joined: Wed May 27, 2009 10:53 pm
Location: Veteran, Australia

Re: Two way relationships for brewing DB

Postby ddhsoftwareadmin » Sat Feb 18, 2017 10:11 am

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!
ddhsoftwareadmin
Site Admin
 
Posts: 237
Joined: Tue Dec 09, 2008 2:13 pm

Re: Two way relationships for brewing DB

Postby greyman56 » Sat Feb 18, 2017 5:53 pm

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
Cheers, Graham
greyman56
 
Posts: 36
Joined: Wed May 27, 2009 10:53 pm
Location: Veteran, Australia

Re: Two way relationships for brewing DB

Postby greyman56 » Sat Feb 18, 2017 5:56 pm

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
Cheers, Graham
greyman56
 
Posts: 36
Joined: Wed May 27, 2009 10:53 pm
Location: Veteran, Australia

Re: Two way relationships for brewing DB

Postby curtterp » Sat Feb 18, 2017 8:18 pm

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.
Have a good day

Curt
I am not a DDHSoftware employee, just a long time HanDBase user.... from Palm to Windows Mobile to Android, to iOS. Thanks to DDH, the database files transferred to each platform without a problem.
curtterp
 
Posts: 681
Joined: Tue May 26, 2009 6:41 pm
Location: Northern IL

Re: Two way relationships for brewing DB

Postby greyman56 » Mon Feb 20, 2017 5:45 pm

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?
Cheers, Graham
greyman56
 
Posts: 36
Joined: Wed May 27, 2009 10:53 pm
Location: Veteran, Australia

Re: Two way relationships for brewing DB

Postby greyman56 » Thu Feb 23, 2017 1:38 am

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.
Cheers, Graham
greyman56
 
Posts: 36
Joined: Wed May 27, 2009 10:53 pm
Location: Veteran, Australia

Re: Two way relationships for brewing DB

Postby greyman56 » Thu Feb 23, 2017 5:04 am

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.
Cheers, Graham
greyman56
 
Posts: 36
Joined: Wed May 27, 2009 10:53 pm
Location: Veteran, Australia


Return to HanDBase General Discussion

Who is online

Users browsing this forum: No registered users and 0 guests

cron