Problems with field size limits in the form

Dear Experts,

I'm having trouble with this one:

I've a form, which has a field called accountNumber.

This field has the following properties:
Type = Text
Editable field
Size is limited to 13 bytes
Allow Multiple values is checked
in the Multiple Value Options the following is selected:
Seperate values when user enters Comma, Semicolon, Newline
Display seperate values with Semicolon.

Now I've a situation where I need to store more than 5000 accountnumbers (remember each account number is 13 bytes long)..

But the maximum i could store is some where around 2100 values. Is there an alternative for this one.

Who is Participating?
Andrea ErcolinoConnect With a Mentor Commented:
"many documents" approach explained

__Current Context
  - a form for storing the account numbers (AccountNumForm)-
  --- a multivalue text field (AccountNumbers)
  - a view for showing the account numbers (AccountNumView)
  --- SELECT Form="AccountNumForm"
  --- a sorted column where 'show multiple values as separate entries' option is checked and the field shown is AccountNumbers
The AccountNumbers field has hit its maximum size, so that new account numbers cannot be stored anymore

Create a new document with the AccountNumForm on demand

The AccountNumbers field acts as a set, i.e. a list where each account number exists only one time, if any. So the operations allowed for this set data structure must be at least:

  - function AccNumSearch( number as string ) as NotesDocument
    returns the document that contains the number, and Nothing otherwise
    side-effects: none

  - sub AccNumAdd( number as string )
      - always the final set contains the number
      - possibly one document is added to the database

  - sub AccNumRemove( number as string )
      - always the final set does not contain the number
      - possibly one document is removed from the database

You need
  - a view for selecting the least used field (AccountNumLoad)
  --- SELECT Form="AccountNumForm"
  --- an ascending sorted column where the field shown is @Sum( @Length( AccountNumbers ) )
  - a LotusScript Library for the set operations (AccNum) as follows:

'****** START ******

Option Public
Option Declare

Dim s As NotesSession
Dim db As NotesDatabase
Dim vSet As NotesView
Dim vLoad As NotesView
Dim maxFieldSize As Long

Sub Initialize
  Set s = New NotesSession
  Set db = s.CurrentDatabase
  Set vSet = db.GetView( "AccNumView" )
  Set vLoad = db.GetView( "AccNumLoad" )
  maxFieldSize = 15000  ' tuning needed here
End Sub

Function AccNumSearch( anumber As String ) As NotesDocument
  Dim d As NotesDocument
  Set d = vSet.GetDocumentByKey( anumber, True )
  Set AccNumSearch = d
End Function

Sub AccNumAdd( anumber As String )
  Dim found As NotesDocument
  Set found = AccNumSearch( anumber )
  If found Is Nothing Then
    Dim d As NotesDocument
    Set d = vLoad.GetFirstDocument
    Dim newDocNeeded As Boolean
    If Not( d Is Nothing ) Then
      If d.ColumnValues( 0 ) < maxFieldSize Then
        newDocNeeded = False
        newDocNeeded = True
      End If
      newDocNeeded = True
    End If
    If newDocNeeded Then
      Set d = New NotesDocument( db )
      Call d.ReplaceItemValue( "Form", "AccountNumForm" )
      Call d.ReplaceItemValue( "AccountNumbers", anumber )
      Call d.GetFirstItem( "AccountNumbers" ).AppendToTextList( anumber )
    End If
    Call d.Save( True, True, True )  ' tuning needed here
  End If
End Sub

Sub AccNumRemove( anumber As String )
  Dim found As NotesDocument
  Set found = AccNumSearch( anumber )
  If Not( found Is Nothing ) Then
    Dim oldValues As Variant
    oldValues = found.GetItemValues( "AccountNumbers" )
    If Ubound( oldValues ) = 0 Then
      Call found.Remove( False )  ' tuning needed here
      Dim index As Variant
      index = Arraygetindex( oldValues, anumber, 5 )
      oldValues( index ) = ""
      Dim newValues As Variant
      newValues = Fulltrim( oldValues )
      Call found.ReplaceItemValue( "AccountNumbers", newValues )
      Call found.Save( True, True, True )  ' tuning needed here
    End If
  End If
End Sub

'****** END ******
When you hit a limitation.. only option is redesign it

1. To create doc/response to this doc which account number (only one) and any other detail
2. or create additional fields dynamically if the length of the field is more than certain limit.. side-effect you have to deal with displaying the data back

kaliosAuthor Commented:
I thought about option 2.. but are these the only options available...

I guess options 2 is the last alternative.. but i've one more issue here...

Let me be more clear..

The above problem is in form named : profile.

I've an other form named : actions

Now , basically the 'actions' form is used to submit requests. all it has it accountnumbers and type of action fields. Now as soon as the actions form is submitted its sits in a view.
This 'actions' form has the accountnumbers that should be added or deleted from the 'profile' from.
so lets say i've 1000 accounts to delete in the 'actions' form.  so my agent should read those 1000 'accounts' from the actions form and should delete it from
the accountnumbers field in the profile form.

the accountnumbers field is the 'actions' form and the profile form are of the same format.. cannot contain more than 2000.  
Lets say I've 2000 accounts to delete  from the profile from.

I've to have a forall loop and again a forall loop inside it which takes lot of time...

what is the best way to do this...
If i'm not clear please let me know. I'll be happy to elaborate...


Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Sjef BosmanGroupware ConsultantCommented:
Why do you want to store all accountnumbers in one document? What's the functional reason for this?
kaliosAuthor Commented:
This DB is an existing DB and its been like this since long. There are so many things associated with this design.... If I change it lot of other stuff has to be changed.

