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

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:
3X_b_0_b017ba85e8f44c00c893c1891a95c63d6c8feb7c.png

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!!

Solved Solved
0 7 315
1 ACCEPTED SOLUTION

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

7 REPLIES 7

Aurelien
Participant V

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.

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

3X_6_b_6b17e26d560af43528ea2d7d5076d9862c7a8ccc.png

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

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.

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:

[quote=โ€œAurelien, post:6, topic:53566โ€]

Thanks!!! Works perfect!

You are welcome

Top Labels in this Space