11 Replies Latest reply on Apr 11, 2011 5:10 AM by ErmannoAntonelli

    how to create in FM Pro a search equivalent to SELECT TOP in SQL

    ErmannoAntonelli

      Title

      how to create in FM Pro a search equivalent to SELECT TOP in SQL

      Post

      Hi Guys,

      I'm new to FM Pro and I have been doing pretty much all I need, but I'm struggling with one particular search.

      Here is what I have done:

      I have a Layout in my main project reading values from table (JOBS), I have a relationship with another table (TASKS) that i present in a portal. 

      What works so far:

      I can search for all records in my JOBS DB that have a particular value in (TASKS::TASK_DESCRIPTION)

      What I want is to show only the result only if the value in TASKS::TASK_DESCRIPTION with the most recent date inserted.

      I have attached a screen shot of a unwanted search result.

      I would fin the value in SQL with SELECT TOP, but I dnot' seem to find the way with the scripting tool.

      Any suggestion is appreciated.

      Thanks,

      Ermanno

      screenshot.png

        • 1. Re: how to create in FM Pro a search equivalent to SELECT TOP in SQL
          Frinholp

          Could you not use Last ( field ) to get the latest record?

          From help:

          Description 
          If field specifies a repeating field then it returns the last non-blank  repetition. If field specifies a related field, then  it returns the last non-blank value in the related set.
          Note  The last related value will  depend on the way related records are sorted. If the  related records are not sorted, then the Last function  returns a value based on the creation order of the records.

          Set a global variable such as $$RecentDate =  Last ( date )

          Filter portal using Date = $$RecentDate

          Lee

          • 2. Re: how to create in FM Pro a search equivalent to SELECT TOP in SQL
            ErmannoAntonelli

            Hi Lee,

            I think I'm doing something wrong because using Last always return an empty value from the portal, sorry if I'm being dumb here...

            This is the script

            Go to Layout [ “JOBS” (Jobs) ]

            Set Variable [ $$Last_Update; Value:Last ( Tasks::Date_Inserted ) ]

            Perform Find [ ]

            Go to Record/Request/Page[ Last ]

            Exit Script [ ]

            The Portal is filtered by Tasks::Date_Inserted  = $$Last_Update

            This is Date_Inserted field type

            Date_Inserted Text Indexed, Auto-enter: “Creation Timestamp (Date and Time)”, Prevent data that is automatically entered from being changed., Always validate

            The Task_Inserted I'm searching in is type Text

            Is it that what you mean, sorry I'm still learning this scripting tool.

            Thx

            • 3. Re: how to create in FM Pro a search equivalent to SELECT TOP in SQL
              Frinholp

              What is Last ( Tasks::Date_Inserted ) evaluating to in your data viewer whilst you are in the Jobs layout?

              • 4. Re: how to create in FM Pro a search equivalent to SELECT TOP in SQL
                ErmannoAntonelli

                This is the problem is empty, it seems like in the portal is going to the new record line (last+1) not the real last.

                I have also tested checking for last Key_index and does the same, so i think the problem is in the portal.... any idea?

                • 6. Re: how to create in FM Pro a search equivalent to SELECT TOP in SQL
                  philmodjunk

                  First, it's important to understand how a find works when you enter search criteria into a portal row's field.

                  Finds search the layout's table for records, not (directly) the portal's table. When you enter "Sent to Ermanna" as criteria in the portal's Task Description field, you are telling FileMaker to find all records in the layout's table that have at least one related tasks record with "sent to Ermanna" in Task Description field. Once those records are found, you are returned to browse mode and ALL the related task records--not just those with "sent to Ermanna" --listed in the portal.

                  If you only want to see the tasks records that match your criteria, you'll need to use a different method. You can use a filtered portal to filter the records in the portal to show only those matching your criteria or you ca search on a different layout that is based on the tasks records. Fields from the current layout's table can be added to this layout to provide additional information.

                  In either case, once you can sort the results by the date field so that the most recent dated record is at the top of the list. In a filtered portal, you can specify this as a sort order for the portal in portal setup... If you sort a tasks table based layout for these records, you can then perform a sort on the found set to put the most recently dated records first.

                  • 7. Re: how to create in FM Pro a search equivalent to SELECT TOP in SQL
                    ErmannoAntonelli

                    Hi Phil, I understand what you're saying....

                    Maybe my description wasn't totally clear, I'm not trying to filter the portal result, is fine to show all the entries.

                    What I need to show are the records where only the last entry in the portal is my search criteria, got me?

                    Maybe I have used a wrong approach, let explain you what I want to achieve

                    This is a job tracking system, so the main table is ordered by job number. Every job number has a series of relationship for different purposes, one specific relationship is this task management, where I auto enter date and user entering the record + task status and task time.

                    Now the search with issue: I want to search for jobs only with a specific task_status, but I want to have all the other status shown in the portal?

                    Makes sense?

                    Thx 

                    • 8. Re: how to create in FM Pro a search equivalent to SELECT TOP in SQL
                      ErmannoAntonelli

                      forgot to say show the record only matching last task status, the rest is there only for history purpose

                      • 9. Re: how to create in FM Pro a search equivalent to SELECT TOP in SQL
                        philmodjunk

                        Not really clear. What you describe appears to be the standard way a filemaker find works. Specify search criteria in the portal, perform the find get all records where the portal has a record with that data but all task records in the portal will be displayed. I can't square that last post with this statement:

                        "What I want is to show only the result only if the value in TASKS::TASK_DESCRIPTION with the most recent date inserted."

                        With your database, I could certainly enter a date in the date field and a description in the description field to find all records with one such combination of date and description somewhere in the portal. Is that what you had in mind?

                        • 10. Re: how to create in FM Pro a search equivalent to SELECT TOP in SQL
                          ErmannoAntonelli

                          Hi Guys,

                          thanks to your suggestions, I made some more testing and I almost sorted my problem.

                          I've noticed that the portal work kinda strange in search but sorting results sorted does the trick.

                          So what I'm doing here is first getting the result searched and than omit the unwanted one.

                          Question: How do I Exit my loop if I have evaluated all my records. Sorry the question might be stupid but can't find a good answer.

                          The script works fine if I let the loop go and exit manually. 

                          Maybe there is a better way to do so?

                          Thanks,

                          Ermanno

                          Go to Layout [ “Jobs” (Jobs) ]

                          Set Variable [ $$Value; Value:Tasks::Task_Description ]

                          Perform Find [ ]

                          Go to Portal Row [ First ]

                          Sort Records [ Specified Sort Order: Jobs::JOB COUNTER; ascending ] [ Restore; No dialog ]

                          Go to Record/Request/Page [ First ]

                          Loop

                          Exit Loop If [ ]

                          If [ Tasks::Task_Description ≠ $$Value ]

                          Omit Record

                          Go to Record/Request/Page [ Next ]

                          End If

                          End Loop

                          Go to Record/Request/Page[ Last ]

                          Exit Script [ ]

                          • 11. Re: how to create in FM Pro a search equivalent to SELECT TOP in SQL
                            ErmannoAntonelli

                            Hi Guys, after some debugging the problem is solved, and works great..

                            See below...

                            Go to Layout [ “Jobs” (Jobs) ]

                            Set Variable [ $$Value; Value:Tasks::Task_Description ]

                            Perform Find [ ]

                            Sort Records [ Specified Sort Order: Jobs::JOB COUNTER; ascending ][ Restore; No dialog ]

                            Go to Record/Request/Page[ First ]

                            Loop

                            Go to Portal Row [ First ]

                            Set Variable [ $$Records; Value:Get ( FoundCount ) ]

                            Set Variable [ $$Current_Record; Value:Get ( RecordNumber ) ]

                            If [ Tasks::Task_Description ≠ $$Value ]

                            Omit Record

                            End If

                            Go to Record/Request/Page [ Next ]

                            Exit Loop If [ $$Records = $$Current_Record ]

                            End Loop

                            Exit Script [ ]