1 2 3 Previous Next 44 Replies Latest reply on Apr 5, 2011 11:14 AM by brett_

    How to get previous record?

    brett_

      Title

      How to get previous record?

      Post

      Since RecordIds aren't always sequential, how do you ensure getting the previous record?

      Also, what does the formula for that look like?  I'm interested in getting the value of a few fields from the previous record.

        • 1. Re: How to get previous record?
          LaRetta_1

          I would suggest that you create a self join (join your main table to another occurrence of itself).  Join as:

          Main::ID > selfJoin::ID

          And then below in the relationship dialog, sort the selfJoin ID descending. You can now place the selfJoin fields directly on your layout or access them via script by just referencing the selfJoin fields directly. :^)

          • 2. Re: How to get previous record?
            brett_

            Where are you doing all this?

            • 3. Re: How to get previous record?
              Kays

              i assume in "file-->manage-->database-->relationships"

              K|Z

               

              • 4. Re: How to get previous record?
                brett_

                Thanks.  I've created another table and joined the two on Date, since I don't have ID.

                I'm trying to calculate gap size in stocks.  The formula is:

                gap size = [previous day close - current day open]

                My query looks like this:

                If (Date - table2::Date  = 1 ; Close Price - table2::Open Price ; "" )

                table2 is the self join table.  Nothing is showing in the calculated column.  Any ideas what I did wrong?

                Once I get the above working, I will need another condition that if it is Monday, then I need to look back three days (previous Friday).

                • 5. Re: How to get previous record?
                  philmodjunk

                  LaRetta's example relies on a relationship where the ID field is an auto-entered serial number. Is that the case here?

                  Do you need to match to the previous record or the previous record with the same stocktype?

                  I suspect you need a more sophisticated relationship such as:

                  Main::StockType = SelfJoin::StockType AND
                  Main::ID > selfJoin::ID

                  One useful temporary check to use is to place a portal to the self join table occurrence on your layout so that you can see what records if any are matching to your current record on your layout. If this portal is empty, you know you have something that needs to be changed in your layout.

                  • 6. Re: How to get previous record?
                    brett_

                    Stock type isn't an issue.  Ticker is the same for the entire table.

                    How do I get the ID you are talking about?  I'm not sure how that is useful though, given I need previous day and not previous ID, which possibly may not be previous day.

                    I placed Date of the duplicate table in a layout and see its dates match the original table dates.  I'm guessing my query needs to be something more like:

                    If (Date - (table2::Date - 1)  = 1 ; Open Price - table2::Close Price ; "" )

                    Notice I added  (table2::Date - 1) so it is the previous date.  The above ensures I have the previous day only.  

                    I've updated the query since current day always uses the open price and previous day always the close price.  If the condition equals 1, I have a previous day.  But how do I get the previous day's close price?  Right now, my query will use the current day's close price.  And that is exactly what I see in the calculated field results.

                    • 7. Re: How to get previous record?
                      philmodjunk

                      How do I get the ID you are talking about?

                      Define a field of type number, select the serial number auto-enter option to auto enter a serial number into the field of each new record. To update your existing records, Go to a layout for this table and add the new ID field to the layout. Show All Records and Unsort them if they are sorted. Click in the field and use Replace Field Contents option from the Records menu to update this field for all existing records with a serial number. Select the update Serial number in entry options check box so that your next new serial number will be the next number in the sequence.

                      I'm not sure how that is useful though, given I need previous day and not previous ID, which possibly may not be previous day.

                      How often do you create a new record in your table? Initial assumption was no more than one per day--given your original post asking for the "previous record". If you create no more than one new record per day, the serial number based relationship will work and you do not need the If function. The if function will not do as you expect here, it'll just return an empty string any time the previous record is not a single day in the past. You could use this relationship to access the most recent record from previous days:

                      Table::Date > Table2::Date   //Sort this relationship by date in ascending order.

                      If you want to link to a record only if it has the previous day's date, you could also use this relationship:

                      Table::cYesterday = Table2::Date 

                      Where cYesterday is defined as Date - 1, but this will return null if a record from exactly one day previous does not exist--just like your If function. If more than one record with yesterday's date is possible, you can use Last(table2::field) to access the most recent record with yesterday's date.

                      • 8. Re: How to get previous record?
                        brett_

                        New records are being added all the time since I'm still building a history.  So any auto generated ID will not always correspond to the most recent day.

                        Don't think I want to define this relationship:

                        Table::cYesterday = Table2::Date 

                        Since I will need to build a condition for Monday.

                        Back to the query I posted, any idea how I can get the previous day's close price?  I'm not sure what that syntax looks like.

                        • 9. Re: How to get previous record?
                          philmodjunk

                          Please note that I suggested two options for this issue:

                          Table::Date > Table2::Date   //Sort this relationship by date in descending order. (error in previous post corrected here.)

                          Should do what you want here.

                          • 10. Re: How to get previous record?
                            brett_

                            Ok.  Just implemented that and it is working fine.  Thanks a bunch!

                            Will work on the Monday condition.

                            • 11. Re: How to get previous record?
                              philmodjunk

                              Shouldn't need any Monday condition here unless you are generating records with Saturday or Sunday dates that you want to skip.

                              • 12. Re: How to get previous record?
                                brett_

                                Shouldn't need any Monday condition here unless you are generating records with Saturday or Sunday dates that you want to skip.

                                Since I'm doing:

                                If (Date - (table2::Date - 1)  = 1 

                                I need to check for Monday because that will be 3 days instead of 1.

                                This query:

                                If (DayName ( Date )  = "Monday" and Date - table2::Date  = 3; Open Price - table2::Close Price ; "" )

                                is generating the error:

                                An operator (e.g. +, -, *, …) is expected here.

                                Can you tell what I'm doing wrong?

                                • 13. Re: How to get previous record?
                                  philmodjunk

                                  Why do you need that calculation? Do you want null returned if there is no record from a previous date or null returned if the previous date is not one day earlier except for Mondays?

                                  What you have here, by the way is not a query, but a calculation. A query performs a search on a table or set of related tables. This expression just calculates a value.

                                  Usually that error is due to a parenthesis or ; missing or in the wrong place. I'm not spotting that error here, but would suggest adding a pair of parenthesis:

                                  If (DayName ( Date )  = "Monday" and ( Date - table2::Date = 3; Open Price - table2::Close Price ; "" )

                                  • 14. Re: How to get previous record?
                                    brett_

                                    Why do you need that calculation? Do you want null returned if there is no record from a previous date or null returned if the previous date is not one day earlier except for Mondays?

                                    Guess I'm just not following.  If I don't check Mondays, then I will never get a gap calculation for Monday.  Monday-Friday != 1.  It equals 3.

                                    In regards to the calculation syntax issue, I have tried parantheses in several locations but still nothing.

                                    --Update on this: Syntax is because of two IF statements.  Working some other way.

                                    1 2 3 Previous Next