problem getting a value for two seperate terms. (cross-classified table?!) help!

[Winddancer]

Hi all,

I have a problem.

In my Filemaker document are three fields.

Two value lists containing text.

One field which shall display the result.

e.g.: Value list one has jobs: "Designer, Programmer, Support".

Value list two has tasks: "Repairing Computer, Creating HTML page, Helping Customer".

Now when I enter my document I choose a job (value list 1) and a task (value list 2).

The last field shall now display the time needed.

Designer needs for:

- HTML PAGE - 1 hour

- REPAIRING COMPUTER - 3 hours

- HELPING CUSTOMER - 2 hours

Programmer needs for:

- HTML PAGE - 2 hours

- REPAIRING COMPUTER - 3 hours

- HELPING CUSTOMERS - 1 hours

So if I chose Designer as a job and Helping Customer as a task, my third field would display 2 hours.

this is just an example.

the easiest way to explain is: you draw yourself (on a piece of paper of cause) a table.

x axis for jobs

y axis for tasks

And I need the result of the x - y match.

I hope I made myself clear. My english isn't all too good sorry for that.

Unfortunately I couldn't find this anywhere and I got to present this feature tomorrow. argh.

So if you have any idea how to solve this:

PLEASE LET ME KNOW!

regards

Sven

Germany

[ February 07, 2002, 03:18 PM: Message edited by: Winddancer ]

[danjacoby]

The result field can have a number of possibilities, that number being expressed algebraically as "xy", which is "x" times "y".

Use a calculation with the "Case" function. That way, you can enter each possible combination and its result. For instance:

Case(x="Designer" and y="HTML Page", 1,

x="Designer" and y="Repairing Computer", 3,

x="Programmer" and y="HTML Page", 2, ...)

and so on.

This particular instance should return a number. I'd label the field "Hours", or some such name.

HTH,

Dan

[Winddancer]

Yes, indeed this case scripting is working.

Unfortunately I have far more than 500 possible results and you can imagine how many cases I have to script.

I tried this however, but Filemaker only allows up to 30.000 characters per calculation.

Is there any other way to store this x / y table?

Best would be if I could add items to the x and/or y row/column.

best regards

Sven

[kraftyman]

I have a slightly different suggestion

You could use the feature of calculations which ignores text when generating an answer formatted as a number. For example

" 1 worker" * "12 jobs" = 12

"10 bosses" * "1 million shares" = corporate disaster ....

What you would then need to do is to generate a different value list for the job hours depending on which worker got picked. Then once the worker got picked, you could direct the user to another identical layout, but with a different value list for the work hour values. Then your unstored calculation field would give you the right number. If it is hours you want, then just use a label on the layout , and leave the filed value alone. This avoids any complex scripting. If the list is going to be very long, then I suggest using another file as the list source +/- some relationships to cut down your workload.

Is this any use?

[ February 08, 2002, 04:45 AM: Message edited by: kraftyman ]

[LiveOak]

Perhaps I don't understand, but I'm assuming that the third field is not a simple multiplication (or other operation) on the first two. I would do a table lookup from a second file. This second file would contain a table of fields which might have:

Task1Code (text)

Task1Name (text)

Task1Hours (text)

Task2Code (text)

Task2Name (text)

Task2Hours (text)

ResultName (text)

ResultHours (text)

Key (calculation, text, indexed) = Task1Code & Task2Code

The reason I added codes for the tasks is that it is easier to build unambiguous compound keys from codes. To make an entry in your main file, have Task1CodeEntry and Task2CodeEntry fields with values lists based upon fields in your new Table file (use Code and Name to make it easy). In you mail file also have a calculated key field:

PrimaryKey (calculation, text) = Task1CodeEntry & Task2CodeEntry

Create a relationship which matches PrimaryKey <---> Key. Display related fields for:

Task1Name (text)

Task1Hours (text)

Task2Name (text)

Task2Hours (text)

ResultName (text)

ResultHours (text)

The lookup is driven by the key created by combining the two tasks. The only other thing to be done is to fill up the second database with the 500 entries. A lot, but still easier than a case statement.

-bd

[Fitch]

This may be too late for your presentation, but...

You should definitely do this as a lookup from a related file. Your JobLookup file, as I understand your problem, only needs 4 fields: Job, Task, Time, and JobTask, which is a calculated text field that = Job & Task.

Make a similar calculated JobTask field in your main file and a Relationship to your JobLookup file based on JobTask. The Time field in your Main file will do a lookup based on this relationship.

One advantage of this method is that it's easy to add new jobs/tasks, you just add a new record to your JobLookup file, rather than messing with a complex calculated Case().

Another advantage is that in FileMaker 5, you can use Value Lists based on Relationships, so when you choose, say, "Designer" as a job, you can have the "Tasks" list show only those jobs that pertain to Designers. Cool!