# How do I limit the number of times the same value is added to a table

Hello,

I have two tables -

• one containing box details including box numbers (unique; key field)
• another containing master cartons details, including master carton numbers (unique; key field)

A master carton can hold 4 boxes.

• A box cannot be added to more than one master carton.
• Each master carton can hold 4 boxes or less
• A box may be removed from a master carton (and another may be added in its place)

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.

1. How do I ensure that a master carton number is not added to more than 4 boxes?
2. Different types of master cartons have differing capacities. If I have a â€ścapacityâ€ť column for each master carton, how do I limit the addition of boxes to a master carton, w.r.t. itâ€™s capacity?

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

2 Likes

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?

1 Like

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]
)
)``````
1 Like

just brilliantâ€¦ thanks!

1 Like