11 Replies Latest reply on May 19, 2011 6:58 AM by brett_

    How to inclue next record in find?

    brett_

      Title

      How to inclue next record in find?

      Post

      When doing a find, is it possible to include the record immediately after a matched record (even if the next record is not a match)?  I want the matched records but also am interested in seeing the record that follows each match.

        • 1. Re: How to inclue next record in find?
          RestaurantCharlie

          The next record depends on how records are sorted.

          If you feel comfortable with scripting, I can think of a couple of ways to solve this.

          However someone else out there might have a solution that doesn't involve scripting.

          • 2. Re: How to inclue next record in find?
            brett_

            I'm not sure how my records are sorted by date sorting is what I do most. 

            I haven't used scripting but if that works, I'm all for it.

            • 3. Re: How to inclue next record in find?
              RestaurantCharlie

              Let's say I have a table with the following records

              Field1       Field2          Field3

              abc           2                  1

              bcd           3                  3

              cde           1                  2

              def           5                  6

              efg           6                  4

              hij            4                  5

              If I do a search for "c" in Field1 the first three lines would show up. But depending on if sorted by field 1, field 2 or field 3, the next line(record) you say you want to see would be  4, 6 or 5 (if sorted ascending).

              So if you don't have a standard way of sorting the records, are you sure you really need/want to see the next record?

              • 4. Re: How to inclue next record in find?
                LaRetta_1

                "is it possible to include the record immediately after a matched record (even if the next record is not a match)"

                It would help to understand the purpose and context here.  What are you trying to accomplish?  It is possible but maybe not in the way you would expect. :^)

                • 5. Re: How to inclue next record in find?
                  brett_

                  "So if you don't have a standard way of sorting the records, are you sure you really need/want to see the next record?"

                  Add another field called Date as the first field in your example.  Date is the field everything should be sorted by.

                  But how do I set Date as the sort field?  And what does that mean?  If I then click the top column of another field, everything will be sorted that way an no longer by Date.  Then I go to Find.  Is it still sorted by date or the last field I sorted by?

                  "It would help to understand the purpose and context here.  What are you trying to accomplish?"

                  I'd like the Find to bring back all days that have the Find criteria, plus the very next day.  So if I get these days back in the results:

                  4/1/2011
                  4/5/2011
                  4/20/2011

                  I'd expect the results to be:

                  4/1/2011
                  4/2/2011
                  4/5/2011
                  4/6/2011
                  4/20/2011
                  4/21/2011

                  If for some reason, 4/21/2011 is not available, I'm not sure what should happen there.  Just bringing back 4/20/2011 is fine. 

                  • 6. Re: How to inclue next record in find?
                    LaRetta_1

                    Okay, then here is how I would do it:

                    Create a global text field called gDates (and of course where I reference table, you will use your table name and 'yourDate' refers to the date field you wish to search).  In this field, let your User type a multiline of dates (meaning just like you displayed with a carriage return in each). 

                    The dates must be valid format for your OS and if no year is specified, it will act just as regular date meaning it will assume current year.  If they are not valid dates, such as 5/88/21z, they will be omitted from the find and they will be presented to the User in a Custom Dialog at the end of the process.

                    Script would look like this:

                    Enter Find Mode [ uncheck pause ]
                    Loop
                    Exit Loop If [ $i   ≥  ValueCount ( table::gDates )
                    Set Variable [ $i ; $i + 1 ]
                    If [ not IsValid (GetAsDate ( GetValue ( table::gDates ; $i ) ) ) ]
                    Set Variable [ $baddates; Value:$baddates & GetValue ( table::gDates ; $i ) & " " ]
                    Else
                    Set Field [ table::yourDate; GetValue ( table::gDates ; $i ) ]
                    Duplicate Record/Request
                    Set Field [ table::yourDate; table::yourDate + 1 ]
                    Duplicate Record/Request
                    End If
                    End Loop
                    Delete Record/Request
                    Perform Find[]
                    If [ not IsEmpty ( $baddates ) ]
                    Show Custom Dialog [ Message: $baddates & "were skipped as invalid."; OK ]
                    End If

                    What this will do ... it will start at top of multiline date field and loop create new find requests.  As it creates one, it will duplicate it and then change the entry (increasing by one day).  If the date is invalid, it will write it to a variable to display at the end.

                    • 7. Re: How to inclue next record in find?
                      brett_

                      Thanks LeRetta.

                      "In this field, let your User type a multiline of dates (meaning just like you displayed with a carriage return in each)."

                      I'm on the only user.  When I search dates, I normally do these types of queries:

                      5/*/2011 (give me all of May for 2011)

                      5/*/* (give me every May)

                      1/*/2011...4/*/2011 (give me Jan thru April 2011)

                      I haven't tried dates as you mentioned with a carriage return.  Why would that be needed?  Can you give an example?

                      • 8. Re: How to inclue next record in find?
                        LaRetta_1

                        I'd like the Find to bring back all days that have the Find criteria, plus the very next day.  So if I get these days back in the results:

                        4/1/2011
                        4/5/2011
                        4/20/2011

                        I'd expect the results to be:

                        4/1/2011
                        4/2/2011
                        4/5/2011
                        4/6/2011
                        4/20/2011
                        4/21/2011

                        ... and now you say:

                        When I search dates, I normally do these types of queries:

                        5/*/2011 (give me all of May for 2011)

                        5/*/* (give me every May)

                        1/*/2011...4/*/2011 (give me Jan thru April 2011)

                        How am I supposed to provide a solution with those two totally different requests?  How would I take 5/*/2011 and add one day to it like you originally requested?  Or if you want 5/*/* how can I add one day to that?

                        You need to be clearer on your requirements.  These two types of requirements aren't even close to each other.

                        • 9. Re: How to inclue next record in find?
                          brett_

                          "These two types of requirements aren't even close to each other."

                          Which two requirements are you referring too?

                          • 10. Re: How to inclue next record in find?
                            LaRetta_1

                            You originally asked (and the subject of your post was) how to find the NEXT RECORD in a find.  You presented example of entering list of 4/1/2011, 4/5/2011 etc (no sequence at all) and wanting the NEXT date so you would get 4/1/2011 and 4/2/2011 ... 4/5/2011 and 4/6/2011.

                            There is no range indicated anywhere that subject, post or example.  That is one requirement.

                            Now you say you want 5/*/* and I ask you how you expect to add THE NEXT DAY to that requirement?  Do you want that range and then 6/1/2011?  These are not the same requirements at all.

                            I have no idea what you want now and I have asked again for specifics because this simply isn't clear.

                            • 11. Re: How to inclue next record in find?
                              brett_

                              Ok.  Thanks.  What I'm trying to do with a query is still exactly the same as the subject to the post.  I will query some criteria and it brings back some rows.  Each of those rows have a date.  Using data from one of my previous post, my query may bring back three rows (I'm only showing one column from the results):

                              4/1/2011
                              4/5/2011
                              4/20/2011

                              I'd expect the results to be:

                              4/1/2011
                              4/2/2011
                              4/5/2011
                              4/6/2011
                              4/20/2011
                              4/21/2011

                              So what ever the query brings back, it should also get +1 day for each row.  

                              Is it how I'm querying that is the issue?  So

                              4/*/2011 (get all April results for 2011)

                              4/*/2011...3/*/2011 (get all March thru April 2011)

                              4/*/* (get all April for every year)

                              will all bring back some results.   And every one of the above three queries may only bring back the same three results I have above, at which point +1 day is added to each row of the result.

                              I have 70 columns.  I won't be just querying the date field.  I may also be adding criteria to other columns.  Still, that brings back results and maybe only the three rows from my example:

                              4/1/2011
                              4/5/2011
                              4/20/2011

                              Once those three results come back, +1 day is added to each row, producing the final result:

                              4/1/2011
                              4/2/2011
                              4/5/2011
                              4/6/2011
                              4/20/2011
                              4/21/2011

                              I suppose if that has to be a two part query (initial find, then run that through something to get +1 day), that is ok.  I'm not sure how it needs to be done in FM.

                              Sorry for the confusion.  Please let me know if anything I'm posting here doesn't make sense or if you need more clarification.  Thanks again for helping.