1 2 Previous Next 23 Replies Latest reply on May 27, 2015 4:57 PM by justinc

    FMSQL - find one-of-many in a field's many?

    justinc

      I have a bit of an SQL question.  I have a list of IDs and I am trying to determine if any of those IDs exist in another field.  This field itself contains lists of IDs, so each record may have multiple IDs to search against.  There are at most 4 IDs that I will be search for in this field.

       

      If I were doing it directly on the table's layout, I would do this:

      enter find mode

      set FieldA = ID[1]

      new record

      set FieldA = ID[2]

      new record

      set FIeldA = ID[3]

      new record

      set FieldA = ID[4]

      Perform find

       

      I am somewhat familiar with SQL's "IN" function, but that seems limited to finding a single value that is in a list of values.  I am trying to find out if any one of a list is in another list.

       

      The whole purpose of this is simply to double check; I have a script that accepts a list of IDs and it is just checking that these IDs don't already exist in some other record in this table, before it adds them as a new record.

       

      Since I only have four possible inputs at the moment, I could just loop through them one at a time and check those results.  But it would be nice to do this in a single step if possible.

       

      Thanks,

      J

        • 1. Re: FMSQL - find one-of-many in a field's many?
          nicolai

          If you field is set as text, try this:

           

          ExecuteSQL ("
          SELECT count(ID) FROM mytable
                 WHERE  ID LIKE  ?
                 OR ID LIKE ?
                 OR ID LIKE ?
                 OR ID LIKE ?"
          ;"";"";
          "%" & 1111 & "%"; "%" & 2222 & "%"; "%" & 3333 & "%"; "%" & 4444 & "%" )
          

          This will be the same as a search functionality you described, but it will have the same problem: ID 111 will match 11111. Unless you use UUID, than this is not a problem.

          • 2. Re: FMSQL - find one-of-many in a field's many?
            nicolai

            Using columns from OP

             

            ExecuteSQL ("
            
            SELECT count(FieldA) FROM mytable
            
                  WHERE FieldA LIKE  ?
            
                  OR FieldA LIKE ?
            
                  OR FieldA LIKE ?
                  OR FieldA ID LIKE ?"
            ;"";""; 
            "%" & ID[1] & "%"; "%" & ID[21] & "%"; "%" & ID[3] & "%"; "%" & ID[4] & "%" )
            
            • 3. Re: FMSQL - find one-of-many in a field's many?
              erolst

              Does it have to be ExecuteSQL()? FilterValues() does this quite neatly, e.g.

               

              FilterValues ( yourField ; yourList )

              • 4. Re: FMSQL - find one-of-many in a field's many?
                user19752

                Depend on the data scale, it would be slow checking value in list since you can't use index for it without relation. Then you should consider splitting list to records.

                 

                And, a value in a column is the way SQL.

                • 5. Re: FMSQL - find one-of-many in a field's many?
                  nicolai

                  erolst

                   

                  Does it have to be ExecuteSQL()?

                   

                  I knew you gonna say that. Where is the fun in that? This does looks simpler than FQL statement.

                  • 6. Re: FMSQL - find one-of-many in a field's many?
                    erolst

                    nicolai wrote:

                    Where is the fun in that? This does looks simpler than FQL statement.

                    Sorry, will try to make my offerings appear more cryptic/complex in the future, so they appeal to Real Programmers™

                    • 7. Re: FMSQL - find one-of-many in a field's many?
                      user19752

                      Hmm, I make it.

                      He said "checking that these IDs don't already exist in some other record in this table", so that calculation should be

                      FilterValues ( List( tableOccurrenceToReferAllRecords::yourField ) ; yourListToCheck )

                      This is I said "data scale" on 1st param.

                      • 8. Re: FMSQL - find one-of-many in a field's many?
                        beverly

                        several suggestions from Filter() to ExecuteSQL(). might I suggest another? do you know that "lists" of value can be a many-to-many (multi-line key) in FileMaker? if you set up a relationship between your search list and whatever field needs to match. a simple NOT IsEmpty() to check would tell you if the value exists (or not).

                        beverly

                        • 9. Re: FMSQL - find one-of-many in a field's many?
                          erolst

                          user19752 wrote:

                          "checking that these IDs don't already exist in some other record in this table"

                          Right; I must have fallen asleep before I got to that point.

                           

                          user19752 wrote:

                          This is I said "data scale" on 1st param.

                          Just because the OP asks for ExecuteSQL() doesn't mean it's the best approach (or a good one, for that matter).

                           

                          Beverly's suggestion appears to be the most straightforward.

                          • 10. Re: FMSQL - find one-of-many in a field's many?
                            justinc

                            Thank you all for the discussion.  I am leaning towards something of a hybrid approach perhaps. 

                             

                            I like the filtervalues() idea; I have only used that once or twice and it is a tool that I think I should become more comfortable with.

                             

                            The relationship check I don't think would work, however, because of the way the data is structured.  And, I hate to create a relationship that is used in one place for one purpose for one extremely unlikely possibility - hence the initial inclination towards SQL.

                             

                            To explain that - and my original quandry more fully - here's some hypothetical data in the table I want to check.  (There are other reasons for this data structure, so changing that structure isn't really an option at this point.)

                             

                            BoxContains
                            123

                            abc

                            aff

                            gii

                            123

                            ert

                            cdc

                            123

                            aaa

                            bbb

                            123

                            err

                            doh

                            222

                            abc

                            lmp

                            uuu

                            222

                            qww

                            irt

                            etc...

                             

                            The new additions that I need to check for could be:

                            BoxTrying to put in:
                            123

                            uuu

                            abc

                            ppp

                             

                            So, I need to compare the 3 values individually (uuu, abc, ppp) and make sure that none of them already exist somewhere in '123'.  As you can see, 'abc' DOES already exist for '123', but it is not in the same grouping of other values, so a relationship wouldn't work.  However, a relation would work to at least give me a list of the 2nd column's values, and then I could do a FilterValues() using the 3 new values to see what shows up.

                            • 11. Re: FMSQL - find one-of-many in a field's many?
                              nicolai

                              Remind me, why we can't do it with ExecuteSQL?

                              • 12. Re: FMSQL - find one-of-many in a field's many?
                                nicolai

                                erolst wrote

                                 

                                so they appeal to Real Programmers™

                                Who do you call a Real Programmer, mister? I hasn't been insulted like that since the last sprint meeting.  It is not my fault I studied Computer Science at University instead of History of Art, but I still want to be a FileMaker developer. Can I, please?

                                 

                                On the other thought, I might add it as a signature now.

                                 

                                Nicolai

                                branded as a Real programmer by erolst

                                • 13. Re: FMSQL - find one-of-many in a field's many?
                                  beverly

                                  what? you can't make a relationship using both fields?

                                  beverly

                                  • 14. Re: FMSQL - find one-of-many in a field's many?
                                    erolst

                                    justinc wrote:

                                    So, I need to compare the 3 values individually (uuu, abc, ppp) and make sure that none of them already exist somewhere in '123'.

                                     

                                    So, then combine the two methods (aka “take the hybrid road”):

                                     

                                    Let (

                                    allValues = ExecutesSQL ( "SELECT values FROM Table WHERE box = ? " ; "" ; "" ; 123 )

                                    not IsEmpty ( FilterValues ( allValues ; checkValues ) )

                                    )

                                     

                                    = some are IN?

                                    1 2 Previous Next