Report to show plant info once and variable numbers of images

[goedible]

I want the report to show one entry for each plant. I want the plant images beside the entry. How can I do this if some plants have more than one image?

For example: I have two images of blackberries so the report shows all the info about black berries twice. Once for each image. Instead I want to see all the info about black berries once and one or more images.

2)plot_list_and_edible_uses1.odt (63.3 KB)

[RobertG]

Two possibilities:
The images should be read from the same row in the data source. It depends on the view you created. So one field for the first image and one field for the second image.
The images will be shown in the detail section. So the images will positioned under the text content.

[goedible]

How can I create a view with the images on the same row?

[goedible]

Are there any examples how to fo this that I could copy?

[RobertG]

It depends on the database you use.
You will need a subquery with line numbering.

SELECT "ID", ( SELECT COUNT( "ID" ) FROM "Table" WHERE "ID" <= "a"."ID" )
AS "Nr." FROM "Table" AS "a"

(See Base Guide, p. 385)
Line Numbering has to be grouped by the rows of you main table. If you have got more than one image for a row you have constructed a 1:n-relation between you maintable “tbl_main” and your imagetable “tbl_image”.

SELECT "Image", 
( SELECT COUNT( "ID" ) FROM "tbl_image" WHERE "ID" <= "a"."ID" AND "maintableID" = "a"."maintableID") AS "Nr.",
"maintableID" 
FROM "tbl_image" AS "a"

From this query you will get images for “Nr.”=1 and “Nr.”=2 in your “tbl_main”. For better understanding: I save the query and call in “qry_images”.

SELECT "a".*,
(SELECT "Image" FROM "qry_images WHERE "maintableID"  = "a"."ID" AND "Nr." = 1) AS "Image1",
(SELECT "Image" FROM "qry_images WHERE "maintableID"  = "a"."ID" AND "Nr." = 2) AS "Image2"
FROM "tbl_main" AS "a"

If the result is what you want try to get a view of it. Now you could add two image controls in the report to show 2 images at the right.

[cpb]

@goedible,
you could use conditional aggregation as shown in this embedded HSQL attachment:
Multiple_Images_Single_Row_Report.odb (695.0 KB)

I did not download your database.
based on the query design image you posted the code below should be close to what is required.
obviously I cannot test and therefore it’s likely to contain errors.
paste the code in the query design window and use direct mode i.e. menu:>Edit>Run SQL command directly

select
	ppc."latin_name",
	s."Common_name",
	s."Edible_uses",
	p."Plot",
	max(case when i.pos = 1 then i."url" end) IM1,
	max(case when i.pos = 2 then i."url" end) IM2,
	max(case when i.pos = 3 then i."url" end) IM3,
	max(case when i.pos = 4 then i."url" end) IM4
from
(
	select
		"Latin_name",
		"Common_name",
		"Edible_uses"
	from
		"species"
) s
join
(
	select
		"latin_name",
		"plot"
	from
		"plot_plant_characteristics"
) ppc
	on s."Latin_name" = ppc."latin_name"
join
	"Plot" p
	on ppc."plot" = p."Plot"
left join
(
	select
		i1."latin_name",
		i1."url",
		count(i2.ID) pos
	from
		"images" i1
	join	
	(
		select
			ID,
			"latin_name"
		from
			"images"
	) i2
		on i1."latin_name" = i2."latin_name" and i1.ID >= i2.ID
	group by
		i1."latin_name",
		i1."url"
) i
	on ppc."latin_name" = i."latin_name"
group by
	ppc."latin_name",
	s."Common_name",
	s."Edible_uses",
	p."Plot"