Retrieving a SUM of numbers mixed with Text

[dcecil]

I think I have a tough one here...

I have a text field that when populated, looks like these examples:

Record #1 -- 2 days (Tony), 5 days (Chris), 1 day (Andy)

Record #2 -- 3 days (Chris) and 4 days (Julie)

I already have a script that will find all of the records needed, but I want to add a line to the script that will parse the undesired data and sum the desired data. For the above examples, I would want a line that would go through the fields and strip away everything except for "Chris'" entries and add up the days, which would be 8 days.

Is this possible, or am I hoping for too much? If not, I'll have to export everything into Excel and "massage" the fields to get a total.

Thanks.

[andygaunt]

Hi,

not the prettiest of solutions, but this might be worth a go (until the master of calcs dj jumps in )

OK, create four global fields.

_gName (text format). This is the field that is used to find the values required.

_gHold Value (text format)

_gHoldNumber (number format)

_gTotalNumber

OK, now the script

Allow User Abort [on]

# Turn off once the script is confirmed as running ok.

Set Field [ _gTotalNumber, "" ]

If [isEmpty(_gName)]

Exit Script

End If

# The If step checks you have a name to search for.

Go to Record/Request/Page [first]

Loop

Set Field [ _gHoldValue, Left(Data field, Position ( Data field, _gName,1 ,1) -7) ]

Set Field [ _gHoldNumber, Right(_gName, 2) ]

Set Field [ _gTotalNumber, _gTotalNumber + _gHoldNumber ]

Go to Record/Request/Page Next, exit after last

End Loop

OK, now this all assumes that the data is always entered EXACTLY as you have stated, no variation.

So, If you want to find the days for Chris, enter Chris in _gName and run the script.

Give it a go anyhow. Cant hurt.

[dcecil]

Thanks, I'll give it a try the first thing in the A.M.

DaveC

[danjacoby]

Create a related file, containing two fields, Days (a number field) and Person (a text field). Then you can add totals for each record in the main file (TotalDays being a calc field = Sum(Relationship::Days). Or you can get totals for each person in the related file; best way to do that is probably with a sub-summary part when sorted by Person and a summary field.

[djgogi]

Tanx for calling me in Andy.

First I must agree with Dan about the poor design of the table.

Now, your script is just fine (exept you misstyped _gName at place of_gHoldValue) but there migth be ( I must admit that I didn't check it) some problems with hardcoded part of it ( -7 ) since in field appear "day" and "days". Also the field is populated manualy (my interpretation) so you cannot be sure on number of spaces between words.

Sad so , here's what I came with:

No need for script only calcs.

I've split the calcs in few fields for sake of readability

textField -- original text field

textP -- calculation text

result -- calculation number

gName -- global text

textP=Substitute(Substitute(Substitute(Substitute(Substitute(Substitute(Substitute(textField, "days", ""), "day", "")," ",""), "(", ""), ")", ""), "and", ","), ",", "

[andygaunt]

See,

told you DJ would come up with some wacky calc. you the man!

As I said in my post, it all depended on the information always being entered in that same way, but I did a test with day and days and it seemed to return the correct result.

Anyhow, said you would come up with a wacky way of doing it.

And yes, I too agree with Dan about the design of the table. But I figured to try and resolve the issue within the constraints of the current system.

I too would have done it with a relationship.

[dcecil]

Andy,

I started putting in the script and then I found a limitation, which I put together into

a text document. I tried to explain everything, sorry if it sounds too elementary,

but I just wanted to make sure you understood what I was doing.

I'd appreciate any help.

Thanks,

DaveC

FM Script.txt

[dcecil]

DJ,

I started putting in the script and then I found a limitation, which I put together into

a Word document. I tried to explain everything, sorry if it sounds too elementary,

but I just wanted to make sure you understood what I was doing.

I'd appreciate any help.

Thanks,

DaveC

FM Script.txt

[The Bridge]

Pardon me for jumping in with my two cents, but I would suggest redesigning immediately instead of working with the data as-is. Based on the text file you attached, it looks like you could add a file called, say, LOE_Estimates, which would contain the person's name, estimated LOE, and of course a foreign key which would be related to the main file. This would make your calculations so much easier!

Butting out now.

[dcecil]

Sorry, but this would be too much trouble. I already have too many files/relational databases and am using a form/process that could take an act of Congress to change.

Thanks anyway,

DaveC

[djgogi]

Hi Andy,

as I said " I didn't test it" and yes it doesn't matter if there were day or days since you have used Right(text,2).

Than, it is basically the same calc you already gave him.

Dj

[djgogi]

andpresent=PatternCount(textToParse, " and ") calc number

alreadyVisited=PatternCount(textToParse, ",")+1 calc number

positionLast=Case(andpresent > 0 and alreadyVisited> 1,

If(Position(textToParse, " and ", 0, PatternCount(textToParse, " and ")) > Position(textToParse, ",", 0, PatternCount(textToParse, ",")), 0, 1),

andpresent > 0, 0, 1) calc number

hasMatch=Case(positionLast=1,

If(PatternCount(Right(textToParse, Length(gName)+1), gName), 1, 0),

If(PatternCount(Right(textToParse, Length(textToParse)-Position(textToParse, ",", 0, PatternCount(textToParse, ","))), gName), 1, 0)) calc number

textP=Substitute(Substitute(Substitute(Substitute(Substitute(Substitute(Substitute(textToParse, "days", ""), "day", "")," ",""), "(", ""), ")", ""), "and", ","), ",", "

[danjacoby]

"I already have too many files/relational databases..."

Um, regarding having "too many" files -- there ain't no sich animal. Certainly, all other things being equal, fewer files is better than more files, but the number of files in a solution is a function of what needs to be done, and the actual number is irrelevant. If you need extra files, create 'em!

[Kundinger]

Hello,

I CONCUR with 'danjacoby'. Related files are much more efficient and managable.

I 'worked myself into a corner' with a similar issue on a system for a client. I was able to 'parse' the data out using a couple of calc fields, but it created a real mess. Switching to a related file improved the system overall. It involved a little work on my part with reports, etc. But in the long run, everything worked better.

FYI...

-- Beware, if you are having the users entering "Andy", "Tony", "Chris", "Julie", "1 day", "2 days", etc.

-- Some users may inadvertantly enter "Andie", "Tonny", "Cris", "Juli", "1 dy", "2 dayss", etc.

-- This could wreck 'havoc' on the 'integrity' of your resulting calculated data

-- Users will then complain of a 'bad' system, it's not their fault, etc.

A suggestion... if you haven't already implemented it...

-- Create an 'air-tight' interface which has menu choices for repetitive data

-- Use "Pop-up Menu" with "Include 'Other...' item to allow entry of other values"

-- You could also use 'editable' auto-entered 'default' data fields

-- This will insure that 'quality' data is entered

-- This will also allow users to 'add' other menu choices when needed

Hope this helps...

Bob Kundinger