AddColumns ...

(1) Share

Report

Posted on by sasrsc

678

I currently have an items property that works, then I'm getting the values that I need within the context of the gallery in a separate text variable. This works and is fine in theory.
What I would prefer to do ... is add a column to the collection and then reference that in the gallery and perform whatever I want. I think the performance would be better that way. Each row will append to a new column with a table of zero or more rows.

The data source is Dataverse.
So you can see below I'm trying to add a column called Rates to the parent table Contracts as I loop.
There's a relationship on the Contract column in the ContractRates table and ultimately I'm joining on the guid column which is Contract.

The way it works (below) now is to NOT add the Rates column using the AddColumns and instead inside the gallery do this...in a text label which works. What am I doing wrong in the code above. It could be a scope thing...the error message is that the specified column is not available in this context???

With(

    {

        _r: Filter(

            ContractRates,

            Contract.Contract = ThisItem.Contract

        )

    },

    With(

        {

            _max: Max(

                _r,

                Amount

            ),

            _min: Min(

                _r,

                Amount

            )

        },

        If(

            Value(_min) = Value(_max),

            Text(_min),

            $"{Text(_min)}-{Text(_max)}"

        )

    )

)

Categories:

Building Power Apps

I have the same question (0)

All responses (3)

Answers (0)

Suggested answer

mmbr1606 14,605 Super User 2025 Season 2 on at

Like (0)

Report
hey

can u try this modification;

ClearCollect( colHotelRates, SortByColumns( AddColumns( Filter(Contracts, DateEnd > Today()), "Rates", Filter( ContractRates, Contract.Contract = ThisRecord.Contract ), "HotelBrand", dictHotel.hotelChain.dictHotelBrand.BrandName, "HotelCity", dictHotel.City, "HotelName", dictHotel.PropertyName, "SASRating", SASRating, "AverageRating", LookUp( colSummarizedAvg, HotelGUID = dictHotel.dictHotel, AverageRating ) ), "HotelCity", SortOrder.Ascending, "HotelName", SortOrder.Ascending ) )

if it worked please mark as verified answer

cheers

Was this reply helpful? Yes No
sasrsc 678 on at

Like (0)

Report

I tried that - and here's the response...

The error is...

I don't think I've ever tried to add a column that would reflect a table value i.e. Filtering. I've done plenty of LookUps.

Was this reply helpful? Yes No
timl 36,393 Super User 2025 Season 2 on at

Like (0)

Report
Hi @sasrsc

This doesn't specifically answer your question about calling AddColumns.

However, if the motivation for your change is to improve performance, you might want to consider one of these other approaches which would actually make a more significant improvement.

The first is to define rollup columns in the Contracts table for the min and max Rates in Dataverse. This would work well if the rates don't change more than (say) once every 12 hours, which I guess could be true for hotel rates. By using this approach, the min and max will be calculated on the server which would be much quicker.

https://learn.microsoft.com/en-us/power-apps/maker/data-platform/define-rollup-fields

If you prefer to do this entirely in Power Apps, the second approach you might consider is to call the Min and Max functions, rather than Filter.

The way it works right now is for each Contract record, the call to Filter retrieves all associated rates for the Contract in order to determine only the min and max values. With Dataverse, the Min and Max functions are delegable, so you can directly retrieve the min and max values without retrieving all the in-between rates.

https://learn.microsoft.com/en-us/power-apps/maker/canvas-apps/connections/connection-common-data-service#power-apps-delegable-functions-and-operations-for-dataverse

For this, the AddColumns part of your formula would look like this:

AddColumns( Filter(Contracts, DateEnd > Today()), MinRate, Min(ThisRecord.Rates.Amount), MaxRate, Max(ThisRecord.Rates.Amount), FormattedValue, Min(ThisRecord.Rates.Amount) & " - " & Max(ThisRecord.Rates.Amount) )

Was this reply helpful? Yes No