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

See also:

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

:+1:t4: :+1:t4: :+1:t4: just brilliant… thanks!

1 Like