Bug 102506 - Find Bar Ctrl+F (search of Cells in Calc): add option to set search for cell Values instead of Formulas
Summary: Find Bar Ctrl+F (search of Cells in Calc): add option to set search for cell ...
Status: RESOLVED FIXED
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
Version:
(earliest affected)
Inherited From OOo
Hardware: All All
: high enhancement
Assignee: Not Assigned
URL:
Whiteboard: target:7.4.0 target:7.3.0.0.beta2 inR...
Keywords:
: 93911 94664 102615 136518 (view as bug list)
Depends on:
Blocks: Find-Toolbar
  Show dependency treegraph
 
Reported: 2016-09-25 05:37 UTC by mahfiaz
Modified: 2022-03-18 07:09 UTC (History)
15 users (show)

See Also:
Crash report or crash signature:
Regression By:


Attachments
Screenshot (16.93 KB, image/png)
2016-09-25 11:57 UTC, m.a.riosv
Details

Note You need to log in before you can comment on or make changes to this bug.
Description mahfiaz 2016-09-25 05:37:32 UTC
This has been reported several times and always been marked as NOTABUG.
It IS a bug, this used to work differently.

1. Open Calc
2. Enter =2+3 somewhere
3. Search for 5
4. Search key not found

The problem is expanded by the fact that there is no way to change the behavior anymore. (Ctrl+H dialog settings used to affect Ctrl+F box as well, but this was removed for other reasons)
Comment 1 mahfiaz 2016-09-25 05:38:42 UTC
*** Bug 93911 has been marked as a duplicate of this bug. ***
Comment 2 mahfiaz 2016-09-25 06:03:21 UTC
*** Bug 94664 has been marked as a duplicate of this bug. ***
Comment 3 m.a.riosv 2016-09-25 11:57:15 UTC
Created attachment 127622 [details]
Screenshot

With search & replace [Ctrl+H] the option is there under other options.

Version: 5.2.2.2 (x64)
Build ID: 8f96e87c890bf8fa77463cd4b640a2312823f3ad
CPU Threads: 4; OS Version: Windows 6.19; UI Render: GL; 
Locale: es-ES (es_ES); Calc: CL
Comment 4 Cor Nouws 2016-09-25 14:07:46 UTC
In version 3.3.0.4

Ctrl+F doesn't find the value either, unless you select "Search in Values" under the options.
Same as in a daily from 20160924.. (same as 93911..)
Comment 5 mahfiaz 2016-09-25 14:17:10 UTC
Cor, by options you mean Search and replace dialog (Ctrl+H)?
In that case are you able to search for values in Ctrl+F field with recent builds?
Comment 6 brucehohl 2016-09-25 14:46:30 UTC
Since there seems to various opinions regarding this UI choice/convention, here are the thoughts of an accountant who has used various spreadsheets nearly everyday for 20+ years:

(1) I find it unintuitive that neither Ctrl+F nor Ctrl+H search Values BY DEFAULT because nearly all of my searches are performed to find calculated values (as opposed to characters within Formulas).  If fact, generally I only search Formulas when performing a Search & Replace to update formulas.

(2) Neither search feature includes a single click way to set search for Values. Ctrl+F does not include a Values check box.  For Ctrl+H the Values selection is placed under 'Other options' in a selection list requiring 3 more clicks.

