form question

Please explain: how can one account have multiple account id's? To me, an account id is a unique id for an account. I can understand 2 (albeit with some difficulty ;) but 3000 id's per account seems somewhat over the top.

steve,

the subaccount id can also cross the 32k limit. so i cannot use the multiplevalue field for the subaccount id.

sjef,

yes, I agree your point, but unfortunately this is how it is. we are talking about a phone company here and think about the number of lines each customer can have. each phone line is a sub-account id. all sub-account id are tied up to a master phone line which is the account id.
each department in a company can have multiple master lines which is multiple account id's. and each account id can have several other lines tied to it which is the sub account id.  

there can multiple sub-account id's for each account id.
the account id is at the top hierarchy.

hope this explains....

what i thought initially is to have one account id field and have a multiple value field for sub account id. so the user will enter one account id and
lets say puts 2000 sub account id's in the multiple account id and click on create accounts button. this will create one document each per subaccount id.
if there are more, the user will have to do it again.

but the problem is if there are 5000 account id's the user has to manually enter the values and then click on the create accts button 5000 times. hope i'm clear here.

then I thought of the following:
put an import feature in the form and have just one multiple value field.
the import feature will lets the user to import from an excel spread sheet.
the first column is the account id and the second column is the sub account id.

so the user will import 2000 rows at once and then click on the create button to create 2000 documents..
so the excel spread sheet will have
account id      subaccount id
123456789     IX123456789
123456789     SY99876504
987654321     223SX93939
987654321     113SX93939

When the user clicks on the import button, it will import from the excel (only 2000 rows per request becuase of the 32k limit) and will place in the multiple entry field like this

Account ID/SubAccount ID:   123456789-IX123456789; 123456789-SY99876504; 987654321-223SX93939; 987654321-113SX93939

Now once the import is done, the user will click the create acct records button.
This will create one document per account id-subaccount id.
in that document, the account id will be 123456789 and the subaccount id will be IX123456789 (considering the first value as example from above).

But as you know importing from excel is a little complicated and it depends on what kind of format the user has in the columns... it  gets messy sometimes.
sometimes it imports as a scientific value...

Please suggest if you have any other suggestions.
thanks

For this amount and type of data Notes isn't really the right solution IMHO.  Data should be in a SQL server or equivalent I would say - you can still access that of course using a data source etc.

Ghastly... And what do you want to do with those numbers? If you just have to store them, then put the lot into a rich-text field.

IMHO Notes isn't very suitable for the application you're developing. Notes is very good at handling "static" information: documents that don't change very often, documents with attachments, historical documents, logging, etc. A more dynamic database that's very relational had better be built in a relational environment, with tables and queries etc.

But if you have to use Notes for this, we'll find a solution for you!

Heh, great minds... ;)

haha.  I guess the big questions are why, what for etc.  i.e. is this a lookup facility for another app. already in Notes or is the start of a new app. completely.  What are the full likely quantities involved.  If you end up with 10000 accounts each with 3000 subid's thats a lot of documents, hence the comments before about using a server based Relational database system.

If quantities aren't huge but just some may have these 3000 subrecords then maybe documents + response documents for each subaccount would be the best idea but if you just need a list and there is nothing else related to each of those sub account id's then it seems mightt wasteful.

If the limit is on the number of values in one multi-value field you could potentially use 2,3,4, as many as you need or as sjef says use a rixh text field but that isn't going to give you easy programmatic access to the list then.

Anyway please come back with some more info. on your goals with this please.

Steve

hi again,

there already an application currently existing in notes/domino
and i'm trying to enhance this feature. the current existing database
only has the account id concept and no sub-account id. so its a little earier compared to the new concept i said above.

current this is what happens.
a user will have a spread sheet with all the account id's in the first column.
and the user clicks on the import feature in the main form and will import 1000 accounts at at time into a multi-value field.

after importing the 1000 accounts the user clicks on create accounts button.
this will basically run an agent to create 1000 documents with the account numbers. if the user has more accounts, he has to import the next 1000 and
click on the button again to create more account documents.

now, there is one field as keyid which is a 7 byte system generated sequential number incremented by 1 each time a new main profile is created (the main form where the button is present to create account doucments).

the keyid value is copied from the main form to the account form for every account created.

