Do I Need to Create a Relational Data-base to Auto-generate a Field based on my Primary Key?

[modern_archaeologist]

Hello all,

I am working on an archaeological database for my PhD, and would like some advice on the following issue:

My database is for objects, collecting information on chronology, provenance, material, dimensions, etc. I have a unique object id # field, which is auto-generated. However, I realize that for my PhD, it doesn't actually make sense for me to have the objects be allocated random numbers. I want to be able to present my data with a numbering system based on material (ivory, bone, terra-cotta, etc). So instead of having object 438 or object 6475, which is basically meaningless, I would have M1, M2, M3 ... for metal objects, S1, S2, S3 for stone, etc. Is there any way to achieve this with FileMaker auto-generating the numbers within each material for me? Will I have to make relational databases for all the different materials in order for this to work? Or can I simply make a relational database that somehow links the unique object # with material to generate a new numbering sequence for each material type? 

Many thanks for any advice/inputs.

[Ocean West]

Internally you should use a meaning less id or UUID for relationship purposes you could create another field that contains meaning should something happen where you realize you entered S1 and meant M1 then that would change or orphan records. 

You could maintain a table of objects that know or set the next sequence number and then append that to with the "designator" M or S  

Or scripted you could isolate the known set of record for a given material then figure out the MAX value by various means then create a new record for this material setting the identifier to the max + 1 such as M4  

 

 

[bcooney]

Add a type field to the objects table. Populate it with the foreign key to ObjectType table. Keys should be unique and meaningless. ObjectType would have at least two fields, Id and Description.

[modern_archaeologist]

16 hours ago, Ocean West said:

Internally you should use a meaning less id or UUID for relationship purposes you could create another field that contains meaning should something happen where you realize you entered S1 and meant M1 then that would change or orphan records. 

You could maintain a table of objects that know or set the next sequence number and then append that to with the "designator" M or S  

Or scripted you could isolate the known set of record for a given material then figure out the MAX value by various means then create a new record for this material setting the identifier to the max + 1 such as M4  

 

 

Would there be any way to do what you describe here: "You could maintain a table of objects that know or set the next sequence number and then append that to with the "designator" M or S" within one table? 

I'm attaching below a screenshot of my layout, where Object ID # contains all the objects, while Material ID# would correspond with material type, so that when marble is selected, it automatically tallies up starting at M1? 

[comment]

I suggest you take at look at:
https://fmforums.com/topic/107418-generating-two-part-idsku-numbers/

 

Keep in mind that you can very easily create the type of numbering you describe ad hoc, using a summary field that restarts for each sorted group. However, such numbering will depend on the current found set and sort order - IOW, it will not be permanent. If you like, you can make it permanent once your data collection has been finalized. But trying to maintain it while records are still being created, deleted and modified is risky.

 

[modern_archaeologist]

22 hours ago, comment said:

I suggest you take at look at:
https://fmforums.com/topic/107418-generating-two-part-idsku-numbers/

 

Keep in mind that you can very easily create the type of numbering you describe ad hoc, using a summary field that restarts for each sorted group. However, such numbering will depend on the current found set and sort order - IOW, it will not be permanent. If you like, you can make it permanent once your data collection has been finalized. But trying to maintain it while records are still being created, deleted and modified is risky.

 

Thank you so much -- this is so helpful! Could I follow up to ask how I can create the ad hoc summary field you describe, restarting for each shred group? This is exactly what I'm trying to achieve. 

[comment]

1 hour ago, modern_archaeologist said:

how I can create the ad hoc summary field you describe

Define a summary field as Count of some field that cannot be empty (e.g. an auto-entered serial number field). Then select both the 'Running count' and the  'Restart summary for each sorted group' options. When records are sorted by Material, you will get a running count for each group. Use a calculation field to add an initial in front.

Note that this assumes records do not need to be sub-sorted within each material. Otherwise this gets more complicated.

 

Edited January 21, 2021 by comment