How to populate two fields with 1 drop list selection?

[DonRidley]

I have a drop down list on a form.  The drop list has two fields:

Security Level and Security Number

For the User I have the same two fields.

When the user makes the selection on the drop list I want:

User Security Level = Drop Security Level
User Security Number = Drop Security Number

Thanks!
Don

[Bruce]

Hi Don,

the answer is really simple - there's a routine in the form called ValidateValue::xxxx which is called for each field as it is entered (if the field is dynamic) and when the save button is pressed. So that's the place to copy data from one field to another. Be sure to store it in the actual file field AND the SessionValue as well.

If you're not sure, post a small example here (mod one of the shipping examples) to show the layout, and someone can inject the embed code for you.

cheers
Bruce

[DonRidley]

Thanks Bruce!! 

[DonRidley]

Haven't figured it out.  I've attached the app in a ZIP file.

The drop down in question is in the UpdateUSERS procedure.

Basically I want the user to select 1 item from the drop down (ie., Super Admin, Admin, User, etc) and populate the User Level field and the User Security Number field.

Login for the app is:

User Name: Demo
Password: Demo

App is set to listen on port 88
Created with C 7.2 7283
NT 5 PR 25

not case sensitive.

I would appreciate any help.  Also, if you have time, look around and see of you have suggestions on how to do anything better.

Thanks!
Don

[attachment deleted by admin]

[Bruce]

Hi Don,

Ok, the fundamental complication here is caused by the fact that you're not normalizing your database correctly. In other words what you're doing is fundamentally "wrong" - although yes, you can do it.

What you should be doing is storing the SecurityId value in a Users:SecurityId field. That way you would have access to the security table, and hence to the SecurityLevel and SecNumber. If wither of these details should be changed then _all_ the users on that Security Id would "get" the change.

But ok, you may not be in control of this, so you have what you have. There are two basic problems here;

a) when the form opens in change mode you need to prime the drop-down to select the correct record from the Security table. You don't actually know which record to display because the one thing not stored in the Users table is the Security Id.

b) when the form is completed you need to read the values from the security table, and store them in the Users table.

So here's what I suggest you do.
a) remove the Users:SecurityLevel field and Users:SecNumber fields from the form.
b) create a local field, say loc:SecurityId to take its place. This variable will be primed when the form is opened, and will be used when the form closes.

The code, when the form closes, looks something like this;
  Access:Security.Open() 
  Access:Security.UseFile() 
  Sec:SecurityId = p_web.GSV('loc:SecurityId')
  Access:Security.Fetch(Sec:KeySecurityId)
  Users:SecurityLevel = Sec:SecurityLevel
  Users:SecurityNumber = Sec:SecNumber
  Access:Security.Close() 
  p_web.SSV('Users:SecurityLevel',Users:SecurityLevel)
  p_web.SSV('Users:SecurityNumber',Users:SecurityNumber)

It goes into the ValidateRecord routine. So it will be executed when the user clicks on Save.

The second bit of code should go at the top of the PreUpdate routine. This is where you determine, from the current values in the User file, what the loc:SecurityId should be. Something like;

  Access:Security.Open() 
  Access:Security.UseFile() 
  set(Sec:KeySecurityId)
  loop until Access:Security.Next()
    If Users:SecurityLevel = Sec:SecurityLevel and |
       Users:SecurityNumber = Sec:SecNumber
       loc:SecurityId = sec:SecurityId
       p_web.SSV('loc:SecurityId',loc:SecurityId)
       break
     end
  end
  Access:Security.Close() 

I haven't run all the above, so you may need to check the code etc.

Personally I'd normalize the database - unnormalized data causes lots of work pretty much everywhere they go.

cheers
Bruce

[DonRidley]

I do have a SecurityId field in the Security levels file and it will be no problem to do what you suggest.  Sometimes I can't see the forest for the trees....

THANK YOU!!!!