Value not in list

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.

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

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?

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.

Sure, but how does that solve your problem?

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

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.

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.

So, my first post solves your problem, you just haven't seen it yet.

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

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.

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!!!

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. :)