Solved

Adding Records in a Field

Posted on 2003-10-25
9
248 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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
 
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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

You’ve got a lotus Domino web server, and you have been told that “leverage browser caching” is a must do. This means that we have to tell the browser everywhere in the web to use cache. In other words, we set (and send) an expiration date in the HT…
IBM Notes offer Encryption feature using which the user can secure its NSF emails or entire database easily. In this section we will discuss about the process to Encrypt Incoming and Outgoing Mails in depth.
Monitoring a network: why having a policy is the best policy? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the enormous benefits of having a policy-based approach when monitoring medium and large networks. Software utilized in this v…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

696 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