(plz help) Prevent duplicated items in same load

[JuanCruzCookdat]

Hello,
I’m stuck with a problem
we have an application where we load the monthly stock of the
restaurant, the goal is to disable the items that were loaded into the stock of THIS load, that is, I want it to be blocked to add twice to the inline.

For example,
last month all the monthly stock was made, but the same item was added by mistake three times.

I would like to disable the ability to add the same item more than once but only to THIS upload, not forever.

I used the following formula:

NOT(IN([_THIS], SELECT(DETALLE INVENTARIO[ARTICULO], [ARTICULO] = [_ThisRow].[ARTICULO])))

and it works but if you loaded it once in another load, save, and choose it again, it is now disabled forever and I would like that not to happen.

Thanks for the help in advance!

[Lynn]

Hi @JuanCruzCookdata
Maybe you need to include a Date and or EOM end of month in there somewhere?

[JuanCruzCookdat]

Hi, @Lynn

I don’t want to filter the same article by month, I want to filter it by the same upload ID.

I have two transactional tables and one master.

The parent transactional table is called INVENTORIES and the inherited one is INVENTORIES DETAIL.

The master table is called ARTICLES.

What I am looking for is that in the inherited table (INVENTORIES DETAIL) when loading an article (ref to ARTICLES table), it disables me to load those that were already loaded (by the same INVENTORY ID)

thank you for answering.

[WillowMobileSys · Accepted solution]

The same principal applies as what @Lynn suggested. You need some way to identify which upload process added the record.

The assumption here is that when you upload the inventory info, a new record is added each time an item is uploaded for THIS upload process. In other words, you have a set of rows added each time you perform the upload.

If so and you truly do have a column named Upload ID, then simply add the Upload ID as a column on the newly added rows and then adjust your expression as follows:

NOT(IN([_THIS], SELECT(DETALLE INVENTARIO[ARTICULO], 
          AND([ARTICULO] = [_ThisRow].[ARTICULO],
              [Upload ID] = [_THISROW].[Upload ID]))))

[JuanCruzCookdat]

Hi @WillowMobileSystems

Thank you very much, it helped us a lot.

Now I find myself with another less important problem, I would like to make it disable duplicates (with the formula that you gave me previously it was perfect) but there are also other filters in the valid if. The formula currently looked like this:

FILTER(
“Articulos”,
AND(
NOT(
IN(
[_THIS],
SELECT(
DETALLE INVENTARIO[ARTICULO],
AND(
[ARTICULO] = [_THISROW].[ARTICULO],
[ID INVENTARIO] = [_THISROW].[ID INVENTARIO]
)
)
)
),
[FAMILIA] = [_THISROW].[FAMILIA],
[STOCK?] = true,
[ESTADO] = ACTIVO
)
)

In this way, when the same article is selected, since the condition is inside a filter since I have to filter by the states

[FAMILIA] = [_THISROW].[FAMILIA],
[STOCK?] = true,
[ESTADO] = ACTIVO

I can’t make the custom error “Item selected twice” appear, only the item does not appear, I would like to be able to put the filter and also the formula that you gave me, is there a way to do it?

I tried with this:

AND(
NOT(
IN(
[_THIS],
SELECT(
DETALLE INVENTARIO[ARTICULO],
AND(
[ARTICULO] = [_THISROW].[ARTICULO],
[ID INVENTARIO] = [_THISROW].[ID INVENTARIO]
)
)
)
),
FILTER(
“Articulos”,
AND(
[FAMILIA] = [_THISROW].[FAMILIA],
[STOCK?] = true,
[ESTADO] = ACTIVO
)
)
)

But it shows it an error message:

Condition AND(NOT(IN([_THIS],SELECT(DETALLE INVENTARIO[ARTICULO],AND(([ARTICULO] = [_THISROW].[ARTICULO]), ([ID INVENTARIO] = [_THISROW].[ID INVENTARIO]))))), SELECT(Articulos[ID ARTICULO COMPRA],AND(([FAMILIA] = [_THISROW].[FAMILIA]), ([STOCK?] = “true”), ([ESTADO] = “ACTIVO”)))) has an invalid structure: subexpressions must be Yes/No conditions

Thanks again for your help!

[WillowMobileSys]

JuanCruzCookdata:

I would like to make it disable duplicates

If I understand correctly, you need to remove duplicates. AppSheet has a UNIQUE() function which does exactly that. Simply wrap the entire resulting list with the this function.

help.appsheet.com

UNIQUE()

Unique items in list

[JuanCruzCookdat]

I attach a photo screenshot, as you can see, it does not throw any error when trying to select that item twice, it simply appears blank, and the reason for this must be because it is inside of a FILTER () function,
if I just grab the function without being inside the FILTER() it throws me the custom error that I generated in Valid If, but if I put it inside the FILTER (), with the other filters I need, it doesn’t throw no error although it does not let you select it

[WillowMobileSys]

Oh ok, so the problem is the ability to select an item on two different occasions? Not that it appears twice in the dropdown list?

Your expression should be implemented so that when a row is added, that item is automatically removed from the dropdown list so that it doesn’t even appear. If the item still shows, then an adjustment is needed to the Valid_If expression for the column dropdown. Then you won’t even need a validation error to show.

[JuanCruzCookdat]

WillowMobileSystems:

Your expression should be implemented so that when a row is added, that item is automatically removed from the dropdown list so that it doesn’t even appear. If the item still shows, then an adjustment is needed to the Valid_If expression for the column dropdown. Then you won’t even need a validation error to show.

Yes. exactly, that would the best of two worlds, but can’t get the way to do it

[WillowMobileSys]

I think I’m confused now. In your original post you mentioned…

JuanCruzCookdata:

I would like to disable the ability to add the same item more than once but only to THIS upload, not forever.

Unless I mis-understood, the NOT(IN()) expression should be the expression that prevents choosing another item already added to THIS load. That expression should be placed in your “ARTICULO” column in the Valid_If property. It provide the list of valid items the user can choose at that moment - i.e. a list of items NOT already added to THIS load.

The resulting expression was:

NOT(IN([_THIS], SELECT(DETALLE INVENTARIO[ARTICULO], 
          AND([ARTICULO] = [_ThisRow].[ARTICULO],
              [Upload ID] = [_THISROW].[Upload ID]))))

With this expression in the Valid_If, the list is automatically adjusted to only valid items so no custom error message (not the associated error message expression) would be needed.

[JuanCruzCookdat]

Do not worry, with that formula that you gave me before you helped me a lot, in the future I will make another publication with the new question so I do not mix things up, thank you!

[Krumel]

For what I can figure out your app has one table for each UPLOAD, one for each ARTICLE and one for each ARTICLE IN THE UPLOAD. If that’s the case, the easiest way to solve your problem is making the key of each ARTICLE IN THE UPLOAD a combinated key of the UPLOAD and the ARTICLE. That way the system warns you that you are duplicating a key and doesn’t let you duplicate ARTICLES in that particular UPLOAD