Jump to content
Salesforce and other SMB Solutions are coming soon. ×

Parsing question and answers text by number


Recommended Posts



I want to create filemaker solution that can Parse the numbered Questions and their answers.


I want to import a Microsoft Word questionnaire.txt file(s) into one Filemaker field called "Text". I will have an equal amount of Parse fields for the questions in "Text". 10 questions and answers = 10 Fields to parse too. I understand how to import the txt file into FileMaker, but need the assistance with the calculation.


Since I want to parse each question and it's answer together by number, I suspect I want to Parse from the question number up to, but not include the next question number. I don't care if that includes paragraph breaks/carriages returns.


Using brackets, here's what I'd like to parse from and to.




[ 1. explain why you prefer frozen yogurt over ice cream?


I guess it's because the frozen yogurt does not hurt my teeth as much. My dentist said i have sensitive teeth and that hard ice cream will hurt them.



2. explain why you prefer to own a dog rather then a cat?


well, the answer is simple. Unconditional love. Let's face it, Dogs will always be there to greet you at the door wagging their tail, happy to see you. Cat's, all they want it recognition for who they are, and food.


Thanks very much for your assistance,



Link to comment
Share on other sites

Hello Jeff,

There are a few different ways to approach this, but based on what you are telling us, one way that you might like to consider would be to place the import field and each of the fields into which you want the questions placed on a layout, then set up a script with a loop which cycles through the fields placing the questions into each in succession. This will work regardless of whether the question fields are in the same file or in a related file


Such a script would run along the lines of the following:


Go To Layout ["Parsing Layout"]

Set Field ["gCounter", "0"]

Go to Field ["ImportText"]

Go to Next Field


Set Field ["gCounter", "gCounter + 1"]

Exit Loop If ["not PatternCount("¶" & ImportText, "¶" & NumToText(gCounter) & ". ")"]

Set Field ["Middle(ImportText,

Position(ImportText, NumToText(gCounter) & ". ", 1, 1),

Position(ImportText & NumToText(gCounter + 1) & ". ", NumToText(gCounter + 1) & ". ", 1, 1) -

Position(ImportText, NumToText(gCounter) & ". ", 1, 1))"]

Go to Next Field

End Loop

Go To Layout ["Original Layout"]


- where gCounter is a global number field which doesn't appear on any layouts.


You'll need to make sure that the tab order for the parsing layout has the ImportText field first, followed by each of the question fields in order.


Bear in mind, though, that the script is parsing on occurrences of numbers followed by a dot and a space. If it's possible that the text of any of the questions or answers also includes numbers which happen to be followed by a space and then a period, it may run foul - but only in the unlikely event that they happen to fall in between the adjacent question numbers. wink.gif

Link to comment
Share on other sites



Thanks for the CobaltSky suggestion. Actually, the custom parsing example they have is more applicable, but the Array solution is worth pondering too.


Regarding structure. You have a point. And I would take that into account by building in more then enough fields. I will never have more then 30 questions. So 31 fields will always be enough. Your point about paragraph breaks is well taken. It will be easier to keep the Question and Answer together, therefore I don't have to worry about parsing between the Q and A.


As far as a sample, well, I hate to keep this simple, but that example is pretty much it for now. I am considering creating a Form-mail solution, but I suspect the scripting would be the same because I'm still going to use Question numbers, and the data in the email will have the same paragraph breaks/carriage returns, etc.


[ Ray ]


Hmm.. thanks for the out-of-the-box suggestion. I've been spending hours studying other solutions. Your suggestion is different. Not that I'm looking for different, but rather, I'm very open and will give it a shot.


Thanks everyone, i'll continue to check in.



Link to comment
Share on other sites

Hi Jeff,

The approach I suggested may be different to some others you've seen, but not so different from all of them, I'd guess, since it seems you'd already downloaded the custom parsing demo file from my web site. The example I cited above is a variant of the method I used in that demo.


FWIW, I've emailed you a quick 'working model' of the above script, set up to deal with a variable number of questions (from 0 to 31 to whatever...).


That may help you to tell if it's an approach you can use.



Link to comment
Share on other sites

Hi Ray,


Well now I'm stimied!


variable number of questions (from 0 to 31 to whatever...).
To whatever? Are you suggesting you provided a solution which will add more fields if there are more questions? The reason I originally suggested Array Expander was because I thought creating a record from each question was the way to go -- not parsing the data into different fields (as Custom Parsing would do).


Am I missing something here ... or did you come up with something interesting that I don't know about yet? smile.gif


BTW, I deleted my original post because I thought your answer provided an 'out-of-box' answer whereas my suggestion would mean a restructure. I still think it's better to have questions as records rather than fields but it's even more important to address the specific request - and you accomplished that. laugh.gif


ps. I hope that, whoever buys Cafe', allows the option of attachments. frown.gif I'd love to see the demo also. smile.gif



Link to comment
Share on other sites

Hello LaRetta,

Whether or not it is best to have questions as separate records in a related file or as separate fields in a record - or indeed as accumulating records in a separate file for each question - depends largely on what it is that you want to *do* with the data once you have it.


Since Jeff has given no hint as to what he wants to do with the data, but has been quite explicit about what format he wants it in, I saw no basis to tell him that he is mistaken in wanting that, and every reason to simply answer the question as asked.


My answer makes no assumptions about where the fields that the question/answers are to be placed in are located structurally - only that they are on the same layout. If they happen to be in a portal on that layout then the questions/answers may end up on separate records in the same or another file, using the same script technique.

Link to comment
Share on other sites

Hello Ray wink.gif


I agree it's important to provide a solution based upon the specific question. I also believe it's important to point out potentially serious problems; such as using Repeating Fields or, as in this case, using 'fields' to store multiple like data. The inflexible nature of these structures may eventually come back to bite you whereas starting with the proper structure removes all limitations. :eek:


Ray said... ... the questions/answers may end up on separate records in the same or another file, using the same script technique.
Using the same script technique?!!! "(which would deal with "from 0 to 31 to whatever...)". The 'whatever' peaked my curiosity and that's why I jumped on this. I wondered ... if this can be used to write to multiple 'unnamed' fields, might it also be used to reverse the process, ie. move data from fields back to records without having to specify every field name within a script - and that's why I wanted to see your 'thinking' in the sample file.