(3) In LO 5.2.1.2 Ctrl+H > Other Options > Similarity search setting is retained after LO is closed/reopened yet any other setting under 'Other options' is not retained. It might be nice if all choices were retained after close/reopen to keep 'Search in = Values' ... and perhaps if Ctrl+F honored this setting but a checkbox per (2) above would be more clear.
Comment 7 mahfiaz 2016-09-25 14:54:37 UTC
bruce, (3b) search and replace settings are also discarded when you do a search using Ctrl+F search box.
Comment 8 Cor Nouws 2016-09-25 19:03:16 UTC
(In reply to mahfiaz from comment #5)
> Cor, by options you mean Search and replace dialog (Ctrl+H)?
> In that case are you able to search for values in Ctrl+F field with recent
> builds?

Ah, indeed, that option does not retain for search in the search bar. Messy..
Reopening.

(In reply to brucehohl from comment #6)

> (1) I find it unintuitive that neither Ctrl+F nor Ctrl+H search Values BY
> DEFAULT because nearly all of my searches are performed to find calculated
> values (as opposed to characters within Formulas).  If fact, generally I
> only search Formulas when performing a Search & Replace to update formulas.
> (2) Neither search feature includes a single click way to set search for
> Values. Ctrl+F does not include a Values check box.  For Ctrl+H the Values
> selection is placed under 'Other options' in a selection list requiring 3
> more clicks.

Could you please open a separate issue for (1) Bruce?
Version " Inherited from OOo"  and libreoffice-ux-advice@lists.freedesktop.org in cc?
And add (2) as argumentation?

> (3) In LO 5.2.1.2 Ctrl+H > Other Options > Similarity search setting is
> retained after LO is closed/reopened yet any other setting under 'Other
> options' is not retained. It might be nice if all choices were retained
> after close/reopen to keep 'Search in = Values' ... and perhaps if Ctrl+F
> honored this setting but a checkbox per (2) above would be more clear.

I opened bug 98544 some time ago: "Find & Replace dialog should remember the 'other options' state". But that is only one part of the discussion, not the relation with the Find toolbar. One could also say: options from dialog should not be used by default, and add check to toolbar to use the options as set in the dialog..
Comment 9 Cor Nouws 2016-09-25 19:18:46 UTC
(In reply to Cor Nouws from comment #8)

> Could you please open a separate issue for (1) Bruce?
> Version " Inherited from OOo"  and
> libreoffice-ux-advice@lists.freedesktop.org in cc?
> And add (2) as argumentation?

libreoffice-ux-advise@lists.freedesktop.org (with an s..)
Comment 10 V Stuart Foote 2016-09-25 22:08:45 UTC
As ruesult of commit for bug 62601 - Quick search (ctrl+f) affected by invisible option from the search-and-replace dialog 

http://cgit.freedesktop.org/libreoffice/core/commit/?id=df9e6ab514ca1c97c7eb69e169c958619c03d429

The Find Bar (<Ctrl>+F) TextFind widget will only "search-in" Formula and textual values by default.

That commit was correct--but a strong use case can be made for needing a default search that includes calculated cell values in addition to formula values.

Request enhancement adding an  Expert configuration option to toggle the Find Bar default "search-in" to include Formula and textual values, and the calculated values for cells--i.e. the Other options: Search in -> Values of the Find & Replace (<Ctrl>+H) dialog.
Comment 11 Gunter Ohrner 2017-05-21 09:59:56 UTC
(In reply to V Stuart Foote from comment #10)
> That commit was correct--but a strong use case can be made for needing a
> default search that includes calculated cell values in addition to formula
> values.
> 
> Request enhancement adding an  Expert configuration option to toggle the
> Find Bar default "search-in" to include Formula and textual values, and the
> calculated values for cells--i.e. the Other options: Search in -> Values of
> the Find & Replace (<Ctrl>+H) dialog.

Actually, I'd strongly assume that especially novice users would expect the search to work on the data one sees, and not (only) on the data one does not see.

I see a point in searching for both values and formulas, but I've a hard time imagining a usecase where a default search for formulas only makes sense.

At least for my use cases, I'm always searching for parts of displayed cell contents, in most cases concatenated or on other ways transformed strings.

Without being able to check, I also have the impression that earlier versions of Libre/Open/StarOffice Calc had a different (more sensible) default search behaviour.

So maybe an expert setting the other way around would make sense - making searching values AND formulas the default, and implement the suggested expert setting here to be able to change the default.

BTW, replacing should be disabled if the search is set to "Values" - I just tested this and it currently silently replaces the whole formular by a static copy of the computed value, on which then the replacement is performed. This causes data loss and should not be allowed in my opinion.

Even worse, in this case even "Undo" does NOT help any more, as it simply restores the original text, but still as a static copy - the formula is still lost and apparently this cannot be undone! :-/ (LibreOffice 5.1.6.2 from Ubuntu)
Comment 12 V Stuart Foote 2017-09-11 18:03:39 UTC
*** Bug 112271 has been marked as a duplicate of this bug. ***
Comment 13 lvm 2017-09-12 08:51:53 UTC
Bug 112271 is about two counterintuitive default values in F&R 'Other options' and only one - searching in formulas instead of values, is discussed here. The other one - searching by rows instead of by columns, is not mentioned yet. Should we expand the scope of this bug to include rows-columns or keep it separate?
Comment 14 Cor Nouws 2020-01-16 13:56:18 UTC
The more I think/read about it, the more I get convinced that we should simply stop discussing more options for the quick Find toolbar.
That should only do a quick find, without additional thinking required. So adding more choices, obfuscates the meaning, breaks the use.

However.. there may be specific exceptions, that we want to prevent people having to hit Ctrl+H .. ?
Comment 15 Cor Nouws 2020-01-17 08:29:59 UTC
(In reply to Cor Nouws from comment #14)
Well, sorry for that comment. Too generic for this case.

This issue is related, but there is more.
Using the find bar, you don't find content that you can see, because there is a setting (Ctrl+H under options) that you don't see in the search bar, nor have influence on, that setting does affect how Ctrl+F works.
There is a risk that users are not aware, and make mistakes.

But then, how to solve this?
- an option in the find bar :\
- remember settings from the search & replace dialog
- an (expert) setting that one has to know?
Comment 16 Cor Nouws 2020-01-17 08:35:39 UTC
I think bug 115665 hasn't been mentioned here before
Comment 17 V Stuart Foote 2020-09-07 14:56:25 UTC
*** Bug 136518 has been marked as a duplicate of this bug. ***
Comment 18 reikenberg 2020-09-07 18:54:33 UTC Comment hidden (abusive)
Comment 19 Dan Dascalescu 2021-11-24 12:53:27 UTC

*** This bug has been marked as a duplicate of bug 102615 ***
Comment 20 Timur 2021-11-24 14:18:40 UTC
*** Bug 102615 has been marked as a duplicate of this bug. ***
Comment 21 Julien Nabet 2021-12-04 22:10:29 UTC
This patch:
diff --git a/svl/source/items/srchitem.cxx b/svl/source/items/srchitem.cxx
index 24a46e28bd0b..f26898b6dde5 100644
--- a/svl/source/items/srchitem.cxx
+++ b/svl/source/items/srchitem.cxx
@@ -110,7 +110,7 @@ SvxSearchItem::SvxSearchItem( const sal_uInt16 nId ) :
                         SearchAlgorithms2::ABSOLUTE, '\\' ),
     m_eFamily         ( SfxStyleFamily::Para ),
     m_nCommand        ( SvxSearchCmd::FIND ),
-    m_nCellType       ( SvxSearchCellType::FORMULA ),
+    m_nCellType       ( SvxSearchCellType::VALUE ),
     m_nAppFlag        ( SvxSearchApp::WRITER ),
     m_bRowDirection   ( true ),
     m_bAllTables      ( false ),

allows to set search by value by default.

But if you change it in Find/Replace, it'll be reset when using Ctrl-F.

Remark: just typing Ctrl-F doesn't reset search options of Find/Replace, it'll reset only when executing a search.

Heiko/Xisco: any opinion about changing the by default behaviour here? (searching in values instead searching in formulas)
Comment 22 Heiko Tietze 2021-12-06 09:29:12 UTC
(In reply to Julien Nabet from comment #21)
> But if you change it in Find/Replace, it'll be reset when using Ctrl-F.

Don't get this but in general I think searching for values is more common. Plus, the combo box shows Find and has the tooltip Find Text, which is not "Search in formulas". (Would be good to be a bit more precise and verbose in the tooltip.)

And btw, I disagree with more options in the quick find bar. So no choice whether values or formula. But there are voices who ask for it...
Comment 23 lvm 2021-12-06 09:38:48 UTC
(In reply to Julien Nabet from comment #21)
> But if you change it in Find/Replace, it'll be reset when using Ctrl-F.
> 
> Remark: just typing Ctrl-F doesn't reset search options of Find/Replace,
> it'll reset only when executing a search.
> 

There is https://bugs.documentfoundation.org/show_bug.cgi?id=112270 just about that: find affecting f&r options. They should be decoupled.
Comment 24 Julien Nabet 2021-12-07 19:53:52 UTC
(In reply to Heiko Tietze from comment #22)
> (In reply to Julien Nabet from comment #21)
> > But if you change it in Find/Replace, it'll be reset when using Ctrl-F.
> 
> Don't get this but in general I think searching for values is more common.
> Plus, the combo box shows Find and has the tooltip Find Text, which is not
> "Search in formulas". (Would be good to be a bit more precise and verbose in
> the tooltip.)
...

Thank you for your feedback.
Here's the patch for making searching by value by default:
https://gerrit.libreoffice.org/c/core/+/126490
If we searching by value by default now, "Find Text" is less clumsy.

Eike: as you may have seen, I put you in cc since you might have opinion here as Calc expert.
Comment 25 Commit Notification 2021-12-10 11:23:39 UTC
Julien Nabet committed a patch related to this issue.
It has been pushed to "master":

https://git.libreoffice.org/core/commit/b60bc1e597032e598caf43d1a13409068b800f57

Related tdf#102506: make Find Bar Ctrl+F searching by value by default

It will be available in 7.4.0.

The patch should be included in the daily builds available at
https://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More
information about daily builds can be found at:
https://wiki.documentfoundation.org/Testing_Daily_Builds

Affected users are encouraged to test the fix and report feedback.
Comment 26 Commit Notification 2021-12-10 18:11:09 UTC
Julien Nabet committed a patch related to this issue.
It has been pushed to "libreoffice-7-3":

https://git.libreoffice.org/core/commit/aec427fd124e73298b46f7b0423fa90f8e024f4b

Related tdf#102506: make Find Bar Ctrl+F searching by value by default

It will be available in 7.3.0.0.beta2.

The patch should be included in the daily builds available at
https://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More
information about daily builds can be found at:
https://wiki.documentfoundation.org/Testing_Daily_Builds

Affected users are encouraged to test the fix and report feedback.
Comment 27 Commit Notification 2021-12-10 22:01:14 UTC
Xisco Fauli committed a patch related to this issue.
It has been pushed to "master":

https://git.libreoffice.org/core/commit/b0e0c15f45a5667814b31ad5f792caf1a0a91308

tdf#102506: sc: Add UItest

It will be available in 7.4.0.

The patch should be included in the daily builds available at
https://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More
information about daily builds can be found at:
https://wiki.documentfoundation.org/Testing_Daily_Builds

Affected users are encouraged to test the fix and report feedback.
Comment 28 Heiko Tietze 2021-12-16 15:10:47 UTC
Added this to the 7.3 release notes.
Comment 29 stragu 2022-01-01 12:49:06 UTC
I guess this is half "fixed" (search values by default) and half "won't fix" (option not added to the toolbar). But marking as fixed as now searching by values is the default in the toolbar, which fixes the main painpoint.

Reviewing 7.3 release notes.

Verified in:

Version: 7.3.0.1 / LibreOffice Community
Build ID: 840fe2f57ae5ad80d62bfa6e25550cb10ddabd1d
CPU threads: 4; OS: Linux 5.4; UI render: default; VCL: gtk3
Locale: en-AU (en_AU.UTF-8); UI: en-US
Calc: threaded
Comment 30 Toby Bartels 2022-03-18 05:01:54 UTC
Well, of course this doesn't really fix anything; it exchanges one default that works for some people but not for others, for another default that works for some people but not for others.  Of course, you've heard from a lot of people that prefer the new default, because those of us who preferred the old way didn't think that there was a bug and didn't come looking until one day recently when Calc just broke for us, and I seem to be the first of that group to track down this report and make a comment.

The desired resolution of this bug report was for an option, and I wouldn't object to switching the default if there really was an option to switch it back.  We only have anecdotal evidence that more people prefer the new way; still, now that I think of it, that's what I would have guessed.  But as the previous comment says, getting an option is WONTFIX, and I guess that the report can only have one status.

Now, I just created this account to make the comment, and I seem to have the power to reopen the bug by editing the status here, but I'm not sure if I really have the right to do that.  I suppose that I should really make a new bug, which I can start by copying mahfiaz's first post on this bug, that it IS a bug because it used to work differently.  And I can open a bug for how using Ctrl-F resets the default for Ctrl-H, so that I not only have to retrain my fingers to use Ctrl-H instead of Ctrl-F, I also have to adjust the settings on Ctrl-H again every time my fingers forget.  And I *actually* have to train my fingers to hit an arrow key before Ctrl-H, in case some cells are selected, since Ctrl-H defaults to searching only in the current selection if there is one, so I'll need to open a bug about how that setting is never remembered.

But I'll make this comment first, since I don't really know my way around here yet.  Commenting is easy; searching for duplicates and writing a good initial report is hard.
Comment 31 Toby Bartels 2022-03-18 06:17:43 UTC
It looks the second of the three bug reports that I should write (see my previous comment) has already been written; it's Bug 112270.

I couldn't find the third one, but strangely there's Bug 129781, which *asks* for what I consider to be buggy behaviour and is marked as WONTFIX; apparently somebody decided to fix it (or a duplicate of it) anyway!  (Although again, what that report actually asked for was something that would have had more options and which I would have been happy to use, but that's not what happened.  Also, it's not clear if the original reporter wanted Ctrl-F or Ctrl-H to change, so maybe Ctrl-H did this all along and the reporter was really only trying to get Ctrl-F to change?)

Anyway, I reported the third one, as Bug 148062.

I'm still figuring out how I want to write the first one (the main one).
Comment 32 Timur 2022-03-18 07:09:04 UTC
(In reply to Toby Bartels from comment #30)
> Well, of course this doesn't really fix anything; it exchanges one default
> that works for some people but not for others, for another default that
> works for some people but not for others.  Of course, you've heard from a
> lot of people that prefer the new default, because those of us who preferred
> the old way didn't think that there was a bug and didn't come looking until
> one day recently when Calc just broke for us, and I seem to be the first of
> that group to track down this report and make a comment.

Exactly. Every once in a while new default for loud minority which makes majority confused and some dissatisfied. And title was clear: "add option".