Is it possible to set a version free variable via API without showing in the file history.

[bnemec]

I would like to set a version free variable on files without creating an entry on the file history. This will be set on several two or three thousand files a day and will make using file history difficult as there will be hundreds of irrelevant rows.

I tried both settings on the version free variable, all versions or latest only. I don't really know the difference between them, I'm guessing the "all versions" option will only have one record for the file in SQL where latest only will store the version in the the table with the version number where value was last changed. I would prefer the simplest method, no historical data needed at all for this variable.

I'm using the BatchUpdate2 utility with flags 1 and 2 to change all configs and update if not on any card. I've searched the help for anything related to file history and if it's there I'm missing it.

Thank you.

[mp3-250]

I do not think it is possible.
We get a lot of history entries from action performed by the pdm system when a user open a file, probably because we transitioned the variable from custom properties to db only and from history to history free.(which was a mess of a choice imho.)

Not logging those actions would be a compliance problem imho.

[bnemec]

I do not think it is possible.
We get a lot of history entries from action performed by the pdm system when a user open a file, probably because we transitioned the variable from custom properties to db only and from history to history free.(which was a mess of a choice imho.)

Not logging those actions would be a compliance problem imho.

Thank you for the info. The variable I just created and it has been version free since first created. I can see that logging when user opens/caches a file is important information for some data. In our case this is a date field that is updated when things happen in the ERP system. I've been doing testing and looking at the SQL tables, namely [dbo].[VariableValue] and [dbo].[HistoryVerFreeVarChanged], don't know where else to look. I'm pretty sure the latter is the table that populates these rows in the file history view. In theory using a SQL update statement on the VariableValue table for the specific file, variableID and revisionNo 0 would do the trick. But I'm not interested in doing edits directly to the vault's SQL tables.

Current behavior is a show stopper for our project, will need to find a new way.

[bnemec]

I'm wondering which would be worse.
Using a SQL command "DELETE FROM [Vault].[dbo].[HistoryVerFreeVarChanged] WHERE UserId = 2 and Name = LastActiveDate" after running Batch variable update.
Or using UPDATE on the VariableValues table.

[mp3-250]

I'm wondering which would be worse.
Using a SQL command "DELETE FROM [Vault].[dbo].[HistoryVerFreeVarChanged] WHERE UserId = 2 and Name = LastActiveDate" after running Batch variable update.
Or using UPDATE on the VariableValues table.

just an idea I do not know if it is silly or it does not work in your peculiar case, but there is the possibility for a field in the PDM datacard to be updated at a fixed interval from an external db with a temporary table and a sql query.

I tested only once with a drop down menu and it indeed fills the field dynamically. just to avoid messing with pdm db directly.

[jcapriotti]

Is this updated every few minutes actually changing a value or is the value the same most of the time but the program updates it anyway?

If this is true, can the import program be made smarter and only import when the value changes?

[bnemec]

Is this updated every few minutes actually changing a value or is the value the same most of the time but the program updates it anyway?

If this is true, can the import program be made smarter and only import when the value changes?

Well, the plan was to run once a day. The query to eternal DB and C# code filters the list down to only the files that should be updated. There will be many files that are updated daily, others once a year or maybe less. We could drop down to run maybe once a week, but there will still be files with 50 extra rows in the file history every year.

[bnemec]

just an idea I do not know if it is silly or it does not work in your peculiar case, but there is the possibility for a field in the PDM datacard to be updated at a fixed interval from an external db with a temporary table and a sql query.

I tested only once with a drop down menu and it indeed fills the field dynamically. just to avoid messing with pdm db directly.

I'm interested. I had not heard of this so I did some searching. I found this in PDM Help:
https://help.solidworks.com/2019/Englis ... AuMA..#Pg0

This sounds similar to what you're saying with the temporary table bit. I was getting hopeful up until the part about the updates wait in that temp table until the file(s) go through an import transition action. I'm already setting this variable to current date as an action on the common workflow transitions. The purpose of the updating through API was to get the files that may not be touched for a couple of years.

Nevertheless this is a good suggestion and something to keep in mind. Would work well for updating a non-version free variable such as "Description" from the ERP side (for cases where the ERP side is the controlling single source of truth.)

When the rule runs, the database server searches the folder for XML files containing valid values and commits them to the database in the ImportBatches table. Imported variable values are stored in the ImportDocumentVariables table. Imported values are not updated in the file stored in the vault until the file goes through an import transition action.

[AlexB]

I'm interested. I had not heard of this so I did some searching. I found this in PDM Help:
https://help.solidworks.com/2019/Englis ... AuMA..#Pg0

I've always been curious about this myself. We don't have any applications that would use this (yet) but nevertheless I feel like it's a good idea to see how to use it in my test vault.

[jcapriotti]

Well, the plan was to run once a day. The query to eternal DB and C# code filters the list down to only the files that should be updated. There will be many files that are updated daily, others once a year or maybe less. We could drop down to run maybe once a week, but there will still be files with 50 extra rows in the file history every year.

So this date for these files actually change daily?

[bnemec]

So this date for these files actually change daily?

Some do, it depends on orders. The intent is to filter out, mostly automatically, CAD files that are not in use. Explaining the why would take me a while, I'm not good at summarizing. Several of us came up with a plan to try. Step one is keep a "last active date" on each model file. The obvious and simple options such as state change are implemented but we need to keep all the files that are used in any active product but seldom touched in PDM, which is where the question in this thread comes in. The plan is to take the list of finished good part numbers shipped each day from the ERP side to get the list of CAD files that were involved in supporting those and update the "last active date". Which all seems to work as desired except for this pesky side effect of filling the file history view with irrelevant rows.

The other portion will be a scheduled task that gets all the files with last active date older than x and does some more filtering then either sends them to an obsolete type state or sends notification to selected user when human decision is needed.

[jcapriotti]

@bnemec I get it now. We have some of the same issues if I understand correctly. We "inactivate" parts and products that are no longer current, but we still need to make parts for repair and replacement. So they are "Active" in our factory. Eventually orders for these parts fall off after products in the field get replaced and upgraded. So I think there are order dates in our ERP and other systems which never really make it back to engineering so we can finally put the product to bed and archive it.

Not sure how exactly I would tackle this. Probably a custom SQL DB that receives the ERP dates. If you didn't require the data card variable be updated, I would assign a command button that opens the date report for that part. Maybe include other ERP data that is useful to engineering.....like cost.

If it must be on the data card, you could try editing the "VariableValue" table directly. I've been "told' by certain "someones" that its fairly safe (no triggers or stored procedures). Pretty sure the company that did our initial PDF import added the meta data directly to this table. I manually correct it all the time and had thought many times about automating the more common tasks. Just need the DocumentID and VariableID.

[bnemec]

In case anyone cares, what I settled on is calling this function. I use the id of logged in user (a user account only used for automation) in the sql statement. The return value is compared against the number of files expected to have been edited.

Code: Select all

    private static int ClearLastActiveDateHistory()
    {
      int rowsDeleted = 0;
      using (SqlConnection findSEfilesConn = new SqlConnection(mssqlConnString))
      {
        findSEfilesConn.Open();
        using (SqlCommand cmd = new SqlCommand("", findSEfilesConn))
        {
          IEdmUserMgr10 userMgr = vault.CreateUtility(EdmUtility.EdmUtil_UserMgr);
          cmd.CommandText = "delete FROM[Vault].[dbo].[HistoryVerFreeVarChanged] " +
            "where Name = '" + LAST_ACT_VAR_NAME + "' and  userID = " + userMgr.GetLoggedInUser().ID + "; ";
          SqlDataReader reader = cmd.ExecuteReader();
          if (reader != null)
          {
            rowsDeleted = reader.RecordsAffected;
          }
        }
      }
      return rowsDeleted;
    }
  }