For a recent web project, there were some very strict security requirements. So there needed to be reader fields placed on all the documents. By the way, the documents were in a parent-child relationship. And there were different levels of approval that needed to happen - all the individual child documents needed to go through approvals. Additionally, the customer wanted to be able to add additional approvers for individual child documents. Putting the additional approver into an Authors field on the child document would take care of the new approver being able to see those children, but the new approver needed to be able to see the parent as well.

There are several possibilities for solving this problem. I could have written a Web Query Save (WQS) agent for the response document and made sure the parent was updated during that WQS agent. But I had no other reason to run a WQS agent - there was nothing else I needed it for. So I didn't want to run an agent through the HTTP task (that's what actually runs the WQS agent) for the 1 time in 100 that it would be needed. Agents run through the HTTP task aren't the friendliest in terms of resources, so avoiding them, if possible, is recommended.

Another choice would be to use some sort of background agent, either through an agent that runs on new and modified documents, or through a scheduled agent. Again, having additional approvers for an individual child document isn't something that happens very frequently, so a new and modified agent is certainly overkill - it would run on every update when it was only needed to run on less than 1% of the updates. (It could be set up to exit quickly if not needed, but it still takes up resources). A scheduled agent (daily, weekly, or even monthly) would help with the resources problem, but then I open myself up for other problems because the parent would not grant the reader access in a timely fashion.

I decided to perform the update through a field on the response document. The field on the response document is computed for display (CFD), so it isn't stored with the response document, but it will be recomputed every time the document is saved, so I can be assured that the parent will always be up to date. Since the value of a CFD field is set through formula language, my performance should be a bit better than running a WQS agent.

The first thing I needed was a view showing additional approvers. I needed this view for other reasons (looking up other information on all the child documents) so I was not adding a new view and just pushing the resource issue to the indexer task. I simply added a new column to an existing view - this is not as big of a resource issue as the other options listed above.

The view selects all the response documents by the form name. The first column is categorized on the parent UNID - the formula for the first column is @Text($Ref). Column 2 lists a unique ID (contained in the response document field called ID) for identifying each response document. Column 3 is the additional approvers. But I don't want empty values in this column. (Remember that additional approvers are the exception, not the norm). So my formula for this column is this:

@If(@Elements(AdditionalApprovers) = 0; "~"; @Implode(AdditionalApprovers; "~"))

I bring all additional approvers together with an @Implode to give me one string. That way I know that I'll have a one-to-one relationship when comparing to the ID column later on. The rest of the columns in the view are irrelevant (used for other purposes).

In the document is where the magic happens. Below the AdditionalApprovers field (because forms are computed top to bottom, and I want this computed after the changes to AdditionalApprovers is known to the form) is my CFD field. The field is set up to be a number field, although that is not relevant (it could have been a text field). The field is hidden at all times - it will still be computed by Notes, but there is no need to show it anywhere.

The first thing the field does is check to see if the document is being saved. There is no need for the formula to compute when the document is being loaded or opened for read/edit, or being recalculated. It is only relevant when it is being saved.

CheckIfSaving := @If(@IsDocBeingSaved; 0; @Return(@True))

If the document is not being saved, the value @True is returned. That is used because it is a number field. I could have easily returned any number value. The important point is that the formula exits at this point instead of going through the remaining calculations.

The next part of the formula looks up the values from the two columns in the view:

LookupIDsStep1 := @DbLookup(""; ""; "vwChildren"; @Text($Ref); 2);
LookupIDs := @If(@IsError(LookupIDsStep1); ""; LookupIDsStep1)
LookupApproversStep1 := @DbLookup(""; ""; "vwChildren"; @Text($Ref); 3);
LookupApprovers := @If(@IsError(LookupApproversStep1); ""; LookupApproversStep1)

Since this is a web application, the server is going to be executing this code. The lookups grab columns 2 and 3, respectively, from the view using the parent UNID as a key.

Remember that the document is in the process of being saved, but hasn't actually been saved yet. So the document with this ID may have incorrect information in the view (it will have the previous value for AdditionalApprovers, not necessarily the value that is currently being saved). So I'm going to need to know what the value should be - all the additional approvers in the other response documents plus the additional approvers in the current response document. So the next couple of statements are used to give me the approvers in the other response documents.

Pos := @Member(ID; LookupIDs);
OtherApprovers := @If(Pos = 0; LookupApprovers; @Elements(LookupIDs) = 1; ""; Pos = 1; @Subset(LookupApprovers; 1-@Elements(LookupApprovers)); Pos = @Elements(LookupIDs); @Subset(LookupApprovers; @Elements(LookupApprovers)-1); @Subset(LookupApprovers; Pos-1) : @Subset(LookupApprovers; Pos-@Elements(LookupApprovers)));

The variable Pos is used to find out what position in the two lists this response document is. If the response document is brand new, it won't show up in either list, so Pos will be zero. The second statement pulls out everything from the second list except the current response document. The different checks are made to prevent a zero value in the second parameter to @Subset. Basically, if the document isn't in the view (because Pos is 0) then return the whole lookup value. If it's the only one in the list, then there are no other documents to worry about, so use an empty string. If it's the first in the list, return everything from the 2nd to the last in the list. If it's the last in the list, return everything from the 1st to the second to last in the list. If it's somewhere in the middle, then return the concatenation of the 1st to the "pos-1" spot plus the "pos+1" spot to the last.

Keep in mind that the view was set up with additional approvers concatenated together with a tilde character. So at this point in the formula we really don't have all the additional approvers, unless every time an additional approver was used it was a single value. So we need to get a list of unique values:

OtherApproversList := @Explode(OtherApprovers; "~");

So we now have a list (array) of all the entries from the AdditionalApprovers field in all the other response documents (excluding the document being saved). We can put this together with the AdditionalApprovers of the current response document (prior to it being saved to disk) and know what the value in the parent document should be:

ValueShouldBe := @Trim(@Unique(OtherApproversList : AdditionalApprovers));

And we can also look up what the current value is through a built-in formula (this is where the parent-child relationship really helps):

ValueIs := @GetDocField(@Text($Ref); "AdditionalApprovers");

I want to get what the value currently is because I don't want to make the update unless I absolutely have to. It would be easy enough just to blindly make the update - the ValueIs variable would then be unnecessary - but that would cause many more updates to the parent document. Remember, the use of additional approvers is the exception, not the rule. So I only want to update the parent when it is necessary. So my next statement checks to see if an update is necessary and does the update.

Compare := @If(@Implode(@Sort(ValueIs); "~") = @Implode(@Sort(ValueShouldBe); "~"); 0; @SetDocField(@Text($Ref); "AdditionalApprovers"; ValueShouldBe));
@True

The last statement gives a result to the CFD field. Again, this could be any number value I wanted (it's not stored with the document, so it doesn't really matter).

I wanted to compare all the entries in each list - I didn't want to make the update if the lists were identical but in different orders. There might be a better way to do that comparison using the "list-wise" operators built into formula language. But I've found that many people don't understand the "*=" and other "list-wise" comparison and operator statements. So imploding the sorted lists into a string was a way that would be easy enough for someone supporting this application down the road to understand.

Well, hopefully this tip was of some benefit to you. It shows how a parent document can be dependent on values in the response document. This technique could be expanded to make response documents dependent on the parent document (if a value changes in the parent, change it in the responses).