Value not in list

Hi there,

I have various fields doing lookups on a form in the following order:

Location - Category
Sub Locaton - Subcategory

All working fine using a lookup view and a mixture of @dblookup and @dbcolumn

Now for all these field, I am allowing the user to enter values not in the list but I want this value to be updated in the lookup view(Keyword form) so that when the client selects this same location next time, the newly added value(by client) will be in the list of sub location without the administrator having to update the keyword form manually. Is this possible?


varvouraAsked:
Who is Participating?
 
marilyngConnect With a Mentor Commented:
If you have a combination of a form that has the following list:

FormList:=thisFormfield;
LookupList:=@dblookup(......);
LookupListErr:=@if(@isError(LookupList);"";LookupList);
NewList:=@Trim(@Unique(formlist:LookupListerr));
newlist


Use newlist in your formula.
0
 
madheeswarConnect With a Mentor Commented:
OfCourse it is possible.... If I am not wrong, you should be having a seperate form for updating Keywords.
Either you can append to the existing list using doc.AppendtoTextList or you can create a new document by checking if the new value entered is existing or not and if not, then create new document by db.createdocument...

Hope it helps.
0
 
shuboarderConnect With a Mentor Commented:
I know what you mean, and AFAIK it's not possible to actually 'add' to the keyword box that pops up.

What you could do however is create a new form and view for the @dblookup and @dbcolumn
Then have your main form fields as comboboxes looking up this new keyword view.

You could then create a button on the form called "add keyword" which would create a new document in the keyword view.

The problem I see with this though is getting the combobox to refresh its content without closing and reopening the form. The "refresh on keyword change" etc. will not work here...
0
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

 
marilyngCommented:
Uh, if the fieldname is Location, and  I have four forms with a value in each:

AAA
BBB
CCC
DDD

and my view column formula is LOCATION: categorized, ascending.

and I add a new keyword: EEE in a new form that is collected by that view, then

I will get
AAA
BBB
CCC
DDD
EEE

If I add another document with a location of EEE I will see in my view:
AAA
BBB
CCC
DDD
EEE
EEE

So, when I do a lookup to the column, I need to remember to say: @Trim (@Unique(List)), so I get the entry only once.

To make things even easier, I should probably do a translation formula to make the value either all CAPS, or all Lower or Propercase.

Also, the view needs to be set to refresh automatically, and go to the first row, rather than display the refresh arrow.


0
 
varvouraAuthor Commented:
Marylin,

I am going to take you up on that challenge and try your suggestion. I am going to post back if things don't work out.

However, the second part to this question was:

I have field "location" on the form.
I have field 2 "sublocation" on that same form

They are both doing a lookup to a view, sorted but not categorized. The lookup as I said earlier is great. But now that the client wants to be able add value of location or sub-location that not in the existing list(add value not in list) in the field on the form. some field validation should be in place to double check if a value of location or sublocation exist, if so, to prompt the client that this location exist already in the list.

Is there an easy way to do this? I found when I tried to attempt this may problems espcially the ability to refresh the field to check it again the list in the lookup.

I hope I didn't confuse you but somehow I think you can work out what I am trying to get at.

Anyway, I'll try your first bit first before I tackle the next bit, which is what the client really needs
0
 
varvouraAuthor Commented:
Marylin,

Your code

FormList:=thisFormfield;
LookupList:=@dblookup(......);
LookupListErr:=@if(@isError(LookupList);"";LookupList);
NewList:=@Trim(@Unique(formlist:LookupListerr));
newlist

Now, i have the following code as the combo box(location field) formula

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

Should I replace my formula with yours, is that what you mean?
0
 
varvouraAuthor Commented:
Worked out what I needed to do, Here's the results:

Added the additional value to the list but it didn't update the value in the view, why would it anyway, we're not catering for it anywhere?

So when I create a new customer, the manually added value doesn't appear on the list of lookup.



0
 
shuboarderCommented:
Sure, but how does that solve your problem?
0
 
varvouraAuthor Commented:
It doesn't solve the problem that I am looking to solve, but it is nice to have. For example, I have location A and sublocations for location A are LA, New York, now if when I am creating a document, I select location A, and I found that I need to add a value, say, Canada, this should save to the Keyword lookup view with the rest of the list. So next time, I create another document and I select location A, the lookup will bring me back New York, Canada, LA.

Its kind of nice to have for customers.

2nd issue, was that if I have New York in list of sublocations and I accidently added New York, in the combo box, as it does allow to enter values not in list, I would like a checking to happen to avoid saving, New York once again in the list. That's the important bit
0
 
marilyngCommented:
The suggestion I am giving is dynamic, that is your lookup view can contain the document with the maintained list:

Keyworddoc, keywordlist="A":"B":"C":"D"

And contain the documents having the field Location
thisDoc, Location<>""

((Form="Keyworddoc")|(Form=("thisDoc") & location<>""))
Lookup view.  Automatically refesh fields.
FirstColumn = @if(form=keyworddoc;keywordlist;form=thisdoc;location).

If you add "New York" in three documents, it will appear three times in the VIEW.  If you don't want it appearing three times in your option list, then you use the @Trim(@Unique(@Uppercase(thisListResult)))

This won't stop people from adding "Neu York", "NY", "ny", "newyork"

The formula I supplied is a suggestion, and incomplete.  It will not work if you paste it without your modifications.
0
 
marilyngCommented:
2nd issue, was that if I have New York in list of sublocations and I accidently added New York, in the combo box, as it does allow to enter values not in list, I would like a checking to happen to avoid saving, New York once again in the list. That's the important bit << is another question.

Let's not muddy the issue by adding new questions, then you will have many EE's providing an assortment of answers.

If you want your dynamically added location to be added to your lookup, then you need to add your form to the
viewName := "locationview" ;

and add the field, "Location" to the second column formula.
0
 
marilyngCommented:
So, my first post solves your problem, you just haven't seen it yet.
0
 
varvouraAuthor Commented:
Uh, the reason why I overlooked this because I had my lookup set up differently, based on one keyword document, that's all.

I think I may take up your recommendation in regards to this one.

I'll advise in five
0
 
varvouraAuthor Commented:
Won't work!

Just to remind that I am using the keyword document to do all the lookups even for the location and sublocation. Using the location document will be a good idea if I have all the location populated automatically in field without lookup, i.e. hard coded in dialog box, which is not true in my case.

I know now what you mean in your first posting, just create a view based on the location doc and sorted, then use it for lookup. I wish it was that easy.

0
 
varvouraAuthor Commented:
Marylin, I just refreshed my browser and saw your posting.

I didn't see this one before, I though you were talking about the first posting you had

MY APOLOGIES!!!

0
 
marilyngCommented:
There are any number of ways to solve the problem.  you can combine the hardcoded document and the location field documents in one view, you can use two different views and combine the lookup results.

Please try to think a bit more abstractly when applying solutions.  All the solutions we provide are valid, but because we are not creating your application, we cannot be specific, we can only provide ideas and guidance.  It is up to you to extrapolate and find creative ways to apply the suggestions.

Also, know that when we make suggestions, we are coming from many years of development and are suggestion solutions that we have used MORE than once, so, of course, we KNOW they work.  if we don't, then we say so, right up front. :)
0
All Courses

From novice to tech pro — start learning today.