[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

lookups

Posted on 2006-05-17
18
Medium Priority
?
334 Views
Last Modified: 2013-12-18
Hi,

I have this fantastic database which has a few forms. Many of these field on those forms are performing view keyword lookup from a lookup view based on a keyword documents.

Now, I want to make this lookup a bit more sophisticated. For example, at the moment, one of the key lookups is product code, this field does a lookup and all the product codes are populated in the product code field. But I want to be a bit more specific, I want to be able to store and lookup product codes based on a customer, for example, if customer A is stored with products, (A, B, C) and I want to go back into that customer document and change the product field, I want to be able to view in the drop down list(A,B,C) only and not the rest of the product codes which may belong to other customer.
The second trick to this, is to populate the entire product list if the customer is a new customer, which does not exist entirely in the database. However, if it is a new doc, but the customer exist in db, I want to be able to pull out only the customer specific list.

Now, is this possible?

Thank you all in advance
0
Comment
Question by:varvoura
  • 10
  • 8
18 Comments
 
LVL 19

Expert Comment

by:madheeswar
ID: 16697618
First, is this Notes or Web?

Notes:
use mix of @DbLookup and @dbColumn
like ...
res:=@Iferror(@DbColumn("":"NoCache";"";"Viewname";1);"");
@if(@Ismember(res;currentcustomername);@iferror(@Dblookup("":"Nocache";"";"Viewname";"CustomerNameasKey";Columnno);"");@iferror(@Dbcolumn("":"Nocache";"";"Viewname";ColumnnowithallData);""))

This will help I think. Viwe should be sorted and first column should have Customer Name.

0
 
LVL 19

Expert Comment

by:madheeswar
ID: 16697626
0
 

Author Comment

by:varvoura
ID: 16697831
Hi madheeswar,

This is a notes client app, I'll try your notes suggestion and will let you know shortly.
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 19

Expert Comment

by:madheeswar
ID: 16697869
Then let me brief the logic....
{This holds all the Customer Names};
res:=@Iferror(@DbColumn("":"NoCache";"";"Viewname";1);"");
{Check if the Customer is a member of res and if he is a member, then get corresponding values using @dblookup, else get all the values using @dbcolumn};
@if(@Ismember(res;currentcustomername);@iferror(@Dblookup("":"Nocache";"";"Viewname";"CustomerNameasKey";Columnno);"");@iferror(@Dbcolumn("":"Nocache";"";"Viewname";ColumnnowithallData);""))


Hope you got the logic and can change where ever their is need to change in the code.
0
 

Author Comment

by:varvoura
ID: 16697931
This is my understanding of what you trying to do in your formula, pls correct me if I am wrong.

1)First, keep my current lookup view as is?
2) then in place of my current formula which i have behind lookup field, as below,

class := "Notes":"NoCache" ;
db := "" ;
key := "Product No" ;
viewName := "lookup" ;
column := 2 ;
result := @DbLookup( class ; db ; viewName ; key ; column ) ;
@If(@IsError( result ) ;
@Return( "" );
@Unique(@Trim(result)) )

Where in this case Product No is the first sorted column in the lookup view.

Replace it with the following:

1) Add the customer name from the customer form as the first sorted column in the lookup view

2) then replace my above formula with the following:

res:=@Iferror(@DbColumn("":"NoCache";"";"lookup";1);"");
@if(@Ismember(res;clientname);@iferror(@Dblookup("":"Nocache";"";"lookup";"ClientName";Columnno);"");@iferror(@Dbcolumn("":"Nocache";"";"Viewname";ColumnnowithallData);""))

Where in above
clientname = ClientName field on form, not a value(Is this right)
ClientName=ClientName field on form, which means that they are both the same
columnno=Is this suppose to be a newly created column in view
column with all data, is what I currently have





0
 

Author Comment

by:varvoura
ID: 16697947
nope, I don't think that'll work. As at the moment I have all these keywords created in the lookup view, if I create the first column as customer name, it will be blank
0
 
LVL 19

Expert Comment

by:madheeswar
ID: 16697949
I will give it in more detail ..
view: customerview
FirstColumn Sorted and value: Customername

On the Form, where u need lookups,place this formulae...
After selecting customer name(I assume users will select from list), else if it is based on UserName, then use that name(like @Name([CN];@username)..and make seure to use Common Name in the first column of the view)
res:=@Iferror(@Unique(@DbColumn("":"NoCache";"";"customerview";1));"");
@if(@Ismember(res;clientname);@iferror(@Dblookup("":"Nocache";"";"customerview";"ClientName";"ProductNamesFieldname");"");@iferror(@Dbcolumn("":"Nocache";"";"customerview";"ProductNamesFieldname");""))

Hope it helps ...
0
 
LVL 19

Expert Comment

by:madheeswar
ID: 16697952
then ..let me know how many forms need to be interlinked and where are the datas are stored with view names...just give in brief..
0
 

Author Comment

by:varvoura
ID: 16698606
OK, take a look at this

At the moment I have only the one lookup view which has the two column.
KeywordName(Key for lookup) and KeywordValue(column 2 in that view) which are the values populated in the field where I am placing the lookup formula.
Now the keywordvalue for each keyword are more than one. eg. product example above.

In using the formula above, I would lose all the stored keyword which I created through I keyword document for lookup.

The only that I think your idea would work is by doing the following, PLS correct if I am wrong.

1)Keep current lookup view as is,
 lookup viewname=lookup
 column1=keywordname - sorted
 column2=keywordvalue

This view is storing the entire keywords used in fields lookups and their values for all forms in db irrelevant for the customer name.

2) In addition to my view, create another view sorted by customer name as follows:

 second lookup view=customerview
 column1: clientname - sorted
 column2:clientcode(field on form, currently looking up value from lookup view)
 column3:clientstate(same as above)
column4:another form field using lookup from current lookup vie
columnn: ...
Until I place all fields from all the forms that are peforming lookup in various columns in that view

Now in your formula, we should change it to call one view or another depending on the existence of the client:
For example,


be able to call either of the views(lookup view via dbcolumn or dblookup as is now, if the customer is not a member of

class := "Notes":"NoCache" ;
db := "" ;
key := "Product No" ;
viewName := "lookup" ;
column := 2 ;

@If(@IsError( result ) ;
@Return( "" );
@Unique(@Trim(result)) )

res:=@Iferror(@Unique(@DbColumn("":"NoCache";"";"customerview";1));"");
@if(@Ismember(res;clientname);@iferror(@Dblookup("":"Nocache";"";"customerview";"ClientName";"ProductNamesFieldname");"");@iferror@DbLookup( class ; db ; viewName ; key ; column);""))

NOW WHAT DO YOU THINK?

0
 

Author Comment

by:varvoura
ID: 16698667
OK, wait a sec, I am thinking maybe your suggestion will be OK, give me five
0
 

Author Comment

by:varvoura
ID: 16698742
Nope, something is still not right.

0
 

Author Comment

by:varvoura
ID: 16699111
Sorry, my head is glued backward tonight.

I misunderstood your idea all together, it will probably work fine, however, to get a complete listing of all the options that I need in each field, there has to be a customer that already has these options, otherwise@dbcolumn won't pick them up.

example,

I have in db customer1 with products(A,B,C) which are already saved to db and in customerview

then I have in db customer2 with product(D,E,F) which are also in db in customerview

now customer3 is new but requires that (S,R,N,....) to be in the product list to be as well as (A,B,C,D,E,F) so he/she can pick up their product code. Where were the list of the additional product codes be extracted if they are not already saved in customerview where existing customers are? I know that @dbcolumn will give customer 3 options(A,B,C,D,E,F) to chose from but this is not the exhaustive list, hence my original suggestion of @dblookup to two different views, based on existence or none existence of customer.

