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
kali958Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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))
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
Sjef BosmanGroupware ConsultantCommented:
I think that won't work, if the field must be editable in a new document.
Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

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?

:)

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Sjef BosmanGroupware ConsultantCommented:
Suggesting a status of some kind??
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?
marilyngCommented:
Of course, we're having a good time discussing the possibilities... kali, we need your input!
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!!!!
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!
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Lotus IBM

From novice to tech pro — start learning today.