Extracting Multiple Text from One Field

[OneStop]

I have a field with a text string separated by right arrows. It goes something like this...

 

Category Field: Cat1>Cat2>Cat3

I need to extract the first two Items Cat1 and Cat2 into their own fields...

 

[LaRetta]

Let (
~values = Substitute ( thatField ; ">" ; ¶ )
;
GetValue ( ~values ; 1 )
)

This will give you the first value.  Change the 1 to a 2 to get the second value.

[OneStop]

Can you explain this script a bit? I don't understand how that works?

 

How does it extract Cat1 and put it into it's own field? Cat2 into it's own field etc?

[LaRetta]

What I gave you is not a script but rather a calculation you would use in your script.  A script might look similar to (and always back up first):

After you've created your new fields and assuming your text field containing the string is called 'thatField' AND when you are sure no Users are in the file and all records are in your found set ...

Replace Field Contents [ newFieldCat1 ; (that calc I gave you).

Replace Field Contents [ newFieldCat2 ; (that calc I gave you but with a 2 to grab the second value)

---

You could also loop which would look like this:

Show All Records
Go To Record/Request/Page [ first ]
Loop
Set Field [ newFieldCat1 ; (that calc I gave you) ]
Set Field [ newFieldCat2 ; (that calc I gave you but with a 2) ]
Go to Record/RequestPage [ next, exit after last ]
End Loop

Sorry it posted earlier, I hit a wrong key 

Also, you don't need a script if this is a one-time thing.  Just run Replace Field Contents by placing your cursor in the new fields one by one.

Edited May 31, 2017 by LaRetta

[OneStop]

This works very well...thank you so much. How do I get this to go about looping through all the records?

 

This is what I've come up with...but i can't figure out what criteria to use to end the loop?:

 

Show All Records
Go To Record/Request/Page [ first ]

Loop

Replace Field Contents [With dialog:Off; TIP::category;

Let (~values = Substitute ( TIP::category_delimited ; ">" ; ¶ ) ; GetValue ( ~values ; 1 ) ) ]

Go to Record/Request/Page [Next]

Exit Loop if [???]

 

End Loop

 

 

 

[LaRetta]

Go to Record/Request/Page [Next] ... should be:  Go To Record/Request/Page [ next ; exit after last ]  it is a checkbox in that step.

Then remove the Exit Loop If.

WAIT!!!!  You don't use Replace Field Contents[] within the loop!  You have mixed the two different methods I proved you.  Use one or the other!

The loop needs to use the SEt Field[] steps!

[OneStop]

Oddly enough...I made a copy of my file and ran the above before you replied.

Show All Records
Go To Record/Request/Page [ first ]

Loop

Replace Field Contents [With dialog:Off; TIP::category;

Let (~values = Substitute ( TIP::category_delimited ; ">" ; ¶ ) ; GetValue ( ~values ; 1 ) ) ]

Go to Record/Request/Page [Next]

Exit Loop if [???]

 

End Loop

 

It actually did what I wanted it to..it extracted the Cat1 and placed the text into the Cat1 field...removed the Cat 2 and put it in the Cat2 field..etc..etc

Why did that work?

[LaRetta]

Use Set Field[] if within a loop.  Step through debugger to watch how this all works when you run it.  You use Replace Field Contents[] when you want it to automatically run through all records.

Why did I provide you with both?  To confuse you, ROFLMAO!  

No, I provided all methods as a way for you to learn and choose the best for your situation.  For instance, if this is an on-going need while Users are in the system, you would want to error trap using the looping method in case a User has one of those records locked and it didn't set.  Using the loop also allows you to do other things at the same time to those records if you wish. 

The reason Replace Field Contents[] worked is because it did it all records and then moved to the next and again did it to all records - MAJOR overkill BIGLY.  Use debugger and watch it working.  So use either a loop OR Replace Field Contents.  Replace Field Contents[] also throws an error but does so only at the end so you don't know WHICH record failed so it is never good to use RFC when Users are in the system.

Edited May 31, 2017 by LaRetta

[comment]

1 hour ago, OneStop said:

I need to extract the first two Items Cat1 and Cat2 into their own fields...

I doubt very much you need or want to do that. What would be the purpose of this? You will end up with several numbered Category fields. Numbered fields are a prime example of poor database structure. You won't be able to find a record by its category, unless you search all the fields. You won't be able to define a relationship matching on category. You won't be able to produce a report of records grouped by category. 

I suggest you either split the field into individual records in a related table or just substitute the > character with a carriage return, so that your field contains a list of the relevant categories (same as a checkbox field would).

 

Edited May 31, 2017 by comment

[LaRetta]

I saw cat1 and cat2 as just names he gave them to imply the values were separated by the chevron; not that he actually wanted to create numerous numbered fields!  Good catch if that's the case, Michael!

[OneStop]

14 minutes ago, comment said:

I doubt very much you need or want to do that. What would be the purpose of this? You will end up with several numbered Category fields. Numbered fields are a prime example of poor database structure. You won't be able to find a record by its category, unless you search all the fields. You won't be able to produce a report of records grouped by category. 

I suggest you either split the field into individual records in a related table or just substitute the > character with a carriage return, so that your field contains a list of the relevant categories (same as a checkbox field would).

 

Well I need to do this because I have imported a file for a customer into a standalone FMPro runtime, from their E-Commerce Db. Within that file they have a field called category_delimited. This field contains information structured as such: Main Category>Sub Category>Sub_Sub Category....this is their Shopping Menu Nav...They want to be able to modify and restructure their Cart system categories so I decided to break out each sub menu item into it's own field. Then when they're satisfied with the overall structure I can concatenate those fields back into the expected category_delimited format.

 

I've used Filemaker Pro for years but only as a UX/UI tool for my Db schemas in MySQL/PHP dev. So the syntax of Filemaker's calculations isn't something i've really fooled with before. The above seemed to be the easiest way to hand my client a tool to modify/plan their nav structure without actually screwing up the Db on the production site, : ) 

 

I would NEVER name fields with numbers...that's a horrible schema. 

Edited May 31, 2017 by OneStop

[comment]

6 minutes ago, OneStop said:

This field contains information structured as such: Main Category>Sub Category>Sub_Sub Category

I see. Well, that makes sense and LaRetta gave you the right answer. Sorry for the false alarm.

[LaRetta]

However, Michael, one would think I'd have learned to ask for context so I could provide a specific answer instead of causing confusion by providing too many alternatives.  If an on-going solution and if I knew the loop were the best choice, I would have provided error trapping as well.  I appreciate you stepping in.

12 minutes ago, OneStop said:

I would NEVER name fields with numbers...that's a horrible schema. 

Yes, well we can't name fields just with numbers but any time we see someone newer to FM naming several fields the same name, followed by a number, it usually indicates they are making the mistake outlined by Comment AND you called yourself Novice so we were protecting you.  :-)

[OneStop]

32 minutes ago, LaRetta said:

The reason Replace Field Contents[] worked is because it did it all records and then moved to the next and again did it to all records

So Replace Field Contents [] replaces the field contents in all the records by default?

[LaRetta]

Correct.  Read up in Help for more specifics.  But it can only address a single field at a time which is why if you have multiple actions, looping is preferred.

[OneStop]

Yes I appreciate all the help very much. And thank you for trying to protect me from myself : )  I can certainly appreciate that.

I'd spend more time learning FMPro's syntax and functions if I weren't up to my eyeballs in keeping up with PHP and Ruby's constant changes....

[LaRetta]

5 minutes ago, OneStop said:

So Replace Field Contents [] replaces the field contents in all the records by default?

Yes.  That is the purpose of it ... to perform replacement through all records in the found set.

[Charity]

1 hour ago, LaRetta said:

Replace Field Contents[] also throws an error but does so only at the end so you don't know WHICH record failed so it is never good to use RFC when Users are in the system.

I did not know this.  It does not say that in FM Help!?  Also, it says "When you use the Replace Field Contents script step, the data must be committed first before the operation is performed, or you may get unexpected results. "  I do not understand what this means.  Should I start a new topic?

[LaRetta]

2 minutes ago, Charity said:

the data must be committed first before the operation is performed, or you may get unexpected results. 

I don't know.  

I suppose it is because, if there is an auto-enter on the field the RFC would happen before the auto-enter which runs after the field is committed?  I would have to consider that more deeply.  Maybe lookups or related records would be influenced?  Maybe others can offer examples as well.