Updating a checkbox every six months

I’m trying to create a check on company level that occurs every six months. The code I’m using is this:

{% comment %}Check when last reviewed{% endcomment %}

{% assign date_to_check_year = company.custom.check.company_info.updated_at | date:"%Y" %}
{% assign date_to_check_month = company.custom.check.company_info.updated_at | date:"%m" %}
{% comment %}
{% capture year_now %}{{ "now" | date:"%Y" }}{% endcapture %}
{% capture year_month %}{{ "now" | date:"%m" }}{% endcapture %}
{% endcomment %}

{% capture year_now %}{{ period.start_date | date:"%Y" }}{% endcapture %}
{% capture year_month %}{{ period.start_date | date:"%m" }}{% endcapture %}

{% if year_now > date_to_check_year %}
  {% assign diff_months = (12-date_to_check_month)+year_month %}
{% else %}
  {% if year_now < date_to_check_year %}
  {% assign diff_months = 0 %}
  {% else %}
  {% assign diff_months = year_month-date_to_check_month %}
  {% endif %}
{% endif %}

{% assign allowed_diff = 6 %}

{% if company.custom.check.company_info %}
{% if diff_months > allowed_diff %}
{% assign unrec_diff = 1 %}
{% assign company.custom.check.company_info = false %}
{% else %}
{% assign unrec_diff = 0 %}
{% endif %}
{% else %}
{% assign unrec_diff = 1 %}
{% endif %}


{% comment %}Show check when last reviewed{% endcomment %}
{% stripnewlines %}
|Controle uitgevoerd
|Laatste controle door
|Laatste controle op
|
{% newline %}
|-----
|-----
|-----
|-----#
{% newline %}
|{% input company.custom.check.company_info as:boolean %}
|{{ company.custom.check.company_info.updated_by.name }}
|{{ company.custom.check.company_info.updated_at | date:"%d/%m/%Y" }}
|{% unreconciled unrec_diff as:indicator unreconciled_text:"De review is niet, of langer dan 6 maand geleden uitgevoerd." %}
{% endstripnewlines %}

This is working fine, but what I would like is for the boolean field to be unchecked after the six month period, so users don’t have to uncheck and the recheck this checkbox. Does anyone know how I can do this? I am unable to assign the company_info field using:
{% assign company.custom.check.company_info = false %}

Hi Ronald,

Can you please read this guidelines w.r.t. code pasting in the community? Some parts are not readable if you just past it.

Thank you for your understanding.

Michiel

Hi Michiel,

Sorry about that. I’ve edited my post and will make sure to include backticks in future posts containing code.

Hi Ronald,

You can never assign database variables (custom.some.thing), only local variables.

What I would advise you to do is to make it a reconciliation template where you compare the date the checkbox is ticked with today.

That specific part of your code could look like this:

{% input company.custom.check.company_info as:boolean %}
{{ company.custom.check.company_info.updated_at | date:"%d/%m/%Y" }}

{% assign date_ticked = company.custom.check.company_info.updated_at | date:"%d/%m/%Y" %}
{% assign today = "now" | date:"%d/%m/%Y" %}

{% assign difference_between_dates  = today-date_ticked %}
{% if difference_between_dates >= 183 %}
  {% unreconciled 1 %}
{% endif %}

This way the template will become unreconciled once the six months period is expired

regards,

Michiel

Hi Michiel,

Thanks for you response and help. However, the reconciliation itself is not the issue. We already have this working with the above code.

What I’m after is that whenever the template becomes unreconciled the company info boolean:

{% input company.custom.check.company_info as:boolean %}

gets unchecked.

I’m assuming that manually unchecking this input field triggers some sort of code. Ideally I would like for that specific code to be triggered automatically every six months.

This is a ‘nice to have’ feature for me, so if it’s not possible I can live with that.

Hi @ronald_groot_RSF,

Resetting a database variable is not possible with the lIquid code.

However, please have a look at the below code. This might be a solution for you.

{% comment %}Check when last reviewed{% endcomment %}

{% assign date_to_check_year = company.custom.check.company_info.updated_at | date:"%Y" %}
{% assign date_to_check_month = company.custom.check.company_info.updated_at | date:"%m" %}
{% comment %}
{% capture year_now %}{{ "now" | date:"%Y" }}{% endcapture %}
{% capture year_month %}{{ "now" | date:"%m" }}{% endcapture %}
{% endcomment %}

