How to combine data from two collection records into one

[biterbit]

I have prepared some data in a collection but, because of the way it is gathered, the data is split over two records (one has a start time and the other an end time). There is no other common data between the records but the records are numbered (1,2,3,4...) and I need to turn each pair (1+2, 3+4)  into a single record before saving it to a database.

I have been using the ForAll function to gather this data and thought I could use it here as well, but I've struggled because of the inability to use variables. Is there any way to write values from two of these records into one new collection record so the data is combined?

Thanks.

[RandyHayes]

@biterbit 

Please consider changing your Logic to the following:

ForAll(Sequence(CountRows(yourTable), 1, 2),
    Patch(Last(FirstN(yourTable, Value)), Last(FirstN(yourTable, Value+1)))
)

This will produce a table with record 1 and 2 combine, record 3 and 4 combined, and so on for the length of the table.

Remember, ForAll produces a table (it is not a For/Loop function), so the above will produce a table that can be used where you need a table - ex. on an Items property.

I hope this is helpful for you.

[biterbit]

Thanks for the response Randy. I tried using the code snippet you supplied but I don't see any change in the collection records after running it. Looking at the code I'm assuming it goes through the existing collection two records at a time and combines the data into one record. Just so the situation is clear, I have pairs of records with values like:

Sequence#: 1, SourceID: 12, DayNo: 1, StartTime: 9, EndTime: <blank>

Sequence#: 2, SourceID: 29, DayNo: 1, StartTime: <blank>, EndTime: 12

Sequence#: 3, SourceID: 48, DayNo: 3, StartTime: 14, EndTime: <blank>

Sequence#: 4, SourceID: 81, DayNo: 3, StartTime: <blank>, EndTime: 16

I want end up with records like this (the fields not mentioned are irrelevant to later processing):

DayNo: 1, StartTime: 9, EndTime: 12

DayNo: 3, StartTime: 14, EndTime: 16

I hope this is clear and that you can tell me whether the code suggested would achieve this.

Thanks.

[RandyHayes]

@biterbit 

Yes the Formula provided will achieve what you want.

For clarity as well - the formula I provide does not "change" anything.  It returns a Table with the combined items as you have in your list.  Since I would not use a collection for this, I left the option to you as to how you use it.

For example, if you set that formula to the Items property of a Gallery, you would get the records combined as you have requested.  Or if you set this to a variable, you will have a snapshot of the table as requested.

However, now that you have provided sample data, there will be one issue with it...That is you have a StartTime in the second records as well.  This will cause an issue if the column is there and is blank as it will override the first. This was not clear in the original post...so thank you for the sample data.  So, we'll need to adjust a little.

There are a couple of ways to go about it, but IF you are really trying to put those two records together and the EndTime and StartTime are what are you are really focused on, then the following formula will provide the information needed:

ForAll(Sequence(CountRows(yourTable), 1, 2),
    Patch(Last(FirstN(yourTable, Value)), {EndTime: Last(FirstN(yourTable, Value+1)).EndTime})
)

Based on the sample data you have provided, these are the results you will get in the resultant table from the  above formula.

To be specific, it will output a table as follows:

[

    {SourceID: 12, DayNo: 1, StartTime: 9, EndTime: 12},

    {SourceID: 48, DayNo: 3, StartTime: 14, EndTime: 16}

]

[biterbit]

Randy,

Thanks for your considered response. The solution you provided gives me results that are close to what I need - it does leave a set of extra 'partial' records as well as the 'complete' ones such as:

{SourceID: 12, DayNo: 1, StartTime: 9, EndTime: 12}

{SourceID: 48, DayNo: 3, StartTime: 14, EndTime: 16}

{SourceID: 53, DayNo: 3, StartTime: 14, EndTime: <blank>}

{SourceID: 53, DayNo: 3, StartTime: 14, EndTime: <blank>}

The reason I was using a collection for this is that the data is captured from a set of controls in a gallery (arranged in a grid showing days and hours), detecting whether the user has selected the control (showing which hours they are available). A button OnSelect function then goes through the controls and builds this collection, but because it scans through one control at a time it can only record a start time and then, later, an end time (at least I could not find a way to capture both in one pass). Hence the need to consolidate the data afterwards. However, I'm now wondering whether I'm going about this in the wrong way as building this consolidated data and then getting it into a database record seems to be getting very complex. I'm not even sure I can turn your record set into a set of database records even if I could take out the 'partial' records.

The current data model shows a person's availability using a set of records with a day, start hour and end hour (eg, I am available Monday 9-12, Tuesday 8-10, Tuesday 14-16, etc). This works well for comparing the common availability of two people (the ultimate goal here) but it seems to be hard to present in a simple to understand way, like a grid, for users to update. Perhaps I would be better to change the data model and have one record per user and columns for every possible hour of availability and show this as a grid of controls, one mapped to each column. I was concerned that this might put a burden on the app with about 100 controls on a screen, but it might be much easier to handle from a data point of view.