6 Replies Latest reply on Jul 31, 2017 9:13 AM by guit4eva

    Last occurrence of record in IF statement

    guit4eva

      Hey guys,

       

      I'm stumped on this one. I have a table such as:

       

      Item   | Color | Qty

      Apple | Green | 30

      Apple | Green | 20

      Apple | Green | 50

       

      I'm trying to build a loop that does the following:

       

      1) If Apple exists and quantity exists, go to the next record. Repeat until the last occurrence of Apple, then create a new record. I know how to create a loop if there is just one occurrence of the record, but checking multiple occurrences has really made me confused.

       

      Thanks so much in advance!

        • 1. Re: Last occurrence of record in IF statement
          beverly

          Can you explain a little more on why you need to create a new record a the end of values (if more than one)?

          And perhaps some screenshot(s) to explain more?

          Thank you,

          Beverly

          1 of 1 people found this helpful
          • 2. Re: Last occurrence of record in IF statement
            ninja

            A curiosity...

            If you are always going to create a record...why do you need to be on the last record?  Just create the record.

             

            If you are trying to harvest data from that last record, simply GoToReocord/Request/Page [Last] and avoid the loop altogether.

            1 of 1 people found this helpful
            • 3. Re: Last occurrence of record in IF statement
              philmodjunk

              Other suggestions that may help:

               

              You can perform a Find to get just a found set of one category (apple) records. This find can use a hidden window to keep your current layout and found set unmodified by this process.

               

              or

               

              A relationship can match to just a selected category and then there are several different ways that you might use a relationship to work with all records in a given category.

               

              ExecuteSQL could also be used to access data from the last record of a specified category.

              1 of 1 people found this helpful
              • 4. Re: Last occurrence of record in IF statement
                guit4eva

                What I am trying to eventually achieve with this example is applying it to a time-logging database. Each staff member can clock in and out multiple times during the day. So basically, when clicking on the "Clock IN" button, the script must:

                 

                1) Open the time-log layout

                2) Check if the member has a clock in time. If so, and hasn't clocked out yet, then exit script.

                3) If there is a record with both clock IN and clock OUT time, then go to the next record for that member

                4) Keep going until either it reaches a record where the member hasn't clocked out yet, or create a new record with a Clock IN time.

                 

                I hope that makes sense?

                • 5. Re: Last occurrence of record in IF statement
                  philmodjunk

                  When you use an analogy as the means of asking a question, you risk getting answers that work well for your analogy and may not be the best for your actual case.

                   

                  But in this case, the above suggestions from the three of us still apply.

                   

                  As an example, you can use a relationship that matches by current date and employee ID to access a group of records for one employee for the current day. The Last function and for that matter GetNthRecord can access data from the last related record.

                   

                  You could also use ExecuteSQL to get a list of data from this same set of records for a specific employee and date. And then you can access the last row of data thus returned to get data from the last time that they logged in/out today.

                   

                  You could open a hidden window and perform a find for the same, then use go to record [last]

                   

                  A portal could list these records and you can access the last portal row (I prefer not to use a portal for this but others do.)

                  • 6. Re: Last occurrence of record in IF statement
                    guit4eva

                    Thank you so much guys!! Ha ha @Philmodjunk, I was expecting an amazing answer from you. What a legend. Yet again, thanks so much for helping me do something in 10 minutes that has literally had me stumped the whole day!