Calculation based on a value list choice

[chrisgv]

I'm ridiculously new at FM and I have a question that I think would be a fairly simple solution (but maye not). I'll try and explain my problem.

I have the fields;

Product 1

Product 2

Product 3

Each of these fields has a pop-up choice where a user can choose from a list of values (all three fields draw from the same value list).

I have another field called;

Effort Level

The list of values looks something like this:

Poster

Pamphlet

Kit Folder

Based on what the user picks, I want the Effort Level field to change to a numeric value.

For example, a poster has a effort value of 1, a pamphlet has a effort value of 2 and a kit folder has an effort value of 3.

So if a user picked: poster from one pop-up menu and kit folder from the other pop-up menu, the Effort Level field would change to 4.

Hope I'm being clear...any help or pointers is greatly appreciated.

[comment]

Why three and not two or five or hundred?

[dansmith65]

Regarding the effort level; it sounds like you will want to create a table with "Product" and "EffortLevel" fields. Then define your value list to display values from the related table.

This will allow you to lookup the effort level that matches the product.

Please don't overlook comment's question though; you may want to re-think using separate fields for Product 1, Product 2, and Product 3.

(research using related table/portal for this)

[comment]

create a table with "Product" and "EffortLevel" fields. Then define your value list to display values from the related table.

This will allow you to lookup the effort level that matches the product.

With three individual "Product X" fields, you would also need three separate relationships.

[chrisgv]

When we get assigned jobs, it's usually only one product, however, sometimes it's two. I decided to make three, just in case.

I still have no idea where to start... The effort level field has to be the sum of all three product fields.

[comment]

Can you elaborate on what your solution is about? There are at least two ways to do this, and I am not sure which one would be more suited.

[chrisgv]

Sure, I'll see if I can be more clear.

I work in a design shop. When a client sends us a request to do a particular job, I want to add it to a database.

I have fields such as; client, date requested, date due, date completed, company name.

I also have to show what product they're requesting, such as a poster or a pamphlet or maybe even a web button.

Sometimes clients request more than one product (not often, but occasionally).

Say we do 3000 jobs a year. It's a lot more effort for us to do 3000 posters than it is to do 3000 web buttons. We don't want to track our time, we just want an estimate of how busy we are per year.

I thought that if we rated the products on a scale of 1-10 (1 being easy and 10 being difficult), we could get an overall effort level at the end of the year.

This brings me back to my original request.

I don't really need to have this, I can just run a report on how many of the different products we get at the end of the year and then figure it out that way, I just though it would be a neat feature to have.

[comment]

I would recommend you use the well-established model of invoicing, where you have (at least) three tables:

• Invoices (Jobs in your description)

• LineItems

• Products

See a basic demo here:

http://fmforums.com/forum/showpost.php?post/309136/

If you assign each product an effort level (in the Products table), you can easily produce a report of the kind you describe from the LineItems table - since every line item is related to a specific product, and thus "knows" the associated effort level.

[chrisgv]

Thanks for your help comment! From your example, I was able to figure out how to solve my problem :(