Solved

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

Posted on 2004-09-29
14
157 Views
Last Modified: 2013-12-18
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:

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

Your suggestions and tips would be greatly appreciated.

Thanks in advance.
CT



0
Comment
Question by:cwtaylo
  • 4
  • 3
  • 3
  • +1
14 Comments
 
LVL 19

Accepted Solution

by:
madheeswar earned 125 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.

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

Assisted Solution

by:BarryTice
BarryTice earned 125 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

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

Expert Comment

by:BarryTice
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.
0
Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

 
LVL 46

Assisted Solution

by:Sjef Bosman
Sjef Bosman earned 125 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

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

by:Bozzie4
Bozzie4 earned 125 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)
@if(@iserror(result);"error";result)

Hope this helps

Tom



0
 
LVL 15

Expert Comment

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

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

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

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

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…
This is an old article, please see an updated version of this article, located here: http://www.experts-exchange.com/articles/23619/Notes-8-5x-Windows-7-Notes-info-and-tips.html
It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

747 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now