Hi, I’m trying to accomplish the following: In a tabular form I have two decimal fields, say Decimal1 and Decimal2. Decimal1 is an input field, Decimal2 must be calculated and shown. The formula is Decimal2 = 1 / CALCSUMS(Decimal1) … no, I’m wrong! [:I] The formula is Decimal2 = Decimal1 / CALCSUMS(Decimal1) That means that every time a value is entered in Decimal1 in any row, all the values in Decimal2, on every row must be updated. I’ve put an “Update” button on the form so that the user can trigger the calculation by hand, but I’m not satisfied with it, nor is the user, of course, who’s already complaining that an excel sheet works better! [xx(] Any suggestion? Anna
Hi Anna! If I understand correctly, then Decimal2 by definition has the same value for all rows, right? So, as a first modification, I would suggest creating a single text field somewhere on the form (outside of the TableBox) which shows Decimal2. Having the same value in every row is somewhat redundant, isn’t it? Second, what’s the problem with using the form’s various triggers to perform the calculation and calling UPDATE? Doesn’t this do what you need?
quote:
Originally posted by xorph
Hi Anna! If I understand correctly, then Decimal2 by definition has the same value for all rows, right?
No, sorry! I actually wrote that, but the right formula is: Decimal2 = Decimal1/ CALCSUMS(Decimal1) [:I] Anna
Hi Anna, I’ve tried to simulate your situation. Here’s what I’ve done: 1. Defined a table with 2 fields: Dec1 = Decimal, SumDec1 = Decimal (flowfield, sum(Dec1)) 2. Created a tabular form with 2 fields Dec1 (from the table) and Dec2 where Dec2 calls a small function “MyFunc” (SourceExpr = MyFunc):MyFunc(): Decimal CALCFIELDS(SumDec1); IF SumDec1 <> 0 THEN EXIT(Dec1/SumDec1) ELSE EXIT(0); 3. Added CurrForm.UPDATE to the OnAfterValidate trigger of Dec1. This seem to work the way you want it to (I hope). ---- added note ------ Alternatively (without the field SumDec1) MyFunc1() : Decimal MyRec.CALCSUMS(Dec1); IF MyRec.Dec1 <> 0 THEN EXIT(Dec1/MyRec.Dec1) ELSE EXIT(0); And of course you’ll need SumIndexField on Dec1
quote:
Originally posted by Steffen Voel
Hi Anna, I’ve tried to simulate your situation. Here’s what I’ve done: 1. Defined a table with 2 fields: Dec1 = Decimal, SumDec1 = Decimal (flowfield, sum(Dec1)) 2. Created a tabular form with 2 fields Dec1 (from the table) and Dec2 where Dec2 calls a small function “MyFunc” (SourceExpr = MyFunc):MyFunc(): Decimal CALCFIELDS(SumDec1); IF SumDec1 <> 0 THEN EXIT(Dec1/SumDec1) ELSE EXIT(0);3. Added CurrForm.UPDATE to the OnAfterValidate trigger of Dec1. This seem to work the way you want it to (I hope).
That might work on the form, but how do I get decimal2 updated on the table too? Because, maybe I didn’t say that before, but this is what it’s all about, actually. My customer makes recipes and they must be converted into BOMs. [:D] Anna
Anna, if you actually need Decimal2 to be stored in the table, then I’m afraid you will have to loop over all the table’s records in Decimal1’s Validate table trigger, calculating Decimal2 for each record. As a side effect, this will take care of your form as well - all you probably need in the form is an additional UPDATE in OnAfterValidate. So, your table’s Validate trigger for Decimal1 could look like MyRec.CALCSUMS(Decimal1); MyLoopRec.RESET; IF MyLoopRec.FIND('-') THEN REPEAT IF MyRec.Decimal1 <> 0 THEN MyLoopRec.Decimal2 := MyLoopRec.Decimal1 / MyRec.Decimal1 ELSE MyLoopRec.Decimal2 := 0; MyLoopRec.MODIFY; UNTIL MyLoopRec.NEXT = 0;
quote:
Originally posted by xorph
Anna, if you actually need Decimal2 to be stored in the table, then I’m afraid you will have to loop over all the table’s records in Decimal1’s Validate table trigger, calculating Decimal2 for each record. As a side effect, this will take care of your form as well - all you probably need in the form is an additional UPDATE in OnAfterValidate.
Yeah! [:D] I had tried that the first time, but something didn’t work right … can’t remember what. I guess I had put it in the wrong trigger or just forgotten to call UPDATE in OnAfterValidate. Thank you. [:)] Now If I just could figure out how to make it work for inserts and deletes … (you know, the Production BOM Lines form has DelayedInsert=Yes [}:)]) Anna
Glad to hear it works [:)] Regarding inserts and deletes: I would suggest putting the code for calculating all those Decimal2s into a global table function which is then called from the OnValidate, OnInsert, OnDelete and OnSomething triggers. [;)]
As I see it you only have the problem when deleting lines and it won’t work calling the function from OnDelete since this is called before the delete and hence will yield a wrong result (we really need a OnAfterDelete trigger [;)]). A possible solution would be to call the function regularly (every second or so) from the OnTimer trigger.
quote:
Originally posted by xorph
Glad to hear it works [:)] Regarding inserts and deletes: I would suggest putting the code for calculating all those Decimal2s into a global table function which is then called from the OnValidate, OnInsert, OnDelete and OnSomething triggers. [;)]
Thanks, Heinz Actually this is what I’ve been trying to do for two days. Every time it seems to begin to work, something new happens that messes things up. [:(] Right now, the code in OnDelete trigger is working (with the caution to subtract the value in the record to be deleted from the total before doing the calculation). The code in the OnInsert just ignores me, no matter what I try. [B)] The code in the OnValidate plays tricks - sometimes it works, sometimes it does not, sometimes it says “Another user did change the record … bla, bla …” and closes the form, if not NAVISION. [:(!] BUAAAAAAAAAHHHHHH!!! [:(][:(][:(][:(] Anna
Hi Anna, I’ve got a solution that works now (without using the OnTimer). If you haven’t solved it yourself by now I’ll be glad to share it.
quote:
Originally posted by Steffen Voel
Hi Anna, I’ve got a solution that works now (without using the OnTimer). If you haven’t solved it yourself by now I’ll be glad to share it.
I think I have, although in a very twisted way. I would like to see your solution anyway, if you are willing. Thank you Anna
Right, here goes: I’ve created a function which looks like this:CalcDec2(Val : Decimal) MyRec1.CALCSUMS(Dec1); SumDec1 := MyRec1.Dec1 - Val; IF MyRec.FIND('-') THEN REPEAT IF SumDec1 <> 0 THEN MyRec.Dec2 := (MyRec.Dec1 / SumDec1) ELSE MyRec.Dec2 := 0; MyRec.MODIFY; UNTIL MyRec.NEXT = 0;I’ve created the function on the form, but you can create it on the table or a separate CU as yo see fit. Also You might possibly need to apply some filters. The function is called from 2 triggers of the form:OnDeleteRecord() : Boolean CurrForm.UPDATE; CalcDec2(Dec1); CurrForm.UPDATE; EXIT(TRUE); OnAfterValidate() (the Dec1 Control) CurrForm.UPDATE; CalcDec2(0); CurrForm.UPDATE; Don’t know if it’s less twisted than your own solution [:D]
quote:
Originally posted by Steffen Voel
Right, here goes: I’ve created a function which looks like this:CalcDec2(Val : Decimal) MyRec1.CALCSUMS(Dec1); SumDec1 := MyRec1.Dec1 - Val; IF MyRec.FIND('-') THEN REPEAT IF SumDec1 <> 0 THEN MyRec.Dec2 := (MyRec.Dec1 / SumDec1) ELSE MyRec.Dec2 := 0; MyRec.MODIFY; UNTIL MyRec.NEXT = 0;I’ve created the function on the form, but you can create it on the table or a separate CU as yo see fit. Also You might possibly need to apply some filters. The function is called from 2 triggers of the form:OnDeleteRecord() : Boolean CurrForm.UPDATE; CalcDec2(Dec1); CurrForm.UPDATE; EXIT(TRUE); OnAfterValidate() (the Dec1 Control) CurrForm.UPDATE; CalcDec2(0); CurrForm.UPDATE;Don’t know if it’s less twisted than your own solution [:D]
Quite similar in its substance. Mine is more twisted because of the many “try this … no, better that … no maybe … etc.”; I hope it will improve after I manage to clean the code. Just one more difference - for deleted records I’ve used the table OnDelete trigger. It was the first to work, so I left it there. In the OnAfterValidate, my code actually is: OnAfterValidate() (the Dec1 Control) CalcDec2(Dec1 - xRec.Dec1); CurrForm.UPDATE; Otherwise you see the values in the form lines change, but if you check their sum, you’ll find out it’s wrong. [:)] Why do you call CurrForm.UPDATE also before calling the CalcDec2 function? Does it some interesting trick? [:p] Anna
quote:
In the OnAfterValidate, my code actually is: OnAfterValidate() (the Dec1 Control) CalcDec2(Dec1 - xRec.Dec1); CurrForm.UPDATE; Otherwise you see the values in the form lines change, but if you check their sum, you’ll find out it’s wrong. Why do you call CurrForm.UPDATE also before calling the CalcDec2 function? Does it some interesting trick? Anna
In the OnAfterValidate() I call the CurrForm.UPDATE initially to update the table before calling CalcDec2. This way I don’t need the xRec subtraction you use. The initial CurrForm.UPDATE in the OnDelete() trigger is superfluous.
quote:
Originally posted by Steffen Voel In the OnAfterValidate() I call the CurrForm.UPDATE initially to update the table before calling CalcDec2. This way I don’t need the xRec subtraction you use.
Bump, bump, bump … (sound of my head bashing against the wall!) [:D] Thank you, guys, for bearing with me! Anna