DOES THIS MAKE MORE SENSE NOW?
0
 
LVL 19

Expert Comment

by:madheeswar
ID: 16700272
If S,R,N is not stored somewhere, how will you fetch? Only option is, make it a Dialoglist field and select Allow values not in list in the field properties. This way, they can enter whatever data they want.

Else best option will be, create a Configuration document, in that create a field for Product list. Database Admin will update the list one time, and if it is a new customer, then we can make use of this config doc (using @Dbcolumn or @DbLookup) and display..

This would be the best bet. And also, you can update this list in config doc, whenever there is need to new Product.

Best of luck
0
 

Author Comment

by:varvoura
ID: 16706776
Do you a configuration document for the entire set of fields that need to do a value lookup?
0
 
LVL 19

Expert Comment

by:madheeswar
ID: 16706915
No....only the required fields which need to get displayed when it is a new Customer.
0
 

Author Comment

by:varvoura
ID: 16716916
Which is pretty much what I am doing in the moment, I'll write down what I currently have in the database and you tell me whether or not what you mean is what I already have set up.

At the moment, I have a document called keyword(which probably what you call configuration), using this document, I have created the 20 keywords, their values and descriptions that I am using them for lookups from the various database forms. Then I have created a view called it "lookup" sorted by the first column which I am using as the key for all field lookups. For example, for field1, state, the key is state and is the first column in the lookup view, then the second column has the values of the state, which my state field on the form is picking up when it does the lookup.

Now is this what you mean by the configuration document? if so, then they'll be no need for me to recreate it. All what what I have to do then is to work with your formula to populate only specific customer details if the customer exists in the database.

What do you think?
 
0
 
LVL 19

Accepted Solution

by:
madheeswar earned 1000 total points
ID: 16716987
Yes...that is correct
0
 

Author Comment

by:varvoura
ID: 16724311
Then what I need you to tell me, how and where manipulate your formula to grab presaved customer details if it is not a new customer.

Now that we've worked out that I have the configuration document for lookup for new customers which I won't touch, will have to create a new view, which has customer name(customerview) as the first sorted column and all these fields in that view. Then would I change your formula in the productnamefield on the form as follows:

res:=@Iferror(@Unique(@DbColumn("":"NoCache";"";"customerview";1));"");
@if(@Ismember(res;clientname);@iferror(@Dblookup("":"Nocache";"";"customerview";"ClientName";"ProductNamesFieldname");"");@iferror(@Dblookup("":"Nocache";"";"lookup";"keyfield";2);""))

What I am trying to do above is to keep two views for lookup field on customers.
1)lookup view, sorted by a key as the first column and used for all new customers. I as an administrator will always make sure that this is an updated list of all productnames,etc..

2) which is the customerview, has the customername as the first sorted column, follow that the 20 column for all the other lookup keys.

The formula above then selects which view to read of based on the results of the first @dbcolumn and IsMember formula.

How does it all sound?
This is so cool!
0

Featured Post

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

  In today’s Arena we can’t imagine our lives without Internet as we are highly used to of it. If we consider our life style just for only 2 min we found that face to face communication is swapped by e-communication.  Every Where from Works place to…
Notes Document Link used by IBM Notes is a link file which aids in the sharing of links to documents in email and webpages. The posts describe the importance and steps to create a Lotus Notes NDL file in brief.
Are you ready to place your question in front of subject-matter experts for more timely responses? With the release of Priority Question, Premium Members, Team Accounts and Qualified Experts can now identify the emergent level of their issue, signal…
Please read the paragraph below before following the instructions in the video — there are important caveats in the paragraph that I did not mention in the video. If your PaperPort 12 or PaperPort 14 is failing to start, or crashing, or hanging, …
Suggested Courses
Course of the Month18 days, 11 hours left to enroll

834 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