cwtaylo
asked on
Table Array(??) to provide number - best way to design/build - 500pts
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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
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.
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.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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...
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...
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
cheers,
Tom
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)...
cheers,
Tom
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
- 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?