search contents of files in a FILE field, and if it finds "Insert", set a form action to set a field to ...

I need to have the ability to search contents of all files in a FILE field, and if it finds the word "Insert", set a form action to set a field value to ...

  • 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??

     :  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]}" );
    }