8 Replies Latest reply on Jan 22, 2015 7:51 PM by curtin.trend

    Displaying next highest number in a field

    curtin.trend

      Hi all,

       

      I am just starting out in Filemaker and am about 3 days into it. 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. The lab work is not always done sequentially and whilst it is easy to find the next sample number, finding the next extract number can be difficult as it may be anywhere in the spreadsheet/database. 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).

       

      I want to make fields entitled "next AD#" and "next MB#" which will display a constant number that is the next highest number and will update once that is used (I think that this should be a global field for that purpose). I have tried searching for some methods to do this and haven't been able to get anything to work! Can someone please give me a walkthrough with specific details of menu/steps to do as some of the things I have read assume a familiarity with the program and processes which I don't have!

       

      Cheers,

      James

        • 1. Re: Displaying next highest number in a field
          erolst

          James –

          curtin.trend wrote:

          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

           

          Let (

            ~f = TrimAll ( yourField ; 0 ; 3 ) ;

            Case (

              ValueCount ( Substitute ( ~f ; "," ; ¶ ) ) < 2 ; // Max() balks if only a single value (or none) is passed

              ~f ;

              Let (

                eval = "Max (" & Substitute ( ~f ; "," ; ";" ) & ")" ; // create list structure for Max(); var for easier debugging

                Evaluate ( eval )

              ) // end Let()

            ) // end Case()

          ) // end Let()

           

          + 1

           

          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 …

          • 2. Re: Displaying next highest number in a field
            mtwalker

            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.

            • 3. Re: Displaying next highest number in a field
              siplus

              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

               

              267

              1678

              1899

               

              now you use a custom function like this one

               

              FileMaker Custom Function:ListPosition ( ListValues ; SearchValue )

               

              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.

              • 4. Re: Displaying next highest number in a field
                curtin.trend

                Hi all,

                 

                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.

                 

                Cheers,
                James

                • 5. Re: Displaying next highest number in a field
                  curtin.trend

                  Would this get the highest number amongst all the records?

                  • 6. Re: Displaying next highest number in a field
                    curtin.trend

                    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.

                    • 7. Re: Displaying next highest number in a field
                      mtwalker

                      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.

                      • 8. Re: Displaying next highest number in a field
                        curtin.trend

                        Hi all,

                         

                        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.

                         

                        Cheers,

                        James