Avoid duplicate order from one specific quote

Hi,

It’s probably basic but still not achieving it after reading different articles about constraint.

I have an Order table which is related with a Quote table (quote validated become order). I try to avoid the possibility to create a second order related to a quote.

I tried to constraint the Quote column in my Order table with NOT(IN([_THIS], SELECT(… but not finding the good way to write it.

Solved Solved
0 2 281
1 ACCEPTED SOLUTION

Steve
Participant V

Try:

ISBLANK(
  FILTER(
    "Order",
    ([_THISROW].[Quote] = [Quote])
  )
  - LIST([_THISROW])
)
  1. FILTER("Order", ([_THISROW].[Quote] = [Quote])) finds all rows in the Order table that have the same Quote column value as this order does. Note that the gathered list may include this row, too.

  2. ... - LIST([_THISROW]) removes this row from the list of rows generated by (1) so that the list only includes other rows.

  3. ISBLANK(...) asks, is the list of other rows (from (2)) empty? If yes (TRUE), this order is the only order that reference the quote.

View solution in original post

2 REPLIES 2

Steve
Participant V

Try:

ISBLANK(
  FILTER(
    "Order",
    ([_THISROW].[Quote] = [Quote])
  )
  - LIST([_THISROW])
)
  1. FILTER("Order", ([_THISROW].[Quote] = [Quote])) finds all rows in the Order table that have the same Quote column value as this order does. Note that the gathered list may include this row, too.

  2. ... - LIST([_THISROW]) removes this row from the list of rows generated by (1) so that the list only includes other rows.

  3. ISBLANK(...) asks, is the list of other rows (from (2)) empty? If yes (TRUE), this order is the only order that reference the quote.

Dang… that a boss-ass way to do that. There’s so many different ways you can do that… but this is… boss.

Top Labels in this Space