• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 184
  • Last Modified:

Display if count great than x

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.

2 Solutions
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?
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).


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.

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

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

Tackle projects and never again get stuck behind a technical roadblock.
Join Now