Hi,
It's going to be a nasty formula in DP. Actually there is a trick to it.
About 15 years ago, I needed a VB function to do a similar thing. The VB
function used FOR loops to loop through and find each word, and then used
the VB mid() function to get the first character and the UCase() function to
capitalise it. It then used the VB Replace function to replace fragments in
words, REPLACE("mccarthy","mcc", "McC") over and over again for each
exception.
Since DP doesn't have a FOR Loop you have to simulate it which is actually
far easier that it first looks. There is absolutely nothing like a FOR loop
in DP, however you can create a faux FOR loop structure by creating a dummy
SubReport,"Subreport Using Virtual Link" (you do not need any link fields)
and putting logic to keep it going until you need to stop, e.g."Repeat
Record if RV is True (not 0)", and "Stop [Sub]Report if RV is False" and ,
using the RV as a loop counter to act as a FOR counter, a WHILE or UNTIL
condition is met. You can initialise these controlling RV within the
FirstPageHeader of the support, or preserve them between calls by
initialising it in the calling report body. The SubReport then contains the
logic that you need for the processing. No one ever said that a subreport
has to be doing anything with the underlying data.
The subfield function can be used to calculate how many words you have.
Length[cat.c[subfield[rv1;" ";1]; subfield[rv1;" ";2;]; subfield[rv1;" ";3];
etc ]] or if you have names with many many words that the formula looks too
complex, use a faux loop, as a DO UNTIL loop by testing when the Length[] of
the last iteration is 0, and the other RV to sum how many time it takes to
get there.
RV1 = [PxFz] /*data to Title case */
Header+++
RV2=1 /* counter */
Body+++
RV3= length[subfield[RV2;" ";rv2]>0
End Subreport if RV3 false
RV2=RV2 + 1
The subfield function can then be used with one of these faux loops to
extract individual words, and using the lower.case and icaps functions to
give you a title case capitalisation of each word. You can use the RV1 from
above to test for the Exit condition from the LOOP
E.g.,
RV1 data to Title case
RV2 preserved from previous counter ie the number of words
RV3 = "" /* initialise result RV */
RV4 = 1 /* initialise counter */
SUBREPORT LINK/PANEL: 0 1
Body+++
RV5 = (RV2>RV4)
End Subreport if RV5 false
RV3 = Cat.C[RV3; icaps(lower.case[subfield[RV1;" ";RV4]])] /* concatenate
RV3 buiding it word by capitalised word */
RV4 = RV4 + 1
Repeat Record if RV(2) is True (not 0) /* choose an RV which doesn't change
e.g., RV2 */
If you wanted to you could combine the above into one step, but I broke it
up so you could see different wants of using the Faux FOR loops
Ok, so this works for "Joe Bloggs", but not "Joe McCarthy"
After this you need to process the exceptions. With another faux loop you
can create the exceptions using the subfield functions to break words up and
concatenate them with the result you want, a faux Replace function
for example:
cat.c["McC"; subfield[lower.case["Mccarthy"];lower.case["McC"];1]]
of course the text "Mccarthy" is in an RV that previously had been icaps'd,
and the exceptions mask "mcc" is also in an RV.
Now here is the wonderful thing, instead of making a gazillion pieces of
formulae to cover the exceptions, you can create an exceptions panel, and
create another Virtual Subreport to loop through these.
It actually takes less lines of code than my old VB formulae, only nest to
about 3 subreports, and is easly maintainable when you find another
exception by just adding it to the exceptions panel.
The exceptions can be whole names. e.g. "IBM", or fragments at the beginning
of names "McC" or "D'A", DeGolyer, or "auDA", you just need to make your
fragments long enough to uniquely cover the exceptions, the exceptions panel
only needs one field.
Basically any time you want to loop through a string of data that you want
to break up and do something with can generally be easier to manage with the
Faux (subreport) Loop than complex formulae. Any time you want to execute
something a specific number of times the Faux loop can be used. It adds a
programming construction that is otherwise missing in DP.
In one DP Web App I wrote, I have a report which loops through an uploaded
CSV file, where the first row contains a header that the user can specify
the fields (in any order) that they want to import, and they can import
thousands of records at a time with up to about 30 or so optional fields. At
the worst point I have about 8 nested SubReports handling loops, working out
if the import is valid, and which records need to be added or which pieces
of data to merge into existing data. Without the faux loops it would be
impossible with DP.
If there was one thing that I would have loved with DP would have been the
ability to use an existing report as a subreport for another report.
Unfortunately if you need to do a particular loop a number of times, then
you have to build it each time in each subreport, there are limits to how
big you can make a report definition before it breaks, but so far other than
when I try to break it, I have not found the limit is large enough.
Good luck
Brian
PS. I didn't actually try these examples when I wrote this email, so just
check before copying and pasting that I haven't missed something in a
formula.
From: [email protected]
[mailto:[email protected]] On Behalf Of Don Friedman
Sent: Tuesday, 23 April 2013 4:30 AM
To: Dataperfect Users Discussion Group
Subject: [Dataperf] Two points of inquiry
First, has anyone written (in English) a formula for scanning text and
adjusting the capitalization of names? As in changing MCCARTHY to McCarthy?
I'm about to undertake this to see how it goes but if someone has done it it
would be helpful. Obviously there's no great trick to it other than writing
a very, very long formula.
Secondly, on one of my larger voter databases that was acting just fine
yesterday, when I opened it up this morning the colors changed on my basic
panel listing and report writing screens. I went in to the STE file and saw
that there was a bit of corruption in the header area which I was able to
correct without having to reindex (that takes all night plus some . . . )
but I'm wondering how often any of you find that happening.
Don
--
Don Friedman
ProfessionalRecords.Com LLC
PRS Data Systems
Liveport WiFi
205 S Main Street
Pittsburgh, PA 15215
412-784-1600 - 1-800-PRS-FILE
412-784-1615 Fax
_______________________________________________
Dataperf mailing list
[email protected]
http://lists.dataperfect.nl/mailman/listinfo/dataperf
