Solved

Problems with field size limits in the form

Posted on 2004-09-17
12
245 Views
Last Modified: 2013-12-18
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.

thanks
Kalios
0
Comment
Question by:kalios
  • 4
  • 4
  • 3
  • +1
12 Comments
 
LVL 24

Expert Comment

by:HemanthaKumar
Comment Utility
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

~Hemanth
0
 
LVL 2

Author Comment

by:kalios
Comment Utility
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...

thanks

0
 
LVL 46

Expert Comment

by:Sjef Bosman
Comment Utility
Why do you want to store all accountnumbers in one document? What's the functional reason for this?
0
 
LVL 2

Author Comment

by:kalios
Comment Utility
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.
thanks
Kalios
0
 
LVL 46

Expert Comment

by:Sjef Bosman
Comment Utility
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
        Loop
        Call doc.replaceitemvalue("AcctNr" & i, FulTrim(acctnrs))
    Loop

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.
0
 
LVL 3

Expert Comment

by:Andrea Ercolino
Comment Utility
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.

0
Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

 
LVL 2

Author Comment

by:kalios
Comment Utility
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.

thanks
Kalios.
0
 
LVL 3

Expert Comment

by:Andrea Ercolino
Comment Utility
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.

0
 
LVL 46

Expert Comment

by:Sjef Bosman
Comment Utility
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.
0
 
LVL 3

Accepted Solution

by:
Andrea Ercolino earned 125 total points
Comment Utility
"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
 
__Problem
The AccountNumbers field has hit its maximum size, so that new account numbers cannot be stored anymore

__Solution
Create a new document with the AccountNumForm on demand

__Details
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 )
    side-effects:
      - always the final set contains the number
      - possibly one document is added to the database

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

__Implementation
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 ******
'AccNum:

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
      Else
        newDocNeeded = True
      End If
    Else
      newDocNeeded = True
    End If
   
    If newDocNeeded Then
      Set d = New NotesDocument( db )
      Call d.ReplaceItemValue( "Form", "AccountNumForm" )
      Call d.ReplaceItemValue( "AccountNumbers", anumber )
    Else
      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
    Else
      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 ******
0
 
LVL 2

Author Comment

by:kalios
Comment Utility
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
Kalios
0
 
LVL 46

Expert Comment

by:Sjef Bosman
Comment Utility
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 :)
0

Featured Post

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Suggested Solutions

For Desktop Techs: How to retain a user's Notes configuration data when swapping out the end user's computer. (Assuming that you are not upgrading to a completely different version of Notes client) All you need to do is: 1) install Notes o…
This is an old article, please see an updated version of this article, located here: http://www.experts-exchange.com/articles/23619/Notes-8-5x-Windows-7-Notes-info-and-tips.html
Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…
In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're interested in additional methods for monitoring bandwidt…

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now