I am making a database for our ancient DNA samples. Each sample has an AD# for each bone, eggshell etc and then each DNA extract taken from that has a different MB#, and there can be multiple extract numbers for each sample. […] I have made a form up with a field for AD# and a field for MB# which can have multiple numbers separated by a comma (e.g. 267, 1678, 1899)
… following from that description (as far as I can grasp the lingo), it seems you should have a relational structure like
Samples --< AD#s --< DNAExtracts (MB#), or maybe
Samples --< AD#s
--< DNAExtracts (MB#)
Seeing as Biology & Chemistry were the curse of my youth, that's the best I can offer here you … but you get the drift.
I'm emphasizing relational because it seems that at the moment you have a flat file, with Samples being the only fully described entity (and table). Assuming that each AD#/extract is not simply described by a number, but possesses multiple attributes (and thus is an entity in its own right), cramming numbers into a field is not an adequate data structure.
(There are cases when it is sufficient, but then you'd probably use a CR-delimited list, which (until we get something better) is the de-facto array structure in FM.)
I suggest reading up on data modelling / entity/attribute identification
Given such a relational setup, you could define a calc field with the next highest number simply via
Max ( DNAExtracts::number ) + 1
or use that formula in a script that automates the creation of Extracts child records.
Just for fun: you can get the next highest number from your field with
~f = TrimAll ( yourField ; 0 ; 3 ) ;
ValueCount ( Substitute ( ~f ; "," ; ¶ ) ) < 2 ; // Max() balks if only a single value (or none) is passed
eval = "Max (" & Substitute ( ~f ; "," ; ";" ) & ")" ; // create list structure for Max(); var for easier debugging
Evaluate ( eval )
) // end Let()
) // end Case()
) // end Let()
If you knew the entries are sorted, you could use
RightWords ( Substitute ( TrimAll ( yourField ; 0 ; 3 ) ; "," ; " " ) ; 1 ) + 1
but I would (literally) not count on that …
If take erolst's advice and use two tables, then the MB# can be an auto enter serial number that will be created whenever a new MB# record is created.
if you have MB_nr containing 267, 1678, 1899 then you can define MB_nr_list = Substitute(MB_nr; ", "; ¶).
So MB_Nr_list will be
now you use a custom function like this one
so that you get the position of your current MB in the list. let's call it current_MB_pos.
You then do a GetValue(MB_Nr_List; 1+current_MB_pos) and it will give you the ID of the next MB.
Thanks for all your posts so far. I just thought I would clarify what I am after as it seems to have been lost in interpretation! The AD and MB numbers would be manually entered as needed so it doesn't need to autopopulate on a new entry at all. I simply want to have a constant (updated when the next number is take) field for each number series (called "next AD/MB no") that somehow scans the data column for the highest number and adds 1 to it. I have tried the max function which doesn't seem to work for me! The AD number will only ever have a single number in it so that should be the simplest one to do yet i cannot get it to work. The MB numbers will be more complicated as there are multiple numbers seperated by a comma.
Would this get the highest number amongst all the records?
New MB records will never be created, they are a secondary information field for the main record which is the sample (AD no). The MB numbers record extracts of those samples and can be either not yet done or have multiple instances for one sample.
Part of the challenge is the way you are doing it. It seems like you are still thinking in spreadsheet mode and not relational database mode. Each DNA extract is a separate entity therefore should be in a separate table linked a record in Sample table by a unique ID. This way it would be easy to see what the next number should be. It also would allow you to store additional info about each extract. You may not need that now but you may want to down the road.
That being said, I hate when I ask question and everyone tells me that I should do it a completely different way so I'll try to offer an option. One way to do it might be to store the "Next MB#" in a field and use a script to "issue" that number each time needs a new MB#. The script would then increment the "Next MB#" by one. You would need to prevent the user from manually editing the MB# list in the sample's record otherwise they could enter the wrong number. They only way they should be able to get a new number is with the script. With all of the MB#s in a field separated by commas, I can't think of a way to quickly and reliably get the highest number.
If the solution is going to be a single user account then you could use a global field to store the "Next MB#" in a global field. However, if it will be a multi-user system, that won't work because the global field value is specific to the user. In that case, you could use a single record "System" , "Globals", or "Whatever" table that is linked to the Samples table. The System table would have the "Next MB#". You would need be sure that it only had one record and that no records could be created in it.
Thanks for your replies and efforts! I decided to take Todd Walker's advice and stop treating it like a spreadsheet. I spent the time making a new data table that had single entries of my extract numbers with a linked sample number and then made a portal for the data to the main form. This will allow me to have a dedicated layout for extract numbers which are sorted in order making adding the next number easy to do and gives me a chance to put a few extra fields of info in.