If the images are in the same folder as the merge file/ID file, then you don't need a path. You can also use a relative path from the ID and data source folder as long as the images are under the ID folder.
As far as I know, you will always have to have the full image name. Otherwise, if there were two files containing that number sequence, how would InDesign know which one to use?
But if you have only one image for each product number, you can copy them all to one folder and use bridge to batch-rename them to just the product number. Then it's a simple matter in excel to bring the number over to the photos field and add .jpg
then, as NW said, put the data file in the folder with your images and you'll be good to go.
Thanks for your suggestions.
I would prefer NOT to rename the images, since these are used in other publications and then I (or other users) would have to update the links.
I would also prefer to keep the images in separat folders, but I can see that this complicates the issue even further.
Do what MW Design has suggested. If the image names don't clash and can successfully be consolidated into the one folder, put the images there and put the text file that will be used for the data merge there as well. If this is done, then only the filename will be required for the merge, and not its complete path.
If the images MUST be in separate folders, then the full paths of the images have to be used.