Solved

Adding Records in a Field

Posted on 2003-10-25
9
244 Views
Last Modified: 2013-12-18
I have the ff fields in a form. Each field is of text data type (except the amount w/c is of number data type), allows multiple value option is enables. I populated the field via a dblookup formula. My problem is how will I get the total amount of all records w/ transaction code 10 and total amount for all records w/ transcaction code 60. And also, I have a view with branch code and branch name column, from my branchcode field here, how will i get the corresponsing branch name?

Branchcode AcctNum   TransCode      Amount
1      00000001                60            1000.00
2      00000002                10            1000.00
3      00000003                10            1000.00
4      00000004                60            1000.00
5      00000005                10            1000.00
6      00000006                60            1000.00
7      00000007                10            1000.00
8      00000008                10            1000.00
9      00000009                60            1000.00
10      00000010                60            1000.00

Hoping for a prompt reply. Thanks in advance!
0
Comment
Question by:macjacinto
  • 4
  • 3
  • 2
9 Comments
 
LVL 46

Expert Comment

by:Sjef Bosman
ID: 9619046
Assuming the number of elements in all fields is equal, you could try some of the following:

AllRecords:= BranchCode + ";" + AcctNum + ";" + TransCode + ";" + @Text(Amount);
TotRec10:= @Sum(@TextToNumber(@Right(AllRecords; ";10;")))

The second question I don't understand, for you seem to have used @DbLookup already. But beware, the sorting in a view is different from the list you gave, for you will find BranchCode 10 first, then 1 and the rest. So please some more info.
0
 

Author Comment

by:macjacinto
ID: 9619100
here's the result when i tried the code

21423.02; 57555.16; @ERROR; @ERROR; 13578.99; 3132.57;
@ERROR; @ERROR; 211346.57; 2.4; 12163.69; 99618.48; @ERROR; @ERROR;
@ERROR; 2095328.73; 78336.36; 222574.01; @ERROR; @ERROR; 579643.92;
24242.51; 4214.38; @ERROR; @ERROR; 42325.98; 31981.35; 7210.74; @ERROR;

@ERROR appeared at the amounts with transaction code 10. The visible amounts are with transcode 60. The opposite happened with transcode 60, only amounts transcode 10 are visible
0
 
LVL 46

Accepted Solution

by:
Sjef Bosman earned 500 total points
ID: 9619446
Ah, correct, I forgot an essential part of the statements. Excuse me...

AllRecords:= BranchCode + ";" + AcctNum + ";" + TransCode + ";" + @Text(Amount);
TotRec10:= @Sum(@TextToNumber(@Trim(@Right(AllRecords; ";10;"))))

The Right is supposed to produce
   "":"1000.00":"1000.00":"":"1000.00":"":"1000.00":"1000.00":"":""
The Trim will reduce that to
   "1000.00":"1000.00":"1000.00":"1000.00":"1000.00"
@TextToNumber will turn it into a values list
@Sum will add the values in the list
0
 
LVL 31

Expert Comment

by:qwaletee
ID: 9621295
Hi sjef_bosman,

I'm going to guess that you started using Notes earlier than R4.

Best regards,
qwaletee
0
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
LVL 46

Expert Comment

by:Sjef Bosman
ID: 9622008
Goodness, must have been R3.21 or so, somewhere in the previous century (1995?). Ah, the days before LotusScript... List operations are very interesting, because lists are equivalent to sets (Set Theory). Mostly all operations are implemented but you really have to look for them. Set addition is easy, set comparison can also be done, but intersection and set subtraction are more difficult. Would be a nice puzzle, wouldn't it? Say you've got 2 sets, and want to obtain the difference, what to do?

Just for having fun:
x:= "1":"4":"6";
y:= "2":"4":"6":"8";

How to obtain (not necessarily in the same order in the list)
a. "1":"2":"4":"6":"8"
b. "4":"6"
c. "2":"8"
0
 
LVL 31

Expert Comment

by:qwaletee
ID: 9625258
a := @Unique(x : y);
c:= @Trim(@Replace(y; x; ""));
"Of course, that doesn't work if there are extra spaces within a set memeber, or null set members -- that's my challenge to you!";
b := @Trim(@Replace(y; c; ""));


First exposure = 1.x, first use = 2.0, first admin and basic programming = 2.1, first serious work = 2.1a.
0
 
LVL 46

Expert Comment

by:Sjef Bosman
ID: 9625692
Full A, but for someone whose Notes-experience dates back to the 80s this must be a trivial thing. Null set members can be handled using an additional character, I use "~" for this purpose normally. Add two extra lines (can do without if necessary, more unreadable code):

x1:= x + "~";
y1:= y + "~";
a := @RightBack(@Unique(x1 : y1); "~");
c:= @RightBack(@Trim(@Replace(y1; x1; "")); "~");
b := @RightBack(@Trim(@Replace(y1; c1; "")); "~");

Preserving the extra spaces can be done in a similar way, by
   @ReplaceSubstring(x; "\\":" "; "\\~":"\\s")
for starters and eventually
   @ReplaceSubstring(@ReplaceSubstring(a1; "\\s"; " "); "\\~"; "\\")

The function below probably won't work in all cases, for someone else to prove:
   @ReplaceSubstring(a1; "\\s":"\\~"; " ":"\\")
0
 
LVL 31

Expert Comment

by:qwaletee
ID: 9627652
80s?  No, Notes was first beta'd in 88/89, I think, and released in 89/90.  I saw it (hands off) 90/91.  One of my colleagues was doing engineering on indexing changes between, I think 1.0 and 1.1.

I don't like the use of ~ as separator, because it can easily be entered on a keyboard.  @Char(250) and @Char(255) works well, and look like a tiny dot and a space respectively.

A way to avod the trim without a separator:

nullCommonY := @Replace(y; x; "");
oneNullCommonY := @Unique(nullCommonY : "");
c:= @If(y="" & !x=""; oneNullCommonY; @Subset(oneNullCommonY;@Elements(oneNullCommonY)-1));


And then do something similar for B.
0
 

Author Comment

by:macjacinto
ID: 9807922
tnx a lot :D
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Problem "Can you help me recover my changes?  I double-clicked the attachment, made changes, and then hit Save before closing it.  But when I try to re-open it, my changes are missing!"    Solution This solution opens the Outlook Secure Temp Fold…
Notes Document Link used by IBM Notes is a link file which aids in the sharing of links to documents in email and webpages. The posts describe the importance and steps to create a Lotus Notes NDL file in brief.
Learn how to create flexible layouts using relative units in CSS.  New relative units added in CSS3 include vw(viewports width), vh(viewports height), vmin(minimum of viewports height and width), and vmax (maximum of viewports height and width).
As a trusted technology advisor to your customers you are likely getting the daily question of, ‘should I put this in the cloud?’ As customer demands for cloud services increases, companies will see a shift from traditional buying patterns to new…

867 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

18 Experts available now in Live!

Get 1:1 Help Now