Function call and Update

Hi developers,

I have a little problem… I have a simple function that only try to modify the value of a decimal field (for example with previous value 100):

calculateCustomerData (VAR rec18 - Record - Customer)
{
rec18.field := rec18.field + 20;
}

The problem is when I call this function from the trigger afterGetCurrRecord of Form 21 (Customer Card), when the form is displayed the current record has the correct value 120, but if I go to the record on the table the new value has not been updated, it still has 100.

I tried writting a new line in the function (rec18.modify;) but an error is displayed “You cannot make any changes in the database until a transaction has been started”. I tried too with the “commit” function but same error is displayed.

I’m lost, can somebody help me please?

Yes it is interesting that although you can set the value of a field on the record, this does not mark the record as having been modified and the change will not be saved to the table unless you manually modify another field.

I cannot find any way to do this - so any other old-hand experts out there with a few tricks up their sleeves?

I can suggest that maybe you should do this in a different way.

Do you really need the field value to be saved to the database if you are always going to calculate it on the Customer Card?

Maybe you want to use the value in reports and other forms. In that case, you should consider adding a Function to the Customer table that returns the correct calculated value and using the function as the Source Expression on your forms and fields, etc.

Problem is that you cannot make changes in DB from this trigger (as it is said in error message).

If you could explain why you want to do that, maybe we could help to solve the problem.

Cheers,

Viktoras

I’ll try to explain…

  • I have created a field “Risk” on Customer table and its value is: “Balance (LCY)” + “Shipped Not Invoiced (LCY)” + .“Outstanding Orders”

  • I want to block (Blocked::All) the Customer if Risk is greater than “Credit Limit (LCY)”

The idea is not to add a Sales line on an Order if the customer is blocked (Risk > “Credit Limit (LCY)”), I can check the field Blocked and works good, but I want to update the field Risk each time a sales line is added, but here is the problem of the “fucken” MODIFY funtion… I call the function to calculate it but I can’t update the field of the customer.

Any suggestion? :frowning:

No one has had a similar problem?, is just how to change the value of a customer field, when you change something related, like the line of an order, invoice… :slight_smile:

Well I took the suggestion of Raspode, and I calculate the field each time I need it, like using the CALCFIELDS function, but I still think it has to be a better way !!.

Thanks anyway…

Hi Ralletin,

from what you described, it sounds like you should be updating the field on the customer record when you change the data you are using to trigger your business rule.

For example, when you post a shipment or an invoice, recalculate your Risk field and set your blocked status appropriately.

Also if you edit the Credit Limit (LCY) you will also need to re-evaluate the Risk and set the blocked status.

This has to be much better than setting it on the Customer Card when you read a record (because that would mean you could still sell to the customer even though the rsik has exceeded their credit limit because no-one has viewed the customer card and triggered your logic.)

Jajajaja that “R” (Raspode-Ralletin) was a revenge, wasn’t it?.

Well I finally created a function recalculateRisk (table 18 Customer) that works perfect, when I want to insert a new order, invoice, etc… (table 36 Sales Header), I check in the onValidate trigger of “Sell-to Salesperson No.” with something like this lines:

rec18.get(“Sell-to Customer No.”);
rec18.recalculateRisk;
if rec18.Risk > rec18.“Credit Limit (LCY)” then error(message);

The value of Risk is the correct at this point, but it doesn’t update yet tha value on the Customer table, and I can’t add a line rec18.MODIFY because the same error is displayed.

My question is that, why it doesn’t update… I tried including the rec18.MODIFY call on recalculateRisk, on the “Sell-to Salesperson No.”.onValidate, and the same with COMMIT and rec18.Validate(Risk) functions but it’s not solved yet !!.

Best regards !!

I prefer to think of it as a subtle joke rather than “revenge” [;)]

Anyway, I have tried to do what you are after doing and it works. I used a different example - I set a value on my Customer Card called “Last Sales No.” to be the sales order number where the customer was last used. The principal is the same so you should be able to do this. Maybe the difference is you should set the value of the risk field by getting the recalculateRisk function to return the value.

So in the onValidate trigger of the “Sell-to Customer No.” on the “Sales Header” table you need to have:

IF rec18.GET(“Sell-to Customer No.”) THEN BEGIN
rec18.Risk := rec18.recalculateRisk;
rec18.Modify;
IF rec18.Risk > rec18."Credit Limit (LCY) THEN BEGIN
MESSAGE(‘Too Risky!’);
Rec := xRec;
EXIT;
END;
END;

Obviously if I used the ERROR command instead of the MESSAGE command then the transaction would get rolled back - maybe that’s why your values are going?