Splitting "text" field content into multiple records

[Dr.Hamed]

I have a table with two fields "ID" and "Paragraph" as this:

 

"ID"         "Paragraph"

----        ----------------

 1            John is a student

 2            Mary works hard

 

I want to add a portal to this table to show the words of the paragraph as separate records:

 

"ID"         "Paragraph"                              "Portal"

----         --------------------                        ----------

 1            John is a student                      John

                                                                is

                                                                a

                                                                student

 

 

 2            Mary works hard                      Mary

                                                               works

                                                               hard

 

THANKS

[comment]

A portal shows related records from another table. You will need to script the creation of those records, so that eventually you'll end up with a Words table containing:

 

"ID"     "ParagraphID"    "Word"
----      ------------    ------
1         1                John
2         1                is
3         1                a
4         1                student
5         2                Mary           
6         2                works
7         2                hard

 

Have your script grab the ID and Paragraph values of the parent record into variables, go to the Words table and loop among the words in Paragraph, creating a new record for each. Then go back to the parent table and proceed to the next record to do the same..

[Dr.Hamed]

Sorry, I'm not familiar with the procedure you are describing. I appreciate it if you can help with the script and loop...

 

Thanks

[LaRetta]

It works best if you create a test file and try it.  You would be surprised how far you can get and what you can learn along the way.  AND … if you then get stuck, you can post your file so we can show you exactly how to proceed at that given point.  And it saves us from having to create the file if you go ahead and do it.  It is also easier for you to translate because it will be using your field/table names.

 

Just a thought … I would never ask how to do something without at least first trying to see if I could figure it out myself.

Edited November 3, 2013 by LaRetta

[Dr.Hamed]

Thanks LaRetta for the advice, In fact I've been trying for the last hour, in vain. But I will try to learn how to set variables and etc......

[LaRetta]

No problem - then just post what you've done so far.  It doesn't matter if it is your test file or your real file - just zip and attach.  In that way, we can see EXACTLY what point you are stuck.  

[Dr.Hamed]

I'll be glad to  . Thanks for helping.

Test.zip

[LaRetta]

You didn't get very far but I understand that it can feel overwhelming so here's something which helps me … when faced with a script which can have many steps, I insert a comment step and then I duplicate it a dozen times and begin envisioning the steps to make it happen and I modify the comments accordingly.  You can easily move the comments around until it feels right and then you begin adding the steps in between to make it so.

 

But having completed this script as I thought Comment suggested, I realised that it isn't the approach I would take, particularly if this isn't a one-time thing but rather an ongoing process.  I've no doubt that Comment's script or process would be leaner than my script but I decided to post this anyway and then also I will, if a few minutes, post another approach.  

 

BTW, I am having major system problems (taking it in tomorrow) so if I am not back soon you will know why.

TestMOD.fp7.zip

[LaRetta]

Never mind on the other approach … I would probably write name-value pairs, looping Paragraph records first before switching to Words which would save the layout switching back and forth but it also adds a bit more complexity so ...

 

My system is crashing like every 5 minutes so I'm feeling a bit 

[LaRetta]

And yes, I know I can put the counter within a Let() within the Exit Loop[].  However, I noticed that Comment does not do that so I wondered why although I kept using it.  And I noticed that there were frequent times that it DID NOT make sense in the sequence and it ended up taking more time to then correct it.  It is kinda like using Case() instead of If() … it takes longer to switch than to use the more flexible one to begin with.

 

Overall, it might save a *nanosecond to use the counter within the Exit Loop[] but sometimes, if it means losing clarity, it is not worth how cute it might look.  It makes sense to me but I have already heard from others that it isn't always clear.  I chose not to use it here because I felt it read more clearly for someone newer to FileMaker if spelled out a bit more.

 

Michael, I did not mean to step in but rather to assist. 

 

* yes, Bruce, I admitted that sometimes saving a nanosecond isn't worth it, LOL.

[LaRetta]

Well wait a minute … words can be turned to repetitions and repetitions can be imported as records so … how about this way?  I got this idea from you, LOL.

 

It must be a full moon.

 

update:  I replaced the file.  I had neglected to specify 'perform auto-enter' during the import.  It is corrected now.

TestMODr.fp7.zip

[comment]

Michael, I did not mean to step in but rather to assist. 

 

That's alright, I wasn't going to add anything anyway. Something about fish and fish rods...

 

 

 

And yes, I know I can put the counter within a Let() within the Exit Loop[].  However, I noticed that Comment does not do that so I wondered why

 

Because it's "clever" and I don't do "clever" anymore. Not that there's anything wrong with it - I just prefer to be explicit.

[Dr.Hamed]

Thanks LaRetta,

 

Your help is very much appreciated, and sorry for the inconvenience. But being a busy doctor, I wish I had more time to learn more about FM.

 

I love this program and I hope that you recommend a book or a site that teachs FM in a simple but concise manner.

 

THANKS

[Lee Smith]

There are lots of resources that are available for FileMaker. Amazon has several books written on FileMaker, look for one that matches your skill level.

 

I recently posted a few sites that have videos that can be helpful. Here I think you might find Lynda.com helpful as they cover things from the beginner to the expert.

[LaRetta]

But being a busy doctor, I wish I had more time to learn more about FM.

 

I understand.  We are ALL very busy; you aren't the only one.  And our time is just as valuable as yours.