Adding Records in a Field

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!
Who is Participating?
Sjef BosmanConnect With a Mentor Groupware ConsultantCommented:
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
The Trim will reduce that to
@TextToNumber will turn it into a values list
@Sum will add the values in the list
Sjef BosmanGroupware ConsultantCommented:
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.
macjacintoAuthor Commented:
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
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

Hi sjef_bosman,

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

Best regards,
Sjef BosmanGroupware ConsultantCommented:
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"
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.
Sjef BosmanGroupware ConsultantCommented:
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":"\\~"; " ":"\\")
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.
macjacintoAuthor Commented:
tnx a lot :D
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.