Good Day,
I am attempting to create a template that SUMS up list of the SKUs made for a specific Lot. I have tried several iterations of the Start expression with no luck.
See a generic dereference start expression below
<<Start: [Related Packagings]>>
<<SUM(SELECT(Packaging[E_QTY_EA],(AND([Product]=[_THISROW-1].[Product],[Lot#]=[_THISROW-1].[Lot#])),TRUE))>>
<<End>>
Output
1,080
1,080
2,520
2,520
2,520
2,520
2,520
2,520
The output is basically what I want except no duplicates, how can I remove them?
Tried both the start expression as well as the general expression, no luck same output
<<Start: UNIQUE(SELECT([Related Packagings][Id],(AND([Product]=[Product],[Lot#]=[Lot#]),TRUE))) >>
<<SUM(UNIQUE(SELECT(Packaging[E_QTY_EA],(AND([Product]=[_THISROW-1].[Product],[Lot#]=[_THISROW-1].[Lot#])),TRUE)))>>
<<End>>
This:
<<Start: UNIQUE(SELECT([Related Packagings][Id],(AND([Product]=[Product],[Lot#]=[Lot#]),TRUE))) >>
Should probably be this:
<<Start: UNIQUE(SELECT([Related Packagings][Id],(AND([Product]=[_THISROW].[Product],[Lot#]=[_THISROW].[Lot#]),TRUE))) >>
That is what I thought, I get an error.
* Error 1 : 'New process 2' task 'New Task 2' Body template. Expression 'UNIQUE(SELECT([Related Packagings][Id],(AND([Product]=[_THISROW].[Product],[Lot#]=[_THISROW].[Lot#]),TRUE)))' is invalid due to: Error in expression '[_THISROW].[Product]' : Unable to find column 'Product'.
* Error 2 : 'New process 2' task 'New Task 2' Body template. Start expression 'UNIQUE(SELECT([Related Packagings][Id],(AND([Product]=[_THISROW].[Product],[Lot#]=[_THISROW].[Lot#]),TRUE)))' should generate a List of Ref values. Please verify that it generates a List and that the contents of the List are Ref values. Ref values should come from the 'Key' column of the referenced table.
* Error 3 : The document body is empty
In this (without the [_THISROW]
dereferences):
<<Start: UNIQUE(SELECT([Related Packagings][Id],(AND([Product]=[Product],[Lot#]=[Lot#]),TRUE))) >>
To what [Product]
and [Lot#]
were you trying to compare the same column values of each related Packaging row?
Basically when I trigger the workflow on the parent table [Lot#] I want to sum up the quantities of the child table [related packagings] by [Product] (which is unique to [related packagings]), for which there are multiple entries. For example there may be 8 related packaging entries for only two [products].
Let me know if something is not clear
I also tried this version, of the Start statement, same issue
<<Start: UNIQUE(SELECT(Packaging[Id],(AND([Product]=[_THISROW].[Product],[Lot#]=[_THISROW].[Lot#]),TRUE))) >>```
You may wish to explore below.
Create
A slice on the child table โPackagingโ. Slice called say โSumQuantitiesโ with row filter expression [ID]=MINROW(โPackagingโ,"_ROWNUMBER", AND([Product]=[_THISROW].[Product],[Lot#]=[_thisrow].[Lot#]))
A rev ref VC called say [RelatedSumPackagingQuantities] in the parent table โLot#โ based on slice in 1) above. VC expression REF_ROWS(โSumQuantitiesโ, โLot# table Keyโ)
A VC in the โPackagingโ table called say [SumQuantitiesbyProduct] with expression
SUM(SELECT(Packaging[E_QTY_EA], AND([Product]=[_THISROW].[Product], [Lot#]=[_THISROW].[Lot#])))
The report template could look like something below
<<Start: [RelatedSumPackagingQuantities] >>
<<[SumQuantitiesbyProduct]>>
<< END >>
Hi @Suvrutt_Gurjar, that workaround makes sense to me. I will try it and let you know how it goes. Thanks
User | Count |
---|---|
40 | |
34 | |
29 | |
23 | |
17 |