Refresh Fields in Documents in a Database using LotusScript

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

ASKER

Thank you for the help on the history field. However the other fields still do not update on the documents. Any ideas how to force the other fields to recalculate their computed formulas to reflect the updates from the position document in the employee documents?

SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

ASKER CERTIFIED SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

sjef.. thanks for your observations.. and the append to text list.

Her example was appending the old history to the new history, I think, which is why I did the array thing to move the latest to the front. chuckle, my last office wanted it this way, too. Somewhat painful.. I'm all ears if you know a more elegant way to do it.

SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

hee, hee, then you have to add the lines to check to see if history is "" or msg is "" or you can

msg = doc.history
doc.removeitem("history")

then recreate it... but then you're into the same amount of coding... :)

And where's your test on the initial value? You'd need it as well. :) Remove is no good, I just want to avoid the single value "" in the History (I hate wasted space ;)

It's just that I don't like ArrayAppend if almost everything can be done using Notes methods.

ASKER

OK The history is working fine I am using the following code:

Sub Postsave(Source As Notesuidocument)
      Dim session As New NotesSession
      Dim db As NotesDatabase
      Dim view As NotesView
      Dim coll As NotesDocumentCollection
      Dim doc As NotesDocument
      Dim workspace As New NotesUIWorkspace
      Dim uidoc As NotesUIDocument
      Dim oldHistory As Variant
      Dim newHistory As Variant
      Dim msg As Variant
      Redim msg(0)
      Set db = session.CurrentDatabase
      Set uidoc = workspace.CurrentDocument
      Set view = db.GetView("EmpJobCode")
      nam = session.CommonUserName
      key = uidoc.FieldGetText("JobCode")
      posit = uidoc.FieldGetText("Position")
      Set coll = view.GetAllDocumentsByKey(key, False)
      For i = 1 To coll.Count
            Set doc = coll.GetNthDocument( i )
            oldHistory = doc.getItemValue("History")
            msg(0)=Now() & " - " & nam & " updated " & posit & " position record"
            NewHistory = Arrayappend(msg,oldHistory)
            Call doc.ReplaceItemValue("History", NewHistory)
            Call doc.computewithform(False,False)
            Call doc.Save( True, True )
      Next i
End Sub

Thanks to all that helped me arrive at this. Now for the automated refresh...

ASKER

Yes we have to have a tiered approach and this is why: There is a company form, department form, position form, and employee form.

In the company form there is housed the company wide training requirements and company wide required documents. These are documents or training courses that must be taken or read by everyone in that company.

In the department form there is department wide training requirements, department wide required documents, and all managers in that department. As well as the requirements passed down from the company doc. So someone can open up a department wide doc and see all requirements for someone in that department. Therefore any changes at the company wide level needs to autoupdate the department level.

In the position form there is the job specific training requirements & job specific required documents, as well as Dept & Company Reqs. This is also where managers are assigned to the position and with it their departments. So for example a Warehouse Worker position could have managers from the Warehouse Canada Dept, the Warehouse US Dept, & the Warehouse PR Dept. All assigned to that particular position. So if for some reason there is a change in management one manager leaves another person takes their place. This change would take place at the Department level and autoupdate the position and employee levels. Unfortunately there are many managers that where multiple hats so they are in multiple depts which caused some confusion in the database originally.

As you may have guessed the employee record contains all of the above tiers' passed information as well as employee specific training and documents that a manager may have assigned them. This tiered approach is necessary because when a department wide policy changes or goes into effect you want to only change one document not every employees document.

We have solved all the coding issues and if I go in manually and Refresh selected all the changes propagate as they should. Problem is we need this to automatically change if someone makes a change at the department level it should pass down, company level should pass down, position level should pass down.

Hopefully this answers all your questions and gives you an idea where we are coming from. As far as coding this is all done with specific codes and dblookups so for example a field in the employee record might contain something like:

pos := @DbLookup("Notes":"No Cache";"";"JobCodeSelect";@Left(@Right(MultiCode_01;18);16);"Position");
@If(@IsError(pos);@Return("");pos)

Like I said it all works if manually refreshed we need an automated solution. Thanks.

SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

ASKER

Unfortunately because of the way the company is formed there are multiple companies under one umbrella of a main company. The company form is for the subsidiaries because each have their own requirement and anything that is from the main company is just added to the handful of copany records there are.

As far as storing in documents those fields are just that lookups from the parent tier. However all must show up in the lower tiers because of the following: This is an employee training records database. Each employee record must have a list of requirements pulled from the other tiers, a list of training taken, and a computation of what is left for a quick view for managers to know what their people must still train in.

