1 2 Previous Next 22 Replies Latest reply on Oct 9, 2014 6:33 PM by bigtom

    Need a little help with eSQL selecting wrong data

    bigtom

      I am using executeSQL in this fashion.

       

      Go to Record/Request/Page [No dialog; ExecuteSQL ( " SELECT RID FROM Invoices WHERE "Invoice ID" = ?" ; ""; ""; $order)]

       

      Invoices::RID = Get(RecordID)

      $order is set to a predetermined Invoice ID that matches the invoice IDs in the database. This scripts brings electronic payment data into the solution and applies them to the invoice.

       

      Not really working how I want it to. There are only 125 records in the test database and the data viewer is returning values like "261 335" for the eSQL evaluation. Pretty weird.

       

      Any ideas? I hoped this would save me the time of doing a find for the record and allow me to go directly to it. I am guessing I have the syntax right.

        • 1. Re: Need a little help with eSQL selecting wrong data
          bigtom

          For example in one case the eSQL evaluates as "261¶385" or

           

          "261

          385"

           

          when the actual value it should find is "100"

          • 2. Re: Need a little help with eSQL selecting wrong data
            Mike_Mitchell

            Get ( RecordID ) returns FileMaker's internal record ID, which might change for a given record (for example, after an Import). Therefore, you really can't rely on it to be a fixed value for use as, say, a primary key. Are you certain your Invoice ID field actually equals the Record ID?

            • 3. Re: Need a little help with eSQL selecting wrong data
              coherentkris

              ExecuteSQL is returning a return delimited list because it is finding two "Invoice::RID"'s from Invoices that match the criteria. Mike is correct that Get Record ID is probably not what you want to be using. You should be setting RID to a unique numeric so it can act as a primary key for the invoice table. Read up on Get Record ID here http://www.filemaker.com/help/html/func_ref2.32.53.html

              • 4. Re: Need a little help with eSQL selecting wrong data
                user19752

                And, you don't need SQL here,

                Find[Invoice ID;$order]

                 

                "Goto Record" step needs "record number" as parameter, not "record id" nor something finding criteria.

                record number is found set counter acording to current sort order.

                • 5. Re: Need a little help with eSQL selecting wrong data
                  bigtom

                  Thanks for the info.

                   

                  I will give the find a try and see how quick it is.

                   

                  I was hoping to use eSQL for this. I guess it is not possible.

                   

                  Thanks again.

                  • 6. Re: Need a little help with eSQL selecting wrong data
                    bigtom

                    The reference says the Get (record ID)  value for a record does not change.

                     

                    But it does seem I was using it the wrong way as there is no way to navigate to a RecordID.

                    • 7. Re: Need a little help with eSQL selecting wrong data
                      coherentkris

                      You are correct I was confusing my references. I was thining of Get ( RecordNumber ) which is totally found set driven while Get (RecordID) is not.. my mistake.

                      • 8. Re: Need a little help with eSQL selecting wrong data
                        Mike_Mitchell

                        Help says,

                         

                        "The number returned is a decimal value (an integer) generated by FileMaker Pro when the record is created. It does not change."

                         

                        However, importing a record from one database to another counts as "creating" a record. Therefore, the record ID WILL change. (Think of it as being similar to the auto-enter serial number function, except you can't reset it or otherwise monkey with it.)

                         

                        Hence, you can't use it as a primary key.

                         

                        HTH

                         

                        Mike

                        • 9. Re: Need a little help with eSQL selecting wrong data
                          erolst

                          bigtom wrote:

                          I was hoping to use eSQL for this. I guess it is not possible.

                           

                          This is because ExecuteSQL() has no notion of a found set. If you have FM13 and a summary field ListOf for your primary key, you can use

                           

                          Go to Record/Request/Page [ by calculation ; see below ]

                           

                          ValueCount ( Left ( Table::sListOfPrimaryKey ; Position ( Table::sListOfPrimaryKey ; $orderID ; 1 ; 1 ) ) ) *

                           

                          to navigate to $orderID within a given found set (preferably after checking that it contains the record with $orderID).

                           

                          *(which is the algorithm to find IndexOfValueInList that one should have handy as a CF).

                          • 10. Re: Need a little help with eSQL selecting wrong data
                            Malcolm

                            "The number returned is a decimal value (an integer) generated by

                            FileMaker Pro when the record is created. It does not change."

                             

                             

                            However, importing a record from one database to another counts as

                            "creating" a record. Therefore, the record ID WILL change. (Think of

                            it as being similar to the auto-enter serial number function, except

                            you can't reset it or otherwise monkey with it.)

                            I don't think that is a suitable comparison. An auto-enter serial number

                            is means of generating data within a field, which you control and it is

                            a part of your data set.

                             

                            A record is not your data, it is the container for your data.

                            Get(RecordID) is not a part of your data.  It is a number identifying

                            the container of your data. Think of it as the data's address.

                             

                            Malcolm

                            • 11. Re: Need a little help with eSQL selecting wrong data
                              bigtom

                              $order is not a key value. It is just a field value.

                               

                              I am just not sure why I cannot navigate directly to a record if I have the Record ID. I only want to do this for temporary navigation in a script.

                               

                              This is the kind of situation that SQL seems to have one up on Filemaker I guess. I just need to set one field on the record that has the specified InvoiceID($order). Finding the found count to veryify the ID exists then finding the record and then navigating to the record seems like a lot of effort to set one field.

                               

                              What use is RecordID anyway?

                              • 12. Re: Need a little help with eSQL selecting wrong data
                                Mike_Mitchell

                                The serial number automatically increments and never decrements, unless you reset it. Every time you create a record, it gets the next number in line.

                                 

                                RecordID automatically increments and never decrements. You can’t reset it. Every time you create a record, it gets the next number in line.

                                 

                                Hence the comparison. I didn’t say it was perfect, or that the functions were identical. I said “similar”.

                                • 13. Re: Need a little help with eSQL selecting wrong data
                                  Mike_Mitchell

                                  Actually, if you have the RecordID, you can navigate to it directly … using the PHP API.  

                                   

                                  You’re blending the purpose of the RecordID with the primary key. They are NOT the same thing. This is where FileMaker differs from a typical SQL database. It has an internal schema that assigns an ID to every object in the database (including records, fields, layouts, layout objects, and so forth). This is why you can change the name of an object without breaking all the references to it.

                                   

                                  RecordID isn’t part of the record’s data. It’s part of FileMaker’s internal schema. So the RecordID is NOT something you can use as a primary key. In SQL terminology, you couldn’t perform a query against it, because it’s not part of the record. It just keeps FileMaker’s parsing engine straight when you refer to a particular record via any one of several methods.

                                   

                                  If you want to set a single field on a single record, just do as has been recommended:

                                   

                                  1) Find the record.

                                  2) Set the field.

                                   

                                  Alternatively, you can set up a self-joining relationship based on a global field (where the global points to the InvoiceID). Set the global equal to the $order you want, then set the related Invoice field equal to the value you want.

                                   

                                  These are two good methods for working with FileMaker’s paradigm.

                                  • 14. Re: Need a little help with eSQL selecting wrong data
                                    Malcolm

                                    Yes, I got that, you are playing up the similarities.

                                     

                                    I think the differences between the two things are more numerous and

                                    more important, that's all.

                                     

                                    The serial number is an auto-enter option available for text and number

                                    fields that saves the user the effort of typing the data.

                                     

                                    The record id is not an option. It is always generated.

                                     

                                    If a serial number is used the value generated forms part of the data

                                    contained by the record

                                     

                                    The record number is always present and is not a part of the data

                                    contained by the record. It is an identifier associated with the record

                                    structure for the table.

                                     

                                    The developer has control over the values generated by serial number

                                    auto-enter options.

                                     

                                    A record id value cannot be controlled.

                                     

                                    and so on.

                                    1 2 Previous Next