{% capture year_now %}{{ period.start_date | date:"%Y" }}{% endcapture %}
{% capture year_month %}{{ period.start_date | date:"%m" }}{% endcapture %}
{% if year_month+0 >= 1 and year_month+0 <= 6 %}{% assign year_month = 6 %}{% endif %}
{% if year_month+0 >= 7 and year_month+0 <= 12 %}{% assign year_month = 12 %}{% endif %}
{% capture key %}{{ year_now }}_{{ year_month }}{% endcapture %}

{% comment %}Show check when last reviewed{% endcomment %}
{% stripnewlines %}
|Controle uitgevoerd
|Laatste controle door
|Laatste controle op
|
{% newline %}
|-----
|-----
|-----
|-----#
{% newline %}
|{% input company.custom.check_company_info.[key] as:boolean %}
|{{ company.custom.check.company_info.updated_by.name }}
|{{ company.custom.check.company_info.updated_at | date:"%d/%m/%Y" }}
|{% unreconciled unrec_diff as:indicator unreconciled_text:"De review is niet, of langer dan 6 maand geleden uitgevoerd." %}
{% endstripnewlines %}

With this code, the variable name for the checkbox that you want unchecked becomes variable itself (linked to the [key]). This means that once 6 months has passed, the checkbox will actually become a new database variable that hasn’t been checked before.

Kind regards,
Kimberly

Hi @Kimberly_Vlietinck,

Thank you for your suggestion! I’m still facing some issues, but this seems like the way to go. I’ll let you know if I’m able to get this going.

We have the working code! Thank you so much @Kimberly_Vlietinck for your time and help regarding this issue. I’ve had to make one final edit in the code after we set it up last. When setting the checkbox for the very first time we should not update the key. Else the check will never be resolved at the initial set up. I’ve also added comments so perhaps someone can use (and understand) the code for future use.

So my final code is:

{% comment %}Check when last reviewed
What we want is a checkbox that must be checked every six months. This code creates a new checkbox when the old checkbox was updated longer then six months ago
{% endcomment %}

{% comment %}Check current period{% endcomment %}
{% capture year_now %}{{ period.start_date | date:"%Y" }}{% endcapture %}
{% capture year_month %}{{ period.start_date | date:"%m" }}{% endcapture %}

{% comment %}
Is this the first time the checkbox will be updated, or has it been updated in the past?
Check if there is an existing key and get result, else set default key (1)
{% endcomment %}
{% assign key = INT(period.minus_1p.rs_fin_2.results.period_key) %}
{% if period.minus_1p.rs_fin_2.results.period_key == blank %}
{% assign key = 1 %}
{% endif %}

{% comment %}Check year and month of the last time the checkbox was updated{% endcomment %}
{% assign date_to_check_year = company.custom.check_company_info.[key].updated_at | date:"%Y" %}
{% assign date_to_check_month = company.custom.check_company_info.[key].updated_at | date:"%m" %}


{% comment %}Check the number of months between the updating of the checkbox and the current period{% endcomment %}
{% if year_now > date_to_check_year %}
  {% assign diff_months = (12-date_to_check_month)+year_month %}
{% else %}
  {% if year_now < date_to_check_year %}
  {% assign diff_months = 0 %}
  {% else %}
  {% assign diff_months = year_month-date_to_check_month %}
  {% endif %}
{% endif %}

{% comment %}Set the number of months alowwed between checks{% endcomment %}
{% assign allowed_diff = 6 %}

{% comment %}
Now we can start or actual check
First, check if the checkbox has been updated before{% endcomment %}
{% if company.custom.check_company_info.[key] %}
{% comment %}
Second, check if this is within out alowed range. If not, set the variable for reconsiliation to 1 and create a new checkbox (by creating a new key)
{% endcomment %}
  {% if diff_months > allowed_diff %}
    {% assign unrec_diff = 1 %}
    {% assign key = key+1 %}
  {% else %}
    {% assign unrec_diff = 0 %}
  {% endif %}
{% else %}
{% comment %}
If this is the first time the checkbox is to be updated, set the variable for reconsiliation to 1, but DO NOT create a new checkbox
{% endcomment %}
{% assign unrec_diff = 1 %}
{% endif %}

