Importing lines of a plain text file into FileMaker fields

[jax]

Hi FMers,

I'm trying to get multiple choice quiz questions into a FileMaker database. The information is in a plain text file. Question and options are simply separated by returns:

Where in the cell is the genome located?
Mostly in the cytosol.
Mostly in the mitochondria.
[X] Mostly in the nucleus.
Stored in the membrane.
Stored in RNA.

Next question...

Each question has 5 options, one of which is correct [X]. There's an empty line between questions.

What's a good approach to get this into a FileMaker database with separate fields for the questions, the options, and the correct solution?

All the best

[comment]

If you only need to do this once, then paste the text into a global text field and have a script parse it from there. Otherwise import the file into a temp table (where each line will be a record) and parse from there.

For best results, make each question a record in a Questions table, and each option a related record in an Options table.

[jax]

Hi comment,

Thanks. Do you have a pointer for me where to find out on how to write a script like that and how to run it on a global text field?

[comment]

Try:

Set Variable [ $txt; Value:AnyTable::gTextfield ] 
# 
# OUTER LOOP 
Go to Layout [ “Questions” (Questions) ] 
Set Variable [ $i; Value:1 ] 
Loop 
  Exit Loop If [ $i > ValueCount ( $txt ) ] 
  New Record/Request 
  Set Field [ Questions::Question; GetValue ( $txt ; $i ) ] 
  Set Variable [ $qID; Value:Questions::QuestionID ] 
  # 
  # INNER LOOP 
  Go to Layout [ “Options” (Options) ] 
  Loop 
    Set Variable [ $j; Value:$j + 1 ] 
    Exit Loop If [ $j > 5 ] 
    # 
    Set Variable [ $option; Value:GetValue ( $txt ; $i + $j ) ] 
    Set Variable [ $correct; Value:Left ( $option ; 4 )  = "[X] " ] 
    # 
    New Record/Request 
    Set Field [ Options::QuestionID; $qID ] 
    Set Field [ Options::Option; If ( $correct ; Right ( $option ; Length ( $option ) - 4 ) ; $option ) ] 
    Set Field [ Options::IsCorrect; $correct ] 
  End Loop 
  # 
  Go to Layout [ “Questions” (Questions) ] 
  Set Variable [ $i; Value:$i + 7 ] 
  Set Variable [ $j; Value:"" ] 
End Loop 

Note that this assumes more than one option can be correct. Otherwise it would be better to store the correct option's ID in the parent question's record.

[jax]

Excellent stuff. I'll give it a try. Still can't believe how you guys type this down in a few minutes. I would have to substitute minutes for hours Thanks a bunch.

[jax]

Ok, I've gone through this cool script line by line reading up on the commands used. I pasted the text into the global text field and ran the script but at first it only read the first question and options. I discovered that this was a newbie error causes by the weird way that the FileMaker Script editor does not allow direct text editing of scripts but forces users to put everything in click by click. This was the bad line I pasted in by mistake: Set Variable [ $i; Value:"Value:$i + 7"]. It should have read [ $i; Value:$i + 7].

I include the some details below for others noobs to be able to reproduce comment's great solution.

It turns out there's no space between the X and the option, so I adjusted the LEFT and RIGHT commands accordingly.

These are the commands I didn't know. Left takes 3 characters from the left of the $option text string. If that matches [X] $correct is set to 1. Right takes the full length of the string minus 3 characters from the right to not store the X in the option. The rest I couldn't come up with is clever looping using 2 counters $i and $j.

This is the test text I used is attached.

 

test questions.txt

 

Edited August 30, 2016 by jax
I found the bug and corrected it.

[comment]

2 hours ago, jax said:

I've gone through this cool script line by line reading up on the commands used.

Good for you - that's a great way to learn.

Two minor points:

1. The Options table should also have an auto-entered serial number OptionsID field~ as should every table (with the possible exception of the Globals table and such);
2. The IsCorrect field should properly be a Number field; among other things, this will allow you to format it is as Boolean.

--
P.S. The looping is actually quite simple. Your file has a block of 7 lines for each question: the actual question, 5 options and a blank line. The outer loop starts at 1 and increments  by 7 on each iteration, so it always corresponds to a line containing the question. Then you just add 1, 2, 3, 4 and 5 to that to retrieve the options belonging to that question.

 

Edited August 30, 2016 by comment

[jax]

I will change the 2 points indicated. In fact, I was looking for a boolean format when I set up the variable but only found number format which didn't seem right.

I guess if you spent enough time with FileMaker looping and other programming becomes 2nd nature. I had already been working on this for an hour when you posted and hadn't arrive yet at the right solution. If this where DNA I'd be faster ;-) Thanks for all the help.

[LaRetta]

On 8/29/2016 at 5:11 AM, comment said:

Note that this assumes more than one option can be correct. Otherwise it would be better to store the correct option's ID in the parent question's record.

Hi Comment,

You may not be wishing to complicate the situation for Jax at this point since their self-rating is novice but I see three tables: Questions, Options and Responses, where the correct Option's response/s would be placed instead into the Responses table along with the ID of the person taking the test.  In this way, multiple 'same questions' asked of multiple people can be imported and parsed.  I just wanted to mention that for future.

Beautiful script. 

Added for Jax:  The responses table would be known as a join table.

Edited August 30, 2016 by LaRetta
removed word 'instead'

[comment]

1 hour ago, LaRetta said:

I see three tables: Questions, Options and Responses

I see at least four:

This is assuming there is a single uniform questionnaire that all subjects must go through, once. Otherwise there would be more.

 

1 hour ago, LaRetta said:

where the correct Option's response/s would be placed instead into the Responses table

I didn't quite get this part.

[LaRetta]

1 hour ago, comment said:

I didn't quite get this part.

"where the correct Option's response/s would be placed instead into the Responses table."

Yes, that was poorly worded; thanks for the correction.  I just meant that the Subject's choice from Options (the OptionID) would go into the Responses table.  The word 'instead' was a typo which I had removed.  :-)

And yes indeed, Jax will want a Subjects table (user or person taking the test).

Edited August 30, 2016 by LaRetta

[jax]

Thanks guys. I will add this at a later point. Just after looking up what a join table is .