Solved: How do I limit the number of times the same value ...

LLD · 04-10-2021 04:24 AM

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.

How do I ensure that a master carton number is not added to more than 4 boxes?
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?

Steve

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:

View solution in original post

LLD

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?

View solution in original post

Steve

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

View solution in original post

rmsmeltz

@Steve, this helped me understand FILTER() even better. Thank you.

View solution in original post

Steve

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

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