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.
Using columns from OP
ExecuteSQL (" SELECT count(FieldA) FROM mytable WHERE FieldA LIKE ? OR FieldA LIKE ? OR FieldA LIKE ? OR FieldA ID LIKE ?" ;"";""; "%" & ID & "%"; "%" & ID & "%"; "%" & ID & "%"; "%" & ID & "%" )
Does it have to be ExecuteSQL()? FilterValues() does this quite neatly, e.g.
FilterValues ( yourField ; yourList )
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.
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™
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.
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).
"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.
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.
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.)
Box Contains 123
The new additions that I need to check for could be:
Box Trying to put in: 123
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.
Remind me, why we can't do it with ExecuteSQL?
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.
branded as a Real programmer by erolst
what? you can't make a relationship using both fields?
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”):
allValues = ExecutesSQL ( "SELECT values FROM Table WHERE box = ? " ; "" ; "" ; 123 )
not IsEmpty ( FilterValues ( allValues ; checkValues ) )
= some are IN?