I suppose it depends on the types of files that you have in the file fields.  If Word or Excel documents, this may work, but I am unsure that this will work.  If ASCII/Unicode text docs, you can use a ModScript on pre-state or pre-transition.  Run a SQL query to retrieve the docs for this one item. If it returns any results then you have at least one doc with "insert".

select convert(varchar(max), convert(varbinary(max),b.ts_blob)), b.ts_blob,f.*
from TS_FILE_OBJS f
join ts_blobs b on f.ts_blobid=b.ts_id
where f.ts_tableid=1014 and ts_fieldid=262
and convert(varchar(max), convert(varbinary(max),b.ts_blob)) like '%insert%'
and f.ts_recordid=

Is this during a transition where the user is adding the files to be searched to the FILE field or editing those files??

If users are allowed to update uploaded/attached documents, what happens if a previous version of an uploaded document contains the "trigger" work but the current version doesn't?

To echo Dave's question: what types of files??

Vickie Ortega :  AFAIK, the files uploaded/attached in File type fields are always stored in the DB (TS_BLOBS).   : Dave is that right?

For this to work you will need to know the Item ID, the primary table name/dbname/id, the FILE field name/dbname.  If you want to further restrict the search to files added during specific transitions you'll need to know the internal name of the transition the user used to upload/attach the file.  I like State and Transition Internal Names because those are guaranteed to be unique.

I haven't worked with this table. Would this still be valid if the files are stored on the file system? Or, would this assume the files are stored in the database?

These are for files fields only which always store in the database.

ModScript run on a pre-transition would have access to the tableid, itemId and fieldId of the running database.  The biggest issue is whether the files in the field would be searchable.

After some tests, I found that .TXT, .RTF and .DOC files would satisfy the SQL to find the trigger word in the BLOB.  DOCX and PDF wouldn't.  Processing those might be "challenging".  ;-)

If the context was finding the trigger word in the files being uploaded in the current transition, the SQL would need to search TS_FILE_OBJS_TEMP table instead.  Some of the columns are different.  That script would run in post-transition context.

ours would be of type .txt or .sql, so those should work for this solution. thank you for this information. 

do you have the modscript sample you used to run this as a modscript? 

I have not actually run this, I just know it's possible.  I am not sure how to dynamically get the fieldId from the calling record.