so the keyid is the field that can be used for retreiving all the accounts for the main profile.

The above is the currently functionality that exists as of today and it is working out well.

But now, we have a need to introduce a new billing system that has a this account id-sub account id concept.

I'm not sure how the rich text field works. if that is a better approach then any assitance on that is greatly appretiated.

Please let me know if I did answer all your questions.
Thanks

also, if you are curious to know what happens to all the info from the domino database currently:

All the main profile and the account information is extracted from the db
and a flat file is generated with the info and Transmitted to Mainframe for futher processing.

this is the existing functionality. the only enhacement that is required is the addition of the subaccount id for account id's for new accounts.
not to the existing accounts.

the notes db is the main profile database for the accounts. there are webforms associated for this database that will do lookups etc and export to excel.. this is a very huge database and it has interfaces to other portals.
http requests are posted into the server for adds and deletes requests from the portal. a webquery save agent acts upon the post of these requests and adds and deletes accounts...

lot to tell..

but right now i'm only worried about how to achieve this sub-account id stuff for new accounts.

aqlso currently there are over 100,000 account records for all the main profiles.  so that means 100,000 account documents.

with the addition of sub-accounts for new accounts, who knows it might even go to 300,000.

Thanks for the info. I need to think this over, I'll be back...

thank you experts. I'll wait for your valuable input.

There's too much to think about. Each possible solution I can think of is still not very "Notesy". Much depends on the functionality you need with these (sub)account ids, in the container where they will be stored. That container could be 1) multiple  fields in a document, 2) a separate document for each account id, 3) a rich-text field in a document, 4) an external SQL-database. And maybe more.

What do you want to do with an account and a (sub)account id? What primitive operations do you need? Some examples:
a) do you need to add an account id?
b) modify?
c) remove?
d) lookup?
e) search for?
f) use as "link"?
g) other functions?

Method 1 will inevitable lead to problems due to size, method 2 is the most flexible solution but costs disk space (disk is cheap these days) and might fail because of the size as well, method 3 cannot be used for @DbLookup and method 4 is complex since two systems need to be maintained.

I think method 2 is the most efficient way to do this.

I'll use method2 (each account id + sub account id is a document).

This will need more disk space, which is not a problem.

One thing I'm concerned about is you said (this might fail becuase of the size).
What do you mean by this. Why do you think it will fail. We already have over
100,000 documents right now and the processing is really fast.
I dont think adding another 100,000 or 200,000 will not create any problems.....

But please advise, what other problems i could face with this.

Also, in terms of inputting the data ( is there any other easy method).
Right now, the only concept I've is

excel spread sheet with two columns (account id in the first column, sub-account id in the second columns).

1000 records at a time per request will be imported to the multi-value field
and when the button is clicked to create account after the import process, it will create the 1000 account documents...
this is what i'm working on right now.
can you suggest any other process.

Thanks

when i say inputting the data, i mean for new add requests.

by the way, i'll be doing all of these functions for an account id/subaccount id

add more sub-account id's to an account id
add new account id's and their respective sub-account id's
modify an existing account id.
modify an existing subaccount id.
remove an existing account id.
remove an existing subaccount id.
lookup
search for account id, subaccount id,

Okay, if you need all those primitive functions, a rich-text field is out of the question.

Okay, let's say method 2, as Steve suggested: a single (sub)account number per document. Advantages: easy access, easy update, easy removal, easy creation. Disadvantage: it may get VERY time-consuming to do @DbLookups, and @DbLookups with lots of results (>32K data) are not possible. A @DbColumn over all the data is completely impossible, so you may have to do a lot in LotusScript. Database size is not the issue.

The import-process can be done differently indeed. How are those import-files generated? Isn't it possible to do it all in one go?

OK, i understand that, for dbcolumn and dblookups, I need to have lot of lotus script done.  I've that as an item to re-visit.

but for now, i'm concentrating on the import process.
the import is always an excel spread sheet. The customer provides the
excel spread sheet to the account team and the account team clicks the
import button in the form to import accts from the spread sheet and once the 1000 accounts are imported into the multi-value field, they click on
create accounts which will run an agent to create a document per account id-sub account id.

