Using a calc field get the most recent date and the oldest date of the records of a repeated group.

[Tirso]

I have a main form called projects, and in this form there are 3 groups.

The first group is called Project and contains the fields that identify the project as project name, description, place, etc.

The second group is a repeated group called Tasks that contains the fields: Activities, Dates and Responsible.

The third group must contain two calc fields, one called Start, which must obtain the oldest date of all the Dates of the activities recorded in the repeated group and another Calc field called End that gives me the most recent date of all the dates of the activities recorded in the repeated group.

Someone knows what code I should use in the calculated fields to extract the oldest date and the most recent date from all the records entered in the repeated group called Tasks.

 

[Bauer]

I would think that the fastest way would be to just do 2 direct queries on the tables.
E.g.
SELECT start FROM tasks ORDER BY start DESC
SELECT end FROM tasks ORDER BY end
in either case $db->loadResult() will give you the calc value you want - though you should use the date function to save/show it as a string.

 

[Tirso]

The idea is to work with the "date field" of the repeated group, something like what is shown in the image

 

[Tirso]

I have worked with calc fields adding the values of a field belonging to a repeated group, but I have no idea how to extract the most recent or oldest date from the values of a date field from a set of records of a repeated group.

 

[Sophist]

Here's how two approach this:

1. Find out what the value of the repeat group placeholder is when evaluated outside the repeat group - hopefully it will be a string representation of an array:

return '{date_field_placeholder}';

2. Manipulate the string to get it into a php array using e.g. the php explode function. So if it was '20180101,20180202' you would use:

$dates = explode(',', '{date_field_placeholder}');

If the dates are themselves in quotes e.g. '"20180101","20180202"' then its a tiny bit more complicated:

$dates = explode('","', trim('{date_field_placeholder}', '"'));

3. Then you use php min or max functions to get the lowest and greatest dates.

So assuming that the placeholder holds all the values your code might in the end look something like:

$dates = explode(',', '{date_field_placeholder}');
return min($dates);

Note 1: This is off the top of my head, so I have no idea whether it is correct or not.

Note 2: The approach suggested by bauer to query the database is an alternative one - but it only works on load / save and cannot be ajaxed to update the values dynamically as they are changed in the repeat group because they have not yet been saved to the database.

Note 3: If you do want the fields updated dynamically, then an alternative to ajax (which runs on the server) )is to use a read-only field element and use javascript to calculate the values in the browser.

 

[Bauer]

Yet another option would be to forget about using calc fields and just do it all with javascript in the form_x.js file.
E.g. This script assumes...

• the parent list is 'test_list', the start date is 'test_list___start_date', and the end date is 'test_list___end_date' - both are input (field) elements set as readonly.
• the joined repeat table is named 'test_list_joined' and the date element is 'test_list_joined___date_time'.

Call this function in the onchange event of the repeat's 'date_time' elements and when the form is saved, the Start and End dates will be saved with the latest edits.

function setDates() {
    var thisDate, startDate, endDate;
    thisDate = startDate = endDate = "";
    jQuery("input[id^='test_list_joined___date_time_']").each( function() {
        thisDate = jQuery(this).val();
        if (startDate == "" || thisDate < startDate) {
            startDate = thisDate;
        }
        if (endDate == "" || thisDate > endDate) {
            endDate = thisDate;
        }
    });
    jQuery("input#test_list___start_date").val(startDate);
    jQuery("input#test_list___end_date").val(endDate);
}

 

[Tirso]

Sophist, thank you for your input, but I still do not find it that way.

Bauer, I'll try the way you propose. Thanks for your time and contribution.

 

[Tirso]

Yet another option would be to forget about using calc fields and just do it all with javascript in the form_x.js file.
E.g. This script assumes...

• the parent list is 'test_list', the start date is 'test_list___start_date', and the end date is 'test_list___end_date' - both are input (field) elements set as readonly.
• the joined repeat table is named 'test_list_joined' and the date element is 'test_list_joined___date_time'.

Call this function in the onchange event of the repeat's 'date_time' elements and when the form is saved, the Start and End dates will be saved with the latest edits.

function setDates() {
    var thisDate, startDate, endDate;
    thisDate = startDate = endDate = "";
    jQuery("input[id^='test_list_joined___date_time_']").each( function() {
        thisDate = jQuery(this).val();
        if (startDate == "" || thisDate < startDate) {
            startDate = thisDate;
        }
        if (endDate == "" || thisDate > endDate) {
            endDate = thisDate;
        }
    });
    jQuery("input#test_list___start_date").val(startDate);
    jQuery("input#test_list___end_date").val(endDate);
}

Perform the substitution of the values in the code that you added to me and I also changed the calculated fields for read-only text fields.

function setDates() {
    var thisDate, startDate, endDate;
    thisDate = startDate = endDate = "";
    jQuery("input[id^='sap_proyectos_28_repeat___fecha_actividad']").each( function() {
        thisDate = jQuery(this).val();
        if (startDate === "" || thisDate < startDate) {
            startDate = thisDate;
        }
        if (endDate === "" || thisDate > endDate) {
            endDate = thisDate;
        }
    });
    jQuery("input#sap_proyectos___inicio").val(startDate);
    jQuery("input#sap_proyectos___final").val(endDate);
}

The call of the function is made from the date field in the repeated group, however it did not work, neither when changing nor saving.

 

[Tirso]

After trying several options, to finish using the calculated fields, with the introduction that Sophis gave me I solved the problem. These are the codes of the calculated fields:

Start calc field

$dates = explode(',', '{sap_proyectos_28_repeat___fecha_actividad_raw}');
$Inicial = min($dates);
return date('d-m-Y', strtotime($Inicial));

End calc field

$dates = explode(',', '{sap_proyectos_28_repeat___fecha_actividad_raw}');
$Final = max($dates);
return date('d-m-Y', strtotime($Final));

The date format of the date function that I chose is d-m-Y, but you can change it.

In both fields, the calculation is only done when saving.

To facilitate the execution of calculations, I enabled in the form the Apply button with the name Update and the button Submit the name like Save & Exit, also activating in that form the redirect plugin so that when saving redirect to the table where all are listed the projects.