PDM Searching for files that have blank variable value (never been set)

[bnemec]

Hello,
I'm trying to find files that have a specific variable value (date type) that has never been set OR older than a date. I can get one or the other, but I'm failing to get the OR to work in one search. I'm about to just do two searches and combine results, but thought I would ask here first.

I have this and it throws exception at search.GetFirstResult() unless I only add one variable. I don't think I understand how to use begin/end AND/OR. I've tried various begin and ends and they all throw exception when adding the variable twice. This works in the Search Tool.

Code: Select all

      try
      {
        IEdmSearch8 search = default(IEdmSearch8);
        search = (IEdmSearch8)vault.CreateUtility(EdmUtility.EdmUtil_Search);
        search.StartFolderID = vault.RootFolderID;
        search.SetToken(EdmSearchToken.Edmstok_FindFolders, false);
        search.SetToken(EdmSearchToken.Edmstok_FindFiles, true);
        search.SetToken(EdmSearchToken.Edmstok_FindItems, false);
        search.SetToken(EdmSearchToken.Edmstok_Recursive, true);
        search.SetToken(EdmSearchToken.Edmstok_AllVersions, false);

        search.SetToken(EdmSearchToken.Edmstok_Name, "%.sld%");

        search.BeginAND();
        search.BeginOR();
        search.AddVariable2(LastActiveUpdater.LAST_ACT_VAR_NAME, minDate, (int)EdmVarOp.EdmVarOp_DateLessThan);
        search.AddVariable2(LastActiveUpdater.LAST_ACT_VAR_NAME, "", (int)EdmVarOp.EdmVarOp_DateEqualTo);
        search.EndOR();
        search.EndAND();

        //prime the loop
        IEdmSearchResult5 searchResult = search.GetFirstResult();
        while (searchResult != null)

edit: I realized that after all that I failed to formulate a concise question. Can someone suggest how to properly use the begin/endOR, or offer link to examples or remarks in the SW online help that I've missed? I found one example where the use of BeginAND is in commented code.

[bnemec]

I apologize for nagging, but I'm open to any type of criticism at this point.

A bit more about what I'm trying. I have not found any way to search for files that have blank value for data card variable of date type. It could be due to being version free variable, I don't know. I'm temped to just search the SQL table instead of bothering with the IEdmSearch API. I avoid going around the API as much as possible though. I'm using AddVariable2("variableName", "", (int)EdmVarOp.EdmVarOp_DateEqualTo) also used EdmVarOp_StringEqualTo. Also tried passing null instead of empty string. All of those searches return results that most certainly have a date value, confirmed by getting the value for the search result file through API calls as well as GUI search tool.

I had also tried using the newer search IEdmSearch9.AddMultiVariableCondition but the syntax was a mystery to me. I gave up and went back to the pre IEdmSearch9 method. Considering that I'm looking for a blank value as well as not in a given state I think it's time I learn the new search functionality.

https://help.solidworks.com/2023/Englis ... dmapi.html

[Stefan Sterk]

Hi bnemec,
Not sure if this will help, but try compare it "1/1/1800" instead of a empty string.

If you add a date (for example, DrawnDate), as a property, the software does not require you to enter a date for Value / Expression. However, if you do not enter a date, Microsoft interprets the empty field as 1/1/1800 when you view the document's properties in File Explorer.

[bnemec]

Hi bnemec,
Not sure if this will help, but try compare it "1/1/1800" instead of a empty string.

Thank you for the suggestion. Unfortunately I was not able to make progress with that route. I tried a couple of "default" date values. I also tried using "-" as my search term and EdmVarOp_StringDoesNotContain; the text format in the database is "yyyy-MM-dd". So I thought I could get files where the text value does not contain that character would work. But it didn't, the search result set was empty.


Also, after doing some studying and testing of the new search syntax I realized that search by !state is still not available. I think I'm going to just filter the big result set in my code. Which kind of stinks as I'm sure it will be much less efficient than using the API to search the DB. But what's a couple more watt-hours? Maybe someday I'll come back and improve it.

[JSculley]

Per the API docs for IEdmSearch9

Extends IEdmSearch8 by providing the ability to peform a search for files and folders using logical operators, multi-variable conditions, and new search syntax.

Have you tried casting the object returned by CreateUtility to IEdmSearch9?

It's a long shot, but it's possible that the Search8 interface has a partial/broken implementation of the And/Or stuff. I'm not at work so I can't check.

[bnemec]

Per the API docs for IEdmSearch9

Extends IEdmSearch8 by providing the ability to peform a search for files and folders using logical operators, multi-variable conditions, and new search syntax.

Have you tried casting the object returned by CreateUtility to IEdmSearch9?

It's a long shot, but it's possible that the Search8 interface has a partial/broken implementation of the And/Or stuff. I'm not at work so I can't check.

Thank you JSculley. I was using search8 as I thought that search9 required the new special syntax from https://help.solidworks.com/2023/englis ... dmapi.html. Well, that doesn't seem true as I couldn't find any changes when casting to 9 instead of 8. The documentation indicates it depends on the accessor used; CreateSearch2 vs CreateSearch that triggers the change in search syntax expectations. It's in the Remarks section here and in the page of a few other preexisting members of IEdmSearch:https://help.solidworks.com/2023/englis ... State.html

So I kept pecking away at the new syntax from most basic search to more complex. I think we've got it.

- search.FileName = "*.sldprt | *.sldasm"; does what it looks like, only file must be a Solidworks part OR assembly.

- search.set_State("!OBSOLETE"); also does exactly as expected, omits all states with the name exactly = "OBSOLETE".

- search.AddVariable2("LastActiveDate", ":!{>2024-03-05}"); this is the one that took me a while.
+ the : character is a special character that is called multi-value specifier and without it the search only returns files with old dates leaving out files with no value for that variable.
+ the ! character is obviously short for NOT but it's placement is critical for getting files that do not have a value. It must be put before the {} pattern so it negates the entire pattern. Problem I ran into, while doing one step at a time, was what do I put inside the {} as a search term? Finally it dawned on me to step back and put the inverse of "older than x date". So now it gets all the files that are NOT newer than 2024-03-05, including those without a value. That's spelled out at the bottom of the Patterns section (look for !P) here: https://help.solidworks.com/2023/englis ... l#Patterns


My function for reference and critique.

Code: Select all

    private static int GetOldFiles(ref HashSet<IEdmSearchResult5> oldFiles, DateTime minDate)
    {
      IEdmSearch9 search = (IEdmSearch9)vault.CreateSearch2();
      try
      {
        string searchRootPath = Path.Combine(vault.RootFolderPath, "CAD_Data");
        search.StartFolderID = vault.GetFolderFromPath(searchRootPath).ID;
        search.SetToken(EdmSearchToken.Edmstok_FindFolders, false);
        search.SetToken(EdmSearchToken.Edmstok_FindFiles, true);
        search.SetToken(EdmSearchToken.Edmstok_FindItems, false);
        search.SetToken(EdmSearchToken.Edmstok_Recursive, true);
        search.SetToken(EdmSearchToken.Edmstok_AllVersions, false);
        search.FileName = "*.sldprt | *.sldasm";
        search.set_State("!OBSOLETE");
        string dateTerm = ":!{>" + minDate.ToString(LastActiveUpdater.DATE_FORMAT) + "}";
        search.AddVariable2(LastActiveUpdater.LAST_ACT_VAR_NAME, dateTerm);

        //prime the loop
        IEdmSearchResult5 searchResult = search.GetFirstResult();
        while (searchResult != null)
        {
          oldFiles.Add(searchResult);
          searchResult = search.GetNextResult();
        }
      }
      catch (Exception e)
      {
        Console.WriteLine(e.Message);
        Console.WriteLine(e.StackTrace);
        throw;
      }
      return oldFiles.Count;
    }