Power Platform Community Forum Thread Details

I have a Power App based on an Excel sheet.
The purpose of the Excel sheet is listing and tracking of different projects and the Power App should replace this.
Within Power apps there is a form where new projects can be entered.
In that form there is a field called 'Current Status' where comments can be made about the status of a project accompanied with date.
When saving the form the project gets the first status: "11 Nov 2024: New Project".
When updating a project there is an extra NewStatusInput field that adds extra comments as the projects goes on and when saving the form this new status will be added. The "save" button has the following code:

SubmitForm(Form3);
Patch(
SCM_SD,
Form3.LastSubmit,
{
'Current Status': DateLabel.Text & ": " & NewStatusInput.Text & Char(10) & Form3.LastSubmit.'Current Status'
}
);

This works fine.

However as the projects go on and more statuses are added the field 'Current Status' gets overpopulated.
So therefor I would like to add a function that moves statuses older then 60 days to a different data field called: 'Older Statuses'.

For that I have another button "Update" with the following code:

Patch(
    SCM_SD,
    Form3.LastSubmit,
    {
        'Older Statuses': Form3.LastSubmit.'Current Status' & Char(10) & Form3.LastSubmit.'Older Statuses',
        'Current Status': Form3.LastSubmit.'Current Status'
    }
);
Notify("Comments moved to Older Statuses", NotificationType.Success)

Moving data with the above code works.

However to move these comments based on date it's becoming an issue. I have the following code for this under the "save" button, but it unfortunately doesn't work:

// First, get the current date
Set(CurrentDate, Today());

// Check if there are existing statuses and split the current status into its individual entries
Set(StatusEntries, Split(Form3.LastSubmit.'Current Status', Char(10)));

// Initialize variables for newer and older statuses
Set(NewStatuses, "");
Set(OlderStatuses, "");

// Loop through existing statuses and filter based on date (older than 60 days)
ForAll(
    StatusEntries,
    If(
        !IsBlank(ThisRecord.Result),
        // Extract date from the status text (assuming the date format is 'DD MMM YYYY')
        Set(StatusDate, DateValue(Left(ThisRecord.Result, 11))),
        If(
            DateDiff(StatusDate, CurrentDate, Days) > 60,
            // If status is older than 60 days, add it to OlderStatuses
            Set(OlderStatuses, OlderStatuses & ThisRecord.Result & Char(10)),
            // Otherwise, keep it in the current status
            Set(NewStatuses, NewStatuses & ThisRecord.Result & Char(10))
        )
    )
);

// Add the new status
Set(NewStatus, DateLabel.Text & ": " & NewStatusInput.Text);

// Update the patch with the new and older statuses
Patch(
    SCM_SD,
    Form3.LastSubmit,
    {
        'Current Status': NewStatuses & NewStatus,
        'Older Statuses': OlderStatuses & Form3.LastSubmit.'Older Statuses'
    }
);

Can someone help me with this? Thanks :)

Categories:

Using Apps Using Formulas