Bug 118561 - External reference not supported as result in the THEN or ELSE paths of an IF jump
Summary: External reference not supported as result in the THEN or ELSE paths of an IF...
Status: RESOLVED FIXED
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
Version:
(earliest affected)
4.1 all versions
Hardware: All All
: medium normal
Assignee: Eike Rathke
URL:
Whiteboard: target:6.2.0 target:6.1.0.2 target:6.0.6
Keywords:
Depends on:
Blocks:
 
Reported: 2018-07-05 20:08 UTC by marcusman
Modified: 2018-07-08 20:38 UTC (History)
5 users (show)

See Also:
Crash report or crash signature:


Attachments
simple testcase - first (main) .ods file (14.88 KB, application/vnd.oasis.opendocument.spreadsheet)
2018-07-05 20:09 UTC, marcusman
Details
simple testcase - second (external) .ods file (15.65 KB, application/vnd.oasis.opendocument.spreadsheet)
2018-07-05 20:09 UTC, marcusman
Details
1st screenshot (1 of 4) (33.23 KB, image/png)
2018-07-05 20:10 UTC, marcusman
Details
2nd screenshot (2 of 4) (34.91 KB, image/png)
2018-07-05 20:10 UTC, marcusman
Details
3rd screenshot (3 of 4) (36.62 KB, image/png)
2018-07-05 20:10 UTC, marcusman
Details
4th screenshot (4 of 4) (42.73 KB, image/png)
2018-07-05 20:11 UTC, marcusman
Details
Sample showing the issue on the IF function (13.92 KB, application/vnd.oasis.opendocument.spreadsheet)
2018-07-05 22:26 UTC, m.a.riosv
Details

Note You need to log in before you can comment on or make changes to this bug.
Description marcusman 2018-07-05 20:08:24 UTC
Description:
It's about doing a lookup in an external .ods file from a main .ods file.
A testcase with 2 simple files is included that demonstrates the problem.
A series of 4 screenshot images gives a quick overview of the problem.

Posted it in other forum first. Person there says it might be a bug. See:
https://ask.libreoffice.org/en/question/159779/how-to-do-this-special-lookup-in-external-file/

Not sure if this is a bug 
-or-
The formula is just wrong
-or-
This mechanism is not possible with LibreOffice

Steps to Reproduce:
1. Store the 2 .ods files in the same directory.
2. Open the main .ods file.
3. Do the things that you see in the screenshot images.
4. (The cell where the problem occurs is marked yellow/orange)

Actual Results:
Lookup in the same file works without problems.
Lookup in the external .ods file doesn't work (this way).

Expected Results:
Lookup in the external file should be possible in a similar fashion.


Reproducible: Always


User Profile Reset: Yes



Additional Info:
Not sure if possible with a slightly different formula.
People in the ask.libreoffice.org say it may be a bug.
See:
https://ask.libreoffice.org/en/question/159779/how-to-do-this-special-lookup-in-external-file/

I have a tandem of 2 .ods files - one that holds a lot of data and one that is the interface.
C:\fakepath\problem_2_main_file.ods
C:\fakepath\problem_2_external_file.ods
Usually I use "Data"->"Validity..." and select "Cell range" and use a formula like the following:
IF($data.$B$1:$B$9999=B6,$data.$C$1:$C$9999)
This formula checks creates a list of things to select from (column "C" in "data") by filtering only those where column "B" matches a certain category.
I try to use a similar formula when looking up in the external .ods file, but this formula fails:

IF('file:///E:/Problem2/problem_2_external_file.ods'#$external_data.B$1:B$9999=B14,'file:///E:/Problem2/problem_2_external_file.ods'#$external_data.C$1:C$9999)

The following screenshot images illustrate the problem.

I will try to do this with Microsoft Office since I'm out of options now - if it works there, then it should(!) work in LibreOffice too.

Maybe the syntax of the formula is wrong? Or perhaps I did not think of something else? One alternative might be to use a real database and a C++ app or something like that. But I want to use two Calc files instead - one that holds the data and one that is the user interface.

My next workaround would be to add the relevant data to the main file - which is a problem. You see, the idea was to separate the 2 files because the database is a big file (10 MegaByte) whereas the other file that uses this data is only 100 KiloByte in size and will be used like a 1000 times to represent a 1000 times the data in different combinations. Thus, I would have 1001 x 10 MB versus 1000 MB + 1000 x 0,1 MB which means 1000 times more disk space used when having the data all in one file.

Also ... I separated the two because if things change in the database then only the database will need an update. The other 1000 files which reference the other .ods file won't need an update. The data .ods is located in a folder above the interface .ods file.
Comment 1 marcusman 2018-07-05 20:09:09 UTC
Created attachment 143330 [details]
simple testcase - first (main) .ods file
Comment 2 marcusman 2018-07-05 20:09:37 UTC
Created attachment 143331 [details]
simple testcase - second (external) .ods file
Comment 3 marcusman 2018-07-05 20:10:06 UTC
Created attachment 143332 [details]
1st screenshot (1 of 4)
Comment 4 marcusman 2018-07-05 20:10:30 UTC
Created attachment 143333 [details]
2nd screenshot (2 of 4)
Comment 5 marcusman 2018-07-05 20:10:51 UTC
Created attachment 143334 [details]
3rd screenshot (3 of 4)
Comment 6 marcusman 2018-07-05 20:11:40 UTC
Created attachment 143336 [details]
4th screenshot (4 of 4)
Comment 7 m.a.riosv 2018-07-05 22:26:31 UTC
Created attachment 143337 [details]
Sample showing the issue on the IF function

issue with array to external file for the IF function on THEN or OTHERWISE
(Using the same linked file)
Comment 8 m.a.riosv 2018-07-05 22:27:06 UTC
Version: 6.2.0.0.alpha0+
Build ID: 67c88e284af74c88e37cc8f66cdfc0e346de45ac
CPU threads: 4; OS: Windows 10.0; UI render: GL; 
TinderBox: Win-x86@42, Branch:master, Time: 2018-06-19_23:41:55
Locale: es-ES (es_ES); Calc: CL
Comment 9 Xisco Faulí 2018-07-06 12:54:19 UTC
Reproduced back to

Version: 4.1.0.0.alpha0+
Build ID: 64dafbe584fe7644ec29b96b6a9a9588ba4619b

@Eike, @Winfried, I thought you could be interested in this issue...
Comment 10 Eike Rathke 2018-07-06 21:06:26 UTC
This boils down to an external reference not being supported in the THEN or ELSE paths of an IF jump if in array/matrix mode. Short reproducer:

=IF('problem_2_external_file.ods'#$external_data.B$1:B$9=B14,'problem_2_external_file.ods'#$external_data.C$1:C$9)

entered in any cell of problem_2_main_file.ods as array formula (closed with Shift+Ctrl+Enter) for the preselected fruits case in B14 displays the first 5 values as Err:502 (which should be the matching 5 fruits) followed by 4 values FALSE (as there are no more matches for fruits in the range).

Taking.
Comment 11 Eike Rathke 2018-07-06 21:22:48 UTC
A workaround is to not have the external reference be the final IF result, introducing an operation such as concatenating with an empty string (...&"") in this case helps:

=IF('problem_2_external_file.ods'#$external_data.B$1:B$9=B14,'problem_2_external_file.ods'#$external_data.C$1:C$9&"")
Comment 12 marcusman 2018-07-06 22:29:57 UTC
(In reply to Eike Rathke from comment #11)
> A workaround is to not have the external reference be the final IF result,
> introducing an operation such as concatenating with an empty string (...&"")
> in this case helps:
> 
> =IF('problem_2_external_file.ods'#$external_data.B$1:B$9=B14,
> 'problem_2_external_file.ods'#$external_data.C$1:C$9&"")

Confirmed, the workaround works for me. Thanks for the idea! :-)
Comment 13 Commit Notification 2018-07-07 22:38:31 UTC
Eike Rathke committed a patch related to this issue.
It has been pushed to "master":

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

Resolves: tdf#118561 handle external references as jump matrix result

It will be available in 6.2.0.

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

Affected users are encouraged to test the fix and report feedback.
Comment 14 Commit Notification 2018-07-08 19:11:00 UTC
Eike Rathke committed a patch related to this issue.
It has been pushed to "libreoffice-6-1":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=57e908136f4b7135e6c4a35a81e45c47f9f1b548&h=libreoffice-6-1

Resolves: tdf#118561 handle external references as jump matrix result

It will be available in 6.1.0.2.

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

Affected users are encouraged to test the fix and report feedback.
Comment 15 Eike Rathke 2018-07-08 19:11:37 UTC
Pending review for 6-0 https://gerrit.libreoffice.org/57148
Comment 16 Commit Notification 2018-07-08 20:38:01 UTC
Eike Rathke committed a patch related to this issue.
It has been pushed to "libreoffice-6-0":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=8f3515b5e54dabb08ddb01253d9503e9c16cfebe&h=libreoffice-6-0

Resolves: tdf#118561 handle external references as jump matrix result

It will be available in 6.0.6.

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

Affected users are encouraged to test the fix and report feedback.