Power Platform Community Forum Thread Details

I'm having some difficulty creating collection.

I have 3 entities（CDS）. ProductList, Sale and Plan.

I would like to make a new collection by using the following table.

Entity: ProductList

Pcode

AAAA

BBBB

CCCC

DDD

FFFF

Entity: Sales

SPcode	SQTY	Sdate
AAAA	5	2019/1/1
AAAA	5	2019/2/1
BBBB	1	2019/1/2
BBBB	2	2019/1/4
BBBB	3	2019/2/1
CCCC	1	2019/1/1

Entity: Plan

PPcode	PQTY	Sdate
AAAA	15	2019/1/9
AAAA	10	2019/1/5
BBBB	2	2019/1/7
BBBB	1	2019/2/1
DDD	100	2019/2/24

---------------------------------------------------------

Here is something I would like to create.

create a table from ProductList(new Collection: SumList)
make month field 2019/1-2019/12(by month) to all Pcode.
add SalesQty and PlanQty field patch calculated QTY (SUM)

new Collection: SumList

*monthFD:first day of the month.

Pcode	monthFD	SalesQty	PlanQty
AAAA	2019/1/1	5	25
AAAA	2019/2/1	5	0
AAAA	2019/3/1	0	0
AAAA	2019/4/1	0	0
AAAA	2019/5/1	0	0
AAAA	2019/6/1	0	0
AAAA	2019/7/1	0	0
AAAA	2019/8/1	0	0
AAAA	2019/9/1	0	0
AAAA	2019/10/1	0	0
AAAA	2019/11/1	0	0
AAAA	2019/12/1	0	0
BBBB	2019/1/1	3	2
BBBB	2019/2/1	3	1
BBBB	2019/3/1	0	0
BBBB	・・・・	0	0
BBBB	2019/12/1	0	0
CCCC	2019/1/1	1	0
CCCC	・・・・	0	0
CCCC	2019/12/1	0	0
DDD	2019/1/1	0	0
DDD	2019/2/1	0	100
DDD	・・・・	0	0
DDD	2019/12/1	0	0
FFFF	2019/1/1	0	0

Thank you for your help!

Categories:

Building Power Apps

Hi @Anonymous ,

Please check my following steps:

1. Set the following codes to OnStart property of App, SumList collection has been formatted to what you want, and the next is to patch the SalesQty and PlanQty to it.

ClearCollect(col_Month,{monthFD:"2019/1/1"},{monthFD:"2019/2/1"},{monthFD:"2019/3/1"},{monthFD:"2019/4/1"},{monthFD:"2019/5/1"},{monthFD:"2019/6/1"},{monthFD:"2019/7/1"},{monthFD:"2019/8/1"},{monthFD:"2019/9/1"},{monthFD:"2019/10/1"},{monthFD:"2019/11/1"},{monthFD:"2019/12/1"});
ClearCollect(SumList2,AddColumns(ShowColumns(ProductList,"Pcode"),"SalesQty",0,"PlanQty",0,"month",col_Month));
ClearCollect(SumList,Ungroup(SumList2,"month"));
ClearCollect(SumList_1,SumList)

Note: SumList_1 is a temporary collection for the ForAll function to avoid ambiguity

2. Set the following codes to OnSelect property to patch the sum value of two columns.

ForAll(SumList_1,Patch(SumList,LookUp(SumList,Pcode=SumList_1[@Pcode] && monthFD= SumList_1[@monthFD]), {SalesQty:Sum(Filter(Sales,SPcode=Pcode && Sdate >= DateValue(monthFD) && Sdate < DateAdd(DateValue(monthFD),1,Months)),SQTY),PlanQty:Sum(Filter(Plan,PPcode=Pcode && Sdate >= DateValue(monthFD) && Sdate < DateAdd(DateValue(monthFD),1,Months)),PQTY)}));