Write username and date to IJC Field

User 0f1e393145

19-02-2013 15:40:00

Hey All,


I was wondering if anyone knew a way, or if it is possible, to write the IJC/MySQL username and to a field in the grid view. For example, I have a username and date field and upon registration I would like to write who is registering this compound and when. I could use DB triggers, but would prefer to use IJC. Any ideas?


Thanks in advance,


Adam

ChemAxon 2bdd02d1e5

19-02-2013 15:56:34

Hi Adam,


If I understand, you'd like to have some predefined values when adding a new row? Should this values be editable?


I think there is no easy way in IJC. You could probably use some button script in a form, but the solution would be quite comprehensive. And it would not affect gridview... Add new row dialog is currently not customizable, I guess that DB triggers are the easier solution.


Best regards,


Filip 

User 0f1e393145

19-02-2013 15:58:44

Thanks so much for the reply Flip. I will go with the DB triggers then. Appreciate the fast reply!


Best,


Adam

ChemAxon 2bdd02d1e5

19-02-2013 17:59:44

Adam, I've just got a hint from Petr and he came up with possible script solution in IJC.


It should be possible to create a schema connect script which attach the listener to EDP (DFEntityDataProvider) and if there is an insert action registered, trigger an update on that newly added row which would change Date and User fields automatically to requiered values.


I'm sorry for not giving more technical details, just letting you know that there is a way in IJC how to do it for both form and grid view in one script.


You are very welcome :)


Filip

User 0f1e393145

20-02-2013 16:31:20

No worries, thanks again for the update from Petr!

User 81fa445828

25-02-2016 00:23:27










fzimandl wrote:

Adam, I've just got a hint from Petr and he came up with possible script solution in IJC.


It should be possible to create a schema connect script which attach the listener to EDP (DFEntityDataProvider) and if there is an insert action registered, trigger an update on that newly added row which would change Date and User fields automatically to requiered values.


I'm sorry for not giving more technical details, just letting you know that there is a way in IJC how to do it for both form and grid view in one script.


You are very welcome :)


Filip



Hi, may I have this script as I need to add username of updating/insert user  in database.

ChemAxon 91ac526de6

26-02-2016 13:47:01

Hi,


I wrote a Groovy script which should deal with these things. It is a hook scriplet triggered using afterEdit action. Simply open your grid view, go to scripts, copy there my script and you are ready to go.


 


/*
The following script controls who and when adds rows to the IJC table.
Whenever a new row is added, username and time are stored in IJC fields.
Attention: fields for username and date must be defined in the IJC table
before running this script.
*/

afterEdit= {event ->
    //Names of fields where username and date is stored
    def userFieldName="username"
    def dateFieldName="textDate"
    println "after edit operation"
 
     def widget=event.widget

    // at first, fields username and date must be manually defined in the database    
    // find the field "username":
     
   // user and date fields found!
   def userField=widget.boundFields.find{it.name==userFieldName}
   def dateField=widget.boundFields.find{it.name==dateFieldName}
      
   // get the username and date
   def vs=widget.vertexState
   // get entity from the widget vertex state
   def ety=vs.getVertex().getEntity()
   // get id of the row you are adding
   selectedRowId=vs.getSelectedRowsIds()
   
   // go to the schema
   def rs=vs.resultSet
   def dataTree=rs.dataTree
   def schema=dataTree.schema
    
    // get user role
    def user=DIFUtilities.findCapability(schema,IJCUserLoginService.class).getMe()
    // get date
    def today = new Date()
   
    // get entity data provider  
    def edp = ety.schema.dataProvider.getEntityDataProvider(ety)
    
    // get access to the environment through lock
        def lock = edp.lockable.withLock('Updating'){ envRW ->
        
       // control whether date and user fields are filled. If so, do not update them
       def data=vs.getData(selectedRowId,DFEnvironmentRO.DEV_NULL)
       println data[selectedRowId[0]][userField.id]
       if((data[selectedRowId[0]][userField.id]==null) && (data[selectedRowId[0]][dateField.id]==null)) {
        // Defines empty map
                def vals = [:]
                      
                vals[userField.id] = user.getUsername()
                // date field is defined as a string, the format can be arbitrary
                vals[dateField.id] = today.format("yyyy-MM-dd \'at\' HH:mm:ss")
                // Create the DFUpdateDescription and update the DFEntityDataProvider
                 def ud = DFUpdateDescription.create(ety, selectedRowId, vals)
                 def submitList = Collections.singletonList(ud)
            
                 edp.update(submitList, DFUndoConfig.OFF, envRW)
                println "Updating field $userFieldName to value  ${user.getUsername()} and field $dateFieldName to value $today"
                }
      }  
// by returning false, the action proceeds as expected    
return false
}

