Refer to the next Item In a collection

[Anonymous]

Hello Everyone,

So I want to refer to the next Item in my collection. For example I have my collection Calendar Transactions Which contains all my daily Appointments from my Transcactions table in cds.

If there is free time between the appointments I want to create a "blank appointment between them so I can see my free time"

ex 1st appointment from 9:00 Am -> 10 Am

    (Create a blank appointment with start time 10 Am and end time 11Am)

     2nd appointment from 11:00 Am -> 12:00  Pm

   (Create a blank appointment with start time 12:00  Pm and end time 12:30 Pm)

     3nd appointment from 12:30 Pm -> 13:00 Pm

etc etc.

What I've done first is to store the daily appointments in a collection:

ClearCollect('Calendar Transactions',Filter('Patient Transactions','Start Time' >= (DateAdd('Calendar Date Picker TL'.SelectedDate,3,Hours)),'Start Time' <= DateAdd(DateAdd('Calendar Date Picker TL'.SelectedDate,1),3,Hours)));

2) I sorted my collection because I need my items sorted:

ClearCollect('Calendar Transactions',Sort('Calendar Transactions','Start Time',Ascending));
(I did not do this at step 1 because for some reason when i put the sort function when I created the collection, It would not show  some fields)

3) Created a second collection that includes all the items from the first collection plus an index column so I can refer to thje next next item (which is what I want):

ForAll('Calendar Transactions',
Collect('Calendar Transactions Temp',
Patch(Last(FirstN(Sort('Calendar Transactions','Start Time',Ascending), CountRows('Calendar Transactions Temp')+1)),
{Index: CountRows('Calendar Transactions Temp') + 1})
)
);

And now here comes the part where I need to use the forAll function to create all the blank appointments that i need between the existing appointments.

ForAll('Calendar Transactions Temp',

                                                                    // This is my approach to point to the next appointment(I know its not correct)If(ThisRecord.'End Time' > Last(FirstN('Calendar Transactions Temp',ThisRecord.Index + 1).'Start Time',
Patch('Calendar Transactions',Defaults('Calendar Transactions '),{'Start Time':ThisRecord.'End Time', End Time: -> Here I want to store the next appointment's start time} <-

);

Sorry if my way seems a bit stupid but I have not figured this out in my head yet.. Any help would me much appreciated..

[poweractivate]

@Anonymous 

You could try like this:

Last(FirstN(CollectionWithIndices, someIndexVariable))

CollectionWithIndices should simply have an additional column serving as an index column where the first item is 1 and each subsequent item is incremented by 1 etc.  Then someIndexVariable can be decremented or incremented to go to next, previous value, etc.

One approach is that If items are being incrementally built with the Collect Function, what you can do is if there is no item, initialize it with 1. But if there already is an item, you would get the very last item of collection with the Last function, get that index column value, and for the new record, use that index plus one.

There are other possible approaches, such as potentially just already having the index columns there initially beforehand for example.

See if something like above helps @Anonymous 

[RandyHayes · Accepted solution]

@Anonymous 

You need to take a slightly different approach to this and utilize the power of the ForAll function.

I pulled this bit of formula out of one of our apps where we do this and modified for your example.

It is easier to explain by just letting you look over the formulas.

I've attached a sample app (download and save.  Then in PowerApp, open an existing app or create new, then click on File->Open->Browse.  Select the downloaded file to view in editor)

The app shows two different ways to view this type of data - as a list and as a schedule.

Here is your journey through the app:

1) Look at the Canvas1.dcdData datacard and the Update property.  This is where all of the data is coming from.  It is just a sample set of data for the app.  I made it a dynamic variable (the update property of the datacard) to simulate a datasource.

2) Take a look at the List view - galList in the Canvas1.dcdList datacard.  Look at the Items property.  There you will find the complete formula for determining the "free" slots of the day and filling in their start and end times.  I've pasted a copy of that formula below, just as reference.

With({_startOfDayTime: ddStartofDay.Selected.startOfDay, _hourDivisionMinutes: ddHourDivision.Selected.Value, _hoursInDay:ddHoursInDay.Selected.Value , _day:dpDate.SelectedDate},
    With({
      timeData:
          Filter(
          dcdData.Update.appointments,
          StartTime>=_day && EndTime<=DateAdd(_day, (24*60)-1, Minutes)
          )
     },

    GroupBy(
        AddColumns(
            ForAll(Sequence(_hoursInDay * (60/_hourDivisionMinutes), 0), 
                With({divTime: DateAdd(_day + Time(_startOfDayTime, 0, 0), Value*_hourDivisionMinutes, Minutes)},
                    With({_appt: Coalesce(LookUp(timeData, divTime >= StartTime && divTime<=EndTime, Title), "FREE"),
                        _lastA:LookUp(SortByColumns(timeData, "EndTime", Descending), EndTime<divTime),
                        _nextA: LookUp(SortByColumns(timeData, "StartTime", Ascending), StartTime>=divTime)},
                       {division: divTime,
                        appt: _appt,
                        lastApptEnd: _lastA.EndTime, 
                        nextApptStart: _nextA.StartTime
                       }
                    )
                )
            ),
           "startTime", If(appt="FREE", Coalesce(lastApptEnd, _day + Time(_startOfDayTime,0,0)), LookUp(timeData, Title=appt, StartTime)),
           "endTime", If(appt="FREE", Coalesce(nextApptStart, DateAdd(_day + Time(_startOfDayTime,0,0), _hoursInDay * 60, Minutes)), LookUp(timeData, Title=appt, EndTime))
        ),
        "appt", "startTime", "endTime", "recs"
    )
)
)

3) Take a look at the Schedule view - galSchedule in the Canvas1.dcdSchedule datacard.  Look at the Items property.  This formula is pretty much identical to the previous, except that it is not grouped, it is done by "slots of the hour" for a schedule.

That's pretty much it.  No collections or variables needed.  The Items property of the Galleries are the only formulas of any significance in the app.

I know your plan is to patch a datasource with those free times, so you can certainly adapt this to your needs by filtering the entire formula for the List for slots called "FREE" (as in this case) and then perform a patch for each with the associated start and end times.

I hope this is helpful for you.  Feel free to ask questions as needed.

[Anonymous]

@RandyHayes  I think I finally figured it out with my way but just for getting into all this trouble you are the best. Thank you very much, I really appreciate it. Merry Christmas.

[RandyHayes]

@Anonymous 

Sorry for the late reply - glad it is all working now!  

I hope you had great holidays and have a happy new year!