{% comment %}Set the key to INT, so we do nat create an extra layer in our variable (so 1.0 becomes 1 etc){% endcomment %}
{% assign key = INT(key) %}

{% comment %}Show check when last reviewed{% endcomment %}
{% stripnewlines %}
|Controle uitgevoerd
|Laatste controle door
|Laatste controle op
|
{% newline %}
|-----
|-----
|-----
|-----#
{% newline %}
|{% input company.custom.check_company_info.[key] as:boolean default:default_checkbox %}
|{{ company.custom.check_company_info.[key].updated_by.name }}
|{{ company.custom.check_company_info.[key].updated_at | date:"%d/%m/%Y" }}
|{% unreconciled unrec_diff as:indicator unreconciled_text:"De review is niet, of langer dan 6 maand geleden uitgevoerd." %}
{% endstripnewlines %}

{% comment %}Push the current key to a result tag, so we can fetch it next period{% endcomment %}
{% result 'period_key' key %}

Great, thanks for sharing @ronald_groot_RSF!

Unfortunately this worked only the first time. The issue I’m now facing is this constraint:

You should know that you can not use a result tag to reference to a variable that has been made within that same template; result tags are used only to reference across different reconciliation templates.

Is there any way to work around this?

Okay, so an update on the issue. The code for fetching the key from the result tag was incorrect. That is why the template didn’t generate an error. After changing the code to the correct syntax:

{% assign key = period.minus_1p.reconciliations.rs_fin_2.results.period_key %}
{% if period.minus_1p.reconciliations.rs_fin_2.results.period_key == blank %}
{% assign key = 1 %}
{% endif %}

The code generates this error:

Circular reconciliation error: rs_fin_2 → rs_fin_2
:arrow_right_hook: rs_fin_2 (around line 13): assign key = period.minus_1p.reconciliations.rs_fin_2.results.period_key

I’ve tried working around this by fetching the result tag of this template in a separate template, but Silverfin still states that I am creating a circular reference.

This error is incorrect as far as I am concerned, as I am fetching the previous period, not the current, therefore there is no circular reference.

@Kimberly_Vlietinck is there any way someone can look into this?

Hi @ronald_groot_RSF,

This might be an interesting read for you, as it shows how to handle calling on results from previous periods within the same template:

You’ll have 2 options:

  1. recalculate the value of previous period
  2. use the rollforward tag

Hope this works out for you; if not, don’t hesitate to ask

Thank you @sven that was really helpfull!

We have managed to work this out quite beautifully thanks (again) to @Kimberly_Vlietinck. The trick turned out to be the creation of a loop. Here’s the actual code:

{% comment %}Check when last reviewed
What we want is a checkbox that must be checked every six months. This code creates a new checkbox when the old checkbox was updated longer ago then the allowed number of periods.
{% endcomment %}

{% comment %}Set the number of months allowed between checks{% endcomment %}
{% assign allowed_diff = 6 %}

{% comment %}Get year and month of current period{% endcomment %}
{% capture year_now %}{{ period.start_date | date:"%Y" }}{% endcapture %}
{% capture year_month %}{{ period.start_date | date:"%m" }}{% endcapture %}

{% comment %}Create a checkbox to allow a user to show the full history of reviews{% endcomment %}
{% stripnewlines %}
|---:+
{% newline %}
|{::font size='xs'}Toon historie {% input custom.history.show as:boolean default:false %}{:/font}
{% endstripnewlines %}

{% comment %}Create the table showing the last review check{% endcomment %} 
{% stripnewlines %}
|Controle uitgevoerd
|Controle door
|Controle op
|
{% newline %}
|--30%---
|--30%---
|--30%---
|--10%---#


