3 Replies Latest reply on Oct 14, 2011 11:02 AM by philmodjunk

    Create New Record Between 2 Database Files



      Create New Record Between 2 Database Files


      Hi. So I am on FMP 11 Advanced & Server.

      I have 2 database files on my server. 1 database file is called Questionnaire. With this file I have setup with the Site assistance Web page for a Questionnaire people can fill out online.

      Then I have a 2nd database file on my server called PERSON that is for inner office use only and contains all the records of everyone who has submitted a questionnaire with us.

      What I would like to do is anytime a new questionnaire is submitted online and a new record is created in the Questionnaire database with all the information entered in from the user, I would like to set it up that it automatically creates a new record in the PERSON database and enters in all the informtion for that new record. Any suggestions of how I can set this up? 

      Separate question: I also wanted to know if there is a way that once someone submits a new questionnaire online for me to tell the server software to send me a notification email either that a new record has been submitted OR what I would rather have it do is send me an email with all the information entered in by the user?

      Another separate question: Is it possible for me to put in my own coding into the PHP files that were created from the Site Assistances setup for the Questionnaire online to have someone upload photos to the Questionnarie and have those photo's saved into my container fields in my questionnaire?

      Okay I think that is all... thanks for your help!

        • 1. Re: Create New Record Between 2 Database Files

          I have 2 database files...

          Best way is to relate the tables in these two files so that when the user starts taking the questionaire, they are also creating the new record in the other file. This can be scripted or also set up via the "allow creation of records via this relationship" option. The user simply fills out the form, but some of the blanks submit data to the related table in PERSON, creating the new record automatically.

          Is is possible for one person to take the questionaire twice? Wouldn't you want just one record in Person if that happens?

          I would rather have it do is send me an email with all the information entered in by the user?

          A script can generate a PDF of the information, attach it to the email and send it to you. If is also possible to construct a plain text email message that contains this info so that it can be sent to you in the body of the email.

          Of course the custom web page with PHP set up will complicate this process and I am unable to help you with the PHP.

          • 2. Re: Create New Record Between 2 Database Files

            Hi Phil,

            Thanks for your help.

            So for now, forget the last 2 questions in my original post.

            With this script you are referring, once someone submits the information online, currently the PHP code created by the site assistance, has it setup that once submit is hit it creates the new record in the Questionnaire file and enters in all the information. So I guess from what you are saying I need to create a relationship between Questionnaire and PERSON (with a unique serial ID field??? or do I have to link up each field that is in the quesitonnaire to the same fields in PERSON) and then setup a script in questionnaire to automatically create a new record in PERSON file when a new record is submitted to the Questionnaire file? I guess I am not even sure were to begin with creating such a script especially since the PHP code on my server automatically adds the new submitted info to my QUestionnaire file, how to then have the questionnaire file send the info to the PERSON file in a new record... Not even sure if I am making sense LOL

            • 3. Re: Create New Record Between 2 Database Files

              I'm handicapped here by a lack of knowledge on how PHP does its job to interact with FileMaker.

              In strictly FileMaker terms, you could set up this relationship:

              Person::PersonID = Questionaire::PersonID

              Enable "allow creation of records" for Questionaire

              Base the relationship on Person and now you can place the fields from Questionaire on your layout. When a user creates a new record, they create a new person record, but when they start entering data in the Questionaire fields, a new related record in Questionaire will be automatically created.

              Please note that in most questionaire systems, you need many more than just one table in order to make this work. As a minimum, you'd have a table for the questions and a table for each person's response to each question.

              Also please note that this approach only permits a single questionaire for a given Person record. a more sophisticated approach would be needed to record multiple questionaires for a single Person record.