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
Solved! Go to 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:
Best regards,
Kris
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.
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:
Best regards,
Kris
Morning Kris,
You legend, this works.
Thanks once again.
Lazz
The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.
This training provides practical hands-on experience in creating Power Apps solutions in a full-day of instructor-led App creation workshop.
User | Count |
---|---|
199 | |
52 | |
41 | |
39 | |
35 |
User | Count |
---|---|
263 | |
86 | |
71 | |
69 | |
66 |