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

Posted on 2004-09-29
Medium Priority
Last Modified: 2013-12-18
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.

Question by:cwtaylo
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
  • 4
  • 3
  • 3
  • +1
LVL 19

Accepted Solution

madheeswar earned 500 total points
ID: 12187159
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.

LVL 46

Expert Comment

by:Sjef Bosman
ID: 12187934
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?

Assisted Solution

BarryTice earned 500 total points
ID: 12190239
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.

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

LVL 46

Expert Comment

by:Sjef Bosman
ID: 12190373
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

Expert Comment

ID: 12190545
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.
LVL 46

Assisted Solution

by:Sjef Bosman
Sjef Bosman earned 500 total points
ID: 12190851
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 http://www.123easyaspie.com/pat16.html

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.

Expert Comment

ID: 12191187
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...
LVL 15

Assisted Solution

Bozzie4 earned 500 total points
ID: 12192636
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


LVL 15

Expert Comment

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



Expert Comment

ID: 12193597
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).
LVL 15

Expert Comment

ID: 12193624
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)...



Featured Post


Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

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…
Suggested Courses

801 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