Calculated fields on subform grid show different from Query

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 5.2.2.2 Data base is hsqldb 2.3.4 wizzard

STUFF.odb

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

I seem to have trouble adding a zip file.

The zip file is not on the list

Hello,

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?

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

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.

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

@Pumper1,

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.

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 ??

I will make a new embedded db file and upload.

@Pumper1,

Not certain what you did. This seems to be an embedded DB set for HSQLDB v.2.3.4 and will not open with Base. Properties file:

#HSQL Database Engine 2.3.4
#Sun Apr 26 16:08:19 EDT 2020
version=2.3.4
modified=yes
tx_timestamp=0

Hello,

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:

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:

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

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

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.

@Pumper1,

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.