Hello,
I have two tables -
A master carton can hold 4 boxes.
The box table has a reference column for master carton numbers. Every time a box goes into a master carton, I add the master carton number to the box’s record (i.e. row) from the reference list.
Solved! Go to Solution.
In the Valid If expression for the master carton number column of the box details table:
(
COUNT(
FILTER(
"box details",
AND(
ISNOTBLANK([master carton number]),
([master carton number] = [_THISROW].[master carton number]),
NOT([_ROWNUMBER] = [_THISROW].[_ROWNUMBER])
)
)
)
< 4
)
(
COUNT(
FILTER(
"box details",
AND(
ISNOTBLANK([master carton number]),
([master carton number] = [_THISROW].[master carton number]),
NOT([_ROWNUMBER] = [_THISROW].[_ROWNUMBER])
)
)
)
< [master carton number].[capacity]
)
See also:
Thank you for this Steve… works like a charm!
However, how do I prevent a full master carton from appearing in the reference list of master cartons?
Try using this as the Valid If expression:
FILTER(
"master cartons",
(
COUNT(
FILTER(
"box details",
AND(
ISNOTBLANK([master carton number]),
([master carton number] = [_THISROW-1].[master carton number]),
NOT([_ROWNUMBER] = [_THISROW].[_ROWNUMBER])
)
)
)
< [capacity]
)
)
@Steve, this helped me understand FILTER() even better. Thank you.
In the Valid If expression for the master carton number column of the box details table:
(
COUNT(
FILTER(
"box details",
AND(
ISNOTBLANK([master carton number]),
([master carton number] = [_THISROW].[master carton number]),
NOT([_ROWNUMBER] = [_THISROW].[_ROWNUMBER])
)
)
)
< 4
)
(
COUNT(
FILTER(
"box details",
AND(
ISNOTBLANK([master carton number]),
([master carton number] = [_THISROW].[master carton number]),
NOT([_ROWNUMBER] = [_THISROW].[_ROWNUMBER])
)
)
)
< [master carton number].[capacity]
)
See also:
Thank you for this Steve… works like a charm!
However, how do I prevent a full master carton from appearing in the reference list of master cartons?
Try using this as the Valid If expression:
FILTER(
"master cartons",
(
COUNT(
FILTER(
"box details",
AND(
ISNOTBLANK([master carton number]),
([master carton number] = [_THISROW-1].[master carton number]),
NOT([_ROWNUMBER] = [_THISROW].[_ROWNUMBER])
)
)
)
< [capacity]
)
)
@Steve, this helped me understand FILTER() even better. Thank you.
just brilliant… thanks!
User | Count |
---|---|
40 | |
34 | |
29 | |
23 | |
17 |