Update field value based on variable in OnAfterGetRecord

bluesoulcurry

Hello All,

I have a custom table where I have to update a field based on a variable. The variable value is calculated in OnAfterGetRecord trigger of the page.

I have to update the field value in the table based on this variable value. How can I achieve it? I came across one post which said to use CurrPage.SAVERECORD. I tried it but it doesn't work. It gives error "You cannot make any changes in the database until a transaction has been started."

Please suggest me a way to update the field value in the record based on calculated variable and see updated values on the page when the page is run.

Thank you in advance for your replies.

vijay_g

If the record being insert same time when you are updating your field based on some calculation with a variable then you can simply assign value to that field(field := value) but if you are updating any field in a record that has already created then you must need to modify that record after assign your field value.

hope it's clear to you..!!!

kine

You need to rethink the solution. OnAfterGetRecord is not correct trigger to update anything. You need to re-design the solution to not be depending on this trigger. What the value represents? when the data, from which it is calculated, are changed? Can you update the dependant data during this update of source data?

thegunzo

I agree with Kine, you will need to update the record somewhere else in your solution.

bluesoulcurry

Hello Guys,

Thanks for your replies.

vijay_g wrote:

If the record being insert same time when you are updating your field based on some calculation with a variable then you can simply assign value to that field(field := value) but if you are updating any field in a record that has already created then you must need to modify that record after assign your field value.

hope it's clear to you..!!!

The problem is even if I assign the value, I cannot modify the record. Because, there isn't any trigger between OnAfterGetRecord and when the page loads, where I can write the MODIFY logic.

kine wrote:

You need to rethink the solution. OnAfterGetRecord is not correct trigger to update anything. You need to re-design the solution to not be depending on this trigger. What the value represents? when the data, from which it is calculated, are changed? Can you update the dependant data during this update of source data?

I agree with you kine. Can you please help me with an example of how this can be implemented.

I have following columns in my table:

No.===Cost===Price===Profit%
01====200===300====TOBECALCULATED

Here, Profit% = =((Price-Cost)/Cost) x 100.

The table already has Cost and Price values, but not Profit %.

What I am currently doing is calculate the Profit% in the OnAfterGetRecord trigger of the page by calling function in the table by passing the values of Cost and Profit. In the function, I am setting the Value of the Profit% field. Now, what I need to do is update the current values to the table, so that when the page loads I get updated Profit% value.

Can you please suggest an alternate way to implement this scenario. Please note that I cannot use OnValidate triggers of Cost or Price fields.

Thanks in advance.

thegunzo wrote:

I agree with Kine, you will need to update the record somewhere else in your solution.

Thank you for your reply.

kine

You need to calcualte the value in code on table, e.g. in OnValidate of each field (Price, Cost). For existing records you need to run one-time batch which will fill the profit for you.

Remember - there is good rule - NO BUSINESS LOGIC on forms/pages.

bluesoulcurry

kine wrote:

You need to calcualte the value in code on table, e.g. in OnValidate of each field (Price, Cost). For existing records you need to run one-time batch which will fill the profit for you.

Remember - there is good rule - NO BUSINESS LOGIC on forms/pages.

Thanks for your reply Kine...I got your point...Will keep it in mind always...

bluesoulcurry

kine wrote:

You need to calcualte the value in code on table, e.g. in OnValidate of each field (Price, Cost). For existing records you need to run one-time batch which will fill the profit for you.

Remember - there is good rule - NO BUSINESS LOGIC on forms/pages.

Hello Kine,

The above solution works fine for the table where the values have to be calculated from the same table. But when I have to update values of fields based on another table, it doesn't work.

For example, I have to use the respective Profit% field of Table1, make some calculations and show the calculated value on another page which has a different Source Table as Table2. In this case, where do I do calculation? The calculation has to be dynamic and independent of Table1.

Table1
No.===Cost===Price===Profit%
01====100===150===50%
02====100===170===70%

Table2
No.===Amount===ProfitValue
01====10000====5000
02====5000=====3500

Here ProfitValue = Amount * Profit%.

User can modify Profit% values in Table1 through Page1. Now, when the Page2 loads which has SourceTable as Table2, I have to recalculate ProfitValue based on the modified data in Table1.

