9 Replies Latest reply on Dec 18, 2009 7:41 AM by comment_1

    setting up relationship



      setting up relationship


      I'm fairly fluent in Filemaker; but, somewhat rusty.  I'm using version 8.5 with Windows XP.


      I've created my layout and everything looks fine.


      But, what I'm having difficulties with is coding the incoming data to do what I need it to do.


      Basically I'm uploading surveys.  The survey responses are answered with numbers (1, 2, 3, 4, or 5).  Each number represents Strongly Agree, Agree, Neutral...etc.


      But, instead of the numbers showing up in Filemaker I want to build a relationship so that it knows when it sees a number "1" to count it as a "Strongly Agree".


      And ultimately I want to be able to find out the percentages of each answered question (25% Strongly Agree, 15% Agree and so on).






        • 1. Re: setting up relationship

          Create a table of Options with two fields (for now):

          OptionID - Number

          Option - Text


          an populate it as:

          1  Strongly Agree

          2  Agree

          3  Neutral




          Then define a relationship between Responses and Options as:


          Responses:: OptionID = Options:: OptionID


          ( Responses:: OptionID is the field where those numbers are). Place the Option field from Options on a layout of Responses to see the meaning of the response.

          • 2. Re: setting up relationship

            If you have a table with 2 fields. A field that stores the number and the other text, then all you have to do is create a relationship to that table and then put the related text field on the layout.



            Edit: Didnt see comment's response.

            • 3. Re: setting up relationship

              I guess I need to add one other caveat to this.


              The data I'm importing needs to retain it's field name.



              For example


              field "R820" correlates to the first question in each of the survey's (I have about fifty of them).  


              So when I import the data it has to read the field "R821" and put that response for question 1 (which is a number instead of the actual word of "agree").  



                strRespID fSubmitted R820 R821 R822 R823 R824 R825 R826 R827 R828

              1               1              3      3     3       3       3       3    3     3    3








              the above is an example of what the raw data looks like.  The first respondent answered "3" (or "Agree") to question 1 which is "R820".  


              Each survey has about 30 respondents and as I said I have about 50 surveys.


              I may be overthinking this



              • 4. Re: setting up relationship

                Well, I am sure some hack can be found to provide the display, but...


                You won't get very far with this data structure - most importantly, producing reports will be somewhere between a nightmare and impossible. There should be a record for each individual response.

                • 5. Re: setting up relationship

                  there is a record for each response.  I just supplied the first line.


                      strRespID    fSubmitted    R820    R821    R822    R823    R824    R825    R826    R827    R828    R829    R830    R831   

                  1        1    3    3    3    3    3    3    3    3    3    3    3    3    3    3    3       

                  2        1    1    1    1    1    1    1    1    1    1    1    1    1    1    1    1   

                  3        1    3    3    4    3    3    3    3    3    2    2    3    3    3    3    3        



                   they actually resemble the above more....but, going down to 30 (and of course going further to the right thant R831)


                  My challenge is making the numbers 1-5 represent "Strongly Agree" - "Strongly Disagree"; 


                  I think the above poster probably has it almost nailed....i'm just still a bit fuzzy with it.

                  • 6. Re: setting up relationship

                    ok...I think I'm there.


                    I created the extra tables and definied the relationships.  


                    But, what I still am stuck on -- when I import that data I still see just the numbers.


                    I don't want to see numbers.  I want to see the words.


                    Or, at least, also the words.  I think i'm almost there...

                    • 7. Re: setting up relationship

                      The thing to remember is that you can remove a field from a layout so that you dont see the data but the data is still there in the database table.

                      On the layout / report that you want to view your data replace the field which currently shows your number with another field which can sow the response given.

                      To do this having created the relationship as described in previous replys add a field to hold the word response.

                      I would use an autoenter looked-up value based upon your relationship then if you change what the response means the surveys that you have already imported will not change, only new surveys will get the new responses (unless you use the Record relookup function)


                      Sequence would be:

                      Manage database

                      create Option  type Text

                      goto Options... and select looked-up value

                      under specify select the related table and the ::Options field


                      each time a new record is created it will look up the option (response  stronly agree etc) and insert it into the new record.


                      I would leave both the number response and the word response on the layout untill you are sure it is working correctly then you can safely delete the number response from the layout (not the field from Manage database)

                      I hope this helps


                      • 8. Re: setting up relationship

                        i knew I was overthinking this....


                        What i've decided to do is in the excel spreadsheet I'm importing from...


                        I'm just going to run a find/replace and replace 1 with "Strongly Agree" and 2 with "Agree" etc.


                        Then it gets pulled into Filemaker exactly how I want it without having to worrk about the relationships.  :-)



                        • 9. Re: setting up relationship

                          jhogan1 wrote:

                          there is a record for each response.

                          If I understand correctly what you show us, there is a record for each 15 responses (or more, if it is "going further to the right thant R831").

                          This may be good for a spreadsheet, but not for a relational database.