Field Rules, Lookup, question.

[Sallenss]

New User, two weeks old, my first post.
I have spent a lot of time reading the Message Board and I have learned a ton from you all. Some of my mental concepts are still corrupted from using an old DOS DBMS for the last 15 years.

My problem/question: I'm trying to define the field rule for last name in Alpha Sports salespeople table as a lookup for the record to be displayed in the form sales_people. Once the record is selected in the lookup, the form only updates the lastname and first name and sometimes the photo, but the sales_person_ID never updates on the form.

I see lots of reference to lookup records from another table, but what if you only want to use the lookup to find and select a record from the same table the form is based on? How do I set that up? or is my concept off the mark?

Thanks in advance, Steve

[aschone]

Re: Field Rules, Lookup, question.

The reason the Sales_Person_Id field does not change is because it is configured as an auto increment field.

I think for what you are wanting to do you should use an index lookup instead.

Open the Sales_People form. Click on "Query" on the toolbar, choose "Select Index/Query". Highlight the index called "Lastname", click "OK". This set the table so you can search on last name.

To search the table, click on "Find", choose "Find by Key". Enter the name you are looking for and then click "Find".

Hotkey
Ctrl-I (sets index)
Ctrl-K (Find by Key)

[Sallenss]

Re: Field Rules, Lookup, question.

The reason the Sales_Person_Id field does not change is because it is configured as an auto increment field.

I think for what you are wanting to do you should use an index lookup instead.

Ok, but Sales_Person_ID is also an Index, does this explude Auto Increment fields from Form Updates when looking up an existing record? If so the Sales_Person_ID number would be worthless.

Open the Sales_People form. Click on "Query" on the toolbar, choose "Select Index/Query". Highlight the index called "Lastname", click "OK". This set the table so you can search on last name.

To search the table, click on "Find", choose "Find by Key". Enter the name you are looking for and then click "Find".

Aww, but what if I don't know the last name and/or anything else that is entered in KEY fields BUT, I'd probable remember who/what I'm looking for if I saw it in a small list of available records?

You are saying I should do a "Browse" instead of a simple lookup?

Hotkey
Ctrl-I (sets index)
Ctrl-K (Find by Key)

[aschone]

Re: Field Rules, Lookup, question.

Field rule lookups are designed to enter or modify a record. I.E. create a new record, perform lookup to external table, populate new record with data from the record choosen in the external table. Hence why a table rule lookup does not work in your case. When you do the lookup, you are actually changing the values in the current record.

Autoincrement field rules are configured to handle themselves. Once configured as auto increment you should leave the field alone, it will take care of its self.

From your last post it sounds like you want a "progressive lookup". That has been discussed numerous times on the board.

I do not recall mentioning anything about browse in my first response, but if you want to use a browse feel free.

[DaveM]

Re: Field Rules, Lookup, question.

Steve,

If I am reading your post correctly??? It sounds like you are trying to find an existing record??? Try using the find on a form/browse. Experiment a little. You can use it to bring up the record by what ever field you choose.

[Sallenss]

Re: Field Rules, Lookup, question.

Andrew, yes, my concept was off. Thank you for a better understanding of the field rule lookups. Yes, the result was that I was changing the data each time, therefor the data in the Salespeople.dbf is completely useless. At one time all sales people had the same last name.(lol)

I will learn more about when and where to rely on autoincrement fields, your warning is well taken.

I will learn about progresive lookups next and try that, too.

Thank again you for your input, I hope to repay someday.
Steve Allen

[Sallenss]

Re: Field Rules, Lookup, question.

Dave,
Yes, I was trying to find an existing record. Once the desired record was found I wanted to update information, like address, cell phone, etc.
It looks like I have a way to go bending my brain around to a new way of thinking.

I'll try your suggestion about "find" on form/browse.

I really appreciate your and Andrew's help.
You guys are great!

Steve

[MoGrace]

Re: Field Rules, Lookup, question.

Hi Steve.
An app based on DOS does things the way you described. A value entered into an empty form either finds the record or starts a new one. But in Alpha a blank form is a new record. You can have this DOS kind of functionality in Alpha too, but it will take a bit of designing. I use such a field in my Employees database, where I can type in a file number and go directly to that record without changing anything in the record that is currently displayed.

The find by key dialog is pretty much the same thing and the field you want to search on can be changed by selecting a different index - which you must first define for the table.

[Sallenss]

Re: Field Rules, Lookup, question.

Robin,
I too was puzzled by forms opening populated with data rather than having to use a search or lookup to get data. Something I took for granted.

Old DOS DOG (ODDs) here (TAS 3), I'll have to learn to adjust to alternate realities. It would be nice for ODDs if Alpha would create some options compatable with old dos apps, it sure would make the learning curve shorter.

If you could brief me on how to open a form blank...sans data, we should start a new thread. I'll search for a current thread, too.

Thanks for your input, it helps to know there are others who have struggled before me.
Steve