Populating fields based on a single value list

[brosenberg]

Hello,

I hope I can explain this correctly. I have two tables, one with client information (name/number/etc) the other is a job ticket table.

What I'd like to do is have the user pick a clients name from a value list in the job ticket table (that's related to the client name field in the client info table) and have the rest of the clients information auto populate in their different fields.

Currently I have the relationship set up correctly between the client name lists on each table, but I don't know where to start to be able to pick a client name and have it auto fill the remaining fields on the job ticket table. Would this be something I would have to script?

[aldipalo]

Setting up a relationship by name will almost always create problems for you. You should set up the relationship based on a serial number that is unique to each client. then set your relationship to that unique key.

Client Table Orders Table

ClientID = fk_ClientID

Now you can have Client Name; Client Address; Client CityStateZip on the Orders layout and by choosing the clientID all the data will be present.

See attached file:

Make sure you look at how the ValueList is set up.

HTH

Al

[brosenberg]

Hi

thanks for the suggestion, I'm not sure I fully understand what you're telling me though. If I switched to using a ClientID would I then be using the ID number in the Orders table to make it auto-pop the rest of the client info? That probably wouldn't work for the people using the database. Maybe I explained it wrong?

Regardless if it's a client ID or name, how do I actually get the information to auto-populate in other fields based on what is chosen in a value list?

Also, for some reason I can't seem to get to the attachment, would it be possible to send it to me directly?

Edited September 28, 2007 by Guest

[Ocean West]

Can you please repost the file so others can download it... the system didn't actually accept it. :sorrysign:

[aldipalo]

What I am saying is that having a name as your key field can create problems for you. What if your client changes their name? All related data must be changed at that time or you lose it. By having your key fields a unique serial number all the other data in the parent record can change and you will not lose the related data.

I have re-posted the file. I hope it takes. Please look at the value list which shows both the ClientID and Client Name. This way your user chooses by client name, but the stores the ClientID in the record. The Client data shows up in the Orders layout by putting the client fields on the same layout as the orders. So look at the orders layout and the valuelist and you should have a pretty good idea how to set this up. Let us know if you need any more help.

Al

**********************

I added a technique where you can store the clientID but only see the Client name. Check out Client Test2. Look at the orders layout. By placing the Client Name field over the clientID field you can hide the ID and only show the Name.

HTH

Client_Test.zip

Client_Test2.zip

[brosenberg]

Hello again,

Sorry to do this but for some reason I can't get this thing working. I took a look at the client test files, especially client test 2. I think I understand most of what's going on in it, however I can not get it working in my database. I've looked through it and emulated everything I could see, but even when I started fresh on a new file I still could not get it to do what Client Test 2 does.

So far, I've got my Client Info layout/table with the one number field that's for the clientID and the rest of the fields are just plain text fields. I have my job ticket field that contains the fk_clientID (which is related back to Client Info::ClientID) as well as the fields from the Client Info Layout. I have the value list for clientID set to show data from both the clientID as well as client name. I set up the Client Name field in my job ticket layout as a button to do Go to Field with the box for Select/perform checked as well as the box for Go to field checked. The syntax seems to match what you have in Client Test 2.

The problem I'm running into at the moment is that when I click on the Client Name field in my Job ticket layout, it doesn't do anything. I can't enter text and I get no dropdown of names/id numbers. I'm sure I'm missing something simple, but I can't figure out what it is.

I've attached my fresh-from-the-start file I tried making to emulate the client test 2 file.

my_test.zip

Edited October 3, 2007 by Guest
Added example file

[aldipalo]

Ok, You must peal apart the name field. Under the name field is the ID field. When you click on the name field a script takes you to the ID field where the valuelist resides. You choose the value list item and it only shows the name and populates the rest of the fields with that chosen data. The ID field is set so that it does not show and resides under the name field.

hth

my_test.zip

[brosenberg]

:thankyou:

Wow, I never would have thought of "stacking" fields on top of each other. Thank you once again. I will definitely keep that one in mind.

[aldipalo]

I wish I could take the credit for it, but, everything I know, which isn't a lot, I learned on this forum. :grad: