Created attachment 62972 [details]
external file that hold values to test

Created attachment 62973 [details]
test file that use the other file to important values

Oliver, I'm not sure if the reference used within the formula + cell reference
is correct. Could you please check the integrity. I've been told by Joost to ask
you.
reassigned to you

.

Perhaps it is the same root cause as issue 101645 ?

Yes it looks like the issue you mention, but at least, i'm sure of one thing:
the way i use is legit and there's no controversy if i need ("A1") or (A1) ref...

Because the way i create it, i simply hit function, then choose BDSOMME, then
when oocalc ask for ref i open the other file, and select my database, oocalc
put the ref (the file:///blahblah.ods...) itself.

Didn't really look at the doc to see if it's the way i should have done it, but
i suppose oocalc won't (shoudn't) allow me to do something bad itself. Some kind
of "hey don't do that with another file!" error message.

To be clear : it's a major issue for me, because i use an dbase.ods file that
hold datas that are use as database by 4 others .ods
It's an easy way to update the datas without having to change the 4 others .ods
files, once update i just have to spread the dbase.ods and all files are aware
of the changes.

i didn't tried it yet, but i guess that not only BDSOMME could be affect, and
the main reason is shared with the bug you mention about INDIRECT. Something
goes wrong with external files.

If this issue has really the same root cause as issue 101645, then it will fixed
for OOo 3.1.1 because issue 101645 is a release blocker.

Regards.

And thank you !

Someone pointed me out in forum to that issue, so i wish to add it here because
i do think the root problem is also shared with that one (mark the 3 duplicates?)
Anyway that might help you to know it too (at least my issue share the 504error
with that one)
issue: 102388 http://fr.openoffice.org/issues/show_bug.cgi?id=102388

i also need to say that i made a mistake, it work ok with 3.0.1 and 3.0 (i don't
know where i do a mistake when i test it with 3.0.1).

This is a duplicate to #i102388#

*** This issue has been marked as a duplicate of 102388 ***

closed bvecause duplicate

Reopening because fix would be different from issue 102388 and too invasive for
OOo3.1.1

does the 3.x means that probability of a fix even for 3.2 is low ? :(

@krinn: it means that I don't know who would commit himself to fix it in which
version. So, if someone jumps in and says "yes, I'll do that for OOo3.2" then
we'll probably have a fix for OOo3.2; if not, I'm not sure I could do it in the
time frame for OOo3.2 because already too many things are piling up in front of me.

let me grab this for the time being.  I guess no one else is wiling to work on this.

Thank you for your effort kohei, i was thinking (except by lack of time as er)
more persons would like to work on that issue, am i really the only one to think
it's a serious flaw ?
Without querying a tutorial on how to do things, is there another way to achieve
my task without using an external file as i do ? (that could explain why no one
else seems to think it's a big issue)

one thing you can try is to fetch the external values into cells first, then
reference those cells from DSUM etc.  That might work as a workaround until this
issue gets fixed.

Created attachment 63270 [details]
test files with base import

I feel I'm morally obligated to fix this for 3.2.

it work, i have add dbasetestimport.ods where the datas are import in another sheet.

Also good, if you move the dbasetest.ods away it still refind values where you
update file location in links (as i work under linux and some computers are
using windows, location is always different)

bad thing: the imported values were 24 lines long in the test, i have import
upto 40 lines (as i don't know how long the real database is as size move up and
down depending on update). And the extra lines made the size from dbasetest.ods
jump from 7.4bytes to 9.5bytes. Not an issue with the test file, but proof size
will get real bigger in my real case.

COUNTIF falls under the category of "not working with external references", too.
 A fix for that is forthcoming.

@kohei: hold it ;-)  I already fixed SUMIF and COUNTIF for issue 102388 in CWSs
dr68ooo311 and calc51, both not integrated yet.

*sigh* I wish I'd known that 3 days ago....

Well, we're both on IRC almost each day, so.. talk to me ;-)

Just FYI, fixing this will require a large amount of change in a lot of places.
 I'm working on this ATM in kohei03 cws, for 3.3.

Ok.  I'm almost finished.  I modified and tested the following functions: DSUM,
DCOUNT, DCOUNTA, DGET, DMAX, DMIN, DAVERAGE, DPRODUCT, DSTDEV, DSTDEVP, DVAR and
DVARP, all of which rely on the same type of data structure / iterator pair
internally.  Let me do my last minute cleanup before calling it "done".

Cleanup is done.  Now I'm officially finished with this.

Fixed in kohei03 cws.

Still doesn't work with Office 3.1.1 OOO310m19 (build: 9420)

That version should have the bug fix ? Because it still doesn't work and you can
still use the 3 files i have upload at first to see that the fix doesn't work.

Now it's getting worst as gentoo push out previous openoffice versions i'm left
with that one that doesn't work

It's fixed in 3.3.  Please don't re-open this unless it's broken in 3.3.

re-assigning for qa verification.

Seen ok in cws kohei03 -> verified

ok in DEV300m77

*** Issue 110972 has been marked as a duplicate of this issue. ***

Hello all.

I downloaded:
OOo_3.3.0rc7_20101126_Win_x86_install_en-US.exe

And DGET still doesn't work.

I downloaded the first two files, 

a) changed the value of the search criteria: I placed a 9 in A2

b) Added the next formula:

=DGET('file:///C:/temp/basetest.ods'#$Feuille1.F2:G25;'Test';A1:A2)

I get:
Err:504

Instead of a 45

The DSUM formula works fine as long as I could see.

Ely.

@elysch:
1. For text values you need to use double quotes instead of single quotes
   => "Test" instead of 'Test'.
2. Then the expression returns Err502 because there is more than one entry
   with Month==2. DGET needs unique entries.

Created attachment 75224 [details]
Error in DGET when text is entered near the data range in the remote file

@er

> 1. For text values you need to use double quotes instead of single quotes
>    => "Test" instead of 'Test'.

YEESSSSS... That did the trick in that example.

> 2. Then the expression returns Err502 because there is more than one entry
>    with Month==2. DGET needs unique entries.

Yes, that's why I changed the value to 9.


There is still a problem though. I've been trying to find out the reason for a
few hours by now, without luck.

I have some calc files since before v3.0.1. This particular file still has a
problem with dget formula, and I can't figure it out why.

I'm attaching tree files: DATA-TEST.ods, DATA2-TEST.ods and TEST.ods (in a zip)

The only difference between the first two is cell A3. It has some random text in
DATA2-TEST.ods

If you see the formulas in TEST.ods the formula with reference to DATA2-TEST.ods
returns "Err:504" (Cell C10).

If you simply delete that random text, save the file and reload the TEST.ods
file, the error goes away.

In the other hand, if you add any text anywhere in the range A3:A13 in any one
of the two "DATA" files, then save it and reload the TEST.ods file, the error
appears.

Any ideas about what is going on?

Ely.

wierd... I added text in A30 cell of DATA2-TEST.ods file and removed any other
text in that column. 

DGET Formula still shows "Err:504" in TEST.ods.

I've found a new thing.

The error I mentioned before appears also with the first two attachments of this
issue, using the DSUM formula.

To reproduce, add any random text in any one cell to the left of the remote
(basetest.ods) "database" area, after the titles row (from row 3 on). The DSUM
formula returns "Err:504" too, when you reload bdsommetest.ods.

I even added the random text in A1048576  (The las row) and got the same error

@elysch: please submit a new issue for that problem.

Done: http://www.openoffice.org/issues/show_bug.cgi?id=115906