How to build tables for evaluation forms

[dmontano]

This is my current table structure that surrounds the problem I can not figure out:

Vendor Companies------Reviews------Criteria

A "Vendor" can participate in many "Reviews", a "Review" has many "Vendors" particpating.

A "Criteria" item is selected for many "Reviews", a "Review" has many "Criteria" items.

A Review is a process that determines:

1. What Criteria we want to evaluate or compare Vendors against

2. Which Vendors we want to Invite to participate in the Review

I can build a "Review Criteria Form" that all of the participating Vendors will be evaluated against, however, I do not know how to build the "Review Criteria Form" in which I can select via checkbox whether a particular vendor has "passed" or "failed" one of the criteria items.

From another perspective, I am trying to build a form or list of criteria that is tailored for the type of review that is being conducted. For example, if we are "Reviewing" for Printing Vendors, I want to be able to select which criteria items (maybe 30 to 50) will appear on the form so we know what to evaluate each vendor against.

As I mentioned earlier, I can build this form or list, but I do not know how to turn it into a "record" for each of the vendors who participated in the review so we can see which vendor passed or failed which particular criteria item.

I think I want the "records" to exist in the "Review Participants" table as that record is only created when a particular vendor is selected to participate in a particualr Review. However, I am assembling the Criteria form or list for a particular Review in the Review table because it is the Review itself that determines the criteria for that Review.

So, I am missing something. Another entity? And, how do I get the Criteria items to become a pass or fail checkbox of the Review Participants Record?

Any help is appreciated, thanks.

[comment]

Yes, you are missing an entity of Results - a join between ReviewParticipation and Criteria (or ReviewCriteria).

Note that I am describing a fully relational structure - possibly, you could cut a few corners by having the results directly in ReviewParticipation, as a checkbox using a value list based on related values from ReviewCriteria. However, this would limit your ability to produced detailed reports.

[dmontano]

Thanks Comment,

I reread what I posted and I sure did not make it easy to understand - even though I suspect you got what I meant. I would like to confirm and find out how to get my "Review Criteria" items into a form for each Vendor so we can checkoff each item per vendor in the review.

For my own sanity, I am going to try and restate what I am trying to acheive:

Imagine I have 50 items of criteria, such as:

1. Vendor must be located within 100 miles

2. Vendor must have own equipment

3. Vendor must have insurance

etc.

During a Review meeting, we decide which criteria is necessary for this particualr review, for example XYZ Review will evaluate vendors based on this criteria:

1. Vendor must be located within 100 miles

3. Vendor must have insurance

I would like this to be a record that identifies the criteria for this one review.

Next, we will select which vendors we want to evaluate agaisnt the criteria we selected. Say 3 vendors.

Next, I want to be able to generate a "Review Participant Criteria" form for EACH vendor selected to be reviewed. The form will have all of the criteria items we selected during the review.

Next, as each vendor is interviewed and evaluated, we will checkoff next to each criteria item whether that particular vendor has "passed" or "failed" that one particular "criteria" item. These three forms (1 for each vendor) will be kept in the database in order for us to compare and keep records of.

I think you somehow understood my initial post - but I can see how I may have made it very difficult. With that said, does your advice still apply? If so, how do I get the ability to have the criteria show up in each record in this new table of "Results"?

Also, can you clarify in your original response when you wrote:

"Note that I am describing a fully relational structure"?

Do you mean "Note" or "Not"? I do want to build this in the correct way, or structurally the right way.

Sorry that I am still stumped. Any help appreciated.

[comment]

Yes, that's what I understood - more or less. I am a bit confused by your using the term "form". In general, it's not useful to think in user-interface terms, such as forms and layouts, when you're making decisions about your data model. It's all about entities, attributes and relationships.

I wrote "Note" and I meant "Note". Note also that eliminating the Results table is not necessarily "incorrect": it all depends on the purpose. There's no point in adhering strictly to full normalization if there are no practical benefits to reap.

[dmontano]

Thanks again Comment for helping me gain a little more clarity.

When I say "form(s)", I should be saying "record". I am envisioning a form (record) as being a piece of paper that a person could look at that shows all of the Review Criteria items for the 1 Vendor with a checkbox next to each Review Criteria item that queries a response of either "pass" or "fail".

Since I want 1 record of each vendor's Review Criteria "Results" who participated in the XYZ Review, I should have 3 "Result" records.

You mentioned I need a "Results" entity - that makes sense.

You mentioned I can place this "join" entity inbetween either "Review Participation" and "Criteria"; or "Review Participation" and "Review Criteria".

I am thinking it should be inbetween Review Participation and Review Criteria since:

Review Participation establishes which Vendors are involved in one particular Review - and ReviewCriteria establishes what the "Criteria" items are for that one particular Review.

So would the the relationship be:

A Review Participant can have many Results, a Result is for one Review Participant;

AND

A Review Criteria can have many Results, a Result is of one Review Criteria

If so - I think I am halfway there.

