Create a table of Options with two fields (for now):
• OptionID - Number
• Option - Text
an populate it as:
1 Strongly Agree
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.
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.
I guess I need to add one other caveat to this.
The data I'm importing needs to retain it's field name.
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
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.
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.
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...
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:
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
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. :-)
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.