It depends on what you want for a serial number. A table where a new record is created each time you create a print could contain an auto-entered serial number--but this will not be a sequential number such as: 1, 2, 3 for photo 1 and then 1, 2, 3 for Photo 2. Instead, you'd get: 1, 2, 5 for Photo 1 and 3, 4, 6 for Photo 2 or some such single series of serial number values.
A separate number series for each image is possible, but takes additional work to set up.
What kind of serial numbering scheme did you have in mind?
And is this a single user database or one where there might be more than one user documenting the creation of new prints at the same time?
Hi Phil & thank you.
it will be a single user database.
The numbering MUST be sequential for each image. I had worked out how to do the alternative (as in your example above) but that will not give the results I need. I would then want to use the numbering to track prints, sales, stock, etc.
The numbering I had in mind was Image number (eg 120) followed by a print number (eg. 1, 2, 3, etc) ~ 120/5 perhaps? That way the the number becomes a product code that can be applied to labels and used to track prints, especially limited edition prints. Am open to suggestions for better systems. As I said I'm new to Filemaker and have only used Access at a low level before.
Please note that you can "track prints, sales, stock, etc." with the simple auto-entered serial number method, you do not need sequential numbering for each photo to do that.
For your two part numbering scheme, keep the numbers in two fields, but combine them for display purposes on your layout. You can set up this table structure and relationships:
Images::__pk_ImageID = PrintLog::_fk_ImageID
PrintLog::_fk_ImageID = PrintsSameImage::_fk_ImageID
PrintLog and PrintsSameImage are occurrences of the same table in what we call a "self join" relationship.
In Manage | Database | relationships, make a new table occurrence of PrintLog by clicking it and then clicking the duplicate button (2 green plus signs). You can double click the new occurrence box to get a dialog to appear where you can rename the new occurrence box as PrintsSameImages .
We have not duplicated a table. Instead, this is a new reference to the same table already present in your database.
Define a field in PrintLog as PrintSerial. Set it up to auto-enter 0 as the default value by entering a 0 in the data box on the auto-enter tab in Field options for this field. On that same tab, select the calculation option and specify:
Max ( PrintsSameImage::PrintSerial ) + 1
Clear the "Do not evaluate if all referenced fields are empty" check box.
Clear the "Do not replace existing value..." check box.
Now, each time you create a new print of an image create a related record for it in PrintLog and it should be automatically numbered with the next number in the series for that image. To get your two part print number, you can either put merge text on your layout:
or define a calculation field that combines them:
_fk_ImageID & "/" & PrintSerial
Thanks again PhilModjunk,
That worked. Took a couple of attempts (the specified table cannot be found!) but it now works on a trial basis.
Yes, I did understand "that you can "track prints, sales, stock, etc." with the simple auto-entered serial number method, you do not need sequential numbering for each photo to do that." but I really do want the more coomplex system for identifying prints.
Now to look at the rest of the database, layouts, tables etc. Thank you this has been a great help.