Find a word in a sentence

[SteveFox950]

All

I am trying to find words in a string and copy the words into different fields. The word locations in different records are not always in the same place. There are quite a few records in my database (25000 or so). Any clue??

i.e

record 1 The + Quick + Brown + Fox

record 2 Quick + The + Fox

If the word exist anywhere in the sentence I want to show it in different

Record 1

Field 1 The

Field 2 Quick

Field 3 Brown

Field 4 Fox

Record 2

Field 1 The

Field 2 Quick

Field 3 "Null"

Field 4 Fox

Any clues would help

Thanks

Steve

[Ted S]

Steve,

Your question is not clear.

Are the words that you are looking for known to you? For instance if the block of text contains the word "quick" you always want to place in in Field_A?

[SteveFox950]

Ted

The answer to your question is yes.

The sentence contains lets say 10 random words and I want to break up the sentence and put the words in associated specific fields. In the case of the quick brown fox, quick would go in field labeled quick, Brown in field labeled brown,etc... But the only problem is that the words are not always in the same position in each record sentence.

Thanks

Steve

Edited November 11, 2005 by Guest

[LaRetta]

I want to break up the sentence and put the words in associated specific fields.

I assume you have a good reason but, for the life of me, I can't figure it. You want each word in a different field?? Do you even know the maximum number of words you will need to trap for? You can't create FIELDS on the fly.

I think you might benefit from considering different approaches to the problem. But you haven't given us a vision to work with! You are giving us the problem in riddle form. If you create a field called BROWN and want all words called BROWN to appear in the BROWN field as multiple records, why don't you just use text functions, ie, PatternCount() etc. (or other techniques) to get your results (count them?)? I question both the purpose and the method here.

You haven't even indicated if this is a one-time conversion of some sort, needed for a statistical analysis, or simply a problem presented out of curiosity if it can be done. We'll help you as best we can regardless. But can you brief us a bit on the project?

LaRetta

Truffle Hunter

[Ted S]

How about something like this:

Set Field [Table_Name::Brown_Field; If ( PatternCount ( Table_Name::Search_Field; "Brown") > 0; "Brown" ; "" ) ]

You would do this for each of the fields you want to populate.

[SteveFox950]

Laretta

I apologize for being so cryptic. I am currently working on a software distribution project and get feeds from a database that puts it in the following formats:

Record 1 WMTP 2.41.1 + Trust 2.1.1 + IAD 2.1.0 + W051011 + W051003 + W051012

Record 2 WMTP 2.41.1 + IAD 3.2 + W051011 + W051003 + W051012

Record 3 WMTP 2.40.1 + Trust 2.1.2 + W051011 + W051003 + W051012

MY goal is to determine the current levels of software and export what software is required to bring everyone up to the latest version. i.e. WMTP 2.40.1 in record 3 but the latest version is WMTP 2.41.1

I created fields for the current level WMTP 2.39.1, 2.39.2, 2.40.1 etc,,, and calculated that if the value in the field is not WMTP 2.41.1 then put WMTP 2.41.1 in another field as required software.

That was the easy part. My problem is to break up the software for each record and put the current data value in a specific field. i.e record 1 (Field WMTP) WMTP 2.41.1 (Field Trust) Trust 2.1.1 (Field IAD) IAD 2.1.0, etc..

My problem is that the software versions are different from record to record and one workstation may have software installed but not in the other. The data order is the same on all workstations but there may as shown above in record 2 it does not have trust installed. I currently am separating the data using access and importing it into the database. If you want me to send you the database I will so you can better understand.

I know this is confusing but I know I can save myself hours of work each day if I figure this out. I appreciate your help.

Steve

[LaRetta]

Hi Steve,

I can't give this much thought until tonight, but ... I take it you don't want to 'fill in' what software they are missing but only isolate which versions need upgraded? Each record is identified as a customer also, I hope (with workstation identifier)? And + separates each station's software versions? And this will be an ongoing issue - receiving this data in this format? I wouldn't be attempting to write this data to fields. I would instead create a 'software' multiline text calculation as:

