Evaluate Text strings?

[LaRetta]

I've attached a file I could use ideas on. We have an administrator who checks incoming data. Sometimes there are records which should not be allowed in (but can't be deleted) and should remain in a Holding table. Currently, she has to boolean check (1) any record to ignore but we are trying to automate the process because sometimes there may be 1,000 records come through all fitting the criteria NOT to allow in. What is the criteria? It is patterns within one single text field.

We want Admin to have rights to tell us the patterns and calc to automatically eliminate those entries. After import, she can change her mind (because the number field "Ignore" will be set but it will assist her a great deal. The same patterns in data have come through for years.

1) I can tell her to put the pattern, one on each line of a multiline. But sometimes the pattern is two individual words within the line and we can't tell where (see example).

2) I thought of using repetitions; each rep being a 'piece'. If she put the entire phrase to omit within one repetition, then use PatternCount(); otherwise, use something like what I used (by adding the pattern pieces together).

3) I even considered records but ...

The global in this test file will be one single field in one record in Admin Preferences file. The Lines are always imports from outside source. Ideas on how best to put this together so it is User-friendly and the person doesn't need to understand calcs? I would sure appreciate it!

UPDATE: I changed the file - adding what I was considering. But as you see, if I use repetitions, it counts incorrectly (seeing each rep as a pattern independenly from the rest). I think I need to count all reps then divide by the number of t fields or something ...

LaRetta

testCalc.zip

Edited January 31, 2008 by Guest

[comment]

I am not sure I fully understand what's required here. How about something along the lines of the attached? It's just a basic sketch, and it could stand a few improvements (such as the ability to switch each individual criterion between "contains", "does not contain" and perhaps some more, e.g. "begins with", "ends with").

I cannot help wondering if it wouldn't be better to use a similar mechanism to generate find requests, instead of a calculated result.

PatternFinder.fp7.zip

[LaRetta]

Wow, I didn't even consider the 'does not contain'! I had originally considered finding but there were a few reasons I wanted to avoid that: 1) this is in a router (software key router) and I must preserve the found set and distribute records in many different directions, 2) the 'ignore' flag is used in a calculation which determins the status of that record and (many of) its sister and child records as they are processed, 3) I believe it will be easier for User to type into field (particularly how easy you've made it) than to deal with find criteria and 4) since it contains multiple words within same field as well as OR criteria but the word order could even switch, it could get complex (I think). Also, I want them to have the visual to work with.

This is perfect in every way except where I neglected to mention one additional need. I would like the option of activating it on line-by-line basis. In other words, deactivate a certain line but keep it there so they know what it said because it'll probably be needed again the following year (or later). If this isn't easily workable, we can handle it another way - copying it to archive field and removing here (if necessary).

But yes, you nailed it, Michael! I sure appreciate all the help. :thankyou:

[comment]

I would like the option of activating it on line-by-line basis. In other words, deactivate a certain line but keep it there so they know what it said because it'll probably be needed again the following year (or later). If this isn't easily workable, we can handle it another way

Actually, it's very easy - almost trivial. Just add buttons to switch repetitions on and off.

PatternFinder.fp7.zip

[LaRetta]

Your modification works perfectly, Michael, thank you so much! It sent me off on playing with various repetition examples you've given us. I also enjoyed the other tidbits in your file, such as boolean (showing as ON/OFF or 'x') and conditional formatting the row highlight based upon the same booleans. And I like the reminders of splitting layout design by placing much of the data in the header/footer and using list in the middle.

But I don't understand your calculation, particularly ( IsEmpty ( gAndIncludeR) OR PatternCount ( Extend ( Line ) and same with the gExcludeR portion. Can you take that calc and make it understandable for a 5-year-old? I would sure appreciate it because, no matter how long I study it, the logic escapes me. It appears that you are saying 'if it is' or 'if it isn't' and it just makes no sense no matter how I look at it.

I had hoped to have it figured out before responding but I wanted to respond and thank you some time this century.

LaRetta

[comment]

Hmm... on further consideration the third criteria doesn't need the IsEmpty alternative. Now, is it any clearer this way:

// MARK A LINE IF IT MEETS ALL THE FOLLOWING CRITERIA:



// 1. IT CONTAINS THE FIRST STRING

PatternCount ( Extend ( Line ) ; gIncludeR ) 



