it must be something simple......

[Samdj]

I have created two tables with a join table inbetween, I am trying to make a layout which will allow me to enter data in both tables on the same screen. I have tried to use a portal, as described in "using filemaker pro 7" to no avail, I am now attempting to use the "create fields in layout" tool, also as described in "using filemaker pro 7" and I am still not able to enter data.... what am I doing wrong???

I can attach the file if that would help..

Thanks for any help in advance....

[Fenton]

Yes, you should probably attach your file. It sounds like a simple fix. Why don't you also describe a little in plain English what your wanting to happen, in case it's not crystal clear. The "create fields in layout" tool?, that would be the "Field ->" button, which you use by dragging on to the layout.

The Portal tool looks like a portal, rectangular boxes (3) on top of each other, at the bottom of the tool palette, next to the Button tool (the one with the finger). You can also let it create the fields; it'll ask you in a 2nd dialog (this is new in 7).

[Samdj]

First let me say, thank you so much!!! I know it is something simple that I just don't see. I have never used filemaker before, and I am trying to learn....

it is the "field ->" button that I have used.

What I would like to have happen it to be able to enter assessment information as well as the survey information in the same screen. What currently happens in data entry is I enter assessment information and then when I go into the survey information screen, it wants me to create a new record... maybe I created my relationships wrong?? I don't know. I will attach the file....

Again Thank you SSSSOOOO much for your help!!!

assessment.fp7.zip

[Fenton]

[Disclaimer] You have to remember that even though we may know FileMaker, we may know nothing about your business or any of the things it deals with. So bear with us.

Generally: The first question is always, "what are these things?" The 2nd question is "how are they related?" The 3rd is "how many of each? Related to how many of each?" The 2nd and 3rd are almost the most important. By "how many?" we mean: 1, 2 or 3, or many?

What I see in the Relationship Graph is 3 tables, 2 "entities" (things) and one join table, Reporting. This would imply that there is a many-to-many relationship between Assesments and Survey Questions. That: Each assessment can have several questions (naturally); but also that each question can belong to several assessments. Is that true? Otherwise you don't need the Reporting join table.

And, if you do need the reporting table, then you're going to need more interface. Because it implies that you can also create a Survey record in that table, and choose an Assessment record to tie it to.

Another minor point. You have EC1, Plan 1, EC2, Plan 2,...EC4 (no plan 4?). Whenever you see this, you should think, "should these be another table?" Say it's the EC table. It would have 3 fields, EC, Plan, and survey_id_fk. It's not critical; but I don't want to see an EC5, EC6, etc. -!

On the layout however, we see the related fields just as fields, NOT in a portal. This would imply that there is a 1-to-1 relationship between the tables; with makes no sense.

So, you want a portal (or portals). But I can't tell how to set it up until you explain more of what you're doing.

[Samdj]

I see what you are saying... I am setting up my tables incorrectly then becuase there is actually an EC 6, 7 etc. This is a database that I am trying to create for a survey that will be administered to 9 program sites. Each site will be assessed by 7 people. The survey itself consists of 8 sections and between 9-18 questions per section. Each of which need to re rated on a scale of 1-4 and the plan to improve has three options. These are the EC fields and plan fields you refer to. I don't know if I can create a question table that will allow me to enter the performance level (1-4) as well as the plan to improve ( right now, this year, next year) if that is possible, how would I do it? I didn't think I needed that reporting table, but I gave it a try to see if it made things work.... The assessment will take place twice a year. I need to report the results of said assement twice a year. My report basically needs to consist of how many times was each site assessed and how many assessors responded with what value per site. When you explain this to me in normal language, I see how silly it sounds. I really appreciate your patience and you help!!

[Fenton]

Yes, it is hard for beginners. Because they are overwhelmed with the technicalities of the FileMaker application (which is the easiest there is, there's no gentler learning curve), they tend to get distracted from the central concept that the database is structured according to what is happening in the real world, of what you're trying to do. It is not imposed "on" the data, it comes "from" the data.

Because they want it to be simple, they tend to oversimplify the structure, then try to force FileMaker to produce the results they want; which not only doesn't work, but is (much) more difficult than building what you need the first time.

As you become more comfortable with the tools, you tend to return to the original question, which is "what the heck are these things, how are they connected, and what are they doing?" When that becomes clear, the structure practically writes itself (if we get it wrong the first time; the structure tends to slap our wrists until we get it right; though sometimes a slap upside the head is necessary -).

The first concept you need is that the "questions" are separate from the "answers." The questions already exist. You're not making them up as you go. Therefore they are separate table(s) from the answers. Question tables are:

Surveys

Sections

Questions

These are one-to-many, going down. They are mostly used as "relational value lists," by the "data entry" tables, filtered by the choice from the table above them.

The "answer" table(s) are:

Sites

Assessments (mostly a Date)

Answers (EC-whatever that is & Plan)

There is a 3rd entity:

Assessors (or whatever you call 'em)

The Answers table is the join & reports table where it all comes together. It has a QuestionID, an AssessmentID. And an AssessorID, if each Assessor asks his own questions; otherwise it's another little table off Assessments, for all at once.

You want to start with the Surveys table, then the Sections and Questions. These need pre-entered data (as does the Assessors). Then the other "data-entry" tables.

We can help with the "relational value lists," but you need those tables with data, or it don't look like much.

I whipped up a quick structure. I think it's right. The value lists are a bit tricky, but they work. Assessments is the main data-entry layout.

Assessment_fej.zip