Display if count great than x

Posted on 2005-03-09
Medium Priority
Last Modified: 2013-12-18
So far we have.  Lotus Notes R5

SELECT ((Form = "First Level Fault") | (Form = "New Fault") | (Form = "Second Level Fault")) &  (@Date(@Adjust(@Now;0;0;-31;0;0;0))<@Date(Created))

Every time a customer calls a fault is created.  We wish to modify to show if customer haved called more than 10 times in the last 30, 60 days or whichever. Only display these companies

Thanks in advance.

Question by:skiltz
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
LVL 19

Expert Comment

ID: 13503349
Then u need to track the no.of times the doc is updated (called).
Place a field in the form and update when ever a call is made and we can have a simple selection formulae.

if you are looking at docs which are modified within 30-60days, this can be achieved through formulae(viwe selection formulae)
Which one u are looking at?
LVL 15

Accepted Solution

Bozzie4 earned 200 total points
ID: 13503534
This is not possible using views, because you want your selection formula for 1 document depend on other documents.  You can show the companies Total Call Count over the last 30 days , 60 days, however using your view.  Make sure the view is categorized by Company, add a column, give it a value 1 (number), then set the "Totals" property to "Total", and "hide detail row".

If you want to filter out the companies that have 10 calls or more, you can write an agent that selects these documents, and then puts them in a folder (that can have the same setup as the view above).


LVL 31

Assisted Solution

qwaletee earned 200 total points
ID: 13513575
Make a hidden view that sorts on customer ID, and only includes items in the past X days (30 days or 60 or whatever)

Modify the code in the fault form, so that when it is created, it looks up matching values form teh hidden view, and stores the count of them.

Now, display where count is >= 9 (because 9 plus the new one will be 10).

Where this falls down:
Let's say your period is 30 days.
You get a new customer.  First two calls are on January 1, next cal is January 2, then six calls on January 30.
Fault 1 - Jan 1 - count is zero
Fault 2 - Jan 1 - count is 1
Fault 3 - Jan 2 - count is 2
Fault 4 - Jan 2 - count is 3
Fault 5 - Jan 30 - count is 4
Fault 6 - Jan 30 - count is 5
Fault 7 - Jan 30 - count is 6
Fault 8 - Jan 30 - count is 7
Fault 9 - Jan 30 - count is 8

Now, nothing still shows in the view, because fault 9 has a count of 8, which is not >= 9.  So far so good

Fault 10 - Jan 30 - count is 9

Now, fault 10 shows up in the view.  Also so far so good (asuming you only need teh most recent call to show in the view, so long as there are nine others that occured, but the nine others do not show in the view).  Where does that fall down?

Let's say customer calls again January 30

Fault 11 - Jan 30 - count is 10

Fault 11 shows in the view (good) but Fault 10 is also still showing in the view.  OK, not so bad, at least no false information, right?

It gets a little worse.  Let's say it is now January 31.  Faults 1 and 2 are now over 30 days old, so they don't count, and they fall out of the hidden view.  So far so good.  BUT, that now means that I have 11 faults, 2 of them not counting, so I have only 9 faults that count.  that means the customer should not show up in the view at all.

But the count is a static field value.  So, faults 10 and 11 still have counts of 9 and 10, and both still show up in the view.

What to do?  Well, you can allow it to remain in the view, and they will naturally fall off within 30 days, because this count-based view still has a 30-day timer built in (make sure you do that!).  Or, you can have an "agin" agent that once a day updates the counts in the fault documents, so they are accurate as of that day.

LVL 31

Expert Comment

ID: 13513583
y the way, this stuff is really made for relational databases, which do the aggregation nicely on a query.  It is caled a self-join.

You can probably use Access or Crytal against Notes, using NotesSQL and ODBC, to get essentially teh same ersult in a report that displays outside of Notes.
LVL 18

Expert Comment

ID: 16161399
No comment has been added to this question in more than 21 days, so it is now classified as abandoned.

I will leave the following recommendation for this question in the Cleanup topic area:
    Split: Bozzie4 {http:#13503534} & qwaletee {http:#13513575}

Any objections should be posted here in the next 4 days. After that time, the question will be closed.

EE Cleanup Volunteer

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Lack of Storage capacity is a common problem that exists in every field of life. Here we are taking the case of Lotus Notes Emails, as we all know that we are totally depend on e-communication i.e. Emails. This article is fully dedicated to resolvin…
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.
In this brief tutorial Pawel from AdRem Software explains how you can quickly find out which services are running on your network, or what are the IP addresses of servers responsible for each service. Software used is freeware NetCrunch Tools (https…
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …
Suggested Courses

752 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