Solved

Using view Lookup  / How to refresh data?

Posted on 2004-08-02
13
632 Views
Last Modified: 2013-12-18
Experts,

I need to build a Louts Notes database, that currently is in an Access database.

The Access database tracks projects using data from a company table, as well as other tables, but this data is then added to a project table, and for updates (using subforms) that populate a project update table.  Well -- don't want to get too detailed on the many relationships in this Access table, so for now -- this info should be sufficient for my question.

Basically, I plan on using a Main Document for the project data and gathering values for specific fields from lookup views (based on what is stored in some of my Access tables).  For the project 'updates' I plan on using response-to-main document type.

I have extracted the company data from a main datasource (our internal ERP system) and was surpised to see how easily I was able to import it into Lotus Notes (I am working with version 6.5).

Okay -- before I continue this project, I need to know how data is updated in a view -- when needing to do so in the future?  In Access, one field will always contain the Unique ID -- and my Access Project table holds a company number (Unique ID).  In the past, when I needed to update the company data, it was easy to import 1200 records, and be certain that only 50 or so were new companies -- not yet in the table -- because of this unique ID.  Also, if I had to update, say a 'sales rep number) also stored in the company table, I could always do an update query to a new sales rep table/data and choose just one field of each record in the company table to update.  The companies that had a sales rep change would get the new data, but all else stayed the same...etc...etc..

So, my question then is, now that i have this company view -- using a form called Company -- with say 10 or so fields, how do future updates work on these documents that store company data in my company view that will be used for a lookup to the Project Main Form/document?  How do I make the company account number field in each document -- the key identifier? Is this possible?  

In the long run, when main documents are used for Projects, with a response-to main for updates, how can 'new' or revised' data be trickled down when changed?  I think I know the answer -- that this isn't possible in Notes -- please tell me this isn't so.  I think maybe I would have to use an agent to update data in documents that have the old data, but how do you indentify this?

I think I'm in trouble on this project.

AliciaVee
0
Comment
Question by:AliciaVee
  • 5
  • 4
  • 3
  • +1
13 Comments
 
LVL 46

Expert Comment

by:Sjef Bosman
Comment Utility
Trickling down data is possible of course, but you have to program that yourself (or get it somewhere). But what you need is a thorough Entity-Relationship-Diagram, how you are going to create the relations between the Notes-documents you need. The tables you have now will have to be "converted" into documents that can be shown in views. Sometimes you need documents for each record in a table, sometimes you can combine multiple records from several tables into one document. Before getting accused by my fellow experts of making things too difficult, could you describe your application in terms of forms and views? I.e. I basically need a Company form, a Project form, a Task form, a.s.o.
0
 
LVL 19

Assisted Solution

by:RanjeetRain
RanjeetRain earned 166 total points
Comment Utility
>> So, my question then is, now that i have this company view -- using a form called Company -- with say 10 or so fields, how do future updates work on these documents that store company data in my company view that will be used for a lookup to the Project Main Form/document?

Use @DBLookup.

That's the short answer. The long answer is, create a company view. Put all the columns you might need in it. Use @DBLookup to fetch the data and display it dynamically. Be sure to use [NOCACHE] . Your values will be up-to-minute, if not up-to-second.

One thing I am missing (I am not sure if you have already given it), are you developing for web or the client?


>> How do I make the company account number field in each document -- the key identifier? Is this possible?  

Its not natively supported (no autoincrement data type here). SO you gotta program it yourself.

Again a view helps you. Some looooooooong time back i had written a generic routine to generate serial numbers from a view. And it was pretty dependable. If you want I will post the routine.


>> In the long run, when main documents are used for Projects, with a response-to main for updates, how can 'new' or revised' data be trickled down when changed?  I think I know the answer -- that this isn't possible in Notes -- please tell me this isn't so.  I think maybe I would have to use an agent to update data in documents that have the old data, but how do you indentify this?

Depends your your design. Notes/Domino won't do it for you. If you physically store the data, you need to run agents to keep updating your data. And as the .nsf size grows, the lines on your head will deepen. My personal experience is, don't store such data in a document physically. Do a lookup. YOur forms load slow, but you save a lot of redundant processing.


Any missing links?


0
 

Author Comment

by:AliciaVee
Comment Utility
sjef -- okay -- will do ;)

First, a little histroy on this Access application -- it currenlty works great and has been in use for about 18 months.  However, the problems we encountered were network infrastructure related, and having some of our other locations connecting to the Access db was becoming increasingly more difficult.  My previous boss asked that is be built in Access and he was a supporter of this application.  My new boss wants it in Notes, and I'll have to admit that users will probably appreciate it in this application.

Anyway -- in looking at the current design in Access -- I realize that most of this would not work in Notes -- in terms of how I have relationships bulit with lookup tables and "enforced referential integrity".  So, in knowing this, in addition to my limited experience of Notes, I need to make this Notes application work -- as simple as possible.

This is what I envsion:

1. Project Form:
Will be Main Document to hold project data.  Fields are listed below with a brief description:

DateOpened: date type - editable
TargetDate: date type - editable
ProductName: would like to use a lookup view -- reading values from a Product Form, Product Name, Product Number (Unique)

Customer: would like to use a lookup view -- reading values from a CompanyProfile form. Documents using this form will hold the Company name, city, state, sales rep, region, territory, global company name, and company account number.  When the user selects a company, they only need to see the company, city, and account number.  All 3 of these values need to go on the project form.

EAC: currency -- editable
Volume: number -- editable
ProjectType: -- Combobox with 5 choices stored
MarketSegment: Combo box with 8 choices stored
Technology: Combo box with 5 choices stored
Planner: radio button -- 3 choices
Overview: text box -- free form typing

2. Project Update Form:
Will hold all updates to each Project document.  Will be response-to-main, so using a view, users will be able to select a current project to update.  All future updates will be response to the project document.  Fields will be:

CompanyName: Inherited (taken from project document)
CompanyCity: Inherited (taken from project document)
DateUpdated: computed when composed
AchievedRevenue: currency -- editable
Feasibiliy: combo box - 3 choices
Status: combo box - 7 choices


Other forms to be used to create documents (actually, most of this will be imported, except for when new data needs to be added -- that is where I am stuck -- for small amounts, new forms can be created, but for larger massive updates, how does this work when you need to ensure that new companies that are imported don't already exist in the view?)

Company Form -- for company profiles (will be 3000+ documents)
Product Form -- for product documents, to be used in a lookup (could be 1500 products)

Views:

Company Project View: to be used for lookups only.
Product View: to be used for lookups only
SalesPerson view: to sort all projects by salesperson.  I may just have one view and use column to sort various fields, company, salesperson, Planner, etc.

Let me know if you need additional Is this the information to understand what I"m doing.

thanks,
AliciaVee
0
 
LVL 19

Expert Comment

by:RanjeetRain
Comment Utility
>> I think maybe I would have to use an agent to update data in documents that have the old data, but how do you indentify this?

No way to identify that, untill you run thru the documents all over again comparing them with the new value doing a replacement wherever needed.

One approach is: whenever your master data changes, run an agent on the server that updates the document in the background. Be warned, your response docs won't have the updated data untill your agent is done running.

In short, that will make your system a "non-real time" system.


0
 
LVL 31

Accepted Solution

by:
qwaletee earned 168 total points
Comment Utility
I'll give a simple example.  Let's say you have a COMPANY table and a DEPT table.  Company table looks like:

UNIQUEID - text
corpname - text

DEPT table looks like:
UNIQUEID - text
CORPKEY - text - foreign key to COMPANY.UNIQUEID
deptname - text

Your select statment should read something like:

Select COMPANY.UNQIUEID, corpname, DEPT.UNIQUEID, deptname
From COMPANY, DEPT
Where CORPKEY = COMPANY.UNIQUEID
Order by COMPANY.UNIQEID, DEPT.UNIQUEID

Now, let's sya the documents you import using this select have the following field:
COMPANYKEY
DEPTKEY
corpname
deptname

Build a view that sorts first on COMPANYKEY then on DEPTKEY.  As you bring in records from Access, you can easily match by key to see if there is a document that matches on both table keys.  If there is one, it is an update, if there isn't, it is a create.  If there are any Notes documents that wer enot matched at all, they are deletes.  (You can do this most effetively by doing a reverse merge sort, walking the view in order as you bring back the rows, which is why I inserted an orde by clause on both keys as well.)

Now, if your needs are simpler -- no joins -- yu do the same thing, but using the lon key instead of a having a compound key on the Notes side.
0
 
LVL 46

Assisted Solution

by:Sjef Bosman
Sjef Bosman earned 166 total points
Comment Utility
About the ERD:
- Project seems okay
- Do you already have a Customer database you can refer to?
- Ditto, Product database?
- Can the SalesPerson reference one of the persons in the Notes Name&Address Book?

Uniqueness of Company is usually guaranteed by the company's name (else lawsuit). Probably not the answer you want to read, but most Notes application work this way. Usually, an internal unique number is generated or taken from Notes itself, for every document has a Universal ID. If you store this number in the document itself and use it for referencing, then even copies of the database will have the correct references. The Universal ID had better be used only internally, it is a 32-byte hex id. Moreover, almost all unique number generators will fail somewhere, mostly when the application has to run on multiple servers or locally on notebooks or so.

The Project Update documents could be response documents to a Project document. Did you already look into response-views and the way to handle these documents??
0
Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

 

Author Comment

by:AliciaVee
Comment Utility
All,

Okay -- I'm with you all and have read all of these posts.  -- and yes, I can do a select query/join in the Access database to provide the needed UniqueIDs, as suggested by  qwaletee so that the data I need will be in a file that I can import into Notes, using the form and creating new documents for a view.

yes, the salesperson reference is someone in the Notes Name&Address book -- and when I pull the company data, this name is stored in the file that I will be using to import the data into the Company form, to create new company documents, to populate the view, which will be used for a lookup.  

But I still don't understand how to ensure that when I update data, for my lookupview (say my Company or my Product) that I don't add new documents that might already exist?  I know I am probably not seeing the obvious here.

Example:  I do my first import of Company data, into my Company form and create new company documents to populuate my lookup view.  There are 3000 documents.

A month later, I extract new data from our internal system and now there are 3233 records.  How do I import them into Notes, and ensure that only the new 233 records will be added?  Can this be done?

Or, is it better then to compare the data (which was last importted into Notes) and indefiy the records in Access, pulling out the 233 and then import those new records into Notes?

Ugh!  I guess this would work -- I'm just finding it difficult to think longterm in terms of updates, like how I am doing them now.  If I have a new sales rep -- now in Access, I change the name of the old sales rep, to the new one that took their place, and when I do this, all projects with this sales rep is now update.  I know this is the basis of relational design, I am just trying to understand how updates are going to happen in Notes?

sjef,  your question on respose-views?  I'm not sure what you mean?  Is there something spefici that I should understand about response/views?  I have a couple of manuals that have good walk throughs on creating discussion type of views and I think I'll be okay -- but if there is something key -- please let me know.

AliciaVee

0
 
LVL 19

Expert Comment

by:RanjeetRain
Comment Utility
>> Or, is it better then to compare the data (which was last importted into Notes) and indefiy the records in Access, pulling out the 233 and then import those new records into Notes?

This isn't all that difficult. Most appliations generate UniqueID in a serial manner. The keys must match some pattern and must provide uniqueness. Then, using a view that is sorted 'descending' on the KEY, it won't be difficult to figure what the "Highest" imported key, will it? Rather than compare all imported values, I will use this 'HIGHEST' imported key metaphor to decide what documents are due being imported.
0
 
LVL 19

Expert Comment

by:RanjeetRain
Comment Utility
>> If I have a new sales rep -- now in Access, I change the name of the old sales rep, to the new one that took their place, and when I do this, all projects with this sales rep is now update.  I know this is the basis of relational design, I am just trying to understand how updates are going to happen in Notes?

I addressed this iin my fisrt post. Use @DBLookp. If your values are not physically stoed in the document, how does it really matter if the refrenced value changes. It may change a million times and every time a document opens, it will open with the newest value.
0
 
LVL 19

Expert Comment

by:RanjeetRain
Comment Utility
Correction!!!! This is assuming that the import has been successfully done in DOmino. Import of changed values, NO OTHER WAY than comparing all the values and importing as necessary.

UNLESS, you are willing to make a design change in the original Access application. Add a FLAG column, say UpdateInDomino, to all your tables. Anytime your data is updated in your access table, set that field to ON, means ready for export to Domino.

An alternative will be to maintain a "LastModified" cloumn in your access tables. Then you can easily filter records to be imorted into your Domino app based on when was the last import done.

0
 
LVL 46

Expert Comment

by:Sjef Bosman
Comment Utility
Two things.
1. Do you want to copy records repeatedly from your repository system to your application? Then what you need is something called Lotus Enterprise Integrator (used to be NotesPump). This tool is able to compare a Notes database and a non-Notes database, and transfer missing or changed data. You do have to tell it how, though... I think you still need a separate licence to use LEI.
2. With response-documents you can create a document hierarchy, not necessarily only a discussion database. The advantage of a discussion db is that documents will never be removed. Once you start chopping the hierarchy-tree, lots of funny things can happen. If you delete a parent document, the children will remain as orphans: they won't show up in a response-view. They do however show up in a flat view. I like response views because they show the intricate relationships between a hierarchy of documents, but I always need a set of functions that maintains the hierarchy.
0
 

Author Comment

by:AliciaVee
Comment Utility
All -- okay -- I think I"m good now. I'm going to take a deep breath -- and jump in!  Based on what you have all said -- my initial understanding of how this is all going to work has been confirmed, and I have gotten some other very good viewpoints and things I should take into consideration -- so thanks!

sjef -- I've read about Notes pump -- and it sounds like a great tool.  Unfortunately for me, the datasource that the company data will be coming from is our ERP system and the powers that be do not allow links to it -- only extracts.  And yes, I will be doing a document hieracchy to ensure all descendant documents will be displayed with each Main Document -- and no -- these documents cannot be deleted -- or will not be -- so hopefully, no orphans will occur.  Will keep your tips in mind.

Thanks all!

AliciaVee
0
 
LVL 46

Expert Comment

by:Sjef Bosman
Comment Utility
Ask the powers what the difference between a link and an extract is, and that NotesPump only extracts if you want it to, because NotesPump has to login as any user to the ERP system. You can even let them manage the NotesPump system...

Company policies are like windmills: hard to fight. I've met a few ;)
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

For users on the Lotus Notes 8 Standard client, this article provides information on checking the Java Heap size and adjusting it to half of your system RAM in attempt to get the Lotus Notes 8.x Standard client to run faster.  I've had to exercise t…
Article by: Rob
Notes 8.5 Archiving Steps and Tips This article covers setting up a Notes archive, and helps understand some of the menu choices making setting up and maintaining a Notes archive file easier.
Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

728 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now