| View previous topic :: View next topic |
| Author |
Message |
binnhood
Red Pill
Joined: Jun 12, 2010
Posts: 6
|
 Posted: Mon Jun 14, 2010 8:06 pm Post subject: DSUM error:504 between files. |
|
DSUM(database_table; field; criteria_table)
[NeoOffice 3.1.1 Patch 0]
When database_table is in another file(data.ods), it results in error:504.
When database_table is in the same file(calc.ods), it is OK.
[NeoOffice 3.0.2 Patch 4 (or earlier)]
It is no problem where database_table exists.
Please tell me how to avoid error:504 on NeoOffice 3.1.1 or later. |
|
| Back to top |
|
 |
pluby
The Architect
Joined: Jun 16, 2003
Posts: 11949
|
| Posted: Mon Jun 14, 2010 9:55 pm Post subject: |
|
Unfortunately, I found starting in OpenOffice.org 3.1.1 (the version that NeoOffice 3.1.1 is based on), you can no longer directly type a range of cells in another file in any of the DCOUNT(), DSUM(), etc. functions.
The good news is that you can still do what you want to do. It will just require you to link the data.ods data into your spreadsheet and then put the cell range of the linked data in your DCOUNT() formula.
To do this, use the following steps:
1. Open your data.ods file and select all of the filled cells in Sheet2.
2. Select the Data :: Define Range menu and in the dialog that appears, give the select range a name and press the OK button.
3. Save and close the data.ods file
4. Open your calc.ods file, select the cell where you want the data.ods data to appear, and select the Insert :: Link to External Data menu
5. In the dialog that appears, select the data.ods file and you should see the name of the range that you defined in step 2 above. Select that range and press the OK button.
6. You can now replace the cell range in your DCOUNT() function with the range of linked cells that you added in step 5.
Note that step 5 does not copy the cells from the data.ods file. Instead, it reads the data directly from the data.ods file so if you save any changes in the data.ods file, go to your calc.ods document and select the Edit :: Links menu. In the dialog that appears you can select your data.ods source file and press the Update button to update the linked data.
Do the above steps allow you to get the DCOUNT() function to work?
Patrick |
|
| Back to top |
|
|
pluby
The Architect
Joined: Jun 16, 2003
Posts: 11949
|
| Posted: Mon Jun 14, 2010 10:05 pm Post subject: |
|
In case my steps are not clear, I have attached updated versions of your sample files that you can look at.
In my updated data.ods file, I have defined the a range called "mydata" for the cells in Sheet2.
In my updated calc.ods file, I have inserted a link to the data.ods file's "mydata" range in Sheet3 and then I have updated the DCOUNT() functions to use that linked range.
Hope my updated files help.
Patrick |
|
| Back to top |
|
|
binnhood
Red Pill
Joined: Jun 12, 2010
Posts: 6
|
| Posted: Wed Jun 16, 2010 3:43 am Post subject: Thank you. |
|
Thank you very much. Your updated files worked very well. I'd like to work with my larger files along your advise.
By the way, why these problems came out? The change from 3.0.2 to 3.1.1 about the method of referring files, for me, seems not reasonable. Why has the decision made? |
|
| Back to top |
|
|
pluby
The Architect
Joined: Jun 16, 2003
Posts: 11949
|
| Posted: Wed Jun 16, 2010 4:29 am Post subject: Re: Thank you. |
|
| binnhood wrote: | | By the way, why these problems came out? The change from 3.0.2 to 3.1.1 about the method of referring files, for me, seems not reasonable. Why has the decision made? |
AFAICT, the change is a bug that started appearing in the OpenOffice.org 3.1.1 code. Since NeoOffice is built on Oracle's OpenOffice.org product, NeoOffice inherits all of the feature bugs that Oracle's engineers may add in their releases. In your case, the "database" functions like DCOUNT(), DSUM(), etc. stopped being able to handle external cell range references.
Unfortunately, our very limited donations only supports one full time developer (me) so this limits the current scope of the NeoOffice project to keeping a native version of OpenOffice.org running on Mac OS X and fixing any critical crashing and hanging bugs that our users find.
Because of these limits, fixing this OpenOffice.org bug is likely too big a problem for us to solve so I focussed on finding a workaround for you.
Patrick |
|
| Back to top |
|
|
binnhood
Red Pill
Joined: Jun 12, 2010
Posts: 6
|
| Posted: Wed Jun 16, 2010 5:33 am Post subject: Thank you, again. |
|
Thank you, again. I clearly understood your kind intent.
binnHood |
|
| Back to top |
|
|
binnhood
Red Pill
Joined: Jun 12, 2010
Posts: 6
|
| Posted: Thu Jun 17, 2010 3:56 am Post subject: SUM( ) error on different Sheets. |
|
Help me again, please.
[NeoOffice 3.1.1 Patch 1]
Data are on Sheet1, Sheet2, Sheet3 of Data_Sum.ods.
SUM( ) are on Sheet1, Sheet2, Sheet3 of Calc_Sum.ods.
Data are different from each other.
All results of SUM( ) are same!
What's wrong? |
|
| Back to top |
|
|
pluby
The Architect
Joined: Jun 16, 2003
Posts: 11949
|
| Posted: Thu Jun 17, 2010 8:50 am Post subject: Re: SUM( ) error on different Sheets. |
|
| binnhood wrote: | Data are different from each other.
All results of SUM( ) are same!
What's wrong? |
What is wrong is that you are finding some very broken OpenOffice.org 3.1.1 code. Like the other bug you reported, your SUM() formulas reference a range in another file. I thought the other bug was limited to only the database functions like DCOUNT(), DSUM(), etc. but it is now clear that OpenOffice.org 3.1.1's code cannot handle any formulas that have a range in another file.
For now, the workaround is to use the "create a link to external data" like you did for the other bug.
Since the workaround is a hassle, I will take a look at the OpenOffice.org 3.1.1 code this week to see if I can see if I can identify what is causing this bug. I found that OpenOffice.org 3.2 does not have this bug (it still has the DCOUNT() bug) so my hope is that if I compare sections of the Calc code in both versions, I might find the fix that they made in OpenOffice.org 3.2.
I will post when I have some news to report. However, it will probably take me a few days do search through Oracle's OpenOffice.org code.
Patrick |
|
| Back to top |
|
|
binnhood
Red Pill
Joined: Jun 12, 2010
Posts: 6
|
| Posted: Thu Jun 17, 2010 9:34 am Post subject: Thank you for your review. |
|
Thank you for your opinion and advise.
Your effort will be highly appreciated.
binnHood |
|
| Back to top |
|
|
ovvldc
Captain Naiobi
Joined: Sep 13, 2004
Posts: 2352
Location: Zürich, CH
|
| Posted: Thu Jun 17, 2010 11:11 am Post subject: Re: SUM( ) error on different Sheets. |
|
| pluby wrote: | | What is wrong is that you are finding some very broken OpenOffice.org 3.1.1 code. |
When did they stop doing regression testing?
Best wishes,
Oscar
_________________
"What do you think of Western Civilization?"
"I think it would be a good idea!"
- Mohandas Karamchand Gandhi |
|
| Back to top |
|
|
pluby
The Architect
Joined: Jun 16, 2003
Posts: 11949
|
|
| Back to top |
|
|
binnhood
Red Pill
Joined: Jun 12, 2010
Posts: 6
|
| Posted: Fri Jun 18, 2010 7:05 pm Post subject: Patch-1-Test-1-Intel |
|
I think the bug was fixed for me.
(Mac OS X 10.6.4, Intel)
Thank you.
binnHood |
|
| Back to top |
|
|
pluby
The Architect
Joined: Jun 16, 2003
Posts: 11949
|
| Posted: Mon Aug 09, 2010 1:48 pm Post subject: |
|
FYI. I have included the fix in NeoOffice 3.1.1 Patch 2. The patch can be downloaded from the NeoOffice patch download page.
Patrick |
|
| Back to top |
|
|
|
