Use color on Lotus Notes View

coronoahcoro
coronoahcoro used Ask the Experts™
on
We have an Excel spreadsheet that is displayed on 60" screen in our lab to track our repair process. We want to create something better so the customer service people do not need to walk to the lab and type the information needed.

We have some parts of the information in the Notes Database (WO#, date received, date approved, shipped date) and we're thinking to integrate this in the Notes database. We're thinking to create a new form and just use Notes View to display it in Excel like format and from that idea comes the very first question: Is it possible to involve colors in Lotus Notes VIew? If you look at the attachment, the PO column (8th from the left) can have 3 different colors that show different states: red, blue, and white (not shown). Is it possible to do that in Lotus Notes view?

The main goal is to create a Notes app that matches as close as possible with the image attached. Thanks

 screenshot
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Commented:
My suggestion is that you use view icons (instead of coloring the whole "cell").
So, instead of colored cell, you can use the circle icon or the flag icon in the same color.

See here for available icons:
http://publib.boulder.ibm.com/infocenter/domhelp/v8r0/topic/com.ibm.designer.domino.main.doc/H_ABOUT_DISPLAYING_AN_ICON_IN_A_COLUMN.html

Commented:
Of course, if you insist, you can use colors.
See here (locate "Setting column colors programatically"):

http://publib.boulder.ibm.com/infocenter/domhelp/v8r0/topic/com.ibm.designer.domino.main.doc/H_EXAMPLES_ABOUT_ADDING_PROGRAMMING_TO_COLUMNS.html

Author

Commented:
Thanks for the links. I have some problems when trying to use color in a column. The color didn't come up. I believe I've set the formula correctly. You can see how I set the formula on code3.jpg

The first screenshot is the regular view, the second is the view with formula but no color (for a test), the third one is the one I coded with color.
screenshot1.jpg
screenshot2.jpg
code3.jpg
screenshot3.jpg
Rowby Goren Makes an Impact on Screen and Online

Learn about longtime user Rowby Goren and his great contributions to the site. We explore his method for posing questions that are likely to yield a solution, and take a look at how his career transformed from a Hollywood writer to a website entrepreneur.

Sjef BosmanGroupware Consultant

Commented:
Does this idea appeal to you:
- use a browser to present the data
- you can do far more with colours in a browser than in a Notes client
- if it's a read-only screen, you can let the browser refresh the screen regularly
- you can even use excel (and IE) to present even better screens

Here's an example of how to build an Excel screen using LotusScript:
http://www.breakingpar.com/bkp/home.nsf/0/87256B280015193F87256E32007999E6

Commented:
Your formula is missing this on top:
red := 255:0:0;

blue := 0:0:255;

yellow := 255:255:0;

pink := 255:193:253;

white := 255:255:255;

black :=  1:1:1;

apricot := 255:155:133;

plain:= 0:0:0;

Open in new window

Author

Commented:
sjef_bosman:
the idea to put the data on the web browser is really appealing. The problem is that it's not a read only screen, I am planning the techs and customer service can change the status of repair from the same screen. I've also looked at the link that you posted and I don't think that will work ok. If I understand correctly, when someone is accessing the webpage that shows the Excel file, other people can see the information but are not able to make any kind of change/update, right?

mbonaci:
thanks for pointing that out, I figured that I missed that code right before I left work and didn't get a chance to update my respond. It is quite a pain that the color column set does not display the data itself, which actually is fine in this case. I need to try a few other things, I believe I will have more things to ask.

Commented:
I feel you, but I would still go with view icons. It's the natural way of flagging documents in Notes view/folder.
From where I stand, it would be an improvement, after that ugly colored cells in Excel :)
Try it, you've got nothing to lose.
Sjef BosmanGroupware Consultant

Commented:
Don't think in the Notes limits when you're targeting a browser! The Excel-idea is just an example, you can use the idea to create charts. You could use frames, or just an Iframe, to put the Excel stuff in. You could then design a web-based form, or a view, and have buttons in the view columns. Behind each button you could stuff an agent invocation to update that document. I didn't say it was easy, but using a browser you have much more flexibility when it comes to presentation.

And if you have R8.5.2 it is really a piece of cake!

Author

Commented:
sjef_bosman: could you please point out some good links that I can look at to make a webpage based on Notes database?

Also how can I check what version of Domino I am using, I know I am using Notes Client, Designer, Administrator 7.03 but I think that's not what you meant on the last post.

Author

