How to create report that can be used as a cookbook

[Conn13]

LibreOffice 7.2.2.2 on Windows 10. The complete record/recipe is not showing consistantly in my report. I used the recipe database created by David Hinkle and changed it to suite my needs. I got over 100 recipes entered and decided it was time to work on the report. I have been working for weeks to get a usable report that I can create a cook book from to give to my family. I obviously missed Christmas. The greatest obstacle seems to have been the subform used for the ingredients, which was designed to work for creating a shopping list. The queries I created didn’t work at all. I finally had some success by creating a view instead of a query. It is called ViewAllByCatagory and the Report that is closest to what I want is called ViewAllByCatagory2. (yes I know Category is spelled wrong. ;))The files and an image of the set up can be obtained here

It should have recipes grouped and sorted by category, then sorted alphabetically by name. Only one of the ingredients is showing and it looks like it is avoiding any ingredient I didn’t add an amount to. I set it to not require amounts or measurements. In the report I set some field labels to not print if the field was empty but that isn’t working consistently either. The instruction I used can be seen in the jpg I included in the folder linked above. I’ve tried various changes, such as keep together or not. I sure hope this can be done right so I haven’t wasted all this time.

To say thank you for your trouble, there are some really good recipes, some my originals developed for the aromatherapy business I use to operate (The Punch is one example). The Cranberry-Orange recipes are great with the addition of Orange Essential oil too.

[Ratslinger]

Hello,

OK. Pretty sure that is what is wanted.

Just some changes to report:

Add a Name footer and place your instructions & notes there.
Ingredients label goes on Name header so it only prints once.
Ingredients on detail - this will repeat until name changes.

Produces:

Of course you will want to arrange to your liking.

Sequence seems OK. Ingredients which are blank amounts do show. You can enhance by placing page breaks at sections. Also insure groups are set to keep together:

[Conn13]

You’re an amazing Ratslinger! Thank you so much. I’ll do a little tweaking, center the names better, see if I can get fractions instead of decimal amounts, an attractive line between recipes…Thanks again!

[Conn13]

Most everything is the way I need with just one problem. The ingredients that don’t have quantity are not showing, such as the chipped beef dip. See images which also show it is set to not be required and field format being fraction. [ETA I found the fraction problem. I needed to also format in the Qty field in the report. It is now formated to be 1234 4/7} So that leaves only the ingredients with the unspecified quantity not showin. I uploaded the new version with the changes

.

[Ratslinger]

@Conn13

The ingredients that don’t have quantity are not showing

That is not correct. It is those ingredients without a Measure which are not shown. Either adjust your view or my choice, place a selection in Measure something like TDB.

[Conn13]

Oh, I see. Thanks. Maybe I could add the measure, “as desired” or something similar. Thanks for all your help.

[Ratslinger]

Can work with a query change:

SQL used here:

SELECT "Recipes"."Category",
       "Recipes"."Name",
       "Recipes"."Picture",
       "IngredientList"."MeasurementQuantity",
       "Measure"."MeasurementUnit",
       "Ingredients"."Ingredient",
       "Recipes"."NumberofServings",
       "Recipes"."Instructions",
       "Recipes"."Notes",
       "Recipes"."Source"
FROM { oj "IngredientList"
  LEFT OUTER JOIN "Recipes" ON "IngredientList"."RecipeID" = "Recipes"."ID"
  LEFT OUTER JOIN "Measure" ON "IngredientList"."MeasurementUnit" = "Measure"."ID"
  LEFT OUTER JOIN "Ingredients" ON "IngredientList"."Ingredients" = "Ingredients"."ID" }
ORDER BY "Recipes"."Category" ASC,
         "Recipes"."Name" ASC

[Conn13]

What a warm surprise to wake up to on this super cold morning. I created a query in SQL view, pasted your generous gift, created a new report laid out like the last one and it is perfect. Thank you so much.

[Ratslinger]

@Conn13,

been working for weeks to get a usable report that I can create a cook book from to give to my family. I obviously missed Christmas.

Wishing a belated Christmas & God Bless.
.
Next time don’t wait so long to ask for help.

[Conn13]

And may the blessings you give come back to you multiplied a hundredfold.

[Conn13]

I hope it is ok to bother you one more time for this project. I didn’t realize until I’d gotten all the index entries and TOC created that the ingredients list is coming into the report in reverse order of what they are in the update recipe form. This is important because they are entered in the order I compile them and the way that makes most sense. Cream the wet ingredients, add the dry… The only thing I changed was the names of the categories, adding a prefix so they were in the order I wanted them, not simply by alphabetical order by category name. I’ve looked everything over and can’t see why the ingredients would be reversed. I’ve wondered if it has something to do with the ingredients being a list box so that the grocery list can also be created from it.

One other small item… there is a very small line in the report produced, right below the name label. I can’t see where that is coming from either.

[Ratslinger]

@Conn13

there is a very small line in the report produced, right below the name label

Cannot tell anything without a sample producing this line. The same is true for why your ingredients aren’t sorting properly. If prefix is good should be simply an order by statement.

[Conn13]

I’m so sorry. I generated a new link but forgot to post it. Here is the [odb file] and a snapshot of an example. I just noticed too, that when I first generated the report the little line wasn’t
there but as I scrolled down and back it, it appeared. (Databases – Google Drive)

.

[Ratslinger]

@Conn13

first generated the report the little line wasn’t
there but as I scrolled down and back it, it appeared

Cannot duplicate this.

As for the sequence, have consistent problem when using a View to source. Change to your Query (simple change in Report - do not need to re-do):

and added last line in Query of Order By:

ORDER BY "Recipes"."Category" ASC,
                 "Recipes"."Name" ASC,
                 "IngredientList"."ID" ASC

resulting in:

[Conn13]

You’re the best. It works perfectly and magically, I can no longer reproduce that little line. I can live with it if it reappears though. Thank you so much.