This is what I'm doing now.. I'm creating another field in the form,, which can store another 2000 values...
So the form has 2 fields.. A1 and A2.    A1 is a multi-value field and can store values uptp 2000 (each value is 13 bytes)..
A2 is also the same as A1 and can store another 2000 values.. A2 is the new field I created...

Now lets say I've to store 4000 values in this form, I store 2000 in A1 and 2000 is A2 which is fine..

But like i said earlier,, I get requests from users to delete or add accounts from A1 and A2.  There requests are stored in another form.  Now in that form, I've another field called A3.
A3's format is also like A1 and A2 (multi-value field).

So my agent should read values from A3 and check if it is in A1 or A2 fields. If it is present it has to delete it from there.....

What is the best way to code this.
Sjef BosmanGroupware ConsultantCommented:
Existing application? Okay, I'll bear that in mind. :|

What you're doing looks like describing an n-to-m relationship the wrong way. In Notes' terms, that is. If, in Notes, a document has many children, you should not store the name of every child in a field in the parent document. Instead, every child will have a field with the name of the parent, and there will be a view of the child documents, sorted by parent, to find all children of a parent.

Could this be applied to your application? Or is the current structure used everywhere, in such a way that changing it would be impossible?

Furthermore, two fields might seem enough at the moment, but on a bad day there will be the 4001-th value, and you will be needing a third field to store values. So you're in need of a more permanent solution probably.

It is of course always possible to write a double for-loop, like this (in quick LS):

    i= 1
    Do While doc.hasitem("AcctNr_" & i)
        acctnrs= doc.getitemvalue("AcctNr" & i)
        j= 0
        Do While j<=Ubound(acctnrs)
            If acctnrs(j)=deletethisacctnr Then 'remove from array
                acctnrs(j)= ""
            End If
            j= j + 1
        Call doc.replaceitemvalue("AcctNr" & i, FulTrim(acctnrs))

I'd suggest not to move data from one field to the other until one of the fields has no data left. In that case, just rename the fields.
Andrea ErcolinoCommented:
from Comment from sjef_bosman (Date: 09/19/2004 01:23AM PDT)
> on a bad day there will be the 4001-th value

Bear sjef_bosman's prevision in mind and adjust everything to a new exponential limit, not just arithmetical:
  2000 ** 2 >> 2000 * 2

In Notes, if you need (a lof of) shrinking space, you have one choice: documents. So try to replace your "one document" with a "many documents" architecture, based on the profile form. Overflow in an AccountNumber field should be coped with the creation of a new document. An agent for compacting sparsely used / deleting empty documents once a month (or any sensible time interval) also should be useful.

I think that in this case two fields in the same document is worse than one field in two documents. Anyway you have to know where and how the AccountNumber field is used in your applications, to properly adjust the use. So, if you solve the problem with the "many documents" approach you can access the AccountNumber field throughout your app in the old fashioned way, just applying a documents loop outside. And it still works the same for presenting the info to the user (as Hemanth said). This approach involves more design elements than the other, but in a very simple way you could get a definitive solution.

kaliosAuthor Commented:
Thanks for all your advises Sjef and Raputa.. But like I said this is an existing DB and it impacts all other things..
One main thing that it impacts is...  There a view which has this accountnumber in its first column and it has 'show multiple values as seperate entries'.

So currently if A1 has 2000 values and A2 has 2000 values stored, then there are two views.. showing 2000 documents in each.  

I extract data from these two views and merge it, eliminate duplicate records in both of these views and then FTP it to mainframe for further processing.
So my main concern is the views....

Sjef, I need more help on the code above. I'll put another question with my current code.

Andrea ErcolinoCommented:
Why do you access AccountNumber values through the view rather than through the (profile) document?

If that view is not used only by you, then it could be tricky to adjust code surrounding "one view access" to "two views access"

Moreover, the "many documents" approach I suggested fits perfectly in your current view, because that one view can show all the needed entries, exactly as it was before you toched it.
The view selection formula should be <<SELECT Form="ProfileDoc" & AccountNumber != "">>. And you can let the column show the same contents and the 'show multiple values as seperate entries' option checked.

Sjef BosmanGroupware ConsultantCommented:
Just a little explanation of the snippet above.

I use items on the form with the names AcctNr_1, AcctNr_2, etc. These items do NOT have to be fields on the form, but you may make a comment on the form that these fields can exist. You have to write some functions to add, remove or change an AccountNumber. The example I gave is about removing one:
- walk through all accountnr-items in the document
    - walk through all numbers in an accountnr-item
        - if this nr is to be removed, do that

You need to write similar stuff for other operations. When you want to add a number, you need to find the first item that is not filled up to its neck. If there is none, then add a new item and put the number in. You also have to take care of removing empty fields if necessary.
kaliosAuthor Commented:
raputa, thanks for the detail explanation. However, I've all ready implemented with 2 fields in the form . each field takes 2000 values which makes to 4000 now.
Now I'm able to do adds successfully. But for deletes my process is taking a whole lot of time. Need to improve the code.

I'll put another question here soon.
thanks again
Sjef BosmanGroupware ConsultantCommented:
Is this the (only) correct answer to your question? Or did you just want to close it? I always thought that size doesn't matter ;)

Awaiting your new question!

Sjef :)
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.