8 Replies Latest reply on Jul 27, 2011 5:02 PM by bumper

    Easiest way to find out the ID number?

    yuichim

      Title

      Easiest way to find out the ID number?

      Post

      Hi,
      I am trying to find an easiest and simplest way to find a record without performing a "find" in a layout.
      For example, if there is a table that has (ID, Name) and you only know the Name to find the ID, how can we achieve it without going to the layout, perform find, set variable with ID.

      Is there an alternative to this? 

      For example, the SQL has something like:  SELECT "ID" from USERS where Name="John"

      Can we do a script like above in FM?

      Any help much appreciated...

        • 1. Re: Easiest way to find out the ID number?
          philmodjunk

          Well, the SQL you describe IS performing a "find", it's just being done in a system that support SQL instead of FileMaker's more "query by example" type of performing a find. The SQL will produce a recordset object in most DB's that suport SQL, where FileMaker's "find" will produce a foundset of records--not exactly the same thing, but with a lot of similarities.

          Can you describe in more detail what you need to do here? Perhaps this is a value list where you want to select the name, but enter the ID number?

          • 2. Re: Easiest way to find out the ID number?
            yuichim

            Hi Phil,

            Well, what I am tring to do is to create a script that will generate the 2-week span daily report on how many "Samples" a user has sent.
            And in order to chieve and create a graph report:

            1. Create "report_sample" table (UserID, date, totalSampleNumber)
            2. Run a startup script to calculate and find out how many samples a logged in user has sent each day in a given 2-week period.

            In a startup script, I have a section to find out how many records are there by the userID number, but since I only know the username (AccountName), I have to find out the userID....

            • 3. Re: Easiest way to find out the ID number?
              philmodjunk

              And why wouldn't you want your start up script to perform a find for this?

              Such a script can use Get ( Currentdate ) and Get ( AccountName ) to specify the criteria.

              A relationship and Go To Related Records is also an option. The results can be nearly the same.

              • 4. Re: Easiest way to find out the ID number?
                yuichim

                I guess I am thinking too much in SQL way and trying to solve and find the records in short way as possible.
                I have decided to go with perform find and switching layouts....

                Thanks for your input though.  It helped me go forward..

                • 5. Re: Easiest way to find out the ID number?
                  philmodjunk

                  If you freeze the window, perform the find, and then switch back to your original layout after performing the find, the user will never know this took place except for the fact that they are getting the results they need from your database. Wink

                  • 6. Re: Easiest way to find out the ID number?
                    yuichim

                    Phil, you are totally right. :) 
                    I am just so used to not to perform so many tasks.  I think I do have to get used with it.  There's definitely pro and cons on both FM and SQLs.

                    Thanks a bunch!!!!

                    • 7. Re: Easiest way to find out the ID number?
                      philmodjunk

                      FM based finds make simple queries in scripts by the developer and simple ad hoc queries by the user a breeze. But I find that as the complexity of the criteria and the tables' relationships increase, you reach a "tipping point" where it becomes easier to do in SQL--not something we can switch to in FileMaker today, but maybe the ability to craft SQL queries as an option in place of Perform Find queries will become possible in the future. (Not making any predictions here, just thinking wishfully.)

                      • 8. Re: Easiest way to find out the ID number?
                        bumper

                        I'm not a SQL guy, nor do I use plug-ins very often, but maybe this will help:

                        myFMbutler DoSQL plug-in 1.4    

                        http://www.myfmbutler.com/index.lasso?p=425