{% comment %}START of the actual check - we will use an iteration to recalculate the value of previous period{% endcomment %}
{% for i in (1..100) %}

  {% capture key %}{{ i }}{% endcapture %}
  {% capture check_key %}check_{{ INT(key) }}{% endcapture %}

  {% comment %}First, check if the checkbox has been updated before{% endcomment %}

  {% if company.custom.check_company_info.[check_key] %}

    {% comment %}Check year and month of the last time the checkbox was updated{% endcomment %}
    {% assign date_to_check_year = company.custom.check_company_info.[check_key].updated_at | date:"%Y" %}
    {% assign date_to_check_month = company.custom.check_company_info.[check_key].updated_at | date:"%m" %}

  
    {% comment %}Check the number of months between the updating of the checkbox and the current period{% endcomment %}
    {% if year_now > date_to_check_year %}
      {% assign diff_months = (12-date_to_check_month)+year_month %}
    {% else %}
      {% if year_now < date_to_check_year %}
      {% assign diff_months = 0 %}
      {% else %}
      {% assign diff_months = year_month-date_to_check_month %}
      {% endif %}
    {% endif %}
  
    {% comment %}Check if this is within the allowed range. If not, set the variable for reconsiliation to 1 and set the reconsilliation text{% endcomment %}
    {% if diff_months > allowed_diff %}
      {% comment %}The show last variable will be used by the table below to determine if we need to show the current check, or the last executed. To show the last executed check, we'll set a variable containing the name and date of the last update.{% endcomment %}
      {% assign show_last = 1 %}
      {% assign updated_at_value = company.custom.check_company_info.[check_key].updated_at | date:"%d/%m/%Y" %}
      {% assign updated_by_value = company.custom.check_company_info.[check_key].updated_by.name %}
      {% comment %}Here we'll set the actual reconsiliation{% endcomment %}
      {% assign unrec_diff = 1 %}
      {% capture unrec_text %}Deze review is het laatst uitgevoerd op {{ updated_at_value }}. Dat is langer dan de toegestane {{ allowed_diff }} maanden. Voer de controle uit en zet het vinkje uit en aan.{% endcapture %} 
      {% comment %}Create a line for each row when history should be shown{% endcomment %}
        {% if custom.history.show == true %}
          {% newline %}
          |{{ i }}
          |{{ updated_by_value }}
          |{{ updated_at_value }}
        {% endif %}
    {% else %}
      {% assign unrec_diff = 0 %}
      {% assign show_last = 0 %}
      {% break %}{% comment %}stops the loop - we don't need a new checkbox in this case{% endcomment %}
    {% endif %}
  
  {% else %}

    {% comment %}If this is the first time the checkbox is to be updated, set the variable for reconciliation to 1{% endcomment %}
    {% if forloop.first %}
    {% assign unrec_diff = 1 %}
    {% capture unrec_text %}Deze review is nog nooit uitgevoerd, voor de review uit.{% endcapture %}
    {% endif %}
  
    {% break %}{% comment %}stops the loop - we don't need a new checkbox in this case{% endcomment %}
  
  {% endif %}

{% endfor %}
{% comment %}END of the actual check{% endcomment %}

{% comment %}Show check when last reviewed{% endcomment %}
{% newline %}
|{% input company.custom.check_company_info.[check_key] as:boolean default:false  %}
{% comment %}When the check is unreconciled, show the last time it was updated using the created variables{% endcomment %}
{% if show_last == 1 %}
|{{ updated_by_value }}
|{{ updated_at_value }}
{% else %}
|{{ company.custom.check_company_info.[check_key].updated_by.name }}
|{{ company.custom.check_company_info.[check_key].updated_at | date:"%d/%m/%Y" }}
{% endif %}
|{% unreconciled unrec_diff as:indicator unreconciled_text:unrec_text %}
{% endstripnewlines %}

So what happens is basically:

  1. The code checks if the database contains a checkbox for the first generated iteration of the loop (i == 1).
    If there is none, the loop stops and presents a checkbox with default value == false.
    Otherwise, go to the next step:
  2. If there is a checkbox, the code checks to see if this is still valid (by calculating the number of months between the last check and the current period.
    If it is still valid, the loop stops an reconciles the template.
    Otherwise go to the next step:
  3. Generate a new key, thus creating a new checkbox with default value == false.

This solution uses data stored in the database on a company level. This is how we can do the check over and over in each period.

So now we have created a template that can be set on a different interval then the interval set in the dossier. So we can have this template pop p every six months, instead of our regular monthly interval.

Also, by using a loop we can show the history of preformed checks within the template.

1 Like

Thanks so much for sharing @ronald_groot_RSF :muscle:

1 Like