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 |
Solved! Go to 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]
)
)
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]
)
)
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.
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 ***
)
)
Thank you. It works.
User | Count |
---|---|
35 | |
30 | |
30 | |
20 | |
18 |