Summary Calcualtion for Next Numbers

[JediPabst]

I am trying to set up a photograph database for my father to use to keep track of the images he has circulating at all the various galleries. I would like to be able to automatically populate the Edition# field with next number for the given ImageID field so that the records would look like this.

ImageID Edition#
101 1
101 2
102 1
101 3
103 1
101 4
102 2

Once a table gets large enough it will be hard to remember what edition you are on, so i would like to be able to populate it automatically as each record is entered. I have done similar things in other databases but can't figure out how to do it this one. This is the database that my father has on his Mac, so I was trying to formulate something there.

Any ideas would be grately appreciated.

Thanks
Bob

 

[JeanW]

There are different ways to go.
Basically you need to set up a relationship linking the ImageIDs and use that to set up a conditional serial link.

I can give you a rough approach, not simple and easy but not bad (thanks to Dr S. Greibach)
.
You need a few more fields.

The ID is given by :

ImageID
ImageID and the combinedID which is a calc
ImageID & Right(“00” & EditionID,3)

The self-relationship Image is based on ImageID = ImageID

What you would like is to make EditionID the max of the related EditionIDs plus 1.
But FM will tell you that is circular.
Sort the Image relationship by EditionID ‘descending’; this means that Image ::EditionID will always give you the largest one and that will be the one looked-up.
Add a field PrevID as a lookup of EditionID via relationship Image with default 0.
Now let EditionID be calculated as PrevID + 1.
Lookup breaks circularity and also allows ImageID to be indexed and stored.

You’ll need a one-time script to set up the values of ImageID and PrevID for existing records (if there are). Exactly how this will run depends on how you now have the information structured.

Maybe better :
if you script your creation of these new records this is simpler.
You still need ImageID and EditionID and the relationship but do not need PrevID, and EditionID is a regular number field, not calculated.
Simply add to the script the step
Set field(EditionID, Case(Image ::EditionID > 0, EditionID+1,1))
after ImageID has been entered.

HTH

John