As a part of the form, the user will select the trading hours from a dialog List, manually input the $ Sales and the customer count. Based on these, i will need to lookup the corresponding labour hours........
So if;
Trading Hours = 16
Sales = $16500
Customers = 62
Result would be 88
or..............
Trading Hours = 16
Sales = $21250
Customers = 139
Result would be 114

The result would be used to multiply by the labour rate p/hour (stored in a profile form) and display the "Benchmark" Labour Cost on the form so the user can compare actual to benchmark.

This table does not need to be displayed or referenced by the user, but will need to modified on an "As Needs" basis by the DB Manager.

I have some questions, because it's early and my brain needs revving up :(

- are your tables computed in Eksel and to be considered fixed in Notes?
- could you therefore put an entire table in one document, for the DB Man to be updated by?
- is the table fixed in size, both rows and columns?
- are the entries also fixed?

Already visible between the lines, but my solution would be
- to create a document per #Trading Hours
- create a form called TradingHoursTable
- make a field on the form to contain one value
- and use that table in your program.

If the Customer Count headers are stable, and the Sales From/To labels are stable, it would be a lot of fields on the form but it would certainly be doable with LotusScript.

For that matter, even if the headers and labels aren't necessarily stable, but are consistent across all the Trading Hours documents, this whole task would be inconvenient but not terribly difficult.

Let's assume a worst-case scenario: that the headers and labels must be user-definable with different options per Trading Hours document.

I would create a form that allows the DB Manager to define these amounts -- the same form s/he uses to set the results. For purposes below, assume that {FieldName} means there's a field of name FieldName.

Start, of course, with a field to identify which Trading Hours document this is. A dialog list would work nicely for that.

Create a table 9 columns wide by however many lines deep you need. Across the top row (excepting the first column) put fields {CustCount_1} through {CustCount_8}. Down the first column (excepting the first row) put the following:
{SalesFrom_1} to {SalesTo_1}
through
{SalesFrom_n} to {SalesTo_n}

where n is how many rows you have. (Actually, the {SalesTo_x} fields could be set up as Computed for Display, being one less than the next line's {SalesFrom_x+1} field.)

For each of the remainder of the cells in the body of the table, put fields {Benchmark_r_c} where r is the row number and c is the column number, e.g., {Benchmark_2_3} in your example above would have an 82 in it.

Populate all those documents in the database, one for each Trading Hours document.

Now you need to query your user for their input for what they want to look up. You can do this with a simple form where they fill in the blanks and then click a button, or you can have a series of input prompts pop up. Let's assume the latter.

Create an agent to run manually from the actions menu. use workspace.Prompt to collect the pertinent information. Use the user-supplied Trading Hours to get the correct document.

You can then use a for-next loop something like these to get the right field. This assumes that doc is the right document, customers contains the user-supplied customer count (as a number), and sales contains the user-supplied sales amount (as a number)

For intCount = 1 to 8
Set item = doc.GetFirstItem("CustCount_" & Trim$(Str$(intCount)))
If customers >= item.Values(0) Then intCustColumn = intCount
Next
' At this point, intCustColumn contains the number of the last column that had a count lower than or equal to the user-supplied number.
For intCount = 1 to n ' however many rows you have
Set item = doc.GetFirstItem("SalesFrom_" & Trim$(Str$(intCont)))
If sales >= item.Values(0) Then intSalesRow = intCount
Next
' At this point, intSalesRow contains the number of the last row that had a count lower than or equal to the user-supplied number.
Set item = doc.GetFirstItem("Benchmark_" & Trim$(Str$(intSalesRow)) & "_" & Trim$(Str$(intCustColumn)))

At this point, then, item contains your target field. You would have to multiply this by the amount set in your profile document to get your final number.

It's certainly conceivable, by the way, that the documents that store these tables could be created as profile documents with the Trading Hours used as the key rather than the user's name. The Notes documentation isn't very clear on that; it implies that user names will be used as keys for profile documents but that doesn't have to be the case. If that's the case, you'll have to have some action that prompts the DB Manager for which document they wish to define or modify and then open the appropriate document, as the document key has to be defined before you open the document.

I see now that that was your plan, sjef. I misread your comment and was thinking you were suggesting just having one rich-text field that held the complete table pasted in from Excel. I dread the thought of having to take that apart.

Oh, well. My mistake. Sorry to rain on your parade!

Besides, I usually go on the notion that too much detail is better than not enough. I still have a lot of threads where I have little idea what people are talking about when they give me answers and am always grateful when someone goes a little deeper.

Anyway, I could use the points. I haven't had 3K in a month for a while, and I just gave the last of my Q points to HamanthaKumar earlier this week. I have 10 points available at the moment. I hope nothing comes up...

I'm not in a panic yet, Sjef. Luckily I have no crisis to fix yet today, and as Notes work is not officially part of my job description (though I'm the only one in my part of the company who does it), I can probably avoid any catastrophies until I get at least 20 points to ask a question ;-)

I've been on the "I'm lost and need as much help as I can get" end of the stick enough times to be overly sympathetic. It's a curse...

- create a form, with the fields "FromTo", and "Trading Hours" , and then "Customers50" to "Customers150"
- now create a view, with columns TradingHours, FromTo, and the Customers colums.
- now you can import the excel file relatively easy, if the columns in Excel match the view-columns

Now you have the data

- create a some lookup views, categorized by TradingHours+FromTo, add the Customers columns....
- now you can lookup the required columnvalue :

Bozzie4's solution would work, but it requires a separate document for each row in the table. If you can accept that restriction his is fairly elegant and intuitive for the end user (the one who has to look things up more so than the one who has to maintain the tables).

Yes, separate document for each row is essential, but it's also easy to manipulate that way, of course. I wouldn't see it as a restriction :-)
If you should have R6 (which you don't sadly) you could also manage the data directly in the view, like in Excel (using inviewedit)...

I thought it will be a good idea to make a post as it will help in case someone else faces these issues. I trust this gives an idea how each entry in Notes.ini can mean a lot for the Domino Server to be functioning properly. This article discusses t…

Problem
"Can you help me recover my changes? I double-clicked the attachment, made changes, and then hit Save before closing it. But when I try to re-open it, my changes are missing!"
Solution
This solution opens the Outlook Secure Temp Fold…

In this video you will find out how to export Office 365 mailboxes using the built in eDiscovery tool. Bear in mind that although this method might be useful in some cases, using PST files as Office 365 backup is troublesome in a long run (more on t…

Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…