Title

Post

I have a database which contains a table for Contact information and another table has a questionaire. There are over 30 questions on this so having it as a portal is not suitable.

I want to be able to click on a button in the contact information layout which will take me to the questionaire layout and automatically draw the contact information into this table. I have tried various combinations of GTTR and have _id_contact in the questionaire table and vice versa on the relationship graph.

I may be getting things confused when I setup the layout and need someone to please explain the concept in vary basic language (I am very new to FMP). I have been trying to sort this out for a few months now and I guess as a novice it is beyond me.

Have you tried searching this forum using "Survey" as your key word? There are several very good discussions several months back that discuss the table structure needed to support a questionaire system.

Your first task is to step back from your script and layout questions and make sure that you have tables and relationships in place that will meet the needs of your questionaire system.

It would help for you to describe your questionaire in more detail.

Will this database be used for more than one questionaire?

How will responses to the questionaire be collected and recorded in the database?

Sorry for not replying sooner. Thanks for the tip on searching for surveys. Mine is pretty straightforward and it is really a checklist as opposed to a questionaire. I don't need to run statistics or anything fancy, just a few reports. There is only 1 checklist and over 1500 contacts which will each have a preclinical record (i.e checklist) against their names.

So far I have 3 tables. Contacts, Preclinicals and Year data. Both Contacts and Preclinicals have their own layouts.

* Contacts contains names, details etc.

* whereas Preclinicals has the checklist on all of the certificates that the person has completed with expiry dates etc.

* Year data has term number and date of the year (e.g. Term 1 2011)

I want to be able to click a button on the Contacts layout which will take me to the related (or create a new record) in the Preclinical table using the contact information. So I want a GTRR. I want to be able to have it automatically pickup the year and enter that into a field as well.

The layout is based on the Preclinical table, however I am not sure how to get the data to automatically fill in from the Contacts table when using a GTRR script. I guess I want the user to enter the Term and Year data from drop down lists.

I am getting confused on TOc's for layouts, relationships between the tables, field data from which TOC's? and need some help please. I have tried including a screenshot and I am not sure how to get images to load up as there is no normal 'browse' button on the screen.

Easiest way to add a screen shot is to click Edit Post on your original post. Then you'll get a button for uploading an image file to the first post in this thread. You cannot upload images to replies to the original, but you can upload images to a file share site, then link them to your reply post.

GTRR only pull up existing records, not create new ones so either you use a script with GTRR that checks for a related record and creates one if none exist or you have a separate button and script for starting a new record. You can also use a scripted find in place of GTRR and get the same results.

Here's a key detail that's not clear to me: "whereas Preclinicals has the checklist on all of the certificates that the person has completed with expiry dates etc." You should have one record for each certificate in this table. Don't try to record multiple certificates on the same table. This lays the gound work for these basic relationships:

Contacts::ContactID = Preclinicals::ContactID

Year Data may or may not be necessary. You can auto-enter dates in the other tables and a calculation field can then produce year and term labels from these dates. Thus, you won't need this third table unless you need to specify data about each term such that you have one record for each term. (Perhaps you need to record the start and end dates of each term, for example.)

With that relationship, GTRR from contacts to Preclinicals will pull up all existing records in Precliinicals for that contact. While you can define a separate relationship between contacts and Preclicnicals that is based on both ContactID and a Year_Term field, I'd more like just use a find script for the same purpose:

Set variable [$ContactID ;Value : contacts::ContactID]Go To Layout [Preclinicals]Enter Find Mode [] //clear pause check boxSet Field [Preclinicals::ContactID ; $ContactID]Set FIeld [Preclicinicals::Year_Term ; Globals::Year_Term]Set Error Capture [on]Perform Find[]If [ Get ( FoundCount ) = 0 ] New / Record Request // or you could use Show Custom Dialog to ask if a new record should be created...End If

Note that Globals::Year_Term must be a field with global storage specified in order for this script to work. Without global storage, you'd copy it to a variable like the script does with the ContactID.