I have a view which current has around 46000 documents, with around 3000 new documents per month. most of documents I uploaded in month end. Problem is with some document that I will need to create individually, but only 10 documents per month. The problem is that I will like to immediately see how it appear by subtotal after I have entere the number, but the view will take too long, for such feature to be implemented in group wide company for end user, as I understand notes will need to refresh the indexes?
Example: year end projection as at now is USD 1.3m. After inputting new document (forecast document), say 3 documents which give to a total year end projection of USD 300k, so new Year End projection number should be USD 1.6m. The problem is that I will need to wait around 30 minutes, for me to see the USD 1.3m change to USD 1.6m.
Or will it be faster by creating a page with a lookup field that will do lookup to another view, but it only displayed up to year end numbers only?
Subject: RE: Financial Reporting view take too long, any suggestion to simplify programming
tqvm Shawn, you really like the Shawn in 4400, with magic hands helping curing people’s problem, but smarter. I have been very curious on how to do write an agent, as I am not familiar with profile document, if that is what you refer to. But I will appreciate if you can bit more details on how to achieve this in the column for the view, as @lookup still do not work in view right, and the field will not exist in my forms, so how am I suppose to do this? tqvm in advance.
Subject: RE: Financial Reporting view take too long, any suggestion to simplify programming
Never seen the 4400 but thanks, I think!?!
You don’t use an agent to calculate the value. The thing to understand is, if you make a form and calculate the total you want, you can then just use that field in your column formula to display the total you want.
You should be able to add up the fields like this in a column:
valueA + valueB + valueC
Where ValueA, ValueB and ValueC are field in your database where the view is.
Or if you need to lookup multiple values from mulitple locations, you could do something like this
valueA := @DBLookup()
ValueB := @DBLookup()
ValueC := @DBLookup()
then do this,
valueA + valueB + valueC
In this second case the values will be returned from the lookup, not fields in the existing DB, allowing you to show totals for values from different databases.
Subject: RE: Financial Reporting view take too long, any suggestion to simplify programming
Okay, let me know which is your favourite series, Numbers, I guest, or Nip Tuck :)? Your suggestion is useful for me to compute the percentage at document level. However, I need the % at category level, which requires the total value capture by column, via the total selection in the column properties, not by document level. So, how am I suppose to do this? Really appreciates your kind assistance.
Subject: RE: Financial Reporting view take too long, any suggestion to simplify programming
The only way I know to do this manually is to do something like using a series of if statements. You can loop docs etc, however I still think you should calculate the values in a field on a form and display those fields.
Subject: RE: Financial Reporting view take too long, any suggestion to simplify programming
Sorry if my earlier quesiton is bit confusing. The problem is that I need percentage by total category of documents, thus, even though there is a field in the document, it will only give me total as per category of document against total of all having the same categories. I will want to have a percentage number, of total of another variable, example, production.
Eg:
Cost Produciton Ave
10000 5 2000 each
say this is made up of 5 document
2000 2 1000 each
6000 1 6000 each
1000 1 1000 each
1000 1 1000 each
as you see, it gives different value if goes by document, what I want is average per all of same category.
Subject: RE: Financial Reporting view take too long, any suggestion to simplify programming
So, would the average not be 10000/5? That would give an average over the documents. If you want another average, you have all of the data there, you just need to calculate it.
If we take another example, say NotProduction that has the following docs:
3 @ 2000 = 6000
2 @ 3000 = 6000
4 @ 1500 = 6000
This is a total of 9 documents and 18000 units.
So the average would be 2000 units for the NotProduction category. Am I following correctly?
So, to get this data programatically you would need to grab a document collection and loop through the docs sorting the category you want and totalling and averaging the values.
Subject: RE: Financial Reporting view take too long, any suggestion to simplify programming
No, still don’t answer my query. I don’t need average by document, but average by category. Would appreciate if you can provide the coding for me, :).
Document 1: USD 2000, 3 Unit
Document 2: USD 1000, 2 Unit
Document 3: USD 2050, 4 Unit,
Assuming all is same category, I need another column which will give value of 2000+1000 + 2050 / 3 + 2 + 4,
but I cannot get this value if I go at document level, i.e value/ unit, as the average for this 3 documents will be average of the respective average at document level.
(assuming selecting average under total for the column selection).
Subject: RE: Financial Reporting view take too long, any suggestion to simplify programming
OK,
I got it…You just have to beat me with a stick…I have a hard head.
You want something like this:
Dim DC as NotesDocumentCollection
dim nextDoc as NotesDocument
Dim totalDollars, average as double
Dim counter as Integer
While not nextDoc is nothing
If nextDoc.CategoryField = “Category” then
Dollars = nextDoc.MoneyField(0)
counter = counter + 1 ’ increment the counter to use as the denominator
average = Dollars/counter
nextDoc = DC.GetnextDocument(nextDoc) ’ get the next Document
End If
wend
You would have to change the category string every time you run the code to get different category totals. You could always abstract it to whatever level you need to get it to run on it’s own. Not sure this would be so good in a view. You might need to categorize your first column and put this is a second column.
possibly you could have two hidden columns that tally each thing and divide the columns.
Subject: RE: Financial Reporting view take too long, any suggestion to simplify programming
tq, Shawn, but my denominator is the value of another field, thus this formula should be:
production = nextDoc.ProductionField(0)
so , UnitProductionCost=Dollars/production
Using script in column yeah? Perhaps easier I create another view, than create another form, with two fields, each lookup by the total category,produciton and value, and create another field to compute unit cost.
Problem is that this will only give me price of one category at one time, so I guest I have to create one document for each category.
I was thinking in notes 7, there is something easier, like they have improved this feature.
Subject: RE: Financial Reporting view take too long, any suggestion to simplify programming
You should be able to create one form with all of the fields. Then create a view that displays them, one field per column. Then use @dblookup to retrieve the values.