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!!
Solved! Go to 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:
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])))))))
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
User | Count |
---|---|
61 | |
25 | |
14 | |
11 | |
6 |