As far as changing documents. A manual change is made for instance say at the department level there was a reorg that changes the name of a department and some managers. One document is updated manually with this change. Now a refresh must be done to refresh the fields in the position & employees docs so that they reflect the change. Currently we make the change in the department doc; have to go to a view that houses the positions and run an agent that performs a @Command([ToolsRefreshSelectedDocs]); then we go to another view that houses the employees and run the same agent. Once the manual refreshes are done all the records and views show the correct information. What we want is some way to automate those refreshes instead of being required to do it manually.

Hope that clarifies things, if not let me know and I can provide more examples.

Ok just did a test to be sure - computewithform does update the computed values, even if you have validations that fail. It won't do it if you don't have a "form" field and it's a new form, or if it can't find the form (because then, it doesn't know what form to use)

Agree with sjef. Can the fields be computed for display?

ASKER

Already am using computewithform in the PostSave event and it is not working:

----- excerpt ----- (complete code above)
For i = 1 To coll.Count
Set doc = coll.GetNthDocument( i )
oldHistory = doc.getItemValue("History")
msg(0)=Now() & " - " & nam & " updated " & posit & " position record"
NewHistory = Arrayappend(msg,oldHistory)
Call doc.ReplaceItemValue("History", NewHistory)
Call doc.computewithform(False,False)
Call doc.Save( True, True )
Next i
---------------------------

The fields cannot be computed for display as they must be used for other computations and displayed in other views.

Hmm.. just read your post after I posted my comment. The problem with computewithform, is that you can't really tell if the values you are trying to find are the updated ones or the old values. With the dblookups in the field formulas, these lookups might be acting faster than their associated views can update the changes, i.e. they're out of sync. If you do the lookups in script in your agent, you can step through debugger and see what values are actually being picked up for each of the fields that have the dblookup, and see how you might need to adjust your logic to update top tier first, then process second tier, then process last tier.

Question - so, if I understand, when the topmost document changes DOCA, then you need to pass those changes to DOCSB and then DOCSC. Each of DOCSB and DOCSC are looking up values in DOCA and DOCSB.

So, if DOCA #1111 =>>is changing DOCB 1111 and docB is expected to find values that are in DOCA 1111 and DOCA 2222, then it won't, because DOCA 2222 hasn't been processed yet.

Add to this if the database replicates, or is in the middle of indexing, or just plain slow, this is kinda like a cycle where one set of values must exist first, before you can expect the lower levels to update the changes. When you do ToolsRefreshSelected Docs, all the topmost and dependent lookups are done, so the dblookups are getting all the updated values.

Does this make sense? There are times when computewithform and @dblookups are convenient and fast programming techniques. But when you have difficult logic that needs to be maintained or available for troubleshooting, then careful and documented script planning might be the way to go, since you can control what get processed when.

I did a similar training database application last year, and didn't put any dblookups or computewithform anywhere - slow, difficult to maintain and imprecise. But that's me.

SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

I agree with Marylin: problem solving first.

You say ComputeWithForm doesn't work. I'd say, it works, but might be doing different calculations than you think. Only field computations are executed, and NO LotusScript whatsoever: "Validates a document by executing the default value, translation, and validation formulas, if any are defined in the document form." So if your computations in the document depend on values computed during QueryOpen, PostOpen or any other LotusScript event, you don't have the right values. In that case, you'd have to put the script-statements into a scriptlibrary, so you can call those statements both from the form and from the agent.

One additional question though: I suppose you don't have a parent-response hierarchy? It would make things terribly complicated...

ASKER

Marilyng - I will try to setup and troubleshoot based on your code the first chance I get.

Sjef -
No Parent-Response Hierarchy they are all main documents. The fields I have in the documents that I want to auto-update are Computed Single Value / Computed Multiple Value fields with formulas pretty much exactly like this format:

pos := @DbLookup("Notes":"No Cache";"";"JobCodeSelect";@Left(@Right(MultiCode_01;18);16);"Position");
@If(@IsError(pos);@Return("");pos)

It does a lookup based on a unique code that has been placed in each document.

In the above example Multicode_01 contains a 36 digit code that stands for the following

Dept(16)Mgr(2)Job(16)FieldSuffix(2)

This lookup pulls the position name based on the jobcode. So if the position was renamed all documents containing that job code should automatically be updated, theoretically. At least that is the goal.

Wit's end...

Very interested though in what comes out of the troubleshooting.

Me too.

ASKER

Sorry was pulled away from this project to work on something else for a brief time. As soon as I can test out the above I will post the results. This is still an issue and I very much want to resolve it but management seems to find more pressing issues to work with at times.

Understood... good luck!

ASKER

My apologies for not being able to return to this as of yet however I have been unable to return to the project this was for in lieu of the current projects I am working on. I am hoping I will be able to return to this project in the next month or so. I do not want this considered abandoned as it still is very much a solution we will need only other projects have taken precedence unfortunately.