this process is ok, but the problem with it is... excel treats big numbers (for example: 00023456789000) as a scientific value and puts like 234E+9
something like that and this value is getting imported. I want to import
everything as text from excel.

Please advise of any other import processes.
thanks

Hm, hm, that seems a different question to me... I'd say that your customer doesn't provide the right spreadsheet. It's his responsibility (I hope) to get it in the right format.

Can you ask him to provide the spreadsheet in text-only format?

yes, that is what I'm going to do.. However, the spread sheet sometimes gets
messed up.

Guess there is going to be lot of Lotus Script coding required to do checks.

Let me know if I can improve the whole process by doing anything different.

thanks

Is it a spreadsheet or a CSV file that they are supplied with and then load into Excel, sounds like it is being completed incorrectly, the columns should be preformatted to be marked as text (custom format code is @) if they contain only numbers but need to be text...

if it is the latter then it is an easy job of course to parse the CSV without using Excel at all.  If it is Xl already someone needs to back track and see how it gets created.  Perhaps even the person creating it could update your database instead??

Steve

Steve

excel spreadsheet and not CSV.  and here is the code I use to get the values from excel...  Currently this only imports the first  column. I can modify the code to pick up both the columns, column1 and colum2 for account id and subaccount id. but the only problem is when there are big numbers, it puts in scientific format. any suggestions on improving the code. Also this code locks the document for a while. not sure why.

i know this must be a diff question. that is why i've increased the points from 100 to 350.

Sub Initialize
      Dim workspace As New NotesUIWorkspace
      Dim uidoc As NotesUIDocument
      Dim doc As NotesDocument
      
      Set uidoc = workspace.CurrentDocument
      Set doc = uidoc.Document
      
      Dim FileNum As Integer
      Dim xlFilename As String
      On Error Goto Errorhandler
      Filenum% = Freefile()
      xlFileName$ = Inputbox("Enter the excel file name and path here? example:C:\filename.xls")
      
      If xlFileName$="" Then
            Messagebox "The file location was not specified correctly. Please enter the file location correctly.",,"Customer Profile"
            Exit Sub
      End If      
      
      Dim One As String      
      Dim row As Integer
      Dim Written As Integer
      Dim Dropped As Integer
      Dim Switch_Y As String
      
      '// Next we connect to Excel and open the file. Then start pulling over the records.
      Dim Excel As Variant
      Dim xlWorkbook As Variant
      Dim xlSheet As Variant
      Print "Creating Excel Object..."
      Print "Connecting to Excel..."
      'Set Excel = CreateObject( "Excel.Application.8" )
      Set Excel = CreateObject( "Excel.Application" )
      Excel.Visible = False '// Don't display the Excel window
      Print "Opening " & xlFilename & "..."
      Excel.Workbooks.Open xlFilename '// Open the Excel file
      Set xlWorkbook = Excel.ActiveWorkbook
      Set xlSheet = xlWorkbook.ActiveSheet
      
      '// Cycle through the rows of the Excel file, pulling the data over to Notes
      Goto Records
      Print "Disconnecting from Excel..."
      xlWorkbook.Close False '// Close the Excel file without saving (we made no changes)
      Excel.Quit '// Close Excel
      Set Excel = Nothing '// Free the memory that we'd used
      Print " " '// Clear the status line
      
Records:
      Let row = 0 '// These integers intialize to zero anyway
      Let Written = 0
      Let tmpnullrows=0
      
      Print "Starting import from Excel file..."
      
      Do While True
            