and 



// 2. IT CONTAINS THE SECOND STRING - OR THERE IS NO SECOND STRING TO CONSIDER

( PatternCount ( Extend ( Line ) ; gAndIncludeR ) or IsEmpty ( gAndIncludeR ) )



and



// 3. IT DOESN'T CONTAIN THE THIRD STRING

not PatternCount ( Extend ( Line ) ; gExcludeR ) 



and



// 4. THIS RULE IS ENABLED

gEnableR

Note that if the first string is empty, the rule will not work (i.e. it will not cause any lines to be marked). Otherwise, you could just leave all three strings empty and have it mark ALL lines as the result.

---

P.S. A liberal use of Trim() on user input is recommended.

[LaRetta]

Yes, I see it! I like to place the calcs into globals in the layout I'm working with so I can see it in action PLUS view the calc itself at same time. And, with the prior calc, I just didn't see the connections. But when I pasted this calc in the footer and watched it in the layout, it makes perfect sense! Thanks for taking the time to walk me through it. :smile2:

[LaRetta]

Oh yes! Thank you again for listing it out like this! You've done for me in 2 minutes what I couldn't see in two days!!

[comment]

LOL, sounds like a revival meeting. Rise! Rise!

[LaRetta]

... feels that way too. I DO spend quite a bit of time on my ceiling. "Down girl" comes to mind as well. ROFLMAO!

I was given a sign once which said, "Kindly restrain your enthusiasm." I couldn't (and still can't) understand why anyone would want to do that!

[LaRetta]

This is a solution provided by Comment a year ago based upon PatternFinder. We need to now add a comment which shows WHY cMarker produces boolean true by listing the results of the test in a comment field. Router is related to Data 1:1.

Please see calculation cComment in Router for my attempt so far. I cannot figure out how to omit the need of hard-coding the repetitions in the Let().

I would like to instead just ask, "Is the record true for cMarker and if so, what is the first non-empty cPassR that is also enabled (gEnableR) ... so maybe add them together and search for the first cPassR + gEnableR = 2.

Then use THAT as the assigned repetition number in the rep variable. Why do I have to test each repetition (in combination) and hard-code the rep numbers? I am hoping the calc can be simplified and more elegant and remove the rep numbers completely. Possible?

PatternFinderRevisit.zip

[comment]

what is the first non-empty cPassR that is also enabled (gEnableR)

I believe this question can be answered by:

rep = Ceiling ( Position ( List ( Data::cPassR ) ; "1" ; 1 ; 1 ) / 2 )

Note that cPassR already includes a test for the status of gEnableR, so the question can be reduced to "what is the first true repetition of cPassR".

[LaRetta]

Oh that answers it, Michael! It looks so much better and it eliminates the hard-code of the rep number (which bothered me a great deal)! So now the calc becomes (unless you can also see other ways of improving it)

Case ( Data::cMarker ;

Let (

rep = Ceiling ( Position ( List ( Data::cPassR ) ; "1" ; 1 ; 1 ) / 2 ) ;

TrimAll (

Case ( NOT IsEmpty ( Data::gIncludeR [rep] ) ; "Line contains '" & Data::gIncludeR [rep] & "' " ) &

Case ( NOT IsEmpty ( Data::gAndIncludeR [ rep ] ) ; "and also contains '" & Data::gAndIncludeR [rep] & "' " ) &

Case ( NOT IsEmpty ( Data::gExcludeR [rep] ) ; "but does not contain '" & Data::gExcludeR [rep] & "'" )

; 0 ; 0 ) & "."

)

)

Perfect, thank you so much!! :smile2:

Edited May 1, 2009 by Guest
Nuanced calc per Comment's suggestions

[comment]

I think you need to wrap the calculation part in a TrimAll(). I would also get rid of the stand-alone spaces and append them to the other literal strings - but these are nuances.

[LaRetta]

Oh funny! I was so focused on eliminating the reps, in both the Let() and the calculation itself, that I ignored the smaller (but still quite important) nuance! Thanks much, Michael, I've corrected the prior calc and I hope that looks better.

UPDATE: Ceiling ( Position ( List ( Data::cPassR ) ; "1" ; 1 ; 1 ) / 2 ) ... is really sweet!

Edited May 1, 2009 by Guest
Added update