Multiple @Dblookup's in a subform

HCL Domino Domino Forum
1

Hello,

I have a form A with 30 rows with 5 fields in each row as dblookups based on one field example X. There is a form B pulls data from datawarehouse so when the user enters the value of field X in form A it gets the values for rest of 5 fields. The data in view B based on form B has around 35000 to 50000.

I am trying to avoid dblookups in form A, 150 dblookups since it slows down the process. I thought of have one computed text field having with dbcolumn with “value X ~field 1” and I am not sure if the text field can handle large data.

Any suggestions would be appreciated. Thanks in advance.

2

Subject: Multiple @Dblookup’s in a subform

Let me see if I understand. The user opens up a blank form A. They go to a field called “X”, and somehow enter a value into that field. The result is that 150 other fields get populated from data that is currently on display in view B.

Is that one field X, or is there one for each row?

I wish I could see what you are trying to do…having trouble truly understanding the data flow.

  • Matt
3

Subject: RE: Multiple @Dblookup’s in a subform

Thanks for responding. Ok Let me clarify try if I can explain right.Form A & Form B. Form B & a view based on Form B. The data for form B is loaded from datawarehouse through scheduled agent. Form A has 30 rows & each row has total of 10 fields out of which 5 fields are auto populated based on the value entered in field X. 29 rows have hide-when formula only if row 1 is complete row 2 is visible. I would say 5 dblookups for each row. some users may enter only 2 rows & some 10 based on product field X. so a total 5*30 dblookups in form A. I am trying to avoid dblookups.

I hope it makes sense. Any suggestions would be appreciated. Thanks in advance.

4

Subject: RE: Multiple @Dblookup’s in a subform

Hema,

Why not use a popup for the lookups and data entry, and then populate a table with the results of the popup? That way, you get an inert table with no delays on the main document, and only when users need to mess with it do they click a button and open the popup window.

It’s more of a hassle to code, yes, but it means that you’re using a single row of multi-value fields which has many reporting benefits over having itemNo, itemNo_1, itemNo_2, etc.

regards,

raphael

5

Subject: Multiple @Dblookup’s in a subform

Here is what we do in our Workflo!Approval Product. We have a Subform that is added to an existing form (not a necessary step for you). On the subform is a formula (Compute For Display so the results is not stored in the document) that looks up a profile document and returns column 3 (in our case) The formula for this column looks something like this :

ACProcessName:

ACMailForm:

ACConditionLock:

ACConditionToggle:

@Text(ACCondition):

ACConditionType:

ACRoutingMethodLock:

ACRoutingMethod:

ACApproversLock:

@Implode(ACApproversDefault;"~"):

ACApproverCommentsLock:

ACApproverComments:

ACAutoSkipLock:

ACAutoSkip:

@Text(ACSkipDays):

ACSkipDaysType:

ACNotifyAssignLock:

ACNotifyAssign:

ACNotifyLateLock:

ACNotifyLate:

@Text(ACLateDays):

ACLateDaysType:

@Text(ACLateTimes):

ACNotifyProgressLock:

@Implode(ACNotifyProgressGeneral;"~"):

@Implode(ACNotifyProgressNames;"~"):

ACNotifyTroubleLock:

@Implode(ACNotifyTroubleGeneral;"~"):

@Implode(ACNotifyTroubleNames;"~"):

so that it concatenates together all of the fields that I want to inherit from the profile. So a single lookup returns at the present time the data from about 175 different fields in the profile document. Notice that some of the fields are Multi-value so we do an @Implode and delimit the result with “~”.

Then in the subform we want to load the results into different fields so we use a Compute when Composed field with a formula like this:

@Explode(@Subset(@Subset(ACProfileData;10);-1);“~”)

in this case return the 10th element in the Computed for Display field and returns an array of in this case Approvers Names (but that is immaterial.

A simpler single value fields looks like this:

@Subset(@Subset(ACProfileData;65);-1)

which returns the 65th item, etc.

What I have done to sort of create an index of the values is that I have pasted the Field list out of the Column in the Lookup View into a spreadsheet and then numbered the items. so that if I want the MailForm name I know that it is the second elemnet in the returned data.

This takes a bit of set up but once you have it working it is pretty easy to add additional fields into the definition. Add them to the end of the list of fields in the Lookup Column formula and then reference them by their appropriate index location. It is really pretty fast in execution considering all then stuff it has to do, but it typically only happens once on Composition. I have written an agent that will reset the values after the document has been composed, but anyway …

You can look at the code by downloading our Workflo!Approval Demo at

http://www.wfs.com/workweb/WFSWebLib.nsf/Src/DocSet*Notes+Net06

look at the Hidden Fields Subform for the field ACProfileDate and the view (Approval Cycle Profiles). It’s really not that complex and I’m sure someone might be able to improve on the process, but it works and has been for a long time.

Found reference:

http://www-10.lotus.com/ldd/nd6forum.nsf/55c38d716d632d9b8525689b005ba1c0/144e971d6849848c85257203005b543a?OpenDocument