Process:
            With xlSheet
                  Let row = row + 1
                  
                  If .Cells (row, 1).Value = "" Then
                        Let tmpnullrows=     tmpnullrows+1
                        If tmpnullrows=2 Then
                              Goto Done
                        End If
                        Goto Process
                        
                  End If
                  
                  If .Cells (row, 1).Value = "Accounts" Then
                        Let Switch_Y = "Y"
                        Goto Process
                  End If
                  
                  Dim EMAN As String
                  Dim EMANTrim As String
                  If Switch_Y = "Y" Then                                    
                        Let EMAN$ = (.Cells( row, 1 ).Value)
                        
                        Dim MANItem As NotesItem
                        Set MANItem = doc.GetFirstItem("MTN")
                        
                        Let EMANTrim$ = Fulltrim(EMAN$)                                    
                        
                                                
                        Select Case Len(EMANTrim$)
                        Case "13"                  
                              
                              Call MANItem.AppendToTextList(EMANTrim$)  
                              Let Written = Written + 1
                              If Written > 999 Then
                                    Messagebox "Only 1000 Accounts could be imported at a time.",,"Customer Profile"
                                    Goto Done
                              End If
                              Print Written
                              Let tmpnullrows=0
                        Case "12"

                              Let EMANTrim$ = EMANTrim$ & " "
                              Call MANItem.AppendToTextList(EMANTrim$)  
                              Let Written = Written + 1
                              If Written > 999 Then
                                    Messagebox "Only 1000 Accounts could be imported at a time.",,"Customer Profile"
                                    Goto Done
                              End If
                              Print "Written Count is: " & Written & " (" & EMANTrim$ & ") "
                              Let tmpnullrows=0
                        Case "10"

                              Let EMANTrim$ = EMANTrim$ & "   "
                              Call MANItem.AppendToTextList(EMANTrim$)    
                              Let Written = Written + 1
                              If Written > 999 Then
                                    Messagebox "Only 1000 Accounts could be imported at a time.",,"Customer Profile"
                                    Goto Done
                              End If
                              Print "Written Count is: " & Written & " (" & EMANTrim$ & ") "
                              Let tmpnullrows=0
                        Case "8"

                              Let EMANTrim$ = "0000" & EMANTrim$ & " "
                              Call MANItem.AppendToTextList(EMANTrim$)  
                              Let Written = Written + 1
                              If Written > 999 Then
                                    Messagebox "Only 1000 Accounts could be imported at a time.",,"Customer Profile"
                                    Goto Done
                              End If
                              Print "Written Count is: " & Written & " (" & EMANTrim$ & ") "
                              Let tmpnullrows=0
                        Case "9"

                              Dim xlFilename1 As String
                              Dim Switch_A As String
                              Let Switch_A = ""
                              Call MANItem.AppendToTextList(EMANTrim$)  
                              Let Written = Written + 1                                                                                                                              
                              If Written > 999 Then
                                    Messagebox "Only 1000 Accounts could be imported at a time.",,"Customer Profile"
                                    Goto Done
                              End If
                              Print "Written Count is: " & Written & " (" & EMANTrim$ & ") "
                              Let tmpnullrows=0
                        Case Else
                              Let Dropped = Dropped + 1
                              Print "Dropped Count is: " & Dropped & " (" & EMANTrim$ & ") "                              
                              Let tmpnullrows=0
                        End Select                        
                  End If      
                  
                  Goto Process
                  
            End With
      Loop
      Return
      
      
Errorhandler:
      Print "Error" & Str(Err) & ": " & Error$
      Resume Next
      
Done:
      Print "Finished importing"
      
End Sub

Do to big no.s look OK in Excel itself?  If it displays OK in Excel try cells(r,c).text instead of value.  Best way though is to mark the cells as text in Excel.

Steve

Hi dragon-it,
 Just  an observation - chakrika, don't consider me for points here.

If the excel worksheet is returning scientific values then the cell is a formula.  Something like: =123456-456456, or =123456/123456
If you grab the cell value and change it to text,
        this= format(xlapp.range(rows,cols).value)
then this will end up:   1234-e02
----------------
if I convert the cell to it's text value:

    Dim curVal As String
    curVal = Format(xlapp.Range(rows,cols).Value)
    xlapp.Range(rows,cols).NumberFormat = "@"
    xlapp.Range(rows,cols).Value = curVal

Then I just end up with 1.0231231... rather than the botched formula.

So, you could check for formula, and then return the formula and parse out the "=" sign

 if     xlapp.Range(rows,cols).hasFormula then
    curVal =   format(xlapp.Range(rows,cols).Formula)
    curVal = extractID(curVal)              'Add a function that will step through and remove the stuff that you don't want
else
    curval = xlapp.Range(rows,cols).value
   select case typeName(curval(0))
    case "STRING"
     'do something
   case "INTEGER"
    'do something
   case else

   end select

end if

-----------
It's always hard to predict the quality of data supplied by clients, and invariably, you end up writing a whole lot of code to trap errors.


Good Luck! You're in good hands with sjef and steve.