6 Replies Latest reply on Jun 3, 2011 11:10 AM by philmodjunk

    Script to select a record in the current found set based on today's date

    RYinSF

      Title

      Script to select a record in the current found set based on today's date

      Post

      I am putting together my first simple application with FMP 11. My goal is to have a simple account register that supports budgeting activity.

      To this end, I have a table named "Register" that has Date, Amount, Status, CommittedBalance and TrialBalance fields. The use of the Date and Amount fields is obvious. The Status field contains one of three values (Paid, Cleared or Budgeted). CommittedBalance is a summary running total of records with a Status of Paid or Cleared. TrialBalance is a summary running total of all records. Thus, when sorted by Date, I have a list of transactions, each indicating the "CommittedBalance" and "TrialBalance" as it exists at the moment of that transaction. I have not found a way to persist the values in the two balance fields, so the balances are correct only if all transaction records are in the current found set.

      I'd like to have a script that, in browse mode, selects a record based on today's (or any given) date. To be clear, I am talking about selecting a record in the current found set. This would allow me to quickly move to the current portion of the register. 

      I have experimented with the "Go to Record/Request/Page-By Calculation" script step, but haven't found a way of identifying a target record's ID.

      Thanks for any help 

        • 1. Re: Script to select a record in the current found set based on today's date
          philmodjunk

          Your script would have to loop or otherwise step through your found set until it reached the first record with that date:

          Say you enter/select a date in a global date field: gSelecteDate and your records are sorted by date in ascending order:

          Go To Record/Request/Page [first]
          Loop
             Exit Loop if [Register::Date > Register::gSelectedDate]
             Go To Record [next ; Exit after last]
          End Loop

          With extremely large foundsets, you may want experiment with a "divide and conquer" algorithim that jumps to the middle of a range of records--effectively halving the number of records to be searched each time.

          • 2. Re: Script to select a record in the current found set based on today's date
            RYinSF

            Excellent - thanks for the help! Your approach worked very well. I made one small change - rather than the global date field you suggested, I set a script variable to Get(CurrentDate) and used that in the Go To Record.

            I haven't got my brain wrapped around the concept of global fields in a db table. I have a great deal of experience in the RDBMS and programming worlds - now need to learn how things get done in FMP.

            • 3. Re: Script to select a record in the current found set based on today's date
              philmodjunk

              The only advantage to using a global field instead of a variable in this case is if you want to specify dates other than the current date. Since it's a field, you can place it on your layout formatted with the pop up calendar and simply select the date you want to scroll to. If you are certain that you will always want to use today's date, use the variable as this will eliminate the need for adding a global field.

              I haven't got my brain wrapped around the concept of global fields in a db table. I have a great deal of experience in the RDBMS and programming worlds - now need to learn how things get done in FMP.

              FileMaker does have its own unique way of doing things that can require a major paradigm shift on the part of developers experienced in working with other RDBMS systems.

              Global fields are often the only way you can set up data entry in circumstances where you might use an unbound text box in other systems. Since it's a field, you can put it on a layout and format it for data entry like any other field. Since it's global, you can only store a single value and this value will not change no matter what record or table is current. You can, in fact, access a global field from any layout in your system even if you have not defined a relationship between the layout's table and the global's. For that reason, I put almost all of my global fields in a table reserved just for global fields to better manage them. The one exception, is that global fields can, with some limitations, be used in a relationship, and then they have to be defined in the table on the "one" side of a one to many relationship.

              Global fields also can be accessed while in find mode, so you can collect search criteria in global fields and then use a script that sets up your find requests, using data from the global fields so you can set up a layout of global search fields and then use them in a script to find your records.

              Global fields behave differently when a file is hosted over a network. Each client gets a different "copy" of the global fields so a change made to a global field by one user is not visible to any other. When the user closes the file, any changes to the value of global fields are not retained--these characteristics can be very useful in networked systems.

              • 4. Re: Script to select a record in the current found set based on today's date
                RYinSF

                A very clear and effective explanation of global fields. I have been wondering how to implement parameter driven queries - sorry - finds. Thanks again for your help. I really appreciate it.

                • 5. Re: Script to select a record in the current found set based on today's date
                  LaRetta_1

                  Another approach, which would be faster than a loop (if the record set were more than 50 records or so) would be to use GTRR (Go To Related Record).  Create a table occurrence of this table and join from the global field to this new self-join as:

                  MainTable::gDate = SelfJoin::Date

                  Below, sort the self join ascending on date (if it doesn't naturally display ascending on date in its unsorted state).

                  Then simple script step of:  Go To Related Record [ from self join ; using < current layout >  ]

                  Because you want to still see all of the records in the table, you do not restrict the lower section of GTRR dialog, i.e. uncheck 'show only related records'.  When you enter a date in the global and trigger the script, it will jump down to that record.  Keep in mind that you would lose your current found set (if you had one) because it would display all records but with running summaries you would be showing all records anyway, right?

                  NB: If your records are sorted and there are duplicate identical dates, it will not follow the sort order of the records in your layout.  Instead, it will go to the first matching date that was created in the table because, although we are sorting on date, if there are two identical dates, the next sort step past 'same date' will be natural sort order of creation.  A GTRR will go to the first match based upon the RELATIONSHIP and not sort order on a layout.

                  • 6. Re: Script to select a record in the current found set based on today's date
                    philmodjunk

                    What if the "first" record of that date is not in the current found set? I'm thinking that GTRR will then bring all the records into the current found set before jumping to that record. I know that will happen if the related record is not in the found set, but haven't tested to see if "one out of several related records" is not in the current found set and the omitted record is the "first", but would predict that this could happen and then our poster's found set has been modified in an undesirable fashion.