Link to home
Start Free TrialLog in
Avatar of AliciaVee
AliciaVee

asked on

Puzzled on computed field not refreshing

I have a computed field (TTLPct) on a form, that takes the value of TCount and TPct  performs a math equation to get TTLPct.  The two fields on the form, are also computed and have an @DBLookup to find values from a view.  

The values are only updating when I open the form in edit mode, and save.  So, the values are not being retrieved from the two other fields.  I am also using TTLPct on a vew, so that number has to be accurate from many forms.  What to do?
Avatar of mbonaci
mbonaci
Flag of Croatia image

I can't understand what exactly are you trying to accomplish.
You want to make changes in one view (that you read using dbLookup) visible in another view (where you show or use TTLPct) via form?

I guess you can create an agent that would periodically refresh the document(s) that contain TTLPct field.

Could you explain your requirements a little better...
Avatar of AliciaVee
AliciaVee

ASKER

Didn't mean to sound cryptic, but this is what i have:

A Main doc that captures information for a key milestone, in this doc there is a PctComplete field (computed with 0 as default).  A response doc that captures information for tasks that support the key milestone, and has a pctComplete field that users enter based on status.

I have a view that displays details for the tasks and this view is embedded into the main key milestone document.

I have anohter view that uses the @Text($Ref) as the first column and groups and counts all the task documents and also totals all their pct complete for status.  Example, 3 doc, each are 25% complete= 75% totals, with a COUNT of 3 documents.

I use this view in two computed fields (number) with the following code:
'this grabs the total percent and is in the TaskPct computed field
x:= @DbLookup("";"";"vTtlPctTasks";DocID;2);
@Sum(@If(@IsError(x); 0; x))

'this grabs the total count of tasks, and is in the TaskCount field:
x:= @DbLookup("";"";"vTtlPctTasks";DocID;3);
@Sum(@If(@IsError(x); 0; x))

These two fields are hidden on the form, located at the top of the main document.  this main document also holds the embedded view that shows all related tasks to this key milestone.  This document has another computed field called TtlMilestonePct and has the following formula:
result:= @If(TaskPct=0;0;TaskPct/TaskCount);
@If(@IsError(result);0;result)

This is calculating correctly, but when a change is made in the tasks documents, the main document only gets updated when I open, save it.

Expectations is not to get the exact percents of each task that rolls up to the key milestone, but to be provided as a rough estimate and also to be used in a view gantt chart, displayed by key milestone only.

Would be cool if I can do it.  Can you help?
I think the best way to tackle this is to catch "ChildChanged" event (but only for the fields that are crucial for our calculation) and when it occurs, update the parent with the fresh data.

How can you do that?
We'll use the standard way to track changes/history in a Notes document.

All the following happens in the response form:

 1. In the form's Globals, create the method called updateParent that will:
       - get the parent doc
       - get all his child docs
       - calculate new values for parent's TaskPct and TaskCount fields
       - set those fields in the parent and save it

 2. In the Globals > Declarations add:
       - Dim isDocSaved As Boolean
       - Dim fieldNamesList(2) As String 'where two is the number of fields you want to monitor (track whether it was changed) minus one
       - Dim valuesAtOpen List As String 'here we'll store the actual field values at doc opening time

 3. In the PostOpen event, fill the array with field names and the list with corresponding values (see the code bellow)

 4. In the PostSave event, set isDocSaved to True

 5. In the QueryClose event, compare the current values of our fields with the ones we collected when the doc was opened. If we detect any difference, we immediately call the updateParent method and stop further checks (exit loop)


Ask if you'll need help with updateParent method...
'Globals > Declarations:
Dim isDocSaved As Boolean
Dim fieldNamesList(2) As String
Dim valuesAtOpen List As String


'PostOpen:
fieldNamesList(0) = "pctComplete"
fieldNamesList(1) = "field2"
fieldNamesList(2) = "field3"

Forall fld In fieldNamesList
	'store field values in the list (named valuesAtOpen cuz' we're remembering field values just after the doc was opened)
	'the simplest way is to use field names as the keys for our list
	valuesAtOpen(fld) = CStr(source.document.GetItemValue(fld)(0))
End Forall


'PostSave:
isDocSaved = True


'QueryClose:
If isDocSaved Then 'skip the whole thing if the doc wasn't saved
	Forall fld In fieldNamesList
		'check whether any of our fields changed since the doc was opened
		If valuesAtOpen(fld) <> CStr(source.document.GetItemValue(fld)(0)) Then
			'if it was - update parent doc
			Call updateParent( source.document )
			Exit Forall
		End If
	End Forall
End If

Open in new window

mbonaci -- your solution sounds cooooool!  And am hopeful it will work!  I have to run to a family thing for a few hours, but will be back and need to finish this work.  I will incorporate your suggestions and let you know how it will work out.  thanks@!!
Avatar of CRAK
Do you need the computed value in a view as well?
If not, I'd choose a computed for display field. They refresh in readmode too!
There is a catch though: the documents that you have now already contain the stored field. You'd have to remove these if you want to make the computed for display field work.
mbonaci,
okay -- I went step by step...and just realized the "updateParent" method.  I don't know how to create that?  Is that part of the code you provided as i don't see it referenced.
yep -- I do need guidance on that step.

Thanks!
crak - I do need the value to dispaly in a view.....
ASKER CERTIFIED SOLUTION
Avatar of mbonaci
mbonaci
Flag of Croatia image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
mbonaci -- sorry, had to go on a business trip -- am back now, but playing catch up -- will respond in a day or so and will try your solution!  Thanks....