13 Replies Latest reply on May 2, 2014 10:31 AM by philmodjunk

    Execute SQL - previous record or previous range of records

    ultranix

      Title

      Execute SQL - previous record or previous range of records

      Post

           Hey, this week I upgraded from Filemaker 11 straight to Filemaker 13 and got introduced to amazing function called ExecuteSQL.

           I am not very familiar with it's coding yet, but what I've seen from demos is nothing short of amazing.

           However, I couldn't find how to SELECT data from previous record or range of previous (let's say, 5, records). For next or upcoming records, there is a:

           SELECT * FROM t1 OFFSET 1 ROWS FETCH FIRST 2 ROWS ONLY

           But what's the code for previous?

        • 1. Re: Execute SQL - previous record or previous range of records
          philmodjunk

               Define "Previous". And keep in mind that SQL does not execute from the context of your layout's found set.

               Thus, what you want may not be possible from ExecuteSQL unless there is some value in a record's field that can identify it as the previous record--as you might do by comparing the current record's serial number to those of other records in the table--but only if "Previous" means "created previous".

          • 2. Re: Execute SQL - previous record or previous range of records
            ultranix

                 Now I use table occurence, which is created using some fields that define "previous". For example, currentbar_2 (calculation: current_bar_number - 2)

                 I am just wondering, would that make my database faster, because I'm having pains, when running Replace field contents script for 225 fields that have to replace data in 30.000 records. It takes 8 hours to do the job. Anything that would make my solution faster and keep the data accurate (loop script performs faster, but has some inaccuracies), would do, so if you have suggestions, they are welcomed.

            • 3. Re: Execute SQL - previous record or previous range of records
              philmodjunk

                   Your post raises many questions:

                   Why do you need to use such a Replace Field Contents operation? (Which will take more and more time with a non linear growth curve as the number of fields to be updated increase if the field is an indexed field.)

                   Why not use an auto-enter field option to copy data from the previous record at the time the record is created and use script triggers to update the needed records/values any time a record is edited in a way that will require the update and use a scripted button to delete any records such that the script also will update (or mark for update during low use periods if this requires a mass update) the needed set of records?

                   (This is actually just as slow or even slower than replace field contents in terms of total time needed to update the same total number of records, but since it (hopefully) updates just a few records at a time, it should avoid large noticeable delays while massive numbers of records and their field indexes are updated.)

                   Basically, I see only a very general description of what you want to do and the lack of detail makes it hard to suggest an answer.

                   It is certainly possible to use ExecuteSQL with either a join or Where clause that links a given record to a previous record if there is a field value that reliably indentifies the correct "previous" record. But this sounds like something that could result in another "chain" of unstored calculations that could easily result in many of the same issues that you see with a calculation using GetNthRecord to access data in a previous record.

              • 4. Re: Execute SQL - previous record or previous range of records
                ultranix

                     Why do I need such Replace field contents operation?

                     Seriously, I wouldn't, but when it comes to calculating of 30.000 records, Filemaker doesn't "count" that many levels down (because almost each record uses result of previous record) and I need those calculations to be performed, and performed correctly, that is why after each calculation, i create another record (index field) with a same name and different appendix.

                     About data. The main concept, in short is to take stock market data from .csv/.txt file of certain timeframe (initially, 2 years, day-by-day, so every working day represents one record and LATER, each new day I add another record of yesterday's price data) and calculate various technical analysis indicators.

                     So once they are calculated, there is no need to re-calculate or do anything else with that data, as when it comes stored, it is imported into another database. This database is for calculation purposes only. What is important, that this data is "self-related". I mean, many of the indicators are based on the same field's result of yesterday or 2, 3, 4 days ago.

                     I never go through manually records in that database, as they are imported into another database, so it's like a medium to the real thing.

                     I would highly appreciate any suggestions to make those calculations faster, because, when I try to implement new function or indicator, sometimes it takes up to 10 minutes to update (Replace field contents) for just 4-5 fields in 800 record demo database.

                • 5. Re: Execute SQL - previous record or previous range of records
                  philmodjunk
                       

                            Seriously, I wouldn't, but when it comes to calculating of 30.000 records, Filemaker doesn't "count" that many levels down...

                       I think you missed my point. Why should you need to "count that many levels down"? Why not use an auto-enter field option to copy the data from the previous record so that you don't have to do any replace field contents operation or other "mass update" as the needed data is already in a field in the record to use in the calculation?

                  • 6. Re: Execute SQL - previous record or previous range of records
                    ultranix

                         First of all, I should note that records into that database are not entered manually, only via import from .csv or .txt file.

                         So I am having concerns whether it will "auto-enter" right calculations as calculation of one is usually in relation with other field's calculation and result.

                    • 7. Re: Execute SQL - previous record or previous range of records
                      philmodjunk

                           Yes, but auto-enter field options can be enabled during import.

                           You do raise a valid question. I've assumed that such a "chain" would evaluate correctly but never needed to test it. So I ginned up a simple test file.

                           Table1: has one field defined to auto-enter a serial number. I created 44 records in this table by holding down the keys for New record. Then I exported this data to produce a CSV with predictable values.

                           I then defined a table 2 with two fields:

                           Data (Number)
                           AutoEnterCalculation {Number: Data * Data + Let ( R = Get ( RecordNumber) ; If ( R > 1 ; GetNthRecord ( Data ; R - 1 ) ) )   }

                           "Do not replace"... was also cleared.

                           I then imported the data from the CSV into the second table with the option to enable auto-enter options selected. I got the result shown in the screen shot, which correctly evaluated the auto-enter calculation in each record.

                      • 8. Re: Execute SQL - previous record or previous range of records
                        ultranix

                             I will try that later, because it will take 2-3 days to adjust 530 fields.

                             I have another question. Let's say I have a date (calc::day) in one field and I need to calculate price (calc::close) of that day. I cannot add another table occurence, because it may be up to 60 more table occurences. I have a guess, that ExecuteSQL may work here, something like

                             SELECT close FROM calc WHERE day = '//calculation//'. (where in that calculation, it should refer to calc::start_date = calc::day)

                             But here I encounter 2 problems. I don't know how to define that //calculation// and other one - as day is reserved word, I tried to put in like 'day', but still it returned "?".

                             Could you help me on this?

                        • 9. Re: Execute SQL - previous record or previous range of records
                          philmodjunk

                               I cannot add another table occurence, because it may be up to 60 more table occurences.

                               I wouldn't let the sheer number of occurrences keep me from doing this, but ExecuteSQL does make more sense as a way to do this.

                               In order to help you figure out the SQL needed. I'd need a much more detailed picture of the data you need to query to pull this off.

                               Are you trying to refer to a record where the Start_date and day fields have exactly the same value? Won't there be other criteria to specify such as some kind of Stock ID so that you don't refer to records for a different stock?

                               Reserved words can be enclosed in double quotes. To keep the double quotes from messing up how FileMaker evaluates the quoted string that contains the SQL, you escape them with back slashes: \"day\"

                          • 10. Re: Execute SQL - previous record or previous range of records
                            ultranix

                                 start_date field is where the price pattern starts, and, as is clear from field name, it's a date field. so, for example, if price meets certain criteria to start a pattern, then start_date field's result is the date from calc::day field. It should be noted, that there is some delay to check whether price pattern confirmed or not, so the scenario, where calc::day is 2014-05-01 and calc::start_day is also 2014-05-01 is impossible. But, let's say, if pattern confirms, then on 2014-05-07 (calc::day) (but not always 6 days apart) calc::day_start field could return 2014-05-01. So, if other calculations confirm, that the price pattern has started, I need to get the price (calc::close) from the record, which day (calc::day) is 2014-05-01 (which is reflected in calc::day_start field after confirmation). Does it get any clearer?

                                 and yes, stock ID is calc::tick field. It should match, but it's not hard to figure this out myself, so I excluded this additional parameter to save reply space.

                            • 11. Re: Execute SQL - previous record or previous range of records
                              philmodjunk
                                   

                                        Does it get any clearer?

                                   Not really. I just end up with more questions.

                                   

                                        It should be noted, that there is some delay to check whether price pattern confirmed or not, so the scenario, where calc::day is 2014-05-01 and calc::start_day is also 2014-05-01 is impossible. But, let's say, if pattern confirms, then on 2014-05-07 (calc::day) (but not always 6 days apart) calc::day_start field could return 2014-05-01.

                                   That does not seem consistent with your previous post where you wanted calc::start_date = calc::day. Here, you say that this is impossible.

                              • 12. Re: Execute SQL - previous record or previous range of records
                                ultranix

                                     I meant, that, if I create table occurence, it would use such relationship: calc::start_date = calc_new_occurence::day and calc::tick = calc_new_occurence::tick.

                                     Any clearer?

                                • 13. Re: Execute SQL - previous record or previous range of records
                                  philmodjunk
                                       

                                            it would use such relationship: calc::start_date = calc_new_occurence::day and calc::tick = calc_new_occurence::tick.

                                       Relationships can be defined in SQL by adding a JOIN clause.

                                       and what you describe might actually work as a WHERE clause such as:

                                       WHERE  calc.\"day\" = ? AND calc.tick = ? " ; "" ; "" ; calc::start_date ; calc::tick )