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 %}
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.
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.
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 %}
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.
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:
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.
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:
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:
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:
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.
rs_fin_2 (around line 13): assign key = period.minus_1p.reconciliations.rs_fin_2.results.period_key