But the restriction is, I cannot use OnValidate trigger of any field of Table1 to update values in Table2. The values of Table2 has to be calculated dynamically before the Page2 loads, and same has to be stored/updated in Table2.

Can you please suggest how can I implement this scenario?

Thank you for the responses.

DenSter

kine wrote:

NO BUSINESS LOGIC on forms/pages

Repeat after me.....
NO BUSINESS LOGIC on forms/pages
NO BUSINESS LOGIC on forms/pages
NO BUSINESS LOGIC on forms/pages
NO BUSINESS LOGIC on forms/pages
NO BUSINESS LOGIC on forms/pages
NO BUSINESS LOGIC on forms/pages
NO BUSINESS LOGIC on forms/pages

Get the point?

Chinmoy

Any specific reason for saying this:

"But the restriction is, I cannot use OnValidate trigger of any field of Table1 to update values in Table2. The values of Table2 has to be calculated dynamically before the Page2 loads, and same has to be stored/updated in Table2."

Why not write a code in the OnValidate of Profit% field in Table1 to update the ProfitValue field on the related records in Table2?

Chn

bluesoulcurry

Chinmoy wrote:

Any specific reason for saying this:

"But the restriction is, I cannot use OnValidate trigger of any field of Table1 to update values in Table2. The values of Table2 has to be calculated dynamically before the Page2 loads, and same has to be stored/updated in Table2."

Why not write a code in the OnValidate of Profit% field in Table1 to update the ProfitValue field on the related records in Table2?

Chn

Hello Chinmoy,

The reason is, the tables that I have given are only an example. The calculation that I have to do is really complex. Lets say that I have to update the ProfitValue of Table2 based on the posted invoices. Now, I can't write code on the "Sales Invoice Line" table's OnValidate trigger for any field. Because this table gets updated when Invoices are posted. So, that part of the routine cannot be touched.

Hence, I have to do dynamic calculations before loading Page2.
Thank you for your reply.

mohana_cse06

You can Open the page first and Create a Function to Update the values..

Chinmoy

Hi!

I understand that probably the only place you can think of writing the code is before opening the page/form (due to the situation). However, I will still go with others, by asking you to find out an event which happens in the database to write a code that updates values of fields inside a table. As you quoted for example the posting of a sales invoice, in such a case my opinion would be to write the code somewhere inside the posting process. The bottom line still remains "no business logic in the forms/pages".

However, I think you are the best person to decide.

Chn

kine

All depends on situation - do you really need to save the result into table? Why not just to have function returning the value, which will be displayed in column on page and recalculated each time it is displayed? If there is some change which have effect to other data, the data should be updated when this record is changed.

In your case, when someone changes the settings, the data will be INVALID until someone opens the page. This is not acceptable! Data must be consistent in any point in time. You have few solutions:

1) Calculating the value on the fly without saving it into table
2) Change the result after the input conditions changes (settings or data).
3) Create batch job, which will periodically update the results based on the settings and data (there is still time when data are inconsistent) and will be manually or automatically triggered (something like Adjust Cost batch etc.)

sylvainroy

I know it has been on long time since this post, but I had a similar request and thought I'd share my solution.

The request was to have the user prompted at the opening of a page to change the Posting Date to the WORKDATE and update the record if CONFIRMED. Obviously I could not do that on the OnAfterGetRecord...

I used the pingpong addin (details available from Gunar here) on the Purchase Invoice page.

You can leave the OnAddinReady as in the previous example, that is:

CurrPage.PingPong.Ping(2500);

Then on the Ping event:

IF "Posting Date" <> WORKDATE THEN
  IF CONFIRM(STRSUBSTNO(Text50000, WORKDATE)) THEN BEGIN
    SavedDocDate := "Document Date";
    SetHideValidationDialog(TRUE);  //rec is Purchase Header
    VALIDATE("Posting Date", WORKDATE);
    VALIDATE("Document Date", SavedDocDate);
    MODIFY(TRUE);
    CurrPage.UPDATE(FALSE);
    SetHideValidationDialog(FALSE);
  END;

And finally, you also need the

CurrPage.PingPong.Ping(2500);

in the OnAfterGetRecord in order to retrigger the pingpong addin in case of a record change.

I think it does the job pretty neatly, and it does not annoy the user if she stays less than 2,5 seonds on the record

Hope this can help!

Syl