User 8a7878ec6d

02-03-2016 18:59:30

If you use mySQL as database system you can easily add the date with a trigger:


CREATE TRIGGER `YOUR_DATABASE`.`ADD_DATE` BEFORE INSERT ON `YOUR_TABLE` FOR EACH ROW
BEGIN
SET NEW.YOUR_DATE_FIELD = NOW();
END


Adding the current user is more complicated.


Best/Evert

User 81fa445828

10-03-2016 23:17:05










osvoboda wrote:

Hi,


I wrote a Groovy script which should deal with these things. It is a hook scriplet triggered using afterEdit action. Simply open your grid view, go to scripts, copy there my script and you are ready to go.


 


/*
The following script controls who and when adds rows to the IJC table.
Whenever a new row is added, username and time are stored in IJC fields.
Attention: fields for username and date must be defined in the IJC table
before running this script.
*/

afterEdit= {event ->
    //Names of fields where username and date is stored
    def userFieldName="username"
    def dateFieldName="textDate"
    println "after edit operation"
 
     def widget=event.widget

    // at first, fields username and date must be manually defined in the database    
    // find the field "username":
     
   // user and date fields found!
   def userField=widget.boundFields.find{it.name==userFieldName}
   def dateField=widget.boundFields.find{it.name==dateFieldName}
      
   // get the username and date
   def vs=widget.vertexState
   // get entity from the widget vertex state
   def ety=vs.getVertex().getEntity()
   // get id of the row you are adding
   selectedRowId=vs.getSelectedRowsIds()
   
   // go to the schema
   def rs=vs.resultSet
   def dataTree=rs.dataTree
   def schema=dataTree.schema
    
    // get user role
    def user=DIFUtilities.findCapability(schema,IJCUserLoginService.class).getMe()
    // get date
    def today = new Date()
   
    // get entity data provider  
    def edp = ety.schema.dataProvider.getEntityDataProvider(ety)
    
    // get access to the environment through lock
        def lock = edp.lockable.withLock('Updating'){ envRW ->
        
       // control whether date and user fields are filled. If so, do not update them
       def data=vs.getData(selectedRowId,DFEnvironmentRO.DEV_NULL)
       println data[selectedRowId[0]][userField.id]
       if((data[selectedRowId[0]][userField.id]==null) && (data[selectedRowId[0]][dateField.id]==null)) {
        // Defines empty map
                def vals = [:]
                      
                vals[userField.id] = user.getUsername()
                // date field is defined as a string, the format can be arbitrary
                vals[dateField.id] = today.format("yyyy-MM-dd \'at\' HH:mm:ss")
                // Create the DFUpdateDescription and update the DFEntityDataProvider
                 def ud = DFUpdateDescription.create(ety, selectedRowId, vals)
                 def submitList = Collections.singletonList(ud)
            
                 edp.update(submitList, DFUndoConfig.OFF, envRW)
                println "Updating field $userFieldName to value  ${user.getUsername()} and field $dateFieldName to value $today"
                }
      }  
// by returning false, the action proceeds as expected    
return false
}



Thanks for reply, I modified your input and used a mixture of MySQL triggers to record any update/insert, but groovy to log user name who did that.


Thanks

User 81fa445828

10-03-2016 23:19:34










evehom wrote:

If you use mySQL as database system you can easily add the date with a trigger:


CREATE TRIGGER `YOUR_DATABASE`.`ADD_DATE` BEFORE INSERT ON `YOUR_TABLE` FOR EACH ROW
BEGIN
SET NEW.YOUR_DATE_FIELD = NOW();
END


Adding the current user is more complicated.


Best/Evert



Thanks Evert,


 


I used your idea of MySQL trigger to log any update/insert in a separate table (kind of archive table) and used groovy to record the name of user who do it. This is not a perfect solution but enough good to keep log of user activities.


 


