Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
Solved

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

Posted on 2004-09-29
Medium Priority
165 Views
Experts,
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:

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;
Sales = \$16500
Customers = 62
Result would be 88
or..............
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.

CT

0
Question by:cwtaylo
[X]
###### 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
• 4
• 3
• 3
• +1

LVL 19

Accepted Solution

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.

Thanks
0

LVL 46

Expert Comment

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?
0

LVL 7

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

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

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.

0

LVL 46

Expert Comment

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
0

LVL 7

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.

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

LVL 46

Assisted Solution

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

LVL 7

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

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
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)
@if(@iserror(result);"error";result)

Hope this helps

Tom

0

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

cheers,

Tom
0

LVL 7

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

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

cheers,

Tom
0

## Featured Post

Question has a verified solution.

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

For Desktop Techs: How to retain a user's Notes configuration data when swapping out the end user's computer. (Assuming that you are not upgrading to a completely different version of Notes client) All you need to do is: 1) install Notes oâ€¦
For users on the Lotus Notes 8 Standard client, this article provides information on checking the Java Heap size and adjusting it to half of your system RAM in attempt to get the Lotus Notes 8.x Standard client to run faster.  I've had to exercise tâ€¦
This course is ideal for IT System Administrators working with VMware vSphere and its associated products in their company infrastructure. This course teaches you how to install and maintain this virtualization technology to store data, prevent vulnâ€¦
Weâ€™ve all felt that sense of false security beforeâ€”locking down external access to a database or component and feeling like weâ€™ve done all we need to do to secure company data. But that feeling is fleeting. Attacks these days can happen in many wâ€¦
###### Suggested Courses
Course of the Month10 days, 1 hour left to enroll