Filter products sales report

[marinocity]

Hi!

I've an inventory app with next tables and columns:

-INVENTARIO (Table) [PART NUMBER] TYPE TEXT / KEY [PRODUCTO] TYPE TEXT [CANTIDAD] TYPE NUMBER SUM(SELECT(DETALLE_COMPRAS[CANTIDAD],[PART NUMBER]=[_THISROW].[PART NUMBER]))- SUM(SELECT(DETALLE_VENTAS[CANTIDAD],[PART NUMBER]=[_THISROW].[PART NUMBER]))
-VENTAS (Table Mother) [ID_DETALLE] TYPE TEXT / KEY [ID_VENTAS] TYPE TEXT [FECHA] TYPE DATE [CLIENTE] REF CLIENTES TABLE [USUARIO] TYPE TEXT [NOTAS] TYPE TEXT	-DETALLE_VENTAS (Table child) [ID_DETALLE] TYPE TEXT / KEY [ID_VENTAS] REF VENTAS TABLE [FECHA] TYPE DATE [PART NUMBER] REF INVENTARIO TABLE [PRODUCTO] TYPE TEXT [USER] VC / [ID_VENTAS].[USUARIO]
-COMPRAS (Table Mother) [ID_DETALLE] TYPE TEXT / KEY [ID_COMPRAS] TYPE TEXT [FECHA] TYPE DATE [CLIENTE] REF PROVEEDORES TABLE [USUARIO] TYPE TEXT [NOTAS] TYPE TEXT	-DETALLE_COMPRAS (Table child) [ID_DETALLE] TYPE TEXT / KEY [ID_COMPRAS REF VENTAS TABLE [FECHA] TYPE DATE [PART NUMBER] REF INVENTARIO TABLE [PRODUCTO] TYPE TEXT [USER] VC / [ID_COMPRAS].[USUARIO]

Create a table called [FILTER]
[REPORTID] TYPE NUMBER / KEY
[FROMDATE] TYPE DATE
[TODATE] TYPE DATE
[CLIENTE] TYPE TEXT / SELECT(VENTAS[CLIENTE],TRUE)
[PRODUCTO] TYPE TEXT / SELECT(DETALLE_VENTAS[PART NUMBER],TRUE)



I create a Slice in DETALLE_VENTAS Table to filter data.

ISNOTBLANK(ANY(SELECT(FILTER[REPORTID],AND([_THISROW].[FECHA]>=[FROMDATE],[_THISROW].[FECHA]<=[TODATE],[_THISROW].[CLIENTE]=[CLIENTE],[_THISROW].[PART NUMBER]=[PRODUCTO]))))

I need is that the [PRODUCTO] column of the FILTER table to be of type ENUMLIST, but doing so generates an error in the slice.

Cannot compare Ref with List in ([_THISROW].[PART NUMBER] = [PRODUCTO])

The idea is to select more than 1 product, currently it only allows me to choose one.

And on the other hand, I would like the column [PRODUCTO] only show the list of products that were sold to that customer [CLIENTE] , I don't want it to show all the products.

I hope I explained well, thanks!

[Marc_Dillon]

https://help.appsheet.com/en/articles/2357277-in

[marinocity]

Thanks @Marc_Dillon 
But I dont know how and where apply this expression.

[Marc_Dillon]

The error is because you're using an equality to compare a single item to a List. IN() allows comparing a single item to a List.

[marinocity]

Thanks @Marc_Dillon 

I changed formula of slice and works, now I can select multiple products from the list.

The only problem to be solved is that it only shows the products belonging to the selected client, not all the products in the inventory.

I tried with this formula in the slice, but it doesnt works...

ISNOTBLANK(ANY(SELECT(FILTER[REPORTID],AND([_THISROW].[FECHA]>=[FROMDATE],
[_THISROW].[FECHA]<=[TODATE],
[_THISROW].[CLIENTE]=[CLIENTE],
IN([_THISROW].[PART NUMBER],LIST(DETALLE_VENTAS[CLIENTE][PRODUCTO]))))))

[marinocity]

any help?

Only need to show in dropdown column [PRODUCTO] , sold products of every client, not all products.

I think is any formula in "Suggested Values", column [PRODUCTO] , table FILTER.
But I dont know how.

[marinocity]

Ok...70 views and only one user tried to help...
I wrote all the detailed information of the problem.
Disappointed with the Appsheet community.