A Result record would then contain all of the Review Criteria items? But how do I get them "in" the "Results" record? I have thought about duplicating the fields from criteria - but that makes no sense so I am obviously missing a fundamental mechanism in how this works.

If the Results table is acting as the table that allows me to enter a "pass" or "fail" checkbox next to each Review Criteria item - what fields would I define in the Results table to hold those "ReviewCriteria" items?

I hope you can read between all of these lines and see what I am trying to do. From what you have read should speak volumes of my lack of experience.

Lastly, your "Note" about the "Results" table not being necessary leaves me wondering then how would I record all of the "results"?...in the Review Participation table?

Any help is appreciated.

Edited July 24, 2008 by Guest
bad writing skills

[comment]

Let me take the last question first. Given:

Vendors ---< ReviewParticipants >--- Reviews ---< ReviewCriteria >--- Criteria

we can define a value list of ReviewCriteria, showing only related values starting from ReviewParticipants. That will give us all the relevant "questions".

To record the "answers", we use a text field Results, formatted as checkboxes. This is possible because (and only if) the answers are true/false. So at the end, the Results field will contain a return-separated list of those questions for which the answer was 'true'. To get a numeric evaluation, you would count the questions and compare them to the value count of Results, e.g. 3 out of 5.

Using a separate table for the results would be almost the same, except there would be a separate record in Results for each 'true' answer. With our Boolean arrangement, the Results table requires only two fields :

ReviewParticipantID

CriteriaID

This would enable us to group the results in a report in any way we want, since they are no longer bundled together in a ReviewParticipant record.

However, if your primary purpose is to find Vendors that meet certain criteria, you'll gain very little advantage here, if at all. On the contrary, it will be more difficult to find a vendor that meets a combination of criteria, for example.

[dmontano]

Thanks Comment - I have been soaking my head in this.

I was following your last response and feeling like you were unraveling "how" I can get this done - until your last sentence.

I am going to have to really think if we will want to search by the criteria "Results". I often assume I will be able to get whatever I need out of FileMaekr if I build my tables in a "correct" structure approach - but I am learning this is not a rigid environment. Furthermore - it seems a myriad of methods exist in how to tackle a given solution - which makes it difficult for me to grasp.

For now, I am going to clean up what I have and build the following tables:

1. Criteria

2. Review Criteria

3. Reviews

4. Review Participations

5. Vendors

6. Results (Not sure how to connect to others)

Earlier in the thread you identified a missing entity "Results". You mentioned I could connect it in between one of two sets of tables:

A: Review Participation and Criteria

or

B: Review Participation and Review Criteria

I choose the latter, B and stated "why" I thought that made sense in an earlier post of mine. However, this structure does not allow me to connect without having a circular reference. FileMaker creates a new TO of "Review Participation (2)"

Is this correct and as you would expect?

[comment]

it seems a myriad of methods exist in how to tackle a given solution

True - otherwise there would be no need to have Filemaker, or any other development environment. A couple of pre-canned solutions could take care of practically any situation.

However, this structure does not allow me to connect without having a circular reference. FileMaker creates a new TO of "Review Participation (2)"

Yes, that's how it's supposed to be - except my preference would be to have a second occurrence of the ReviewCriteria table, rather than of ReviewParticipation.

Note that you might need to attach more TO's of other tables to this new occurrence, if you want to see their data from the point-of-view of Results.

[dmontano]

Comment - THANK YOU for hanging in there with me on this.

I will scratch the second TO of Review Participation and create a a second TO of Review Criteria.

As a side note: I have searched high and low for a book, links, demos, etc. specifically covering business processes and how to integrate them into a filemaker solution for development - and I have not found ANY such book.

I have 4 FM books and NONE of them touch on a situation as I am describing where I can extrapolate for my use. I have found a wealth of information regarding implemetation of the tools for Filemaker, but the table structure for business processes (such as I have described in this thread) truly eludes me.

I find this peculiar. Many of my struggles relate directly to modeling FileMaker to facilitate business processes.

I suspect many "developers" at my level and below have many solutions that are not optimized to reflect the true business process within Filemaker. I have read plenty of threads where the advice given is to first fix the structure. I truly think that if an advanced developer were to create a book that illustrates a couple dozen scenarios of business processes - it would be a big seller.

I find a corollary in the forum boards as well. If there was a section in this forum (business process modeling) that would be great. Each thread begins with a unique business process and all contributions are restricted to that one business process. It would be enlightening to see the different approaches that others have taken regarding that one topic.

The 4 books I have mentioned (while good in their own right) all basically go over the same topics - and never delve into what I have mentioned.

I find myself turning to this forum for such help. Thankfully this forum exists!

[comment]

That's an interesting point. I am not sure how one would approach such a task - it seems to me somewhat similar to writing a book on "How to design a car" or "How to tell a joke". At best, one can share some anecdotal experience that may be relevant to the reader's situation - or not.

BTW, data modeling for Filemaker is the same as data modeling for any relational DB (though the implementation may be quite different), so perhaps you may find what you're looking for in the more general RDBMS library.