varvoura
asked on
lookups
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
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
ASKER
Hi madheeswar,
This is a notes client app, I'll try your notes suggestion and will let you know shortly.
This is a notes client app, I'll try your notes suggestion and will let you know shortly.
Then let me brief the logic....
{This holds all the Customer Names};
res:=@Iferror(@DbColumn(""
{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;currentc
Hope you got the logic and can change where ever their is need to change in the code.
{This holds all the Customer Names};
res:=@Iferror(@DbColumn(""
{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;currentc
Hope you got the logic and can change where ever their is need to change in the code.
ASKER
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(""
@if(@Ismember(res;clientna
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
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(""
@if(@Ismember(res;clientna
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
ASKER
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
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
res:=@Iferror(@Unique(@DbC
@if(@Ismember(res;clientna
Hope it helps ...
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
res:=@Iferror(@Unique(@DbC
@if(@Ismember(res;clientna
Hope it helps ...
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..
ASKER
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(@DbC
@if(@Ismember(res;clientna
NOW WHAT DO YOU THINK?
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(@DbC
@if(@Ismember(res;clientna
NOW WHAT DO YOU THINK?
ASKER
OK, wait a sec, I am thinking maybe your suggestion will be OK, give me five
ASKER
Nope, something is still not right.
ASKER
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?
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?
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
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
ASKER
Do you a configuration document for the entire set of fields that need to do a value lookup?
No....only the required fields which need to get displayed when it is a new Customer.
ASKER
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?
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?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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(@DbC
@if(@Ismember(res;clientna
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!
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(@DbC
@if(@Ismember(res;clientna
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!
Notes:
use mix of @DbLookup and @dbColumn
like ...
res:=@Iferror(@DbColumn(""
@if(@Ismember(res;currentc
This will help I think. Viwe should be sorted and first column should have Customer Name.