var searchForText="%select%";
Shell.Db().ReadDynaSQL("select convert(varchar(max), convert(varbinary(max),b.ts_blob)) as textOfFile" &&& 
		"from TS_FILE_OBJS f join ts_blobs b on f.ts_blobid=b.ts_id " &&&
		"where f.ts_tableid=? and ts_fieldid=262 " &&&
			" and convert(varchar(max), convert(varbinary(max),b.ts_blob)) like '?' and f.ts_recordid=?"
			,[SQLColumnDef(DBTypeConstants.VARCHAR,textOfFile]
			,vectorOut
			,[Pair(DBTypeConstants.INTEGER,Shell.TableId(),Pair(DBTypeConstants.VARCHAR,searchForText),Pair(DBTypeConstants.INTEGER,Shell.Item().GetId().to_int())]
			
if (!vectorOut.empty()) {
	var found = true;
}

The ModScript guide has an example of using a Vector to get the output from ReadDynaSQL.

I would expand Dave's SQL and join TS_FIELDS, TS_TABLES, TS_TRANSITIONS and pass in the DB or Internal names of the table, field and transition.  Let SQL find the IDs.

Here's another example (I formatted this as Format / Formats / Inline / "Code" but it's not displaying like the listing in Dave's post)

def log_counts() {
var out = []; // creates an empty Vector for output from ReadDynaSQL
var rslt ; // result of the ReadDynaSQL method call

rslt = Shell.Db().ReadDynaSQL(
"Select " &&&
"(Select Convert(BigInt,Count(*)) AS REC_COUNT From ts_File_Objs With (NoLock,ReadUncommitted) Where ts_ID>0) As FILE_OBJ_COUNT," &&&
"(Select Convert(BigInt,Count(*)) AS REC_COUNT From ts_File_Objs_Change With (NoLock,ReadUncommitted) Where ts_ID>0) As FILE_OBJ_CHANGE_COUNT," &&&
"(Select Convert(BigInt,Count(*)) AS REC_COUNT From ts_File_Objs_Temp With (NoLock,ReadUncommitted) Where ts_ID>0) As FILE_OBJ_TEMP_COUNT," &&&
"(Select Convert(BigInt,Count(*)) AS REC_COUNT From ts_Blobs With (NoLock,ReadUncommitted) Where ts_ID>0) As BLOBS_COUNT," &&&
"(Select Convert(BigInt,Count(*)) AS REC_COUNT From ts_Changes With (NoLock,ReadUncommitted) Where ts_ID>0) As CHANGES_COUNT," &&&
"(Select Convert(BigInt,Count(*)) AS REC_COUNT From ts_ChangeActions With (NoLock,ReadUncommitted) Where ts_ID>0) As CHANGEACTION_COUNT," &&&
"(Select Convert(BigInt,Count(*)) AS REC_COUNT From ts_ChangeText With (NoLock,ReadUncommitted) Where ts_ID>0) As CHANGETEXT_COUNT," &&&
"(Select Convert(BigInt,Count(*)) AS REC_COUNT From ssf_ChangeActions With (NoLock,ReadUncommitted)) As SSF_CHANGEACTION_COUNT",
[
SQLColumnDef(DBTypeConstants.BIGINT,"FILE_OBJ_COUNT"),
SQLColumnDef(DBTypeConstants.BIGINT,"FILE_OBJ_CHANGE_COUNT"),
SQLColumnDef(DBTypeConstants.BIGINT,"FILE_OBJ_TEMP_COUNT"),
SQLColumnDef(DBTypeConstants.BIGINT,"BLOBS_COUNT"),
SQLColumnDef(DBTypeConstants.BIGINT,"CHANGES_COUNT"),
SQLColumnDef(DBTypeConstants.BIGINT,"CHANGEACTION_COUNT"),
SQLColumnDef(DBTypeConstants.BIGINT,"CHANGETEXT_COUNT"),
SQLColumnDef(DBTypeConstants.BIGINT,"SSF_CHANGEACTION_COUNT")
], out);

// Should return 1 rows of 8 columns.
for ( row : out ){
Ext.LogInfoMsg("ReadDynaSQL Result=${rslt} " &&&
"; FILE_OBJ_COUNT=${row[0]}" &&&
"; FILE_OBJ_CHANGE_COUNT=${row[1]}" &&&
"; FILE_OBJ_TEMP_COUNT=${row[2]}" &&&
"; BLOBS_COUNT=${row[3]}" &&&
"; CHANGES_COUNT=${row[4]}" &&&
"; CHANGEACTION_COUNT=${row[5]}" &&&
"; CHANGETEXT_COUNT=${row[6]}" &&&
"; SSF_CHANGEACTION_COUNT=${row[7]}");
}
}

Here's an alternate approach that uses a UNION query instead of a bunch of subqueries:

rslt = Shell.Db().ReadDynaSQL(
"Select 'FILE_OBJ_COUNT' As COL_NAME, Convert(BigInt,Count(*)) AS REC_COUNT From ts_File_Objs With (NoLock) Where ts_ID>0
Union All
Select 'FILE_OBJ_CHANGE_COUNT' As COL_NAME, Convert(BigInt,Count(*)) AS REC_COUNT From ts_File_Objs_Change With (NoLock) Where ts_ID>0
Union All
Select 'FILE_OBJ_TEMP_COUNT' As COL_NAME, Convert(BigInt,Count(*)) AS REC_COUNT From ts_File_Objs_Temp With (NoLock) Where ts_ID>0",
[
SQLColumnDef(DBTypeConstants.VARCHAR,"COL_NAME"),
SQLColumnDef(DBTypeConstants.BIGINT,"REC_COUNT")
], out);

// Should return 3 rows of 2 columns each.
for ( row : out ){o
Ext.LogInfoMsg("ReadDynaSQL Result=${rslt} ; ${row[0]} Count == ${row[1]}" );
}