Commented:
The question that I'm going to ask is not related to column coloring but related to this whole project.

To create Notes View that I showed on my previous posts, I based the view from one form 'RepairProcess'. That form inherit the value from the 'WorkOrder' form because some of the data needed in the 'RepairProcess' are already available in the form 'WorkOrder' (i.e. WO#, Date Received, Date Shipped, etc). So what I am thinking is when a 'WorkOrder' document is created (saved), it will also create 'RepairProcess' doc with some of the fields are 'Computed when Compose'. The problem is if some of the fields in 'WorkOrder' doc are updated, those updates are not shown on 'RepairProcess' doc. I know Notes is not relational database, but I can't help but fall to that concept a lot of time. So is there a way to carry the update to 'RepairProcess' without having to open the doc an update the information manually?
Sjef BosmanGroupware Consultant

Commented:
By all means, try for yourself! Open the database on a server, but make sure that the HTTP task is active on the server first.

The server version can be found in the Server document in the Name & Address book on the server itself. Any server with a release higher than 4.61 (I think) should be capable of showing at least something of the database.

A form for web-use is much like a standard form, yet there are quite some differences, most notably when you want to have perfection. The early versions weren't easy at all, later versions provided more functionality, but since R8 and especially R8.5.2 the development of a web-based application is a lot easier.

That said, much can be found in the Designer Help database and on the Internet, by searching for it. Unfortunately, you'll have to learn most of it by experience. This forum may help a lot, because alone you'll have a very hard time getting a solution. Here's just a page that could answer some of your questions: http://www.keysolutions.com/NotesFAQ/all7x.html

About copying info from parent to child document: put your code in the WorkOrder's PostSave-event. The fact that you ask that question makes me a little worried. If you're a (relative) novice to Notes development, I'm afraid you'll have a hard time developing a Notes/web application... It's not undoable, it's difficult, and it'll probably take more time than you have for this project.

Author

Commented:
I am a novice to Notes development, just to make it clear :)

If I understand correctly the PostSave event doesn't help in this situation or maybe I should change my code. What I have right now is the code attached. That code is in a button that is meant to save the 'WorkOrder' form then create 'RepairProcess' form based on some of the information from the 'WorkOrder' form. Again the problem is when someone updates the information in the 'WorkOrder' form, the updates are not shown in the 'RepairProcess' form because those fields are 'Computed when Composed'.  Is there a way to get this to work like in relational database?
@Command([FileSave]);
@Command([Compose];"";"RepairProcess");

Open in new window

Sjef BosmanGroupware Consultant
Commented:
> Is there a way to get this to work
Yes!

> like in relational database?
No!

What you'll probably have to do in the PostSave of the WorkOrder form is this (and in LotusScript):
- get the WorkOrder's key
- search all RepairProcess documents (there will be only one but you still have to find it, in a view)
- for each RepairProcess document
-    update the fields that should be updated
-    save the RP document
- end for

One more opportunity to polish up your LotusScript skills!

Author

Commented:
Thanks for the hints sjef_bosman. I am stuck at the point when I click 'Save/Close' button and nothing happens but when I debug LotusScript the code actually compose and open the document. What am I doing wrong here?

The code of 'Save/Close' button is: @Command([FileSave]);
@Command([FileCloseWindow])

The Postsave code


Sub Postsave(Source As Notesuidocument)
  Dim woNum As String
  Dim doc As NotesDocument
  Dim workspace As New NotesUIWorkspace
  Dim docRepair As NotesUIDocument

  Set doc = source.Document
  woNum = doc.WO_No(0)
	
  If doc.WO_Dept(0) = "FLEX" Then
   Set docRepair = workspace.ComposeDocument("","","FlexRepairProcess")
  End If
	
End Sub

Open in new window

Author

Commented:
So while I'm waiting for someone to respond my last comment, I created another action button just to create a Repair doc with this formula: @Command([FileSave]);
@Command([Compose];"";"FlexRepairProcess");

Then I changed my Postsave code a bit so I can test if I am able to get the doc by key and update the fields. The code works ok, except when someone is editing the Repair doc while someone else updating the WorkOrder doc. When the person updating the WO saves the doc, the code in the PostSave event updates the fields in the Repair field, and if after that the person editing the repair doc saves the doc, that person will get a message 'Another copy of this document was saved while you were editing it. Save your changes also as a Save Conflict document?" . If the person chooses yes the doc save will show as 'Replication or Save Conflict' on the view.

