Filter many to many table with 2 different conditions from 2 different tables

[caburity]

I need a many to many table with 2 different conditions from 2 different tables to add on "Referenced Rows" in "Execute an action on a set of rows from a different table".

This filter is for change TABLE-3.UNITARY_VALUE when TABLE-3 is change, add or delete some row and the sum(TABLE-3.QUANTITY) for TABLE-3.FK_COD_TABLE-1 (same of affected row) and Contains(TABLE-2.NAME, "bell pepper").

This is for a promotion where, depending on the number of bell peppers (regardless of their color), the unit price will change.

e.g.  If I change QUANTITY of FK_COD_TABLE-2 = 7 from 1 to 10 of FK_COD_TABLE-1 = 2 and FK_COD_TABLE-2 = Select(TABLE-2.CODE, Contains(TABLE-2.NAME, "bell pepper")) (Something like that). then I need: have a filter result like this RESULT_TABLE

COD	FK_COD_TABLE-1	FK_COD_TABLE-2	QUANTITY	UNITARY_VALUE
5	2	1	1	1
6	2	2	1	1
7	2	3	10	1

Can someone help me about it? Thank you

REFERENCE TABLES BELOW.

TABLE-1

COD	NAME
1	Name 1
2	Name 2
3	Name 3

TABLE-2

COD	NAME
1	Red bell pepper
2	Green bell pepper
3	Yellow bell pepper
4	Orange
5	Tomato

TABLE-3

COD	FK_COD_TABLE-1	FK_COD_TABLE-2	QUANTITY	UNITARY_VALUE
1	1	2	1	1
2	1	3	1	1
3	1	1	1	1
4	1	5	1	1
5	2	1	1	1
6	2	2	1	1
7	2	3	1	1
8	2	4	1	1
9	2	5	1	1
10	3	2	1	1
11	3	3	1	1
12	3	4	1	1
13	3	5	1	1

[TeeSee1 · Accepted solution]

IF I understand your requirements correctly, you can use an expression like

FILTER(
 "TABLE-3",
 AND(
  [FK_COD_TABLE-1]=[_THISROW].[FK_COD_TABLE-1],
  CONTAINS([FK_COD_TABLE-2].[NAME], "bell pepper")
 )
)
  

to return a list of [Table-3].[COD] values filtered on the specific case you gave in your example.

However, you do not want to "hard code" the second filtering condition (i.e. "bell pepper") because this works only when the changed row is related to a bell pepper record.

I would add a category to Table-2 to group your items. Then you can modify the above expression to something like

FILTER(
 "TABLE-3",
 AND(
  [FK_COD_TABLE-1]=[_THISROW].[FK_COD_TABLE-1],
  [FK_COD_TABLE-2].[CATEGORY]=[_THISROW].[FK_COD_TABLE-2].[CATEGORY]
 )
)

[caburity]

Hi @TeeSee1 ,

It works. Thank you.

Just one more question regarding this one.

In "Execute an action on a set of rows from a different table". I add this filter in Referenced Rows and TABLE-3.UNITARY_VALUE should be change using as basis sum([TABLE-3].[QUANTITY]), for this I create a new action and add this action as Referenced Action. This new action is a Data: set the values of some columns in this row and in Set these columns I added TABLE-3.UNITARY_VALUE and in the formula something like 

if(and(sum([QUANTITY])>= 11, sum([QUANTITY]) <= 30), 0.7, if(and(Sum([QUANTITY]) > 30, Sum([QUANTITY]) <= 50), 0.6, if(and(Sum([QUANTITY]) > 50), 0.5, 1.0)))

But I have this message

Can you help me about it?

Thank you.

[TeeSee1]

I am not quite sure what you are trying to do but here are some comments.

SUM expects a list of numeric values as its argument. [QUANTITY] is a single value, thus, it fails.

I suspect your SUM([QUANTITY]) needs to be replaced with

SUM(
 SELECT(
  TABLE-3[QUANTITY],
  *** HERE goes a condition to filter the rows you want to sum the quantity over ***
 )
)

[caburity]

Thank you. It works.