Needed - a more elegant solution to my problem!

[philipcaplan]

Hi All

I have an FMPro 8.5 database with a field called "Category" which might contain a single word such as "Insurance" or "Food", or a phrase such as "Travel and Holidays", or more likely multiple words and/or phrases from a list of possibly 25 or so choices, each separated by a comma and a space, and in any order.

What I need is to have a number of fields called for example "Insurance" and "Food" and "Travel" (there will eventually be about 20 such fields, and new ones may need to be added at a later date).

Into each I paste a calculation as follows, which causes a "y" to be inserted in the field if the "Category" field of that record includes anywhere in it the appropriate 'trigger-word' such as 'Travel':

========================

If(MiddleWords ( Category ; 0; 1 )="Travel";"y";""

& If(MiddleWords ( Category ; 1; 2 )="Travel";"y";""

& If(MiddleWords ( Category ; 2; 3 )="Travel";"y";""

& If(MiddleWords ( Category ; 3; 4 )="Travel";"y";""

& If(MiddleWords ( Category ; 4; 5 )="Travel";"y";""

& If(MiddleWords ( Category ; 5; 6 )="Travel";"y";""

& If(MiddleWords ( Category ; 6; 7 )="Travel";"y";""

& If(MiddleWords ( Category ; 7; 8 )="Travel";"y";""

& If(MiddleWords ( Category ; 8; 9 )="Travel";"y";""

& If(MiddleWords ( Category ; 9; 10 )="Travel";"y";""

& If(MiddleWords ( Category ; 10; 11 )="Travel";"y";""

& If(MiddleWords ( Category ; 11;12 )="Travel";"y";""

& If(MiddleWords ( Category ; 12; 13 )="Travel";"y";""

& If(MiddleWords ( Category ; 13; 14 )="Travel";"y";""

& If(MiddleWords ( Category ; 14; 15 )="Travel";"y";""

& If(MiddleWords ( Category ; 15; 16 )="Travel";"y";""

& If(MiddleWords ( Category ; 16; 17 )="Travel";"y";""

& If(MiddleWords ( Category ; 17; 18 )="Travel";"y";""

& If(MiddleWords ( Category ; 18; 19 )="Travel";"y";""

& If(MiddleWords ( Category ; 19;20 )="Travel";"y";""

& If(MiddleWords ( Category ; 20; 21 )="Travel";"y";""

& If(MiddleWords ( Category ; 21; 22 )="Travel";"y";""

& If(MiddleWords ( Category ; 22; 23 )="Travel";"y";""

& If(MiddleWords ( Category ; 23; 24 )="Travel";"y";""

& If(MiddleWords ( Category ; 24;25)="Travel";"y";""

& If(MiddleWords ( Category ; 25; 26 )="Travel";"y";""

& If(MiddleWords ( Category ; 26; 27 )="Travel";"y";""

& If(MiddleWords ( Category ; 27; 28)="Travel";"y";""

& If(MiddleWords ( Category ; 28; 29 )="Travel";"y";""

& If(MiddleWords ( Category ; 29; 30 )="Travel";"y";""

))))))))))))))))))))))))))))))

========================

This works, but for each field I create I have to paste in the above, then go through it replacing "Travel" (or whatever the trigger-word is) 30 times, since there might be up to 30 words in any "Category" field.

Can anyone suggest a more elegant calculation or way of achieving this? Perhaps something involving a variable at the top of the calculation containing the trigger-word, so it only has to be changed once for each use?

Any help will be much appreciated. PHILIP

[mr_vodka]

Look into the functions of PatternCount, Filter, FilterValues, etc.

See if it helps you along. If you still have problems figuring it out then come back and post where you are having the issues with.

[efen]

Not an elegant way - more a cheap and nasty way of using your present calculation:

Create a global field into which you enter your chosen text string for which you want to search.

In your long calculation substitute the word in quotes with:

"""& globalfield & """

[philipcaplan]

Thanks for your prompt reply, efen, but I want to have many different fields with "y" in them if the trigger-word is present, and nothing if it is absent.

For example, if the "Category" field contains the following one line of text:

'Travel & Holidays, Food, Pet Supplies'

then I would want the presence of "Travel", "Food" and "Pet" somewhere within it to trigger a "y" in 3 calculation fields called say "TRAVEL", "FOOD" and "PETS", but nothing within any other fields looking for other trigger-words (eg, DRINK, INSURANCE, ELECTRICALS, etc, etc).

I'm not clear how a global field would work for me here. I'd be grateful if you could explain further.

[bcooney]

I'm surprised no one has mentioned that your calc indicates a very poor data model. You should, first of all, have a table of categories, each with a unique serial ID and the description. Perhaps categories have as an attribute a group type of some sort?

It is not clear what you do with these categories. Do you assign them to a record?

[philipcaplan]

Thanks for your response, bcooney.

In fact, this is not a 'database' as such, it is a spreadsheet (.xls file) which I have opened in FMPro8.5 in order to "massage" it into the shape I need.

The people who created the spreadsheet chose to use one cell to hold the data showing which Categories a given company are servicing, whether that is only one category or multiples!!

So, you see, I have no choice about the data and its structure!

I'm just trying to 'break' this agglomerated data into a number of separate fields, then I can say do an export of every record where "Food" equals "y". I am choosing to do this in FMPro because I have a **little** more knowledge of FMPro than I do of Excel, and I find it more forgiving to work with :

So, if you can help me construct a calculation that will require less typing I'd be much obliged! TIA. PHILIP

[David Jondreau]

What's your end goal here?

If you're not interested in cleaning up the data, but just exporting certain records, you could simply do a find in that field for the key word and export the found set.

PS: To get something more manageable, you could start off a repeating calculation field (with 50 reps)=

Let([

rep = Get(CalculationRepetitionNumber);

attrList = Extend(field);

attrList = Substitute(attrList; ","; "¶")

thisVal = GetValue(attrList; rep)];

thisVal

)

This will drop each value into its own repetition. From there you can do finds or export the field, splitting the reps into new records into a nice normalized table.

Edited February 2, 2010 by Guest

[efen]

If your category field is a list of categories separated by commas then the attached file may be of some use - it will break out each of these categories into separate related records for each main record. These related records can then be used for your purposes?

split_categories.zip