10 Replies Latest reply on Jun 22, 2016 6:29 AM by dtcgnet

    Record Number in sequence


      I have a database, where I store daily stock market data. Main fields, that differentiate records are DATE and SYMBOL. I also have open, high, low, close and volume data, but those numbers are of secondary importance.


      Like here:


      DateSymbolOpenHighLowCloseVolumeRecord ID


      Record ID field gives me the most of trouble. I want to have as auto-enter serial number, but I have all of the symbols in one database, so if next bunch of data is not from AXP, but, i.e. from MSFT, that should have Record ID's of 1, 2, 3, 4, 5, 6, 7, 8, 9 and 10, and not 11, 12, 13, 14, 15, etc.


      Get (RecordNumber) also will not work, as it gets the number of the record in database without filtering of unique symbol.


      So how do I get correct numbering of particular symbol in the database? I sort those by date in ascending order, so #1 is the oldest date and the most recent date has the highest Record ID.

        • 1. Re: Record Number in sequence

          How are you trying to display the data? When do you want to display the number? Does it need to be dynamic? Can each symbol be in a different table? Why do you need the number and how do you use it?


          As you are seeing this is not as easy as you might think. If you keep each symbol in table get(record number might work for you as long as you always have the records sorted. The other option is if you set the number on record creation, but if you ever delete a record there will be a gap in the numbers.


          You can generate the numbers daily or hourly using a script run on the server and save them to a field.

          • 2. Re: Record Number in sequence

            Well, it's not for display purposes, that field is very important, as it is used in all the derivative calculations. So it needs to be 100% accurate.


            Each symbol cannot be in different table, as all daily data of every stock is placed in one STOCK_DATA table, which becomes related to other tables (but that's another end of the story).


            Running a script is OK, as I do it anyway, as many fields of the derivative calculations cannot be used as calculations, because they become too heavy for the Filemaker to handle if unstored (one calculation is based on second field, which is based on 3rd field, which is based on the 4th field, and so on).


            So, whenever I import data from .csv, I run "Replace Field Contents" script, so I could add a calculation of that Record ID. Earlier, I used another field: STOCK_DATA::date > STOCK_DATA_ID::date AND STOCK_DATA::symbol = STOCK_DATA_ID::symbol, but it didn't produce correct results.


            So ideas to solve this would be appreciated.

            • 3. Re: Record Number in sequence

              RecordID is a really bad thing to even think about building reliable data on. If you delete any record at all it will likely mess with your system if you use RecordID. Maybe forget about RecordID as it is there for FM to use internally for the most part from what I understand. Lets just call your field "Serial"


              What version of FM are you using? Assuming it is FM12+:


              Find all records with no Serial and sort by date ascending.

              Loop through each record starting from the first and set a variable $serial to ExecuteSQL("SELECT MAX(Serial) FROM Table WHERE Symbol = ?"; ""; "";  Table::Symbol)  //This finds the existing highest number in Serial for that symbol.

              Set Field (Serial: $serial +1)

              Got to next record.


              If you are using FMS and have access to PSOS run it server side it will be MUCH faster. Depending on your server you should be able to process at least 80-100 records per second this way. Maybe more. Since it will be done incremental it should be reasonable unless you have 500k entries per day, that might take a few minutes to process.


              I am sure you are storing you derivative calc results for each symbol daily already. Maybe run this ahead of that process?

              • 4. Re: Record Number in sequence

                i'm with BigTom on this.. "Smart" primary keys can easily cause data integrity problems when the method of generating and validating inserting and committing the value does not account for every possible edge case that one can encounter in the lifetime of a file. This is especially difficult with multi user/hosted solutions.

                I would use UUID for the primary key and use a pseudo alternate key (looks like an alt key but is not used for actually relating data) built for the purpose of grouping record numbers by symbols.

                • 5. Re: Record Number in sequence

                  Well, that Record ID is very important, as it is one of the most important fields. Because it is used as Bar Number. Records actually  cannot be deleted from database, so messing the sequence up is not the case. So, unique record ID isn't solving anything here. Some formulas only apply, if Bar Number (i.e. Record ID) is > 20, >100, > 200, etc. So, changing that, would mess a whole lot more.

                  • 6. Re: Record Number in sequence

                    Thanks, though I'm not very familiar with ExecuteSQL usage.


                    As with daily import. It would be tricky. Because, let's say database contains 100 records of individual symbol x 30 symbols - 3000 records. Each day I add another 30 records (1 record per symbol). So, if I find all records without Serial, it would be 30 records, and each is of different symbol.

                    • 7. Re: Record Number in sequence

                      Your use of the term Record ID is what's causing problems, since there is the FileMaker concept of RecordID in which every record is assigned an internal ID by FileMaker. You can get this value with the Get ( RecordID ) function.


                      There is also the common, recommended practice of creating an ID field in every table which will store a unique, permanent identifier for the record. Commonly, this is a text field with an auto-enter calculation of Get ( UUID ).


                      In your case, you really want a serial # that increments for each record but respects the Symbol. In the past I've used a technique of creating a self-join relationship for the table which matches Symbol = Symbol. The "Record ID" field you have would have an auto-enter calc like: Max ( SelfJoin::Record ID ) + 1.


                      To avoid confusion (among other FileMaker developers) in the future, I'd recommend renaming the Record ID field to something like "EntryNumber" or something.


                      Hopefully I've fully understood what you're looking for.

                      • 8. Re: Record Number in sequence

                        Thank you for understanding me.


                        This may be what I'm looking for!

                        • 9. Re: Record Number in sequence

                          The suggestion of the self join auto enter is a pretty good one.


                          The script I suggested is not tricky at all. Dealing with 30 records is nothing difficult and the script takes care of respecting the increasing ID number per symbol.

                          • 10. Re: Record Number in sequence

                            RecordID is persistent, and can't be modified by the user. One of the main reasons I would never use it as a primary key is that it is auto-generated by FileMaker at record creation, and you can't change that behavior. So...if you ever need to export and re-import your data, all of the RecordIDs will change. It certainly has its uses...but not for what you're after here.


                            PeterDoern's method will work well for you, and you could easily create a value in whatever method you'd like. You might consider a variant where you have Table linked to TableSymbolJoin (for instance) by the ticker symbol. On your relationship graph, sort the records in TableSymbolJoin in descending order based upon the serial number you generate. Then you don't have to use Max ( TableSymbolJoin::SerialNumber ) + 1, you could simply use TableSymbolJoin::SerialNumber + 1. The sort would ensure that the first related record was the one with the largest serial number. I'd probably create two fields: SerialNumber and SerialNumberDisplay. SerialNumber would be as above, and SerialNumberDisplay would be an auto enter with a formula something like Table::Symbol & "-" & Table::SerialNumber.


                            Make both values auto-enter calcs.