There has been an important fix recently (https://bugs.documentfoundation.org/show_bug.cgi?id=159595): > Validity = List with A,B > > Entering C gives a warning and empties the input. > > But if the option "Show error message..." under > Error Alert is off, the value is accepted anyway > and validation fails. Users like me (I hope I'm not the only one) have "used" this bug as if it was a feature in order to achieve a sort of "suggestion dropdown" (like a helper: you can chose a predefined value or enter yours), which can be a very powerful tool. Now this bug is resolved, I'd like a proper way to allow users to enter their own value if the ones proposed in the dropdown are not suitable. It could be achieved with a new option in data validity ("allow users to enter a custom value"), or with a new kind of dropdown aside data validity (like "suggestion list").
This is weird, because if you use a "List" and set the "Error alert" to not show "Error message", this should accept other values that are not in the list. In Excel this works. It should work in Calc as well. This looks like a bug to me.
FTR a related bug... in the "Error Alert" tab, if you uncheck "Show error message", the controls under "Content" should be disabled. But they remain enabled, which is weird because they have no effect if no message is to be shown.
(In reply to Rafael Lima from comment #1) > This is weird, because if you use a "List" and set the "Error alert" to not > show "Error message", this should accept other values that are not in the > list. Unless I am misunderstanding and/or misremembering, that sounds exactly as what was reported as a bug in the aforementioned tdf#159595. IOW, it sounds as the solution for tdf#159595 should be reverted and tdf#159595 should be NAB. Hmm. I have no access to Excel, so I cannot test this. > > In Excel this works. It should work in Calc as well. > > This looks like a bug to me.
Quoting from: <https://support.microsoft.com/en-us/office/more-on-data-validation-f38dee73-9900-4ca6-9301-8a5f6e1f0c4c> * Warning Warn users that the data they entered is invalid, without preventing them from entering it. * Information Inform users that the data they entered is invalid, without preventing them from entering it. This type of error alert is the most flexible. Whereas: * Stop Prevent users from entering invalid data in a cell. So it would seem that Excel allows any of these cases, depending on the type of error alert.
Those are the alternative error alerts. I don't recall what is the expected behavior when no error alert is selected in Excel (which is what was changed in tdf#159595 for Calc).
Confirming that Excel does this, if the error message is off "wrong" values are silently accepted. It's a bug IMO and at max we max add a compatibility option. Excel has means to highlight ("circle") the wrong data, ie. validation is supposed to be applied after input. I wonder if you are looking for the selection list (alt+down) https://help.libreoffice.org/latest/en-US/text/scalc/01/06130000.html
(In reply to Heiko Tietze from comment #6) > Confirming that Excel does this, if the error message is off "wrong" values > are silently accepted. It's a bug IMO and at max we max add a compatibility > option. Excel has means to highlight ("circle") the wrong data, ie. > validation is supposed to be applied after input. > > I wonder if you are looking for the selection list (alt+down) > https://help.libreoffice.org/latest/en-US/text/scalc/01/06130000.html @Heiko, Selection list is not the same thing as the behavior described in comment 0. Since users have been using Calc with this behavior for 3+ decades and Excel does the same, please consider the simpler solution (which happens to result in better compatibility too): reverting the patch from tdf#159595, which was implemented very recently only. As for the difference between being consider a bug or not, it would be resolved by adding the (prior, old) "real" behavior to the official Help content and guides, closing any possible assumption regarding how it is supposed to work/behave. Just add a patch for the Help content immediately after the proposed reverse.
(In reply to ady from comment #7) > ... > Since users have been using Calc with this behavior for 3+ decades and Excel > does the same, please consider the simpler solution (which happens to result > in better compatibility too): reverting the patch from tdf#159595, which was > implemented very recently only. > ... Quote from link provided https://support.microsoft.com/en-us/office/more-on-data-validation-f38dee73-9900-4ca6-9301-8a5f6e1f0c4c) " 1) Stop: Prevent users from entering invalid data in a cell. A Stop alert message has two options: Retry or Cancel. 2) Warning: Warn users that the data they entered is invalid, without preventing them from entering it. When a Warning alert message appears, users can click Yes to accept the invalid entry, No to edit the invalid entry, or Cancel to remove the invalid entry. 3) Information: Inform users that the data they entered is invalid, without preventing them from entering it. This type of error alert is the most flexible. When an Information alert message appears, users can click OK to accept the invalid value or Cancel to reject it. " So there's no case when invalid entry is silently accepted. The most resembling case is 3 but it informs user. Why defining a validity list if a value not on the list is accepted without indication? So no pb for adding the feature but strongly disagree for reverting the patch. Now I know I'm nobody to decide here so I can only say that if it must be reverted, I won't be the guy who'll do this.
(In reply to Julien Nabet from comment #8) > Why defining a validity list if a value not on the list is accepted without > indication? There is no evidence that the “validity list” should be interpreted solely as a “permitted list”, there are only habits on this matter. What's not valid in a context is not necessarily forbidden, nor necessarily invalid in another context. e.g.: I have some sheets where validity list are displaying a dropdown with a precise list (with a formula) IF user choose A. In that case, user can choose his an item in the list. But IF user choose B, then he must enter a custom value in the same column. In that case, the validity list's formula is something like that: IF($K2=A;{some_field};"") e.g. 2: I have some sheets where validity list are displaying a dropdown with optional choices, which are valid. But the user might have the need to enter a custom value which is not proposed. For invoicing for example: when I enter a new item in my invoice, depending of the type of the stuff I sold, a list of suggested labels that I might need is displayed. Sometime those labels do the job, sometime they don't and I enter a custom value. We could find a lot of other example that illustrate how much this is practical and powerful. > So no pb for adding the feature but strongly disagree for reverting the > patch. I agree it could be a feature or an enhancement, not a reverse. But in the meantime we have to keep in mind that a lot of workflows are broken. That's life and I'm ok to deal with it by rolling back my LO version, but I guess something has to be done somehow to solve this situation.
(In reply to ornanovitch from comment #9) > (In reply to Julien Nabet from comment #8) > > > Why defining a validity list if a value not on the list is accepted without > > indication? > > There is no evidence that the “validity list” should be interpreted solely > as a “permitted list”, there are only habits on this matter. What's not > valid in a context is not necessarily forbidden, nor necessarily invalid in > another context. > ... In this case, it would be a "suggestion list". Let's quote again Ms doc since it's been used as a ref to revert the patch: "When is data validation useful? Data validation is invaluable when you want to share a workbook with others, and you want the data entered to be accurate and consistent. Among other things, you can use data validation for the following: Restrict entries to predefined items in a list— For example, you can limit a user’s department selections to Accounting, Payroll, HR, to name a few. Restrict numbers outside a specified range— For example, you can specify a maximum percentage input for an employee’s annual merit increase, let’s say 3%, or only allow a whole number between 1 and 100. Restrict dates outside a certain time frame— For example, in an employee time off request, you can prevent someone from selecting a date before today’s date. Restrict times outside a certain time frame— For example, you can specify meeting scheduling between 8:00 AM and 5:00 PM. Limit the number of text characters— For example, you can limit the allowed text in a cell to 10 or fewer characters. Validate data based on formulas or values in other cells— For example, you can use data validation to set a maximum limit for commissions and bonuses based on the overall projected payroll value. If users enter more than the limit amount, they see an error message." So it's just about restriction, not suggestion. I know that there's a lot things in life that can be interpreted differently but sometimes but I don't think it's the case here. Anyway, I'll stop here since it seems I won't convince you as you'll won't convince me. Feel free to revert the patch. uncc myself.
(In reply to Julien Nabet from comment #8) > https://support.microsoft.com/en-us/office/more-on-data-validation-f38dee73- > 9900-4ca6-9301-8a5f6e1f0c4c) > > So there's no case when invalid entry is silently accepted. > The most resembling case is 3 but it informs user. > > Why defining a validity list if a value not on the list is accepted without > indication? Let me clarify then. * The error messages and the consequent respective behavior are there **when you ENABLE the error alert** checkbox. * The patch in tdf#159595 is about the error alert being DISABLED.
(In reply to ady from comment #11) Since you sent me an email in private to show me your response, let's just respond to it. > ... > Let me clarify then. > > * The error messages and the consequent respective behavior are there **when > you ENABLE the error alert** checkbox. No, the checkbox is just labeled: "Show error message when invalid values are entered" not "Show error message when invalid values are entered or don't show error and disable validity list". So you'd like a separate checkbox to disable validity list and that's ok for me but disabling error message shouldn't mean disabling validity list. > > * The patch in tdf#159595 is about the error alert being DISABLED. Yes
(In reply to Julien Nabet from comment #12) > No, the checkbox is just labeled: > "Show error message when invalid values are entered" > not > "Show error message when invalid values are entered or don't show error and > disable validity list". We can agree that the checkbox label might not be completely accurate regarding the resulting behavior. That might be the reason for the whole "is it a bug or not" question (and the origin for tdf#159595, which could had been avoided). The matter of the label and the adequate Help content could be solved as a consequence of this report too (see my comment 7), but the main point is to make this less traumatic and more compatible. > > So you'd like a separate checkbox to disable validity list and that's ok for > me > but disabling error message shouldn't mean disabling validity list. With the Error message checkbox disabled, it has been like that for 3+ decades, and also in Excel (according to other posts in this report). The simpler and pragmatical solution – for users, please remember us – is to adapt the label and the Help content (and/or tooltip), instead of breaking prior behavior just because of a language inaccuracy. > > > > > * The patch in tdf#159595 is about the error alert being DISABLED. > Yes With the Error message checkbox enabled, we have 3 possible alternative status/behavior, depending on the type of error. We get the forth status/behavior by disabling the checkbox. Adding a separate checkbox in order to obtain the same alternative as we already had before the very recent patch in tdf#159595 seems not wise. We are forcing users to an unnecessary change in their spreadsheets, plus the potential import/export incompatibility, plus users having to change their already-known workflow from other spreadsheet tools (learned years ago and still valid in other tools) in order to adapt it to something else – tdf#159595 is only1 month old ATM. What for? For a language inaccuracy and the need for Help content improvement??? @Heiko, Please re-consider this. This is not a hard one.
(In reply to ady from comment #13) > Please re-consider this. This is not a hard one. I also strongly advice against reverting the patch. Run myself into a scenario where two choices are accepted and everything else should be rejected (silently to not interrupt the workflow). It is ridiculous to copy even the bugs from Microsoft, and I recommended to use the selection list for the "suggestion dropdown" (comment 6).
(In reply to Heiko Tietze from comment #14) > (In reply to ady from comment #13) > Run myself into a > scenario where two choices are accepted and everything else should be > rejected (silently to not interrupt the workflow). It is ridiculous to copy > even the bugs from Microsoft, and I recommended to use the selection list > for the "suggestion dropdown" (comment 6). The only reason to call the prior behavior a bug is a language inaccuracy and lack of better Help content. Now that you know what the checkbox was supposed to be labeled and how it used to work, are you saying that you cannot get to the your desired result at all (using a version before tdf#159595)? If you think that the Error message setting was not working as you expected, think about users suddenly discovering that their prior workflow doesn't work anymore, without having a clue about why, nor what to do to obtain the same result as before.
(In reply to Heiko Tietze from comment #6) > ...we may add a compatibility option Since apparently more people abuse the bug for a "suggestion dropdown" I suggest the compatibility flag again.
(In reply to Heiko Tietze from comment #16) > (In reply to Heiko Tietze from comment #6) > > ...we may add a compatibility option > Since apparently more people abuse the bug for a "suggestion dropdown" I > suggest the compatibility flag again. There seems to be some misunderstanding about the Data Validity feature. The only case when the behavior is to utterly _reject_ (IOW, "do not allow") a value that does not match the validity rule is when the "Error message" checkbox is enabled AND the type of message is "stop". Otherwise... When the "Error message" checkbox is disabled, or when it is enabled while the type of error message is different than "stop", the validity check is indeed (and has always been) a "suggestion". Each type of message (and the "no message" option) gives some "relaxed" variant on the "suggestion" path. The changed introduced in tdf#159595 means that the more-relaxed behavior was eliminated, and instead the complete rejection is now triggered by 2 different statuses. IOW, tdf#159595 reduces the variants and duplicates 1 of the alternative results. This is not an improvement (and the only reason is a language inaccuracy).
*** Bug 160638 has been marked as a duplicate of this bug. ***
*** Bug 160766 has been marked as a duplicate of this bug. ***