I made a major update on one application and s*it happened.

I have this table "Quotation" that is the header and its related details table "Quotation_rows". The header contains things such customer code et cetera, while details contains - one for a row - the item quoted with its price. The price of the item is in two different fields: the first ("unit_price") is a virtual with a derivation, the second ("unit_price_frozen"), has the same derivation BUT is not virtual, so it's value is recorded and not calculated each time. This because the price depends on some calculation that takes in account raw material costs and they can change almost every day. So, if I made a quote today the unit price can be 10€, but the next week for the same item it can be 12€: this situation will be clearly visible in the database with both prices available.

Currently, the calculation is made in another table ("item_details", linked to the "Quotation_rows" table), and depends only on the code of the item. Now, I made this update and the price calculated in "item_details" depends not just on the code of the item but also on the quantity sold, stored in the "Quotation_rows" table. From a practical point of view, "unit_price" has a lookup linked to the "item_details" table, but the "item_details" table to calculate the price takes some infos from "Quotation_rows" table, using fields with derivation that "lookup" field in "Quotation_rows" table. This creates a circular references and with it bad things happens, mainly unexpected crashes of the apllication.

Someone has suggestion gow to tackle this situation?

Regards,
Gio

Hi

If I understood correctly, you have the following set up:

Quotation ---< Quotation_Lines >---Item_Details

In Quotation lines, you are entering the Item Code, and have the product description and price returned from Item_Details.

The issue is that in Item_details you are basing a calculation based on a value (quantity) in the Quotation_Lines.  I am assuming that Quantity in Quotation_Lines is based on a virtual field with a sum of derivation.

You can solve this by:

Quantity - remove derivation and make it prevent entry and not virtual.

When saving a quotation, add an ExecDQL to update the quantity field in the item_details (thus this is kept up to date without being viru

Move the calculation of the price based on quantity to the Quotation_Lines. Thus you have a field PrevQuantity - Lookup of item_details Quanity and a price calculation field which shall be based on PrevQuantity + Quantity + other calcs.

HTH, 

Josef

Hi Josef,

and thank you for your answer. The solution you suggested is not 100% applicable because there are some constraints (in my explanation I tried to simplify the situation for clarity sake), so I cannot move some fields. But it contains the hint that probably is the solution for my problem: update the related table when I save the record. However I am somehow blocked in the implementation of that solution, because I cannot find a viable way to trigger the ExecDQL when I save the record using f8 key. I tried with OML but it did not work. Could you explain how you woud trigger the ExecDQL when saving the record?

Thanks,

Gio

George

I presume you are not using buttons, but the traditional dataease keys....

You can update the F8 Functionality by:

Click on DOCUMENT > CUSTOM MENU

Go to the F8 Functionality

do as per attached screen shot.

regards

josef

Thanks Josef, I have benn using DataEase for quite a time now, and never used that feature.