Substitute(textField; "+" ; ¶)

I would then create a Current Software table containing two fields: Version and Description (description not critical but helpful). In fact, I would bet you already have such a list, no? It would be easy to keep this Current Software table up to date. I would join this customer multiline calc to Current Software::Version on =.

There are various themes and perspectives which can be implemented by@

[SteveFox950]

Laretta

It works great if its there is only one version of software as shown below:

If (PatternCount (Endpoint_Scan;"WMW 2.40.1") > 0;"WMW 2.41.1";"")

but I may have more than on version (ie: WMW 2.40.1, 2.39.1, etc... I tried the above and it did work for version WMW 2.40.1 but how do I add additional versions in my script???

Thanks so much for your help. I feel I am so close to this one.

Steve

[LaRetta]

Hi Steve,

I've attached my 'morning vision'. I am convinced it would be easier than what you were attempting. But it's far from elegant or even good. I've been up 38 hours and have simply hit the wall.

My vision was to use relationship then script to strip. I then realized that *I* use a recursive CF for handling (comparing) two 'value lists' (oh dear).

In demo, I assumed text at beginning could uniquely identify the vendor. If you can provide more specifics on that pattern portion (is it similar to VIN numbers, for instance?), we could help you with the VendorKey calculation. I didn't know what the codes meant (what software vendor they stood for) or how those numbers split into a 'vendor' and 'version' portion, so I removed the Wxxxxxxx codes from the demo because I couldn't assign them different Vendors with the same single W letter.

... and last but not least ... it breaks. Look at record 6. First 5 records work - records work right with that same code in them that breaks - but the last record breaks (on WMTP 2.41.1). And I don't know why. It's something simple in the strip; but my mind is just too shot. It only requires one Value List and 3 Process fields (and could even be tweaked further, I'm SURE). The DEMO fields in this file are only to show what the script is working with (Strip Leave for instance - script sets data in the field then removes it again. So 'mirror' field Strip Leave shows you what was there).

And I STILL believe there are even better ways. But I hope this gets you going ... or spurs ideas from others. Otherwise, I'll address it again after many hours of sleep and my morning

[color:blue]UPDATE:

[color:blue]Sample file has been corrected thus: I added the red to the very first loop line in the script:

Set Field [ Data::UpgradeSuggestions ; ValueListItems(Get(FileName) ; "Version List" ) [color:red]& ¶

[color:blue]My vs. 6 thinking keeps wanting to mix with vs. 7 on multilines. I keep seeing paragraph markers everywhere (except where they SHOULD be). HA HA!

LaRetta

Versions.zip

Edited November 14, 2005 by Guest

[comment]

It seems to me that you have not chosen a good starting point to attack the problem. The problem, as I see it, is the data not being properly structured. Instead of trying very hard to process badly structured data (and inevitably getting errors), I would start at correcting the structure. It seems that the entitites are:

- Workstation

- Application

- Installation (a join between Application and Workstation, indicating the installed version)

If we can separate the incoming data into these entities (and CREATE RECORDS in the corresponding tables), the rest should come pretty easily.

However, I don't see a way to separate "W051011" into Application and Version. You are assuming that numbers indicate the version. That could be right, and it could also be wrong. An application could be named B2B - and some applications don't even use numbers for versioning (and how about v.5.05b?).

Having a list of all applications is not going to help much: since we don't know where (in the input data) the application name ends, we are going to have trouble when "Word######" matches "Word" as well as "WordPad". It might be possible to do a recursive calc here to find the longest match - but is that a good approach?

I would put this aside until the poster can tell us how to separate applications from their version in the incoming data. Otherwise it's an excercise in GIGO.

[comment]

Well, maybe - if the data is consistent - it can be done this way.

Versions2.fp7.zip

[SteveFox950]

Loretta

I dont know what to say but Thank you. I rhing this will work and I will make some changes to my database structure. BTW. Do you ever sleep. It really is appreciated all of the time that you havr put in t help me. I new to this and cant believe how great this database program is. If there is anything I can do for you please ask

Steve

[LaRetta]

If there is anything I can do for you please ask

I dearly hate having to admit this in front of God and the world ... but yes, there is something you can do for me ...

Don't use my technique. It breaks.

I didn't rush back to my demo to fix it because, after seeing Michael's solution (Comment) there was no need (I thought). His solution is more solid, safe, efficient and ... it works.

And then I got side-tracked (a rarity for me ) so haven't fixed it yet.

I appreciate your appreciation very much, however. And if you still want to use my method, I'd try to fix it for you - I stand behind my work but it's going backward in elegance/efficiency.

L

[SteveFox950]

Loretta

I like your technique and what do you mean it breaks. What is wrong with it???

Steve

[LaRetta]

... and last but not least ... it breaks. Look at record 6. First 5 records work - records work right with that same code in them that breaks - but the last record breaks (on WMTP 2.41.1).

And I found other times it breaks also. My parsing multiline (I believe) is the problem. I probably need a ¶ somewhere. I parsed in vs. 6 using LeftWords() and ¶. In vs. 7, I'm using xValues functions and I think I lost the logic. I suppose I should fix it. But in truth, I'd rather delete it (and learn from the error myself). Why slip into a Plymouth (with cracked head) when a Farrari is free? What do you think mine gives you that Comment's doesn't? His produces correct results with less overhead and the split on Vendor and Version is the same theory! Even if mine worked it wouldn't be as good!

Update: A girl has SOME pride. I'm not going to keep admitting my demo sucks. ROFLMFAO!!

Okay it doesn't suck but you certainly can't drive it without a mechanic...

LaRetta

[LaRetta]

Alright. I wanted to understand why it breaks. Because the technique can be a powerful tool in many other ways. I have ran it through debugger 20 times. I see WHERE it breaks but not WHY. It only breaks sometimes and I can see no pattern.

It breaks here:

Set Field [ UpgradeSuggestions ; LeftValues(Substitute(Data::UpgradeSuggestions ; LeftValues(Data::Strip ; 1); ""); 99) ]

It's one of those things that drives me nuts. Now I must figure out my logic error. It may not be the best technique in this particular application but I'd like to understand why it breaks. This is wonderful! I feel my hounds start to bay as we speak! I'm off on the hunt!

LaRetta

[SlimJim]

A very fast reply - I'm on my way out. You cahnged from LeftWords on the preceding line to LeftValues on this line. That might be the problem.

[Fenton]

LaRetta, I think you've been bitten by the extra return of LeftValues.* Your Upgrade Suggestions does not have one at the end (it's just ValueListItems(). But LeftValues(Split; 1) does. What you can't see can hurt you -|

I also don't see that you need LeftValues(US; 99). So you could write that line:

Substitute( Data::UpgradeSuggestions; Substitute ( LeftValues(Data::Strip ; 1); ¶; ""); "")

or, in 8:

Substitute( Data::UpgradeSuggestions; GetValue(Data::Strip ; 1); "")

or just add a return to UpgradeSuggestions first.

*I don't see why FileMaker adds a return to any of the "Values" functions, including FilterValues(). It's one of the only places where FileMaker adds something that you don't really expect, and which you almost never want. I suppose it's so you can join 2 lists together; but one could just add a return in that case. There is probably some situation it's needed that I don't see. Fortunately GetValue() does not add one.

[LaRetta]

I think you've been bitten by the extra return of LeftValues...What you can't see can hurt you -|

Yep. That was my thinking error! And then mixing my 'old vs. 6 thinking' with xValues twisted my head a bit. I wish FM would provide a 'show markers' option to display carriage returns (like most word processing programs can do). Ah well, I should have caught it nonetheless.

Thanks guys. I'll correct the file. :wink2: