Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win


Separating a field of un-predictable comma-separated values into individual records and repeating remaining field values in each record for each of the new, separated records?

Posted on 2011-02-10
Medium Priority
Last Modified: 2012-05-11
I have a Filemaker dB table named "Property".  Each record in the table describes an individual owner's property as such:

- Township (Twp)
- Range (Rng)
- Section (Sec)
- Legal Description (Legal)

Twp, Rng and Sec fields all have single, non-comma delimited values per record, but the Legal field contains multiple, comma-separated values, as follows:

Record1      Twp1      Rng1      Sec1      Legal1, Legal2, Legal3
Record2      Twp2      Rng2      Sec2      Legal4, Legal5, Legal6
and so on...

Ultimately I want to have an end-product w/ the values in the Legal field in their own individual record while duplicating the Twp, Rng & Sec fields for the original record into the new records, i.e.

Record1      Twp1      Rng1      Sec1      Legal1
Record1      Twp1      Rng1      Sec1      Legal2
Record1      Twp1      Rng1      Sec1      Legal3
Record1      Twp2      Rng2      Sec2      Legal4
Record1      Twp2      Rng2      Sec2      Legal5
Record1      Twp2      Rng2      Sec2      Legal6

I am unsure of how to do this w/in FM11 solely and directly and I am even willing to export into Excel, manipulate the data and then import into a new table in FM11, if necessary.

This will not be a repetitive task, as this is simply cleanup on a legacy dB that I inherited from a previous employee and therefore accuracy is my primary focus, not repetition.

Thank You in Advance for any and all help you can offer!!!
Question by:johnsodolak
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 2
  • 2
  • +1
LVL 33

Expert Comment

ID: 34866931
Did you tryed Data-Text to Column feature?

Author Comment

ID: 34866964
No, I have not...can you elaborate?
LVL 33

Expert Comment

ID: 34866987
This will separate the fields in each row into each column.
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.


Author Comment

ID: 34866990
JPPINTO...I just viewed a quick article on "Data-Text to Column" feature in Excel.  How would I go about moving that "separated" text in each of the new columns back into the original column, create a new row, and duplicate/fill-down the data from the remaining columns in the original row?

Thank You for your response!

Expert Comment

ID: 34867294
Interesting question!

You could do it with a script in FM.

(If the Legal values were in related records then exporting would get you half way there - the same place as the Data-Text to Column)

Going the route you are currently going... maybe this Columnizer software may help:
LVL 25

Expert Comment

by:Will Loving
ID: 34867336
In order to move this data from your current structure to the structure you describe with multiple records and each "Legal" value you need to do an import from the old structure in a way that isolates each of the comma-separated legal values. Here's how I would do this: Create a calculation field the will determine the N number value in the Legal field:

LegalCalc = GetValue( Substitute( Legal ; "," ; "¶" ) ; 1 )

This calculation will give you the first value in the legal field. It uses the Substitute () function to convert all commas to Carriage returns giving you a return-separated list of all the values in "Legal". The GetValue() function then determines which value get's used, in this case it's value "1". I might take this calculation a little further by adding a Trim() function to it to cleanup and leading or trailing spaces that might be left over after converting the commas, so:

LegalCalc = Trim( GetValue( Substitute( Legal ; "," ; "¶" ) ; 1 ) )

You are now going to do a series of imports from your old table into your new one. The first import is going to simply be all records. You will import the LegalCalc field into the Legal field in the new table. Then, go back, change the 1 to a 2 and do a find in the old table for all records that now have a value in LegalCalc. This will be all records that have a second value in the old Legal field. Import those records into the new table.

Now, go back change the value in the calc from 2 to 3 and perform a find again in the old records which will result in all records that have a third value and import those records. Rinse and Repeat until you have performed as many imports as the maximum number of values that the Legal field might hold. YOu'll know because when you put in a number and perform the Find, no records will be found.

You will now have a set of records for each Property, each one having a unique Legal value.

Expert Comment

ID: 34867388
I was thinking about this and just came up with the same multi export (or multi import) scheme, came back and there's your answer willmcn (that's why you're master level!)

I was going to suggest the MiddleWords Text function which will just grab the right value without having to substitute.

With MiddleWords on the string "one, two, three, four"
MiddleWords ( field; 2; 1) gives "two"
LVL 25

Accepted Solution

Will Loving earned 2000 total points
ID: 34867427
If the comma is a reliable separator in the Legal field and there is any possibility that the value in legal might be more than one word, then using the GetValue and Substitute functions are a cleaner way to go. Using Middlewords() does eliminate the need for the Trim() that I suggested and ignores the comma, but can't deal with multiple words between commas.

Author Comment

ID: 34867931
Thanks for the great responses!  I am going to test all of these out and hopefully be grading tomorrow...

Author Closing Comment

ID: 34984437
I cannot apologize enough for taking so long to apply these points.  Thank you so much for your response and I still cannot apologize for the tardiness.

The solution was ideal for my situation!

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

650 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question