Index per Day

(0) Share

Report

Posted on by dobroo

201

Hi,

can someone help me to build this formula:

My table looks like this:

Ressource	StartDate	EndDate	StartTime	EndTime
R1	01.01.2021	01.01.2021	10:00	12:00
R1	01.01.2021	02.01.2021	12:00	13:00
R1	01.01.2021	01.01.2021	09:00	10:00
R1	01.01.2021	01.01.2021	16:00	17:00
R2	01.01.2021	01.01.2021	10:00	12:00
R2	01.01.2021	02.01.2021	12:00	13:00
R2	01.01.2021	01.01.2021	09:00	10:00
R2	01.01.2021	01.01.2021	16:00	17:00

Now I want to get the data in this format

INDEX	Ressource	StartDate	EndDate	StartTime	EndTime
2	R1	01.01.2021	01.01.2021	10:00	12:00
3	R1	01.01.2021	02.01.2021	12:00	13:00
1	R1	01.01.2021	01.01.2021	09:00	10:00
4	R1	01.01.2021	01.01.2021	16:00	17:00
2	R2	01.01.2021	01.01.2021	10:00	12:00
3	R2	01.01.2021	02.01.2021	12:00	13:00
1	R2	01.01.2021	01.01.2021	09:00	10:00
4	R2	01.01.2021	01.01.2021	16:00	17:00

My Solution for one Ressource is with a collection like this:

ClearCollect(
 colIndex;
 {Index: 0}
);;
ClearCollect(
 colDailyINDEX;
 AddColumns(
 SortByColumns(Filter(
 tbl_Reservation;
 StartDate = _dateSelected || 
 EndDate = _dateSelected || 
 (StartDate < _dateSelected && EndDate > _dateSelected);
 cra8d_ressource="R1"
 );"EndDate";Ascending;"EndTime";Ascending);
 "INDEX";
 0
 )
);;
ForAll(
 RenameColumns(
 colDailyINDEX;
 "INDEX";
 "INDEX2"
 ) As Table0;
 Collect(
 colIndex;
 {Index: Value(CountRows(colIndex))}
 );;
 Patch(
 tbl_Reservation;
 LookUp(
 tbl_Reservation;
 cra8d_autowert = Table0.cra8d_autowert
 );
 {cra8d_tagesindex: Last(colIndex).Index}
 )
);;

This works fine for the selected Ressource "R1" oder "R2" manually. But how can I index it automatically for every Ressource?

I thought with a ForAll()-Function. But in a ForAll()-function is a ClearCollect()-function not possible.

Categories:

Building Power Apps

All responses (4)

Answers (2)

Sort by

RandyHayes 76,299 Super User 2024 Season 1 on at

Like
a
(0)

Report
Copy link

Link copied!

@dobroo

ForAll is a function that returns a table. It is not a For/Loop like in programming (because PowerApps is not programming!)

So your ForAll's are backward.

However, I am not sure what you are trying to get to as your picture of what you have and what you want are the same.

Can you clarify?
dobroo 201 on at

Like
a
(0)

Report
Copy link

Link copied!

I edited the first Post. now is the first column the Index-column and not the last.
it is now clearer?
Verified answer

RandyHayes 76,299 Super User 2024 Season 1 on at

Like
a
(1)

Report
Copy link

Link copied!
@dobroo

Thank you...much clearer!

I had to make some assumptions on your data types, so I leaned toward text/strings for all of the records. That can be adjusted.

Using your data, the following formula on a Gallery Items property produces the results seen below:

With({_items: Table( {Ressource:"R1", StartDate:"01.01.2021", EndDate:"01.01.2021", StartTime:"10:00", EndTime:"12:00"}, {Ressource:"R1", StartDate:"01.01.2021", EndDate:"02.01.2021", StartTime:"12:00", EndTime:"13:00"}, {Ressource:"R1", StartDate:"01.01.2021", EndDate:"01.01.2021", StartTime:"09:00", EndTime:"10:00"}, {Ressource:"R1", StartDate:"01.01.2021", EndDate:"01.01.2021", StartTime:"16:00", EndTime:"17:00"}, {Ressource:"R2", StartDate:"01.01.2021", EndDate:"01.01.2021", StartTime:"10:00", EndTime:"12:00"}, {Ressource:"R2", StartDate:"01.01.2021", EndDate:"02.01.2021", StartTime:"12:00", EndTime:"13:00"}, {Ressource:"R2", StartDate:"01.01.2021", EndDate:"01.01.2021", StartTime:"09:00", EndTime:"10:00"}, {Ressource:"R2", StartDate:"01.01.2021", EndDate:"01.01.2021", StartTime:"16:00", EndTime:"17:00"} ) }, Ungroup( AddColumns( GroupBy( _items, "Ressource", "StartDate", "_records" ), "_index", With({_rows: Sort(AddColumns(_records, "_t", TimeValue(StartTime)), _t)}, ForAll(Sequence(CountRows(_rows)), Patch(Last(FirstN(_rows, Value)), {Index:Value}))) ), "_index" ) )

Result:
Verified answer

dobroo 201 on at

Like
a
(1)

Report
Copy link

Link copied!
Wow, I am enthusiastic about the formula!!!
Now I am able to get an Index without this two Collections-Formula. In addition, this has accelerated the app many times over.
Thank you very much!!!

I modified the formula because I also wanted to have the days that end on that day also.
GroupBy( Ungroup( AddColumns( GroupBy( Filter( MyCalendarRessources; cra8d_datum = _dateSelected || cra8d_enddatum = _dateSelected || (cra8d_datum < _dateSelected && cra8d_enddatum > _dateSelected) ); "cra8d_ressource"; "SortDATA" ); "_index"; With( { _rows: SortByColumns( SortDATA; "cra8d_enddatum"; Ascending; "cra8d_ende"; Ascending ) }; ForAll( Sequence(CountRows(_rows)); Patch( Last( FirstN( _rows; Value ) ); {Index: Value} ) ) ) ); "_index" ); "cra8d_ressource"; "DATA" )