Can't remove duplicates from list generated from t...

Elijah_Magrane · 10-05-2021 07:05 AM

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?

Steve

Elijah_Magrane

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>>

Steve

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))) >>

Elijah_Magrane

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

Steve

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?

Elijah_Magrane

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))) >>```

Suvrutt_Gurjar

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 >>

Elijah_Magrane

Hi @Suvrutt_Gurjar, that workaround makes sense to me. I will try it and let you know how it goes. Thanks

Can't remove duplicates from list generated from template Start expression