Append a collection on a new column based on a nested ForAll

[AnthonyRegnier]

Hello guys,

I'm sure you find the subject of this post super sexy.... And you're all going to love scratching your head around it...

Something cool I want to achieve here but struggling a bit between shaping a collection that I'm looping into through another table...

Here is the thing:

1 - A user is going to select from a date picker a start date and an end date out of which I make a collection showing every day within the range he selected (PLEASE SEE COLLECTION1 PIC).

2 - Based on the selected start date and end date, I create another collection that is returning from the SP list source, all the desks that are reserved during those days (even if they are in between, or overlapping). (PLEASE SEE COLLECTION2 PIC).

In order to achieve that I'm using this formula (not that it can be any useful for you but we never know):

ClearCollect(ColBookingsForSelectedDays, Filter('Desk Reservations', Office = SelectedLocation, Or(startTime >= 'Check In' && startTime <= 'Check Out', endTime >= 'Check In' && endTime <= 'Check Out')))

Here is what I try to achieve and I'll use a simpler syntax like PowerShell for better understanding:

ForEach (day in collection1.Date) {
      ForEach (booking in collection2) {
            If (date in booking) {
                   If not exist (AddColumn in Collection1 "numberOfBooking"); 
                   append value "+1" in row where column "Date" = day
             }
      }
}

As you probably understand, the goal is to calculate the number of reservations for each day in the date range that the user selected in the date picker, by querying the source (SP list) for each of these days.

If the office occupancy is reached, then the app should return a message saying:
"The office is full at the date $Date, please choose a different date".

I'm not sure if I can "dynamically" create a new column and append a value to eat while looping into it in the same time in a different column if it...

Thank you very much in advance!

[FilipeRelvas]

Hi @AnthonyRegnier ,

I would create the column in advance since you will need to have it in the table either way.

After that you can try to nest two ForAll() functions and Set a Variable to hold the value for the number of reservations.

You keep increasing that variable each time you meet your conditions and after you finish your inner ForAll() you can use Patch() to add that value into the Table Row in question.

It would be something like this:

AddColumns(Collection1, "numberOfReservations", <Desired Formula>);
Set(NumberOfReservations, 0);
ForAll (Collection1 as Record1, 
           ForAll (Collection2 as Record2, 
                      If(Record1.date in Collection2, Set(NumberOfReservations, NumberOfReservations + 1)))
                          Patch(Collection2, Record2, { numberOfReservations: NumberOfReservations, ... }))

Documentation:

ForAll - https://docs.microsoft.com/en-us/powerapps/maker/canvas-apps/functions/function-forall

As Operator - https://docs.microsoft.com/en-us/powerapps/maker/canvas-apps/functions/operators#as-operator

Add Columns - https://docs.microsoft.com/en-us/powerapps/maker/canvas-apps/functions/function-table-shaping

Patch - https://docs.microsoft.com/en-us/powerapps/maker/canvas-apps/functions/function-patch

As for the rest I might have missed some stuff so please go through the documentation before implementing it.

Hope this helps! 🙂

Best regards,

Filipe Relvas

[AnthonyRegnier]

Hey @FilipeRelvas ! Thanks so much that definetly helps.

The problems with this solution are that I cannot invoke "SET" and I cannot patch the collection that is already being used in the ForAll...

I could work around the SET by just using the current value and patch +1 every time I get a true, but since it's the same collection...

Do I then need to create 3rd collection??

Thanks

Anthony

[AnthonyRegnier · Accepted solution]

Found how to do it!!

Wasnt easy

ClearCollect(
    ColDateRange,
    AddColumns(
        FirstN(
            [
                0,
                1,
                2,
                3,
                4,
                5,
                6,
                7,
                8,
                9,
                10,
                11,
                12,
                13,
                14,
                15,
                16,
                17,
                18,
                19,
                20,
                21,
                22,
                23,
                24,
                25,
                26,
                27,
                28,
                29,
                30,
                31
            ],
            DateDiff(
                DateValue(selectedStartDate),
                DateValue(selectedEndDate)
            ) + 1
        ),
        "Day",
        "Day " & (Value + 1),
        "Date",
        DateAdd(
            DateValue(selectedStartDate),
            Value,
            Days
        )
    )
);
Clear(ColCount);
ForAll(
    ColDateRange,
    Collect(
        ColCount,
        {
            Date: ThisRecord.Date,
            Count: 0
        }
    )
);
ForAll(
    ColDateRange As Record1,
    ForAll(
        ColBookingsForSelectedDays As Record2,
        If(
            Or(
                Record1.Date >= Record2.'Check In' && Record1.Date <= Record2.'Check Out',
                Text(Record1.Date) in Record2.CheckInTxt,
                Text(Record1.Date) in Record2.CheckOutTxt
            ),
            UpdateIf(
                ColCount,
                Date = Record1.Date,
                {Count: Value(Count + 1)}
            )
        )
    )
)

[FilipeRelvas]

Hi @AnthonyRegnier ,

I agree with you that doesn't look as straight forward as we initially thought it would be! 😄

A 3rd collection to hold the Count for each Date was in order indeed.

Thanks for sharing with the community.

Best regards,

Filipe Relvas