Solved

Adding Records in a Field

Posted on 2003-10-25
9
243 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
What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
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

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

  In today’s Arena we can’t imagine our lives without Internet as we are highly used to of it. If we consider our life style just for only 2 min we found that face to face communication is swapped by e-communication.  Every Where from Works place to…
Article by: Rob
Notes 8.5 Archiving Steps and Tips This article covers setting up a Notes archive, and helps understand some of the menu choices making setting up and maintaining a Notes archive file easier.
This video discusses moving either the default database or any database to a new volume.
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…

708 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

14 Experts available now in Live!

Get 1:1 Help Now