Trying to build a collection from a sequence and including column values from another collection

[zuurg]

I'm trying to build a collection of available appointment dates and times that are based on 12 different time slots over a 5 day period with variable start dates, so essentially, I need a collection with 60 rows (5 x 12)

The appointment times for the specific session are stored in a settings file as a comma delimited single line of text for ease of adjustment by the application users. I've added the RowNumber to be able to figure out what the Appointment Time will be when building the second collection.  I've built the first collection of available time slots like this:

ClearCollect(
    colTimeSlots,
    AddColumns(
        Split(
            varSettings.'Interview Slots',
            ","
        ),
        "RowNumber",
        0
    )
);
With(
    {wRecords: colTimeSlots},
    ForAll(
        wRecords,
        UpdateIf(
            colTimeSlots,
            RowNumber = 0,
            {
                RowNumber: Max(
                    colTimeSlots,
                    RowNumber
                ) + 1
            }
        )
    )
);

The result is correct:

Where I'm having problems is figuring out how to build the 2nd collection which needs to have the 60 rows (I'm using this as the Items in my Vertical Gallery with a Wrap Count of 5 (to represent Monday - Friday)).

The Collection needs to look like:

Where value is the sequence number, and Appointment Date and Appointment Time are the correct values for that slot.

The Appointment Time needs to be based on a LookUp of the colTimeSlots collection where the RowNumber=RoundUp(Value/5,0) and returns the colTimeSlots Value.

The Appointment Date is going to be (this is what I was using as a label within the gallery to test):

DateAdd(varSettings.'Week Start Date',If(Mod(ThisItem.Value,5)=0,4,Mod(ThisItem.Value,5)-1))

So here's what I've tried to do to build the final collection (using just the appointment times to test it out first):

ClearCollect(colAppointmentSlots,AddColumns(Sequence(CountRows(colTimeSlots)*5),"AppointmentTime","00:00","AppointmentDate",Blank()));
With(
    {
        wRecords: colTimeSlots
    },
    ForAll(
        colAppointmentSlots,
        Patch(
            colAppointmentSlots,
            ThisRecord,
            {
                AppointmentTime: LookUp(wRecords,RowNumber = RoundUp(ThisRecord.Value/5,0),Value)
            }
        )
    )
);

The Patch line is giving me an error saying "This function cannot operate on the same data source that is used in ForAll"

I don't understand why since the first part that adds the RowNumbers to the colTimeSlots collection is operating on the same data source.

Can anyone help me fix this or if there's a more efficient way tell me how to do it?

@WarrenBelz If you're around, this seems right up your alley with the With() stuff.   🙂

[CarlosFigueira · Accepted solution]

You can create the collections in a single step, without first creating it then updating it with the values. For the first collection, this expression should work:

With(
    { slots: Split( varSettings.'Interview Slots', "," ) },
    ClearCollect(
        colTimeSlots,
        ForAll(
            Sequence( CountRows( slots ) ),
            Patch(
                { RowNumber: Value },
                Index( slots, Value )
            )
        )
    )
)

For the second collection, you can use something similar:

ClearCollect(
    colAppointmentSlots,
    ForAll(
        Sequence( 5 * CountRows( colTimeSlots ) ) As indexer,
        {
            AppointmentDate: DateAdd( varSettings.'Week Start Date', Mod( indexer.Value + 4, 5 ), TimeUnit.Days ),
            AppointmentTime: Index( colTimeSlots, RoundUp( indexer.Value / 5, 0 ) ).Value
        }
    )
)

That can give you the data which you can use in a gallery similar to the one below (in my case, varSettings.'Week Start Date' was set to 2024-04-15):

Hope this helps!

[zuurg]

Super helpful, thanks!  You even solved the issue that I was having with the 5th day ending up with a Mod of 0.

I understand what you did with the first collection, but can you explain the logic behind the AppointmentDate Date & Mod calculation?

[CarlosFigueira]

In the second ForAll call, the indexer is a table with values ranging from 1..60 (in your case, where there are 12 appointments), so those would be the values of each iteration:

/--------------------------------------------------------------------\
| Indexer | Mod( indexer.Value + 4, 5 ) | RoundUp(indexer.Value/5,0) |
|---------+-----------------------------+----------------------------|
|    1    |             0               |             1              |
|    2    |             1               |             1              |
|    3    |             2               |             1              |
|    4    |             3               |             1              |
|    5    |             4               |             1              |
|    6    |             0               |             1              |
|    7    |             1               |             1              |
|   ...   |            ...              |            ...             |
|   60    |             4               |             5              |
\--------------------------------------------------------------------/

That will create a collection similar to this one:

[
  { AppointmentDate: Date(2024,4,15), AppointmentTime: "8:30" },
  { AppointmentDate: Date(2024,4,16), AppointmentTime: "8:30" },
  { AppointmentDate: Date(2024,4,17), AppointmentTime: "8:30" },
  { AppointmentDate: Date(2024,4,18), AppointmentTime: "8:30" },
  { AppointmentDate: Date(2024,4,19), AppointmentTime: "8:30" },
  { AppointmentDate: Date(2024,4,15), AppointmentTime: "9:10" },
...
  { AppointmentDate: Date(2024,4,19), AppointmentTime: "16:40" }
]

Which can be displayed similar to the image from my original post, in a vertical gallery.

You may want a different organization of your table, though, with the records in a "chronological" order. To do that we would need to revert the indices, using the RoundUp for the date, and the Mod for the time, like in the example below:

ClearCollect(
    colAppointmentSlots,
    ForAll(
        Sequence( 5 * CountRows( colTimeSlots ) ) As indexer,
        {
            AppointmentDate: DateAdd(
                varSettings.'Week Start Date',
                RoundUp( indexer.Value / CountRows( colTimeSlots ), 0 ),
                TimeUnit.Days
            ),
            AppointmentTime: Index(
                colTimeSlots,
                Mod( indexer.Value - 1, CountRows( colTimeSlots ) ) + 1
            ).Value
        }
    )
)

That would create the "transposition" of the table that was created with the original expression to something like this:

[
  { AppointmentDate: Date(2024,4,15), AppointmentTime: "8:30" },
  { AppointmentDate: Date(2024,4,15), AppointmentTime: "9:10" },
  { AppointmentDate: Date(2024,4,15), AppointmentTime: "9:50" },
  { AppointmentDate: Date(2024,4,15), AppointmentTime: "10:30" },
...
  { AppointmentDate: Date(2024,4,15), AppointmentTime: "16:40" },
  { AppointmentDate: Date(2024,4,16), AppointmentTime: "8:30" },
  { AppointmentDate: Date(2024,4,16), AppointmentTime: "9:10" },
  { AppointmentDate: Date(2024,4,16), AppointmentTime: "9:50" },
...
  { AppointmentDate: Date(2024,4,19), AppointmentTime: "16:40" }
]

And that would make for a better organization of the data if you want to display all items in order, for example (using the new Table control):

Hope this helps!