Write username and date to IJC Field

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

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 

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

Best,

Adam

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

No worries, thanks again for the update from Petr!

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.

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
}

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

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

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

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

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...

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

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