How to make table with image urls for database

[goedible]

My goal is to create a table for plant images that I can click through in LANdpLAN.odb database form like this one:

A problem is in generating the correct latin_name from the name of the image url when more than one space is needed. For example: ‘Malus X Purpurea’ needs two spaces.

This is how I’m doing it for plants that only need one space:
I have a folder with images and a calcsheet that uses it’s file path to create a url and latin_name. I copy/paste this sheet into my database to create a table called ‘images’.

The calc file has sheet1 for the formulas and calculations and images to display latin-name and url for the base table.

it’s called aaaimages.ods

The database has
-‘species’ table with ‘Latin_name’ and
-‘images’ table with ‘latin_name’ and ‘url’

When I want to add images to the database I:

drop them in the folder and rename the files with the latin name of the plant minus spaces and a number to create a distinct file name like Malus X Purpurea = MalusXpurpurea

open the aaaimages.ods file

open LANdpLAN.odb

rename ‘images’ table to ‘imagesold’

copy ‘images’ sheet from aaaimages.ods to LANdpLAN.odb

My problem is creating a formula to deal with some plant names that need more than one space. I need to generate the latin name from the image url.
For example:
url: ‘/home/j/Desktop/link to LANdpLAN/LANdpLAN/Drawing/Plant_images/images/MalvaAlcea2.jpg’

latin_name ‘Malva alcea’
(WIth latin names, the first name is capitalized and the second is not… if it is a cross, the letter X is capitalized.)

The system fails when I have more than one space needed in the latin name like where: ./Drawing/Plant_images/images/MalusXPurpurea.jpg = MalusXPurpu rea

The space is in the wrong spot.
To get the first space in the correct spot…

i used sum… There has to be a better way, I did my best… it’s complicated and confusing. Help me? I was looking for the capital letters to indicate where a space needs to go.

This way doesn’t work when there is more than one capital.
here is a link the folder of images and the calc sheet.
https://www.dropbox.com/scl/fo/4dl0f0qr2ciimuu9i4o56/AJwPQfxEUTKO8Nnvei1xB8k?rlkey=tb8hncvl5pb4yml4pupi36gy3&st=1znedx2v&dl=0

[Villeroy]

https://www.mediafire.com/file/l3n161t1yvy2604/Headshots2.zip/file
Just put the pics in the same directory or some sub-directory. Double-clicking on a form’s picture control opens a file picker which stores the relative URL in the linked database column.

[goedible]

There are multiple images for some plants. The form has < and > buttons to cycle through the images. Can this be done the way you are suggesting? Also, there are over 1000 plants. Does it mean I have to use the file picker for every plant?

[Villeroy]

Well, that would be a one-to-many relation where every picture belongs to one plant.

CREATE TABLE URLS(
    URL VARCHAR(30) NOT NULL,
    PID INTEGER NOT NULL,
    ID INTEGER GENERATED BY DEFAULT AS IDENTITY,
    FOREIGN KEY (PID) REFERENCES "Your Plant Table" ("primary key")
)

@goedible

Does it mean I have to use the file picker for every plant?

Depends on your file names.