Field separation for import/export

[saralee]

Hi,

I have a field called Sources_and_Dates which basically includes the sources information (text field) with the dates info (date field). Now I would like to separate this big text field into two separate fields with the corresponding field types. And later on I would like to export them into two different fields in another table. Can I do that?

Also, ideally in the future I would like to have the Source field be a drop-down menu and the Date field be a calendar type. Can I change that after I export the existing data?

Please advice. Thank you so much! ???

[Peter (duksis3)]

I have seen lot of posts here regarding similar questions. Probably you can find answer via search

[Fenton]

Yes, you can put dates into their own field, of type Date (not text). But since you're copying data which is currently text, you'll need to be sure the date you're copying is valid for a date field.

So the real problem here is how to get dates out of a text field (unless you're just going to copy/paste). If they have been entered consistently you could likely parse them out using Text functions, or a Custom Function (though I could not find one), or using an external tool which supported regular expression parsing, such as grep.

You would need to create the records for the Dates & whatever else, including the unique (to the parent table) ID of the parent record.

We need a bit more detail to really see what the problem is.

[saralee]

Thanks, Fenton!

I'm migrating the data of an old database to a newly designed one. The field I was talking about is called "Source and Date", a text field. I was able to migrate the whole field to the new database, but for the new database I will be building a better design for this field by separating them into two fields (one drop-down text and the other calendar date) I don't know if I can change this text field after I migrate all the data. If I can do that, it would save a lot of effort of separating the field into two now. Does it make sense?

Also, how to do text parsing in filemaker? Can any function in FM differentiate letters from numbers?

Thanks a lot! ???

[Fenton]

Well, I imagine you could build a text-parsing calculation in FileMaker to select a date. If there are multiple dates, it would be harder, requiring either a Custom Function (recursive) or a script with a Loop.

The other thing is that the dates would have to be entered consistently, like 6/1/2010, or similar. Text parsing would not see 6-1-2010, unless you wrote that in also.

FileMaker can identify a number, but it's a bit clunky. Filter ( text field; "0123456789/" ) gives you numbers & "/". Personally, I just wouldn't do it with native FileMaker. It would be tedious to write, and would require looping if more than 1 date.

You really want pattern matching for this:

[0-9]+/[0-9]+/[0-9]+

will match all dates created with slashes as the separator.

Yes, it's a bit geeky, and it's not installed on Windows by default. But there are text editors on either platform which have basic pattern-matching.

There are a few FileMaker plug-ins which can do this. For one that works and has documentation you can actually follow, you could try:

http://360works.com/regular-expressions-plugin/

It will run in Demo mode for 2 hours at a time, which should be enough to do what you want.

[P.S. I'd love to see this functionality in FileMaker. Most programing languages have it. But there are several variations of grep, slight differences.]

Edited June 1, 2010 by Guest

[saralee]

Thank you, Fenton for your idea!

I forgot to mention that there is a hyphen separating the "source" and "date" part. I don't know if this would make the separation in FileMaker easier.

I found some calculation functions like LeftWords and RightWords. But the exact numberofwords required by these functions cannot be defined in my case. Are there any other good functions I can call on?

Thanks a lot!

[comment]

If there's only one "source-date" pair in the field, and only one hyphen, then it can be very easy.

[saralee]

Thank you for the follow-up, comment!

There are actually one or more "source-date" pairs and each pair has a hyphen, normally..

What is the solution for the one "source-date" pair? I think I can just build a loop upon that?

Thanks for the great help! ???

[comment]

Source =

GetValue ( Substitute ( pair ; "-" ; ¶ ) ; 1 )

Date =

GetValue ( Substitute ( pair ; "-" ; ¶ ) ; 2 )

[saralee]

It works for me! but I couldn't figure out how to do the looping.. The triky thing is I can't really replace space with ¶, as the formatting isn't consistent. There might many spaces in between the first and the second pairs.

Do I need scripting for it?

Thanks a lot as usual!

[comment]

Spaces??? Perhaps you should post an example of the data.

[saralee]

sorry.. here is an example with more than source-date pair-

Hospital- 11/2002

Online Birth Announcements- 12/2002

There is two enters between the two pairs. How to retrieve the second source and date after the enters? I was thinking of using repeating fields Sources and Dates to contain all pairs, but got stuck with the looping..

Thanks!

[Fenton]

If these multiple source-date entries are on different lines, then a script could Loop thru the lines one at a time looking for them. That way it would only have to parse one pair at a time.

P.S. Gak, repeating fields? Yes, you could, but no, you shouldn't. Because later you'll need to ask how to break apart the repeating field; many people do. Create a separate table for these, 3 fields, parent ID (hopefully you have one), source and date. The script can pass these values to new records.

Edited June 2, 2010 by Guest
Gak

[saralee]

That's a great idea! But I don't quite understand how to import the calculated source and date fields into a new table.

Since I'm migrating all these data from an old design which only has one big giant table to a new design with multiple related tables. I have actually created a table Source_and_Date, so I was thinking about doing all the calculations in the old database and then separate the potential repeating fields into individual records while importing it to the new table.. I don't know if it makes sense.. Am I on the right track?

Please enlighten me ???

[comment]

Anti-gak:

Actually, since this is a part of a conversion process (IIUC), repeating fields are not such a bad idea: importing them into the child table while breaking them out into separate records may be easier than scripting a nested loop.

[saralee]

Thanks, comment!

I'm still stuck with the looping script. How to get the loop to go through each line? and how to perform the calculation and store the results in repeating fields?

Is the separation calculation a kind of function I can call on in the looping script? It is just the option I defined when I created the calc fields Source and Date. I'm a bit confused..

[comment]

I think it's either a looping script (which would create records directly in the target table) or two repeating calculation fields (which would then be imported into the target table). Having both makes no sense.

[saralee]

Thank you both, Fenton and Comment!

I will come back tomorrow to try the text parsing plug-in thingy. :)

[Fenton]

This is an example of a Loop doing this. It will handle "dates" like "12/2002" or "2002". It turns them into real dates. If the date is too inconsistent, or if you'd prefer to leave it text, then that part can be simplified.

It uses Position ( text, search, length; -1 ) to locate the position of the last hyphen in the line of text.

P.S. The plug-in may still help, if your text is less consistent that the data you posted. But you may not need it.

Source_Dates.fp7.zip

Edited June 3, 2010 by Guest

[saralee]

Thanks so much, Fenton! I couldn't open the script as it is read-only? Could you send me a modifiable version? Thanks!

[Fenton]

"Read-only?" It's just a standard new FileMaker file. There's no added security.

This is likely a Windows issue. Did you actually unzip it? I know it's possible on Windows to look into a zipped file, and everything looks normal, except the files are not modifiable. I suppose it's a "feature", though I've run into several people who have trouble with it.

Edited June 3, 2010 by Guest

[saralee]

Thank you, Fenton!

Please ignore my previous msg here. I was able to figure out the bugs!

Thanks for the great help.

Edited June 4, 2010 by Guest