Tip

Filling in multiple fields without using a computed field

You Can View User Feedback To This Tip

There has been many times that I want the user to select an item from a drop down list and have multiple fields update according the choice the user made. The users usually want to be able to make changes to those fields if they need to. So, I have come up a solution using a button.

All of the following formula goes under the Click event of the button.

I create a button that assigns a @DbColumn function or two to a variable, depending on what I want the user to see in the drop down list. If I use more than one @DbColumn I create another variable that appends the results of my @DbColumn functions.

See below for an example:

ALPI := "85256A56:00663314";
p1 := @DbColumn( "" : "NoCache" ; ALPI ; "By Part Number" ; 1 );
p2 := @DbColumn( "" : "NoCache" ; ALPI ; "By Part Number" ; 2 );
p3 := p1 + " - " + p2;

I then use this variable in @Prompt function, which I assign to another variable. 
Example of the @Prompt function:
choice := @Prompt([OKCANCELLIST]; "Part Number"; "Please select an Part Number."; "";p3);

If I am using more than one @DbColumn, I use the @Left statement to pull the first item from the selection. 
Example: 
part := @Left(choice;" - ");
FIELD IMKEY := part;

Now I will use the part variable in a @DbLookup function that find the information I have set up in the lookup column in a previously created view, this column will have several fields appended together, separated by a special character such as "Y".

The view column formula should look something like below:

IMKEY+"Y"+Description+"Y"+P_C+"Y"+LEADTIME+"Y"+@Text(UnitPrice)+"Y"+Type+"Y"+Catalog

Example of the @DbLookup function:
lookup := @DbLookup("":"NoCache";ALPI;"By Part Number";part;7);

Once I have the lookup variable set I can now use @Word to populate the fields.
Example of populating the fields:
FIELD Desc := @Word(lookup;"Y";2);
FIELD UnitPrice := @Word(lookup;"Y";5);
FIELD LEADTIME := @Word(lookup;"Y";4);
FIELD Catalog := @Word(lookup;"Y";6);

USER FEEDBACK TO THIS TIP

  • There has been many times that I want the user to select an item from a drop down list and have multiple fields update according to the choice the user made. The users usually want to be able to make changes to those fields if they need to. So, I have come up a solution using a button.

    Greg used "Y" as a delimiter for parsing a word string in his example. This shouldn't be used because if a "Y" occurs in the description field, it will mess up the whole parsing process and he will end up loading wrong information in the fields. I have found that using the "~" symbol is the best because it is very rarely used anywhere else. In the code below, I have substituted the "Y" with a "~". I haven't changed any of his other code and have not actually tested it.

    All of the following formula goes under the Click event of the button.

    I created a button that assigns a @DbColumn function or two to a variable, depending on what I want the user to see in the drop down list. If I use more than one @DbColumn, I create another variable that appends the results of my @DbColumn functions.

    See below for an example:

    ALPI := "85256A56:00663314";
    p1 := @DbColumn( "" : "NoCache" ; ALPI ; "By Part Number" ; 1 );
    p2 := @DbColumn( "" : "NoCache" ; ALPI ; "By Part Number" ; 2 );
    p3 := p1 + " - " + p2; 

    I then use this variable in @Prompt function, which I assign to another variable.
    Example of the @Prompt function:
    choice := @Prompt([OKCANCELLIST]; "Part Number"; "Please select an Part Number."; "";p3);

    If I am using more than one @DbColumn, I use the @Left statement to pull the first item from the selection.
    Example:
    part := @Left(choice;" - ");
    FIELD IMKEY := part;

    Now I will use the part variable in a @DbLookup function that find the information I have set up in the lookup column in a previously created view, this column will have several fields appended together, separated by a special character such as "~".

    The view column formula should look something like below:

    IMKEY+"~"+Description+"~"+P_C+"~"+LEADTIME+"~"+@Text(UnitPrice)+"~"+Type+"~"+Catalog
    
    Example of the @DbLookup function:
    lookup := @DbLookup("":"NoCache";ALPI;"By Part Number";part;7);
    
    Once I have the lookup variable set, I can now use @Word to populate the fields.
    Example of populating the fields:
    FIELD Desc := @Word(lookup;"~";2);
    FIELD UnitPrice := @Word(lookup;"~";5);
    FIELD LEADTIME := @Word(lookup;"~";4);
    FIELD Catalog := @Word(lookup;"~";6); 
    Steve Cooper

This was first published in June 2001

There are Comments. Add yours.

 
TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
Sort by: OldestNewest

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

Disclaimer: Our Tips Exchange is a forum for you to share technical advice and expertise with your peers and to learn from other enterprise IT professionals. TechTarget provides the infrastructure to facilitate this sharing of information. However, we cannot guarantee the accuracy or validity of the material submitted. You agree that your use of the Ask The Expert services and your reliance on any questions, answers, information or other materials received through this Web site is at your own risk.