Look-ups

[Megee]

if i am doing a lookup of a customer last name and there more than one 'SMITH' in my table, can i get an error message to display and possibly ask me which 'SMITH' im looking for?

[Reed]

Here is one possible way to do it:

Create a global field in the table for which you want to look up the names. In my example I have a names table and an orders table. I would type the last name of a customer into the global text field in the orders layout. Tabbing out of this field will bring you to a popup list of all customers with that last name where you can choose the correct one. I have chosen to reference customers by a serial number in case there could be people with the same first and last name.

The file is attached. Look it over and let me know if you have any questions.

Dana

names.fp7.zip

[transpower]

Professional programmers use a unique customer ID for each customer record.

[Vaughan]

Some amateur programmers use unique IDs too!

[Megee]

should be what i need, BUT im only using the lookup to fill in related fields (address, phone number) so is there a different way about that or should i try this global field?

[Reed]

What I would suggest is to modify my file to base the lookup on the customer id field which is populated from the value list. So you would type a last name into the global field, choose the right person's id by seeing the list of names in the popup list, then the rest of the fields to be looked up would be based on the unique id. That way there wouldn't be any ambiguity about names etc.

Another thing you might want to consider is whether you want to look up the address, phone, etc. each time you create a related record (like an order or invoice) or if you simply want to display the related information directly from the other table. Lookups are good for things like prices because when they change you want to keep track of what the price was at the time of the order, not the current price. But usually for contact information, you want to know the current information, and then by updating it in the customer table, the proper values show in the order layout.

I've attached a modification to my file that illustrates this point.

orders.fp7.zip

[Megee]

im having trouble getting the multiple records to pop up. ive done what i could to make my file like yours, but i cant tell everything because your file is not modifiable. is that on my end or yours? is it the tab order im having trouble with because i cant go from my global field to the "cust id ::full name field"

[Reed]

I'm not sure why you can't modify the file, it's set to automatically open with full access. Check in the OS to see if the file is locked or set to read-only for your windows account.

The multiple records will only pop up in my file based on what is typed in the global field. If there's no match, then no values will show.

I've place the cust_id::full_name related field over the top of the id field for display purposes, but I've set it not to be enterable in browse mode. So it's not included in the tab order. The id field behind it is in the tab order after the global field.

[Megee]

do you have any idea what step i should take to make the multiple names pop up?

[Deep Thought II]

isn't there an option in value list to display values from two fields? in that case, first name and last name can both be displayed...?

but displaying only last name can be confusing too... if there are several same lastnames... still a Customer ID for relationship is better... or display its related fields on the layout once a value is selected can prevent confusion as well...

[RyanD]

I am new to this forum or any forum for that matter. Have you set the tab order for your layout, also have you specified which pop up list you want displayed?

Deb

[Reed]

Maybe you could post your file so everyone can try to figure out the problem.

[Megee]

ok, im attaching my file. let me try to explain exactly what im trying to do.

only working with 3 layouts: WMU, prof, and dept

on the main layout (WMU) i want to type in Lname and have everything fill in. by everything i mean the department at the top of the layout, the professors phone number(s), title, email, and customer ID. also with the department i want their number and address to fill in. i have this much working. now when i go to type in a professors last name say "SMITH" i want an alert to let me select which "Smith" info id like to copy into my layout just like REED(username) created for me earlier. take a look at my relationships and field attributes because i cant tell from REED's example how mine differs.

come on back with some questions if you have them.

t

FM7.zip

[Reed]

I've modified your file.

I added the necessary value list at the end of all of your other value lists and called it popup id list

Then I changed the title and full name related fields on the WMU layout so that they are from the prof 2 table occurrence. (The one based on id)

Check it out and see if you have more questions.

Dana

1096489339-t_scopyofwmu.fp7.zip

[-Queue-]

Nice job, Reed. It needs a couple of changes though. Use prof 2 for the office and other phone fields and email field, and match the dept TO to prof 2 instead of prof, so that the address shows for the selected prof's department, not the first related prof's department based on last name.

[Reed]

Yeah, I didn't look at the whole layout to find every thing that was wrong, I just wanted to show how to do it.

[-Queue-]

I figured as much. I just didn't want Megee to be confused about it.

[Megee]

can i say WOW?!?!?! that is exactly what i was trying to accomplish. now the next step is to get the phone number fields along with the email fields to change with each name. i think i can take care of that part tho. thank you and maybe you will be able to help with my next fine tuning step!

travis

[Megee]

may i ask, when QUEUE said to use prof 2 for the phone fields and email do you mean to remove the current fields from the layout and replace them with the fields from prof 2?

t

[Megee]

dont answer that one, i got it

[Megee]

something happened to the point where my options for last names arent popping up now. maybe you can look, or maybe you can tell me what i should do.

funstuff.zip

[-Queue-]

Your value list for names no longer exists. I don't see any field to use for the pop-up list either.

[Megee]

thanx for looking. all i did was copy/paste the file so we could enter data in one and play with the other and the name list wouldnt pop up.

[Megee]

i have it back where i want it except for the fact that my popup list doesnt popup with the names. by looking at REEDs example i cant tell what option does pops up the list. where should i look? my latest file is attached if you would like to look to see where im at.

funstuff.zip

[-Queue-]

Um, actually, it does pop-up. You have to click or Tab into the field first. You may want to redo the Tab order (Layouts -> Set Tab Order, in Layout Mode) so that it isn't the last one in the list.

[Megee]

ok, i thought originally that reed mentioned that the popup field was left out of the tab order. thanx for your help.

[Reed]

Nope, it's the related fields that are left out of the tab order. They are just for display purposes. I placed a related field directly on top of the popup field and made it exactly the same size so it would look like one field.

[Reed]

Oh, and BTW, I *love* that they fixed the type-ahead behavior in popup lists sorted by the second field in the v3 update.

[Megee]

ok, now im wondering one of two things. first, how do i make the first item in my popup list selected?...it seems so easy but i cant find the option. the closest i see is to select entire contents upon entry. secondly, which part of the validation do i choose if i want an alert message box to tell me the last name entered does not exist in the professor database?

[Reed]

Use the auto-enter option to look up the id value based on the related table occurrence that's used to make the popup list.

For the validation, you'll want to validate by calculation... something like:

not IsEmpty (relatedTable::idField)

Then if there are no related id values for the name entered in the global, the error message will appear.