How to view/search list of data from related database

Discussions for the HanDBase for iPad and it's conduits and desktop add-ons

How to view/search list of data from related database

Postby jennx » Sun Oct 20, 2013 2:07 pm

Hi, I'm new to this app and I'm trying to figure out a way to display a list of items pulled from a related database (many to one)

It's a recipe database. So I have one database with ingredients and amounts and then one recipe database. I can like the ingredients with the recipe but I can't display all the ingredients within the recipe view. You have to click to view the list of ingredients. How do you display everything on one page.

Also, how would one search for a specific ingredient (or several ingredients) and pull up the recipes in which those ingredients appear?

Posts: 2
Joined: Sun Oct 20, 2013 1:58 pm

Re: How to view/search list of data from related database

Postby dhaupert » Mon Oct 21, 2013 10:09 am

Hi there,

Thanks for your post and question. I have and use a calorie database that tracks my calories each day, and over the years it's kind of evolved to be my recipe database as well. If I were to fully modify it's structure to be solely for recipes, this is how I'd go about it:

Top Level Database: Recipes - fields like Recipe Name, Category, Cooking Time, Instructions, pictures, notes, etc, and a link field pointing to Ingredients
Second Level Database: Ingredients - linked field pointed back to the Recipes Database, A group of DB Popup fields that refer to the Foods table below. There would be one for Ingredient and one for serving unit. By setting the group number of these fields to the same non-zero value (eg, 1) they will both be filled out when you select an ingredient. Then there would also be a float field for the serving size.
Third Level Database: Foods - has a text field for the food name, a popup for the category (meat, dairy, spice, vegetable, condiment, etc), the serving unit (could be a popup with ounce(s), tablespoon(s), etc).

So in this layout, you build a recipe by opening the recipe database and creating a new record. Fill out the overview type details and then tap Ingredients to begin adding ingredients. Tap the compose icon to create a new ingredient and then tap the Ingredient field drop down to select from the Foods list what ingredient that would be.
Being that the Foods table is probably empty to start, you'd begin by adding the items for that recipe, entering the ingredient, category, and standard unit of measure. Once you've added it, you press Save and then select it from the slowly growing list. Now that ingredient is copied back to the Ingredients list for this recipe record. You can create some popup values in the Serving size (I have 0.1, 0.25, 0.33, 0.5, 0.66, 0.75, 1, 1.5, 2, 3, 4, etc in my list) and then you can easily select the values as needed.

Repeat this process for each ingredient and you should see a list of ingredients for the recipe. Adjust your field widths and order so that it's easy to see something like this:
Ingredient Serving Size Serving Unit
Milk 1 Cup
Eggs 2 Eggs
Flour 1 Cup
Canola 1 Tablespoon

now you can hit back and enter your steps and comments and such. You should have all you need to create the recipe database. I would then go and create a nice looking set of forms for it. On the form you can have two buttons- one to show the list of ingredients, which would be set to the link field as the data source and the button action of 'show link/linked/..'. This button will thus jump to that list. I'd add another button of 'Add Ingredient' which is also set to the same data source but uses the 'jump to form in other database' and the option to create a new record. Now you can easily add a new ingredient without having to go to the list and then press new.

I hope the above is useful- this would make a great tutorial video and perhaps I will go through the exercise of doing this in the near future. In the meantime, let me know if you have any other questions!
Posts: 4111
Joined: Tue May 26, 2009 11:51 am

Re: How to view/search list of data from related database

Postby jennx » Tue Oct 22, 2013 9:04 am

Thanks, Dave. That's almost exactly what I have except my ingredients aren't in a separate database, they're in a pop up field in the amounts form. Now that I have it though, I'd like a view where the linked data (the amounts and ingredients) can be viewed on the same page as the rest of the recipe. So instead of providing a link, the view would actually pull that data in from the other form and display it. Also, from within the parent form I can't figure out how you would search for an ingredient and have all the recipes that contain that ingredient show up. It seems like you have to search from within the amounts form but then there's no obvious way to get to the actual recipes once you do so.
Posts: 2
Joined: Sun Oct 20, 2013 1:58 pm

Re: How to view/search list of data from related database

Postby dhaupert » Tue Oct 22, 2013 9:14 am

Hi again,

Thanks for your response. Your question brings up a very important point about HanDBase. Forms in HanDBase are always designed to show a single record of data- there is no table view element to include in forms at this time. The list view is the screen where you see more than a single record at a time, can search, etc. There is no crossover between those two at this point. Some day I may implement the ability to design forms with list view type capabilities, but right now this is how things are designed:

List View- multiple records, searching/filtering/sorting capabilities
Form or Edit Record View- more detail on a single record.

So if you went with my sample above, you would certainly have to jump to a separate screen. Of course by having the ingredients in a separate table, it opens up some different possibilities- like an unlimited number of ingredients. If you go the route of having DB Popups in your recipe database, you can create maybe 20-25 ingredients max per recipe since you'll need 3 fields for each ingredient (ingredient name, serving size, serving unit).

In both cases, showing recipes where a certain ingredient is used could be done from the list view- in my example, you'd search from the ingredients list and can see the recipes that included that ingredient. In the design you had (and mentioned above) you can search from the recipes list view- making sure to set to search all fields and you'd see the recipes that mentioned that ingredient in them.
Posts: 4111
Joined: Tue May 26, 2009 11:51 am

Re: How to view/search list of data from related database

Postby peterromao » Tue Feb 06, 2018 9:20 am

Hello all,

I've been reading the posts all around to try to find something like what's exposed here in this thread. This one dates back to 2013!

So the question begs to be asked as I am dearly looking for this type of functionality.

Can there be Forms where I put a list for viewing purposes only? Exactly like the user in this post is asking?

Thanks in advance for any input.
Posts: 1
Joined: Tue Feb 06, 2018 9:07 am

Re: How to view/search list of data from related database

Postby ddhsoftwareadmin » Sun Feb 11, 2018 10:39 pm

Hi Peter,

Thanks for writing. Unfortunately this is still not an option within HanDBase. It's something I have always wanted to do but it requires a major update, and at this point in time there isn't much demand for HanDBase for me to be able to invest that level of time and effort to add such features. I wish things were different but still grateful I'm still able to keep this app running for nearly 20 years!
Site Admin
Posts: 308
Joined: Tue Dec 09, 2008 2:13 pm

Return to HanDBase for iPad

Who is online

Users browsing this forum: No registered users and 0 guests