Ask Your Question

Calculated fields on subform grid show different from Query

asked 2020-04-26 20:31:45 +0200

Pumper gravatar image

updated 2020-04-27 02:02:10 +0200

I have a form with 1 subform. The subform has a grid with 2 calculated fields. The calculation is correct in the SQL query, but most of the calculations show on the grid as way off. If any one could look at my example and detect an error, I would appreciate it.

Debian 9 LibreOffice Data base is hsqldb 2.3.4 wizzardC:\fakepath\STUFF.odb

edit retag flag offensive close merge delete


You can upload your sample file here. To do this, edit your initial question and click the paper clip icon (attachment).

ebot gravatar imageebot ( 2020-04-26 20:35:22 +0200 )edit

I seem to have trouble adding a zip file.

Pumper gravatar imagePumper ( 2020-04-26 20:39:45 +0200 )edit

The zip file is not on the list

Pumper gravatar imagePumper ( 2020-04-26 20:43:11 +0200 )edit


Just change the extension from .zip to .odb and note that in your edited post.

Also, is there a reason you are using such an old version of LO?

Ratslinger gravatar imageRatslinger ( 2020-04-26 20:45:38 +0200 )edit

I clicked on the paperclip, clicked on my zip file, it showed up and i clicked ok. But zip was not an accepted file. or maybe it just didn't get there yet

Pumper gravatar imagePumper ( 2020-04-26 20:49:02 +0200 )edit

zip is not allowed. Change this to something acceptable like odb and when we download to review, as you will note this change, we will change it back to zip so it will work properly. See my previous comment. This is commonly done for this situation.

Ratslinger gravatar imageRatslinger ( 2020-04-26 20:51:57 +0200 )edit

There is a reason, Sometimes the db doesn't work the same after an upgrade. I will try the extension Change

Pumper gravatar imagePumper ( 2020-04-26 20:54:43 +0200 )edit


Have worked with split since LO v4.3.x and now in the 6.x series. Yes, there is an occasional problem but have not held upgrades for that many versions. Your decision. Just didn't understand.

Ratslinger gravatar imageRatslinger ( 2020-04-26 21:05:03 +0200 )edit

I can't seem to get the zip file small enough. The .odb 1,5 m and when I zip it, it is still 1.5m ??

Pumper gravatar imagePumper ( 2020-04-26 21:34:13 +0200 )edit

I will make a new embedded db file and upload.

Pumper gravatar imagePumper ( 2020-04-26 21:47:26 +0200 )edit

1 Answer

Sort by » oldest newest most voted

answered 2020-04-27 00:42:15 +0200

Ratslinger gravatar image


It took some effort but do have an answer.

First note the posted .odb was not in proper format. Had to break it down and create a split DB from the internals. then copy the form over.

Took a while just to figure out what you were doing. Think there may be further problems with the design but put the effort into solving your problem.

Here is your SQL for the grid control:

image description

Please note the links from table to table. IT in "SOLD" to "ITEMS" is OK. But IT from "ITEMS" to CU in "CUSTOMER" makes no sense at all. In fact you are missing an entire table here for linking:

image description

By adding in the "INVOICE" table and correcting the links, the calculations are correct. You were using the incorrect discounts with the incorrect linking.

edit flag offensive delete link more


Ratslinger, that was great, I thought it would never work. I tried various combinations on the query grid, but not the right one. Thanks very much. Pumper

Pumper gravatar imagePumper ( 2020-04-27 01:58:27 +0200 )edit

Ratslinger The calculations all work, but now I can't enter in the QTY. I had this problem also, would change something and then what worked before doesn't work.

Pumper gravatar imagePumper ( 2020-04-27 02:25:15 +0200 )edit


That grid control is base upon a query where data is arriving from multiple tables. As noted in my answer, I believed there were other design problems. This is part. With a quick glance, this should be part of the entry for adding a item to the invoice. I would only use the grid as a guide to the complete order as it currently stands.

Ratslinger gravatar imageRatslinger ( 2020-04-27 02:35:32 +0200 )edit
Login/Signup to Answer

Question Tools

1 follower


Asked: 2020-04-26 20:31:45 +0200

Seen: 94 times

Last updated: Apr 27 '20