Gallery - Distinct

[Anonymous]

Morning all,

The below shows the formula without any Distinct formula, but gives every record from the database as per the filters.

Gallery -> Items = Filter(Search(DATABASE, DETAIL A.Text, "COLUMN A"), Now()-0.5>Value(Date&" "&Time))

Date and Time are within the DATABASE as seperate columns.

LabelB -> Text=COLUMN B

LableC -> Text=COLUMN C

LableD -> Text=CountIf(DATABASE, (Text(DETAIL A.Text)=COLUMN A), Now()-0.5 > Value(Date&" "&Time), COLUMN B=DETAILB.Text, COLUMN C=DETAILC.Text)

This LableD's purpose is to count all of the records to provide a sum or cycles. Lableetc -> Text=Column etc

DATABASE=SQL

DETAIL A=Input in the application

COLUMN A=Column which relates to the DETAIL A input from DATABASE.

COLUMN B, COLUMN C, COLUMN D & COLUMN etc= Details wanting to be shown from DATABASE.

Hence this give me a situation where I have the below.

Record 1 DETAIL A(1), DETAILB(1), DETAILC(1), DETAILD(1), etc

                      A,B,C,1

Record 2 DETAIL A(1), DETAILB(1), DETAILC(1), DETAILD(1), etc

                      A,B,C,1

Where as I want to show;

Records DETAIL A(1), DETAILB(1), DETAILC(1), Sum(DETAILD(1)), etc

                      A,B,C,2

Once I put the above in a Distinct formula, it doesn't allow each of the Lables to be changed as per what I want to show in them. It'll only show the "expression" section or column inwhich I like it too in all of the Lables.

Gallery -> Items = Distinct(Filter(Search(DATABASE, DETAIL A.Text, "COLUMN A"), Now()-0.5>Value(Date&" "&Time)), COLUMN B)

Record 1 DETAIL A(1), DETAILB(1), DETAILC(1), DETAILD(1), etc

                      B,B,B,B

Lazz

[iamlee]

Distinct will only return one column. This is what I'd do in your situation. 

NewColumnA will be: Distinct(Filter(Search(DATABASE, DETAIL A.Text, "COLUMN A"), Now()-0.5>Value(Date&" "&Time)), COLUMN A)

NewColumnB will be: Distinct(Filter(Search(DATABASE, DETAIL A.Text, "COLUMN A"), Now()-0.5>Value(Date&" "&Time)), COLUMN B)

and so on and so forth. 

Then you do ClearCollect(NewTable,NewColumnA,NewColumnB,NewColumnD). It'll be a multiple distinct formula and for columnD would be a CountIf formula. 

[Anonymous]

Morning iamlee, When you say "NewTable" in the last formula, what code / function do you mean exactly? I tried ClearCollect(Table({COLUMN A: ""}), Distinct(Filter(Search(DATABASE, DEATIL A.Text, "COLUMN A"), Now()-0.5.Value(Date&" "&Time), COLUMN A)) as a test and it came up with the below. "Behavior function in a non=behavior property. You can't use this property to change values elsewhere in the app. Behavior functions change the state of the app by changing values elsewhere in the app. ........"

[v-xida-msft · Accepted solution]

Hi @Anonymous,

Could you please share a bit more about your scenario?

Based on the formula that you provided, I think there is something wrong with your formula.

The Distinct function evaluates a formula across each record of a table, and returns a one-column table that contains the results, with duplicate values removed.

The Distinct(Filter(Search(DATABASE, DETAIL A.Text, "COLUMN A"), Now()-0.5>Value(Date&" "&Time)), COLUMN B) formula you provided would return a one-column table which contains the Column B value (with duplicated values removed).

Based on the needs that you mentioned, I think the GroupBy function and AddColumns function could achieve your needs.

I have made a test on my side, please take a try with the following workaround:

Set the Items proeprty of the Gallery to following formula:

GroupBy(
    Filter(Search(DATABASE, DETAIL A.Text, "COLUMN A"), Now()-0.5>Value(Date&" "&Time)),
    "ColumnA",
    "ColumnB",
    "ColumnC",
    "GroupData"
)

Within the Gallery control, set the Text property of the LabelB to following:

ThisItem.ColumnB

Set the Text property of the LabelC to following:

ThisItem.columnC

Set the Text property of the LabelD to following:

Sum(ThisItem.GroupData, ColumnD)  /*<- If you want to sum the ColumnD column */

If you want to count the total rows which match the filter condition, please set the Text property of the LabelD to following:

CountRows(ThisItem.GroupData)

More details about the GroupBy function and AddColumns function in PowerApps, please check the following article:

GroupBy function

AddColumns function

Best regards,

Kris

[Anonymous]

Morning Kris,

You legend, this works.

Thanks once again.

Lazz