Table Array(??) to provide number - best way to design/build - 500pts

I am tasked with providing a benchmark figure to be displayed based on several factors within a Profit & Loss Form that I'm building.

My question basically is what would be the best way to build this beast ( am using R5).

The benchmark is for Labour Hours.

I need to display a result on three fields in the form -  Shopsales ($ turnover) & Customer Count (number) & Trading Hours (number)

The table (already built in excel, but need to provide the solution in notes) is like this:

                Trading Hours 16
 Sales              |    Customers
From   -   To    |  50+  60+ 70+  80+  90+  100+  125+ 150+
$10000 - 12499|   72   74    78    80    82     86     88     90 |
$12500 - 14999|   76   78    82    84    86     90     92     94 |
$15000 - 17499|   86   88    92    94    96    100   102   104 |
$17500 - 19999|   92   94    98   100  102   106   108    110 |
$20000 - 22499|   98  100  104   106  108  112   114    116 |
.......and so on
There is a table for each variant of trading hours being...12, 16, 18, 24
The sales are in increments from 0-9999 up to 60,000

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
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 only have next week to have this built and in pilot.

Your suggestions and tips would be greatly appreciated.

Thanks in advance.

Who is Participating?
madheeswarConnect With a Mentor Commented:
You can achieve it by generating a Report through Lotus Script.
Else you can manupulate all the required to display this format at form level using Lotus Script.

My assumption, it is doable. But through workaround. No straight method.

Through direct view, it will be somewhat difficult.

Sjef BosmanGroupware ConsultantCommented:
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.

Is this a way to proceed?
BarryTiceConnect With a Mentor Commented:
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}
{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)

dim item as NotesItem
dim intCount as Integer
dim intCustColumn as Integer
dim intSalesRow as Integer

For intCount = 1 to 8
   Set item = doc.GetFirstItem("CustCount_" & Trim$(Str$(intCount)))
   If customers >= item.Values(0) Then intCustColumn = intCount
' 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
' 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)))

Messagebox "Your looked up benchmark number is " & item.Text, 0, "Benchmark Report"


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.

Does this help any?

-- b.r.t.

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Sjef BosmanGroupware ConsultantCommented:
Hi Barry, I thought I'd just give the big picture, but you already supplied the details! Well, thank you! The next one should post full code, please...

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

-- b.r.t.
Sjef BosmanConnect With a Mentor Groupware ConsultantCommented:
So you're badly in need of 6 big ones! Today! You'd better shoot at every parrot passing by (suppose you know the joke about free-fall parachuting). See

Usually, I don't give the details, as I think that there must be something left to do for the asker. My advice is for free, the rest is (supposed to be) paid.
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...
Bozzie4Connect With a Mentor Commented:
I'd say :

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

the key will get you, in your example
Trading Hours = 16
Sales = $16500
Customers = 62

(how to translate the sales and customers values to the keys, I will leave up to you , it"s not too difficult)

key := "16"+"15000";
getValue := "Customers60";
result := @dblookup( "":"nocache" ; @dbname;"theview" ; Key; getvalue)

Hope this helps


A, yes, you now can create a categorized view, you can embed it in the form , and use Show Single Category to show the user the corresponding "benchmark" results


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


Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.