I've never figured out how to reverse the process without using GetField. Since I won't ever want to produce a rigid structure such as this, my only focus is how to 'undo' it... as a dynamic 'field array' to records and your use of Go To Field might be the answer. smile.gif


In the first situation, they have 60 fields in one db, 85 in another and 165 in a third - which should have been records. I'll be facing this migration issue in approximately 3 months. They use vs. 6 so I figured I would use GetField. In the other situation, they only have 30 Options fields to 'move') but they use vs. 5.0. I was looking for a solution for this type of data migrationn problem even for those without GetField or an even better solution.


Using Go To Field appears to be something I can use that might eliminate the need to include each field name in a tedious script. smile.gif But the creation of a new record will lose field and record focus. So, I thought I might use it instead to dynamically capture the field contents into one field, and that's why I got excited!


Is this what you mean by using the same technique to create records? Might this not work or is there a better way?


Place the fields I need to convert to records on one layout (eliminates scripting field names), identify first field within the script as: Go To Field [FirstField] (in tab order) to start the process. Write each field to a global text, apending the data within it and inserting a '|' between each field content. Then using your Array Expander process addressing only one field (GlobalText) to expand to records. It is certainly simpler to place all fields which should be records onto a layout than scripting their names in the array script.


The one drawback is that each new record should be identified by it's field name, ie, Question 6 or whatever. It would have to include inserting each record's 'field name' using globals or custom dialog via user input or script.


I was just hoping for miracles or ideas in understanding this process and solving the problem, I guess. laugh.gif



Link to comment
Share on other sites

I agree with you Laretta.


Each question should be a record unto itself. Complete with a field holding the question number and a field holding the survey origin or author. Than they could sort all the Question #1's together etc.

Link to comment
Share on other sites

  • Create New...

Important Information

Terms of Use