We help IT Professionals succeed at work.

Run Formula once on form

kali958
kali958 asked
on
Medium Priority
241 Views
Last Modified: 2013-12-18
I have a field on a form with the following formula

Results:=@DbLookup("Notes":"NoCache";"";"Position";Level_8;"P_Name");
@If(@IsError(Results);"0";@Count(Results))

It basically goes out and counts how many grade 8 associate are in the database. I have to run this report weekly and I store the results "report form" and view.  The problem is that if I go back and edit a older report to put comments in or adjust it, it will recalculate this formula to today's value. I would only like this to calculate once, on creation and save. Is there a way to set that on the form. I have tried various ways and I keep getting errors. Any help would be most appreciated.

Thanks
Comment
Watch Question

Sjef BosmanGroupware Consultant
CERTIFIED EXPERT
Commented:
Use
    @If(@!@IsNewDoc; @Return(@ThisValue); "");
    Results:=@If(@IsNewDoc@DbLookup("Notes":"NoCache";"";"Position";Level_8;"P_Name");
    @If(@IsError(Results);"0";@Count(Results))

or, change only the last line
    @If(!@IsNewDoc; @ThisValue; @IsError(Results);"0";@Count(Results))

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts
Commented:
Hi kali958,
use "Computed when composed" type of field (it's computed when you create the doc and never again)

Hope this helps,
Marko
Sjef BosmanGroupware Consultant
CERTIFIED EXPERT

Commented:
I think that won't work, if the field must be editable in a new document.
Commented:
If you set the computed when composed value to "", then you can decide when you want to push a value into the field, I think :)  It will only update when you decide to update, i.e. on the post open or query save, or by using the validation formula in another field... could be wrong.

But when you say I only want it to recalculate once, and then list two times you want it to recalculate, that kinda says you need more logic. Like on what save do you want it to go out and check?   For instance, when you go back and edit an old form and save it, well, what would tell the document not to update the collected values it previously stored?  How would it know?

:)
Sjef BosmanGroupware Consultant
CERTIFIED EXPERT

Commented:
Suggesting a status of some kind??

Commented:
Yeah, or a comparison at the very least.. you know, when the form opens, grab the value of some field and put it in an "_OLD" field, when you save, compare the current with the "_OLD" and if they don't match, well, push a new value to the date field.

It would need some kind of "YO!" trigger... :) Yes?

Commented:
Of course, we're having a good time discussing the possibilities... kali, we need your input!

Author

Commented:
thank you so much everyone for your input, i was out of the office today - let me try the computed with the new code in and see what happens when the report is generated. I will be able to complete looking at it by tomorrow morning. I think that having a check on the formula will help but also there is a mix of computed and editable fields in this report. So let me double check everything and get back to you by Thursday morning. thanks again for all the help!!!!

Author

Commented:
Thank you so much. This "report" is actually bigger than my first question. I have field that need to be computed when composed but I also have fields that need to be editable but pull in a number from another location and I have to let the associate running this report be able to adjust some the numbers. So some fields I could set to the Computed when Composed, so they can not change that number, but then on the editable fields the I combined the answer from Sjef and Marilyng - I needed to be able to vaildate that it did the look up in the correct week but still let it be editable in another field and merge the two together.  Thanks again!
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.