Improve formulas or convert to LotusScript

HCL Domino Domino Forum
1

I am successfully using @Picklist with @DbLookups in a form action button to allow the user to select a vendor name and have 4 other fields of vendor information populate automatically. All of the fields involved must be editable.

What I need is to have this occur in the field, Vendor Name. Can this be done with formula language? Can you use a picklist in a field? If not, what would be the equivalent of my formulas in LotusScript? Would it be used in the exiting event?

Here is the code for my @picklist. Thanks for all advice, Tammy

FIELD VenName := VenName;

@SetField(“VenName” ; @PickList([Custom]: [Single]; “”; “Vendor Import View”; “Vendor Information”;“Please select the name of the Vendor.”; 1));

FIELD VenContact := @DbLookup(“”:“NoCache”; “”: “”; “Vendor Import View”; VenName;2 );

FIELD VenPhone := @DbLookup(“”:“NoCache”; “”: “”; “Vendor Import View”; VenName;3 );

FIELD VenFax := @DbLookup(“”:“NoCache”; “”: “”; “Vendor Import View”; VenName;4 );

FIELD VenEmail := @DbLookup(“”:“NoCache”; “”: “”; “Vendor Import View”; VenName;5 );

2

Subject: Improve formulas or convert to LotusScript

Hi Tammy,

Couple of thoughts that may help you here.

  1. The fields should NOT be editable as you will end up with a data integrity issue in your application (unless you have another reason for them being editable) Never let users have the ability to change data like this unless you have some way of controlling/validating it.

  2. You should execute one lookup and parse out the data to reduce overhead on your application and on the server:

a. Create a hidden computed field on the vendor profile document with the following formula:

"&VenContact="+venContactFieldName+"&VenPhone="+venPhoneFieldName+"&VenFax="+venFaxFieldName+"&VenEMail="+venEmailFieldName+"&"

b. Refresh your documents so this field is computed.

c. Make this column the second and only column (besides the first of course) in your view.

  1. You then have two options to parse out this data:

a. If the fields must be editable:

FIELD VenName := VenName;

FIELD VenContact := VenContact;

FIELD VenPhone := VenPhone;

FIELD VenFax := VenFax;

FIELD VenEMail := VenEMail;

tmpVenName:= @PickList([Custom]:

[Single]; ""; "Vendor Import View"; "Vendor 

Information";"Please select the name of the 

Vendor."; 1)

tmpValues:=@DbLookup(“”:“NoCache”;

ServerName:FilePath; "Vendor Import View"; 

VenName;3

@SetField(“VenName” ;tmpVenName );

@SetField(“VenContact”; @Left(@Right

(tmpValues;"VenContact=");"&")

@SetField(“VenPhone”; @Left(@Right

(tmpValues;"VenPhone=");"&")

@SetField(“VenFax”; @Left(@Right

(tmpValues;"VenFax=");"&")

@SetField(“VenEMail”; @Left(@Right

(tmpValues;"VenEMail=");"&")

b. If you use computed fields, just parse out the values as above in the field formulae. Pull the string to be parsed out in via a computed hidden field.

3

Subject: RE: Improve formulas or convert to LotusScript

Hello File Save,

Thanks for responding. It’s good to know you’re there. I want to remind you that I am such a beginner…

I like the code you sent me, I can see that it is more efficient than my original. Is this code still in the form action button? If not where does it go?

Here are my issues.

  1. my manager does not like the action button, he wants the picklist/lookup actions to be driven in the Vendor Name field. “the user should not have to leave the field”

  2. the Exiting event is probably the best way but I don’t know how to convert my formulas to LS

  3. the fields must be editable.

Do you think LS in the exiting event would be better? If so would you help me with the code?

Thanks again, Tammy

4

Subject: RE: Improve formulas or convert to LotusScript

Is the manager a line of business manager or the CIO?

Why must the fields be editable? This shows the lack of an internal/business control over your IS Systems unless there is a compensating control in place to handle any data discrepencies/conflicts. Master data should be managed through the central store, not in a document by document basis. These controls should be defined by Senior Management, not by line of business managers or CIOs.

As far as the action issue, you have a couple of choices. You can use a combobox for the vendor field that gets its values through a @DBLookup if based on a key (such as the users department) or an @DBColumn. Alternatively, if the return size is too great (over 64K) it can be a computed field (Computed to itself) and have an arrow graphic next to the field to call a picklist. The formula behind this graphic can hold the code I posted above.

I would be careful with an exiting event, especially if you have a requirement that “the user should not have to leave the field”.

5

Subject: Improve formulas or convert to LotusScript

I would like to point out that you could make the lookups a lot faster by creating a column with the combined values, and parse it after your retrieve it. Something like:

VenCombined := @Explode(@DbLookup(“”:“NoCache”; “”: “”; “Vendor Import View”; VenName;2 ));

FIELD VenContact := VenCombined[1];

FIELD VenPhone := VenCombined[2];

FIELD VenFax := := VenCombined[3];

FIELD VenEmail := := VenCombined[4];

6

Subject: RE: Improve formulas or convert to LotusScript

Thank you Ben for responding.

I can see that what you suggest would be faster but…but what I need is to autopopulate these fields (which must be editable) without using an action button.

Any suggestions?

7

Subject: RE: Improve formulas or convert to LotusScript

Couldn’t you just have the QueryOpen event query for the vendor name, and then use default formulas for the fields?

8

Subject: RE: Improve formulas or convert to LotusScript

Alternatively, you could set the default value field with the Query for the vendor and populate from there.

There are just so many ways to skin this cat. My biggest issue/concern is with the Data Integrity/Consistency issue if the fields are editable.

9

Subject: RE: Improve formulas or convert to LotusScript

…Of course, this technique doesn’t work if you already have multiple-values inside the fields…

Nicolas Abesdris

Quintessence e-solutions Inc.

10

Subject: RE: Improve formulas or convert to LotusScript

In the scenario of what is being looked up here, this should (Big emphasis on Should) not be an issue. Another reason why I like to use a computed field…

11

Subject: RE: Improve formulas or convert to LotusScript

Except do not use this method as coded here in a mixed environment. I got burned using @ServerName last week when an errant R5 user popped up:-(.

12

Subject: *In which case you could use @Subset, but it is still more efficient

13

Subject: *Absolutely…

I just prefer parsing out from a string instead of @Subset just in case data is missing, etc.

14

Subject: Improve formulas or convert to LotusScript

you could set your field venName to be a dialog list using a view for your dialog list. Then you might be able to use the Input Translation field to populate the other fields, or set the other fields to be computed.

Depends on how u need the form to work, but hope this helps