Collection with calculated column

[RomanP]

Hello there,

following scenario:

There are two tables in my CRM environment:

• "rooms" - in this table there is a column "available places" 
• "roombooking" - this table stores for witch interval there are bookings for rooms

  Room	Time from	Time to
  Yellow	10:00	17:00
  Green	08:00	16:00
  Green	08:00	12:00

In my App there are two inputparameters. "Booking_from" and "Booking_to"

The user has to fill in both parameters and now I want the generate a collection looking like this

Room	Available places	Places left
Yellow	5	4
Green	2	0
Red	3	3

After this the user may choose a room with "places left" > 0 and generate a roombookig.

So this is how to create a static table

ClearCollect( col_freerooms, 
{ Name: "Yellow", Available places: 5}, 
{ Name: "Green", Available places: 2 } 
)

And this is how to create a collection by a table from the CRM

ClearCollect(col_rooms;rooms)

My first question:

How can I generate a mix of this? For example a collection generated from the table "rooms" but I just want to add the columns "Name" and "Available places"?

And the next step would be to add a calculated column to this collection.

My first attempt was like this:

On the "OnSelect" function of my input parameters I addes the following code:

Set(
    _collision;
    Value(CountRows(
    Filter(
        roombooking;
        'time from' < datum.SelectedDate + Time(
            Value(Booking_from.Selected.Value);
            0
        );
        'time to' > datum.SelectedDate + Time(
            Value(Booking_to.Selected.Value);
            0
        )
    )
))
);;

Now I was able to use this function on my "Items" section of my "rooms"-Lookup on my form

Filter(rooms;Available places - collision > 0)

In the end I realised that this makes no sense because I count rows for all rooms and compare this variable with each room individually. I could create a "count_collision"-variable for each room but this is nonsens too. Adding a new room in my CRM table I would also have to add that room in the PowerApps code every time.

So I came up with the idea of a collection + calculated column.

But i neither manage to construct the collection nor to add the calculated column.

Any tips for me how to achive this or even a different approach?

Thanks!

Roman

[daruom]

Hello,

I think you just need to use the AddColumns function.

https://docs.microsoft.com/fr-fr/powerapps/maker/canvas-apps/functions/function-table-shaping

[RomanP]

I´ve already tried to use this function but it didn´t work for me

The function on my Button for "OnSelect" looks like this:

ClearCollect(rooms; { Name: "Yellow"});;
AddColumns(rooms;"TestColumn";"Test")

But "TestColumn" isn´t added to my collection.

[shoog]

AddColumns doesn't modify the source table/collection, so you have to store the result somewhere.

Easiest is to combine both statements:

ClearCollect(rooms; AddColumns( datasource;"TestColumn";"Test"))

[RomanP]

Thanks shoog. That helped me to understand collections.

But I already encountered anohter problem...

ClearCollect(col_rooms; rooms);;
ClearCollect(col_rooms_available_places; AddColumns(col_rooms; "Available_places"; CountRows(xxx)))

When I insert "rooms" for xxx, then it works.

When I insert "roombooking" for xxx, it doesn´t.

Placing "CountRows(roombooking)" somewhere else works. Just not in this function.

I can´t find any restictions for the function "CountRows".
So i don´t understand why it works in some places and in others it doesn´t.

My goal is to get this function working as "Count Rows"

Ofcourse the GUID should not be hardcoded. But thats my next step

CountRows(
    Filter(
        roombooking;
        'time from' < datum.SelectedDate + Time(
            Value(Booking_from.Selected.Value);
            0
        );
        'time to' > datum.SelectedDate + Time(
            Value(Booking_to.Selected.Value);
            0
        );
        ThisRecord.room.room= GUID( "a33834b3-7514-ec11-b6e6-000d3ab19038" ) 
    )
)

[shoog]

You probably want to use CountIf for this one in stead of CountRows, something like:

AddColumns(col_rooms as col_rooms; "Available"; CountIf(roombooking; room = col_rooms.room && 'time from' ... && 'time to' ...))

[RomanP]

I tried to use CountIf and it still didn´t work. 

After reading a dozent forumposts I found a solution. 

I had to wrap "roombooking" in [@roombooking] so that PowerApps knows what I´m referring to.

But once again I´m stuck...

This works know as it is intended but there is still this hardcoded GUID.

How can i refer to the GUID of the room record.

Using "ThisRecord" in the filter, looks for the roombooking record.

I tried to use "ThisItem" but then i receive a syntaxerror. PowerApps also doen´t auto suggest "ThisItem" when i type "This..."

ClearCollect(
    col_rooms,
    rooms
);
ClearCollect(
    col_rooms_available_places,
    AddColumns(
        col_rooms,
        "Available Places ",
            CountRows(
                Filter(
                    [@roombooking];
                    'time from' < datum.SelectedDate + Time(
                        Value(Booking_from.Selected.Value);
                        0
                    );
                    'time to' > datum.SelectedDate + Time(
                        Value(Booking_to.Selected.Value);
                        0
                    );
                    ThisRecord.room.room= GUID( "a33834b3-7514-ec11-b6e6-000d3ab19038" ) 
                )
            )
    )
);