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!
Solved! Go to 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]))))
Hi @JuanCruzCookdata
Maybe you need to include a Date and or EOM end of month in there somewhere?
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.
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]))))
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!
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.
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
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.
Yes. exactly, that would the best of two worlds, but canโt get the way to do it
I think Iโm confused now. In your original post you mentionedโฆ
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.
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!
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
User | Count |
---|---|
43 | |
26 | |
24 | |
14 | |
12 |