What is the best way to fix that?
Sub Postsave(Source As Notesuidocument)
 Dim view As NotesView
 Dim woNum As String
 Dim doc As NotesDocument
 Dim workspace As New NotesUIWorkspace
 Dim docRepair As NotesDocument
	
 Set doc = source.Document
 woNum = doc.WO_No(0)

 If doc.WO_Dept(0) = "FLEX" Then
  Set view = db.GetView("FlexBoardByWO")
  Set docRepair = view.GetDocumentByKey(woNum,True)
  If Not( docRepair Is Nothing) Then
    docRepair.FRP_RptShipDate = doc.RptShipDate
    docRepair.FRP_CustomerApprovalDate = doc.CustomerApprovalDate
    docRepair.FRP_WO_Model = doc.WO_Model
    Call docRepair.Save(False, True)
  End If		
 End If
	
End Sub

Open in new window

Sjef BosmanGroupware Consultant

Commented:
Ok, splendid code! I'd probably have used ReplaceItemValue but that's beside the point (for it creates fields with upper- and lowercase characters).

As for the Replication or Save Conflict handling: you can set your database to allow locking. A warning though: the cure may prove to be worse than the disease. If you enable locking, here's a scenario:

      if updating is required then
            for every repairdoc do
                  lock the repairdoc
                  if the lock fails
                        skip it
                  else
                        update those fields
                        save the repairdoc
                  fi
            od
      fi

And in the repairdoc itself, in the QuerySave, the code should check the WorkOrder document in order to make sure that those fields have the right values.

Still it may happen that there are differences, so you also need an agent that checks a view during the night.

Author

Commented:
Thanks sjef, I tried to code that and successfully did it. But a problem came up when that update button is clicked and someone else is in the middle of editing the repair doc. My boss just told me to drop the update fields idea and go with locking system. There are cases during the test when I close a repair document after editing and on the test account, Notes says that the doc is locked. I just the unlock function now on the close event.

Anyway my question now is when I update the repair doc and save it. My view gets updated with the update but not on the test PC. Is there a way to make the view on the other PC to update the view automatically too (let'say every 15 mins) without having to press F9? I looked on the View Properties but there option to refresh a view is limited by hour.
Sjef BosmanGroupware Consultant

Commented:
And the refresh of the view is internal, not on the screen. Apart from dirty tricks there's only the F9 key. And maybe one option in the View Properties, 2nd tab: set On Refresh to Refresh display from top row.

Author

Commented:
What are the dirty tricks? I've tried setting On Refresh to Refresh but that didn't make a difference.

I'm having problem with sorting on of my column, it doesn't show correctly. From what I understand the 1st click should sort the data ascending, 2nd click sort the data descending, and the 3rd click returns the original sorting. But here is what I got:

 1st click 2nd click
 3rd click
Any idea what is going on here?
Sjef BosmanGroupware Consultant

Commented:
What is the value in the sortable column? Is it a text value or a date value? Is it the same type for ALL documents?

Commented:
That's obviously sorted alphabetically, as text, not as dates.

BTW, I'm glad you went with view icons in the end. This looks much better...
Sjef BosmanGroupware Consultant

Commented:
@Marko: it was a hint!!

Author

Commented:
Yes, you both are right. And thanks for the hint sjef ;)

Yeah I decided to go with icons since it serves much better. It's just a pain when I can't find icons that I want from Google. Maybe I should learn to create one.

Anyway I presented that to the users today and they want to be able to add Pending Approval (shorten to PA) on the Date Approved information and still able to sort the date and text altogether.

This is what I have now Appvd1
This what the users want  Appvd2
This is how I get the data (Calendar Control)  Appvd3
Any idea how to implement this?

Commented:
Have you tried like this:
@If( dateApproved != "";
    dateApproved;
    "PA" )

Open in new window

Commented:
BTW, this should've been a new question...
Sjef BosmanGroupware Consultant

Commented:
There are 2 ways you can sort that column:
1. convert date values to a YYYYMMDD string, like "20101118"
2. leave the date value, and add a to the left of it, with a string value like "    20101118" (the additional spaces serve to hide the value; the column should be fixed-width, 1 character)

And I agree with Marko: where indeed comes colour into view?

Author

Commented:
Sorry guys. I wasn't sure if I need to create a new question because this is still related. Anyway here is the new question and my response to your solution:
http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/Lotus_SmartSuite/Lotus_Notes/Q_26624434.html

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial