AliciaVee
asked on
Using view Lookup / How to refresh data?
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
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
Sjef Bosman
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-Diagra
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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
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
>> 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.
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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
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
>> 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.
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.
>> 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.
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.
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.
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.
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.
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.
ASKER
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
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
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 ;)
Company policies are like windmills: hard to fight. I've met a few ;)