More interesting support questions to help you customize your EvolutivoFW application.

What if I need to create a field dependency on a numeric displaytype 4 field which is updated only by workflow?

Depending on the value in the readonly field, we may need to remove some options from a picklist (for example).

This is a delicate request because field dependencies happen all in the browser but the displaytype 2 and 4 never even get to the browser in edit mode, so those values are not available for the field dependency to do its work.

This is probably more of a validation map than a field dependency; when the user clicks save, you launch a validation that tells them if the values are valid or not. The validation checks the displaytype 4 value, and if the user selects an option from the picklist we don't want to be available, we don't let him save. But that said, it is still a valid request, so we can do some extra work to get the field dependency working.

Create a business action to inject a hidden field into the browser with the value you want. Once it is in the browser the field dependency will work with it.

This business action injects a hidden field which will be filled in by the application.

<input type="hidden" id="recordnew" name="recordnew" value="{$ID}">

We can do this with displaytype 4 fields too as long as we set the HTML ID and NAME to the real name of the field. So we change the business action to:

<input type="hidden" id="redimpacto" name="redimpacto" value="{$FIELDS['redimpacto']}">

<input type="hidden" id="cf_1329" name="cf_1329" value="{$FIELDS['cf_1329']}">

Now the readonly field is present in the browser like all the others and you can use the normal field specification of the field dependency maps.

There are three ways to do this.

1.- Link to Document record

Add a custom relation field with the Documents module. After the workflow task that generates the document, we have in the workflow context the information we need to capture the document ID that has been created and save it in the new custom field. Let's suppose that the new field is named gendocid. We would add a new Execute Expression task with this SQL:

executeSQL('update vtiger_invoice set gendocid=? where invoiceid=?', getCRMIDFromWSID(id), getFromContext('oorecord'))

2.- Link to Shared Document

The generate document workflow task automatically generates a shared document URL and loads it into the context. The goal of that shared link is for it to be sent via email for the client to download it, but we can also use it for our users to directly download the document without having to go through the Document record. If we add a custom URL field to our module we can fill it with the shared download link with this SQL:

executeSQL('update vtiger_invoicecf set gendocurl=? where invoiceid=?', getFromContext('GenerateDocument.SharedDocumentLink.0'), getFromContext('oorecord'))

3.- Image field

This is the less reliable of the three and the more complex. The image field can hold references to any type of document, not only images, so we could add a custom image field to our module and load the reference to the generated document in it. This requires two SQL instructions and some knowledge of what is happening internally. When a file is uploaded to the application we generate two internal IDs, one for the Document record and one for the attachment. The id we referenced in option 1 is the internal ID of the Document so we have to add one to that number if we want to get the attachment the image field needs. Further, we need to add 3 because of the way the generate document workflow works and then we need to save in the image field the name of the generated file. The two SQL commands look like this:

executeSQL('update vtiger_invoicecf set gendoc=? where invoiceid=?', getFromContext('GenerateDocument.GeneratedDocumentName.0'), getFromContext('oorecord')) executeSQL('insert into vtiger_seattachmentsrel values (?,?)', getFromContext('oorecord'), getCRMIDFromWSID(id)+3)

Similar to the Expire Password After Days, I would like to block access for a user who has not logged in for 2 months. It would be nice to send them an email sometime before.

This is ..., you guessed it: a workflow!. A complex one that requires knowledge of how the system works. It may be easier to create a new scheduled task but the workflow will make it a lot easier for each administrator to establish the conditions he needs and serve as an interesting blog post.

First, we need to get the list of users who have not accessed the system in some determined amount of time. Normally, for this type of request, we would create a scheduled workflow starting from the module we want the records for. But, in this case, we cannot create workflows on the Users module, so we need to force the query through any module using a Business Question. I picked Assets, but any would do. This is the business question:

This is the query:

SELECT id
FROM vtiger_users
inner join cb_settings on setting_key = concat('cbodLastLoginTime',id)
    and setting_value < (UNIX_TIMESTAMP(DATE_SUB(NOW(), INTERVAL 2 MONTH)));

where you can adapt the interval to whatever you need.

As I said above, that query requires a lot of knowledge of how the application works, trust me, it will get you the users you are looking for.

Now that we have the records we can create the scheduled workflow to run once a day and do whatever we need.

As you can see in the previous image I have already configured a task to execute an expression. If you read the previous question you can already imagine that I am going to do another direct SQL update to change the users who have fulfilled the condition but you can add any other tasks that you want. For example, if you want to send an email a few weeks before blocking them, then change the condition (creating another business question and workflow if required) and define a send email task.

The execute expression task looks like this:

executeSQL('update vtiger_users set change_password=1 where id=?', getCRMIDFromWSID(id))

Note. As I was testing this response I noticed that I missed a condition in the business question query. As it is I am only checking on the last login time, so if we have already blocked the user, the query does not consider that and will update them again every day, doing unnecessary work. You can add the change_password=0 to avoid that extra work.

SELECT id
FROM vtiger_users
inner join cb_settings on setting_key = concat('cbodLastLoginTime',id)
    and setting_value < (UNIX_TIMESTAMP(DATE_SUB(NOW(), INTERVAL 2 MONTH)))
WHERE change_password=0;

Happy New Year. I wish you the best in the year ahead!

Photo by Neil Thomas on Unsplash