Solved: Re: Filter to create a check of two conditions to ...

Inventarios_FOO · 08-24-2021 03:05 AM

Hello, how can I create a filter for a form that checks the table where the data will be recorded if the value of one of its columns does not exist according to 2 conditions, basically what I want is to check that it is not duplicating the document number of the same vendor in the data table records to avoid data duplication. This table has a column for vendor and another for document number.

Here I show you the form:

Proveedor = Vendor
Nº Documento = Document Number

Right now the formula I am using certainly checks that the document number is not being duplicated, but I need to apply a second condition to check that that document number does not already exist from the same vendor.

ALL these statements are true:
…1: This statement is false:
…(The value of column ‘Nº Documento’) is one of the values in the list (The list of values of column ‘Nº Documento’
…from rows of table ‘Cuentas por Pagar’
…where this condition is true: (This statement is false:
…(The value of column ‘ID Transaccion’) is one of the values in the list (LIST(
…The value of ‘ID Transaccion’ from the row referenced by ‘ID Transaccion’))))
…2: This statement is false:
…(The value of column ‘Nº Documento’) is one of the values in the list (The list of values of column ‘Proveedor’
…from rows of table ‘Cuentas por Pagar’
…where this condition is true: (This statement is false:
…(The value of column ‘ID Transaccion’) is one of the values in the list (LIST(
…The value of ‘ID Transaccion’ from the row referenced by ‘ID Transaccion’))))

Thanks!!

Aurelien

OK.

Then, what about this:

ISBLANK(
  FILTER("Cuentas por Pagar",
  AND(
    [_THISROW].[Nº Documento]=[Nº Documento],
    [_THISROW].[Proveedor]=[Proveedor]
  )
)

Or, if you set this in the [N° Documento] Valid_If field:

ISBLANK(
  FILTER("Cuentas por Pagar",
  AND(
    [_THIS]=[Nº Documento],
    [_THISROW].[Proveedor]=[Proveedor]
  )
)

Let us know if that works for you

Edit: For reference:

View solution in original post

Aurelien

Hi @Inventarios_FOODis

Did you use this ?

For example, for checking if a value is not in a list:

NOT(
  IN("valueToLookFor",yourTable[ColumnToSearch])
)

If that does not help you, can you please share the expression and your table columns.

Inventarios_FOO

Hello, sorry, you’re right, I thought I had written the expression and what I copied is the msg! Then I leave you the formula that I am using and a screenshot of the table.

AND(NOT(IN([_THIS], SELECT(Cuentas por Pagar[Nº Documento], NOT(IN([ID Transaccion], LIST([_THISROW].[ID Transaccion])))))),NOT(IN([_THIS], SELECT(Cuentas por Pagar[Proveedor], NOT(IN([ID Transaccion], LIST([_THISROW].[ID Transaccion])))))))

Aurelien

Something attracts my attention:

Isn’t there a redundant check here ? I mean, if the document number is not being duplicated…then it can’t already exist for the same vendor or even another, right ?
If so, your expression may be more simple:
NOT(IN([_THIS], Cuentas por Pagar[Nº Documento])

Explanation: Cuentas por Pagar[Nº Documento] will give the list of all existing “Nº Documento” in your table

Inventarios_FOO

No, because there may be that different sellers have the same document number and in that case I want the form to allow it, but if it is the same seller and a document number is being duplicated, the idea is that it does not allow it.

With the expression that I published, is doing the check but only on the condition that the document number is not duplicated as the only condition, but what I am looking for is to check that according to the seller the document number is not duplicated.

Aurelien

OK.

Then, what about this:

ISBLANK(
  FILTER("Cuentas por Pagar",
  AND(
    [_THISROW].[Nº Documento]=[Nº Documento],
    [_THISROW].[Proveedor]=[Proveedor]
  )
)

Or, if you set this in the [N° Documento] Valid_If field:

ISBLANK(
  FILTER("Cuentas por Pagar",
  AND(
    [_THIS]=[Nº Documento],
    [_THISROW].[Proveedor]=[Proveedor]
  )
)

Let us know if that works for you

Edit: For reference:

Inventarios_FOO

[quote=“Aurelien, post:6, topic:53566”]

Thanks!!! Works perfect!

Aurelien

You are welcome

Filter to create a check of two conditions to avoid duplicating data