22 Replies Latest reply on Oct 22, 2012 2:40 PM by Lawrence Kiefer

    Update blank variables to allow Mandatory Values setting

    Fred Rasmussen

      In order to validate content that users enter into data cards, I would like to define some variables as Mandatory Values. If you try to set them to mandatory and there are already blank values, you get a message like this...

       

      mandatory error.png

       

      I had our IT DBA run a SQL update to place a non-blank value in any existing variables on my list that it finds with a blank value. The query is like this...

       

      UPDATE VariableValue
      SET ValueText='not  specified', ValueCache='not specified'

      WHERE (VariableID=130 or  VariableID=132 or VariableID=143 or VariableID=146)
      AND (CAST(ValueText AS nvarchar(max)) IS NULL) OR  (CAST(ValueText AS nvarchar(max))=' ')

       

      This was effective for some variables but not for others. I am wondering if anyone has been successful setting the Mandatory constraint after blank values have been added? Can you think of what I may be missing here?

       

      I saw a post with a suggestion to set the minimum text length to 1, but that is only available for Edit Boxes, not for ComboBoxes or Dates.

       

        • Re: Update blank variables to allow Mandatory Values setting
          Kim Nguyen

          You can still set the variable to be "Mandatory" by changing the flag "FlagMandatory" in table Variable: (though in general it's not recommended to modify in the database):

           

          a.jpg

           

          Just change the "FlagMandatory" value from False to True, the variable will become Mandatory from-now-on. For existing files which already contain a blank variable value, you will be asked to provide some value when those files are checked in the next time.

          • Re: Update blank variables to allow Mandatory Values setting
            Calvin Nelson

            You're update query looks like it is trying to assign a string to all empty variables. Date variables won't accept a string, usually.

            I think you'll need a different update query for those variables that don't accept a string.

            • Re: Update blank variables to allow Mandatory Values setting
              Joy Garon

              Hi Fred,

               

              Just a friendly reminder that modification of the database outside of the application is not supported.

               

              Regards,

              Joy

                • Re: Update blank variables to allow Mandatory Values setting
                  Fred Rasmussen
                  In other words, "You can't get there from here." Is that a fair statement?
                    • Re: Update blank variables to allow Mandatory Values setting
                      Joy Garon

                      Hi Fred -

                       

                      The best practice for mandatory value usage is to set it prior to placing files in the vault.

                      Using the method you have chosen, you need to ensure that you update values for every instance of the usage for the selected variable(s) (ie: every version of every document).

                       

                      Is your VAR assisting?

                       

                      Regards,

                      Joy

                       

                      p.s. what variables (names) and type are these?VariableID=130 or  VariableID=132 or VariableID=143 or VariableID=146

                        • Re: Update blank variables to allow Mandatory Values setting
                          Fred Rasmussen

                          UPDATE VariableValue
                          SET ValueText='not  specified', ValueCache='not specified'

                          WHERE (VariableID=130 or  VariableID=132 or VariableID=143 or VariableID=146)
                          AND (CAST(ValueText AS nvarchar(max)) IS NULL) OR  (CAST(ValueText AS nvarchar(max))=' ')

                           

                          What puzzles me is that this SQL statement should have updated all versions. The error message mentions the LATEST version. But it doesn't make any difference. There is clearly something missing in my approach.

                           

                          All of the variables being updated are TEXT type variables, except for one DATE. I could live without the date.

                           

                           

                          ECN Variable List
                          VariableID VariableName Mandatory Completed
                          169BOM Syspro ImpactYesYes
                          165Class ChangeYesNo
                          132Customer ImpactYesYes
                          166Customer TypeYesNo
                          161Description of ChangeYesNo
                          257Doc-Drw ImpactYesNo
                          130Document TypeYesNo
                          195ECN Part In SysproYesYes
                          282GA/Legal Change OnlyYesNo
                          258Inventory ImpactYesNo
                          143Production Database ImpactYesYes
                          146Purchased ImpactYesYes
                          147Reason For ChangeYesYes
                          148Required DateYesNo
                          152SPC ImpactYesYes
                          170Syspro CompanyYesNo
                          171Wheres Used AttachedYesYes

                           

                          As you can see, I was 35% successful.

                          • Re: Update blank variables to allow Mandatory Values setting

                            Joy:

                             

                            CADimensions is assisting this customer; however, we have told them we discourage inserting, deleting, and/or updating SQL records in the database for Enterprise PDM. Like SolidWorks, CADimensions does NOT want to be responsible for any damages that may occur with corporate data. Furthermore, we are trying to prevent any additional consulting charges for Ultralife Batteries if such damages occur.

                             

                            CADimensions does have the expertise to write add-ins, applications, macros, and SQL script for various SolidWorks software. Most SQL script uses "Select" statements ONLY (queries).

                             

                            CADimensions, Inc.

                            Scott Blackwell

                            Application Engineer (PDM and API specialist)

                          • Re: Update blank variables to allow Mandatory Values setting
                            Kim Nguyen
                            It did work for me though I know that it's not recommended
                        • Re: Update blank variables to allow Mandatory Values setting
                          Michael Dekoning

                          Hi Fred,

                           

                          I was in your same predicament. After doing some research, I decided not to even attempt to update the database to use mandatory variables. For one thing I found that our users weren't placing the required information in every configuration in files that I needed to import. I did put in an enhancement request to allow mandatory variables to be configuration specific. Don't know if that will ever happen.

                           

                          There is a way to make combobox enrties required. I will share the technique if you would like. It could probably be adapted for dates as well.

                           

                          I also have written an add-in that will remind a user when a file is checked in that certain entries are required. Subsequently, we do not allow a state change without a value for those variables. This covers files that are copied into the system without using the input card. Again, let me know if you are interested in this.

                           

                          Mike

                           

                          EPDM 2009 SP3

                            • Re: Update blank variables to allow Mandatory Values setting
                              Fred Rasmussen

                              >There is a way to make combobox enrties required. I will share the technique if you would like. It could probably be adapted for dates as well.

                               

                              It would be helpful to know about this. Anything to improve the limited validation that is available!

                                • Re: Update blank variables to allow Mandatory Values setting
                                  Jeff Sweeney
                                  I am with Michael on the validation issue. Forcing the mandatory values in SQL is just too restrictive. The most typical solution I see is to create a condition in your workflow transitions so the file cannot pass through until all conditions are met.
                                    • Re: Update blank variables to allow Mandatory Values setting

                                      Jeff:

                                       

                                      I agree with you. Use transition conditions to perform validation of data. We also try to use combobox droplists on the card wherever possible and if a text box control is used, then apply max/min values to ensure something gets entered but isn't a "diary/journal" for the user. :-)

                                       

                                      CADimensions, Inc.

                                      Scott Blackwell

                                      Application Engineer (PDM and API Specialist)

                                        • Re: Update blank variables to allow Mandatory Values setting
                                          Fred Rasmussen

                                          Throw out the combobox droplist, in this case Scott. There's no way to require the user to make a selection, which is what I'm trying to do here. I take exception to performing validation on transition because not only does it fail to give immediate feedback, it lacks the means of stating exactly why the "Conditions of the transition could not be met." How is it acceptable to leave the user guessing what happened?

                                           

                                          Can anyone tell me why I am prevented from changing the Mandatory flag because of blank values in the first place? If it were a situation of primary keys violating SQL server, I could accept that. For Pete sake, another user who circumvented the system via SQL update said she observed no ill effects.  Listen to me, I'm getting very impatient because my problem persists and everyone is talking about everything I cannot do and should not do. Validation on data cards is sorely lacking. I just want the ability to set a value mandatory.

                                      • Re: Update blank variables to allow Mandatory Values setting
                                        Michael Dekoning
                                        Create an Edit-box control and tie it to the same variable as the droplist. Enter an Input Formula that is also tied to the variable. Set the Validation Min property to 1. You should make the Edit-box Read-only.
                                          • Re: Update blank variables to allow Mandatory Values setting
                                            Jeff Sweeney
                                            Nice tip!! By connecting controls this way, controls can inherit new properties! I had never thought of this! Sweet and elegant!

                                            It might make sense to hide the control rather than making it readonly - to avoid confusion by the user.
                                            • Re: Update blank variables to allow Mandatory Values setting
                                              Fred Rasmussen

                                              This is a useful solution and it will satisfy my requirements.

                                               

                                              It should be noted that the cursor jumps into the read-only cell when you try saving with the data field blank. It is necessary to go and select from the droplist instead. This is slightly confusing. But it's also an opportunity and here's why...

                                               

                                              I tried leaving all of my required fields blank and hitting save each time to see how it prompted me. The fields are scattered across several tab controls and I noticed each time a value was missing, it would jump all over the place prompting me for everything that is missing (in no particular order!).

                                               

                                              It turns out that the mandatory values, whether set for the variable or by way of these text boxes, are checked from top to bottom and from left to right. It pays no attention to the tab controls. The next physical location on the card with a blank value is the next variable you are prompted to enter. This is the reason for all the jumping between tabs.

                                               

                                              The solution is to position the text boxes in the order you want them from left to right. The ones in the first tab are top-most. The ones in the next tab are slightly lower and so on. That way, any missing values will prompt you and set the focus on each tab in order from left to right until the last blank is filled.

                                        • Re: Update blank variables to allow Mandatory Values setting

                                          To All:

                                           

                                          I'm going to provide some suggestions for validating control values.

                                           

                                          1) See Michael's reply below about linking a droplist to a text box through input formula function. This is very cool because now you are expanding the properties of the droplist control to include those of a text box. In the programming world (OOP) this is known as inheritence. Now you are forcing the the user to pick an item from the droplist because a Min value of 1 forces the user to put something in the text box. In this case, the text box is filled out per the value stored in the variable. I'm simply going to add that the following message box will appear if you don't input a value: Min Message Box.PNG

                                          Once you click okay, the cursor will place focus on the control that doesn't meet the conditions outlined in the message box. Hence it forces the user to go to the control and put a value in. Unfortunately, it doesn't go to the droplist but it gives the user some direction.

                                           

                                          2) You can make sure that the "mandatory" setting for the variable is setup in the system before inserting documents that are linked to this.

                                          3) If you choose to use conditions through the transition and they don't meet the conditions, then you get a generic message box telling you that the conditions haven't been met.

                                          Transition Conditions Message Box.PNG

                                          I know, your first argument is it doesn't tell you exactly what condition hasn't been met and this is a valid statement. I suggest proper employee training about how to fill the cards out properly. If this isn't enough, then put a button on the card that is linked to a website whereby a help page is displayed. You could also do the same with Wordpad. The button has several different actions such as opening a webpage or running another executable such as Notepad.exe. Store the Word files in the vault and point the command line string for Wordpad.exe to the associated files. Hey, if you want to be more clever, you could generate HTML pages and point a favorite browser to them instead of getting a webserver involved.

                                           

                                          4) You could still use transition conditions but you have to be creative with your workflows.

                                          Sample Workflow.PNG

                                          The secret to this workflow is the automatic transitions. You'll notice there is a automatic transition for both Bad Conditions and Good Conditions. The user will select Validate Card Data when changing state. Under the hood, the file goes to Quality Control state but because of the automatic transitions, the file will either pass through to the Good State or back to the Initiated state pending on the conditions of the automatic transitions. The cool thing about the Bad Conditions automatic transition is you can either send an email to users/group telling people that someone screwed up filling out the card (probably not a good idea ) or you can execute a command. By executing a command you could perform the same action as I described in item 3 (see above). By opening a help file automatically, this would include more detailed explanation about what your are missing and how to fill out the card rather than an ordinary message box. It would be nice if one of the transition action items was available to show a message box. I suggest putting an enhancement request for this.

                                           

                                          Happy Document Management!

                                           

                                          Cordially,

                                          Scott Blackwell

                                          CADimensions, Inc.

                                          Application Engineer (PDM and API Specialist)

                                          • Re: Update blank variables to allow Mandatory Values setting
                                            Lawrence Kiefer

                                            There is a way around this even if you have items in your vault that are linked to these variables, and they are empty. Open the data card you want to edit. Then in the variable that you want to make mandatory typically if you check the mandatory box, you get the message that it could not save the variable because the vault already contains files that lack values for this variable. So, simply change the variable in that field to anything else (I recomend making a new variable that is not linked to anything) and save the data card. Once you do this, the variable that you want to make mandatory is no longer linked to those files. Now you can make that variable mandatory. (If you have this variable linked on other cards, you have to do the same to all of those cards as well before you make the field mandatory). Once you make the desired field mandatory, go back yo your data cards and change the variable back to the one you just made mandatory and you are all set. I hope I explained this so everyone can get what I did. It does work.