Thanks

User a0cdded560

12-12-2016 02:10:26










osvoboda wrote:

Hi,


I wrote a Groovy script which should deal with these things. It is a hook scriplet triggered using afterEdit action. Simply open your grid view, go to scripts, copy there my script and you are ready to go.


 


/*
The following script controls who and when adds rows to the IJC table.
Whenever a new row is added, username and time are stored in IJC fields.
Attention: fields for username and date must be defined in the IJC table
before running this script.
*/

afterEdit= {event ->
    //Names of fields where username and date is stored
    def userFieldName="username"
    def dateFieldName="textDate"
    println "after edit operation"
 
     def widget=event.widget

    // at first, fields username and date must be manually defined in the database    
    // find the field "username":
     
   // user and date fields found!
   def userField=widget.boundFields.find{it.name==userFieldName}
   def dateField=widget.boundFields.find{it.name==dateFieldName}
      
   // get the username and date
   def vs=widget.vertexState
   // get entity from the widget vertex state
   def ety=vs.getVertex().getEntity()
   // get id of the row you are adding
   selectedRowId=vs.getSelectedRowsIds()
   
   // go to the schema
   def rs=vs.resultSet
   def dataTree=rs.dataTree
   def schema=dataTree.schema
    
    // get user role
    def user=DIFUtilities.findCapability(schema,IJCUserLoginService.class).getMe()
    // get date
    def today = new Date()
   
    // get entity data provider  
    def edp = ety.schema.dataProvider.getEntityDataProvider(ety)
    
    // get access to the environment through lock
        def lock = edp.lockable.withLock('Updating'){ envRW ->
        
       // control whether date and user fields are filled. If so, do not update them
       def data=vs.getData(selectedRowId,DFEnvironmentRO.DEV_NULL)
       println data[selectedRowId[0]][userField.id]
       if((data[selectedRowId[0]][userField.id]==null) && (data[selectedRowId[0]][dateField.id]==null)) {
        // Defines empty map
                def vals = [:]
                      
                vals[userField.id] = user.getUsername()
                // date field is defined as a string, the format can be arbitrary
                vals[dateField.id] = today.format("yyyy-MM-dd \'at\' HH:mm:ss")
                // Create the DFUpdateDescription and update the DFEntityDataProvider
                 def ud = DFUpdateDescription.create(ety, selectedRowId, vals)
                 def submitList = Collections.singletonList(ud)
            
                 edp.update(submitList, DFUndoConfig.OFF, envRW)
                println "Updating field $userFieldName to value  ${user.getUsername()} and field $dateFieldName to value $today"
                }
      }  
// by returning false, the action proceeds as expected    
return false
}



Hi Ondrej, this script is very important to me. But i met with an issue in triggering the afterEdit in IJC. I put your scripts into the Code window of my Grid view, the scripts cannot be triggered after I add a new row into the data table. Can you advice what might have caused this issue?


 


Xiangping

ChemAxon 91ac526de6

12-12-2016 13:12:36

Dear Xiangping,



it is difficult to answer you without knowing the details.


First of all, I would like to ask whether you created the two textfields, username and textDate. If so, do you have some errors associated with your problem? I have retested this on my environment and it works fine...

User a0cdded560

15-12-2016 02:42:28

Hi Ondrej, 


I have created the field 'Submitted By' (varchar(100)  in the table to capture the submitter of the record, this field was set to hide in the new row dialog. And I copied your scripts into the Code mode window of this grid view, the scriptable was set to the table name/table, and I modified the field name and commented the Get Date part in your scripts. Then I tried to add a new row into this table, there wasn't any error messages and the 'Submitted By' field was left blank in the new record. 


I guess the afterEdit scripts is not triggered after the new row is added, since there is a line ' println "after edit operation"' at the beginning of the scripts which output nothing in the output window. 


I hope it is not a version issue. I am using Instant JChem 16.10.10. I wonder if there is anything else i need to do to make the afterEdit trigger work...


 


Xiangping

ChemAxon 91ac526de6

16-12-2016 16:12:37

Dear Xiangping,



thank you for reporting this. It seems this is a bug. We will fix it and tell you about working version as soon as possible.


Ondrej