• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 224
  • Last Modified:

Run Formula once on form

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
0
kali958
Asked:
kali958
  • 3
  • 3
  • 2
  • +1
3 Solutions
 
Sjef BosmanGroupware ConsultantCommented:
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))
0
 
mbonaciCommented:
Hi kali958,
use "Computed when composed" type of field (it's computed when you create the doc and never again)

Hope this helps,
Marko
0
 
Sjef BosmanGroupware ConsultantCommented:
I think that won't work, if the field must be editable in a new document.
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
marilyngCommented:
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?

:)
0
 
Sjef BosmanGroupware ConsultantCommented:
Suggesting a status of some kind??
0
 
marilyngCommented:
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?
0
 
marilyngCommented:
Of course, we're having a good time discussing the possibilities... kali, we need your input!
0
 
kali958Author 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!!!!
0
 
kali958Author 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!
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 3
  • 3
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now