Multidimensional Array help 1

[roswelltx]

I have an extremely large text field on SQL that I am having to parse through and pull out several keys things. I am thinking a Multi-dimensional array would work for this but I cannot find an example of one anywhere. Does anyone have an example of what it is suppose to look like.
Thanks...

 

[synapsevampire]

If you're attempting to build arrays in SQL, they won't be available as arrays in Crystal.

Crystal itself does not directly support multidimensional arrays, though you could handle them by using numerous arrays, and even a controlling array.

However rather than stating that you wish to use arrays, why not just post requirements:

Crystal version
Database/connectivity used
Example data
Expected output

Someone may have a different approach, which I suspect will be the case.

-k

 

[roswelltx]

I am using crystal 10, with SQL database.
I am trying to parse through an extremely large text file, I need to be able to pull out the group assigned, the user id assigned too, and the date and time fields, here is an example of the text field:

Group Assigned changed from '' to 'ITCC-Soft1' by epere5 at 1/21/2005 3:40:15 PM

Status changed from '' to 'Open' by epere5 at 1/21/2005 3:40:15 PM

Ticket Type changed from '' to 'Problem' by epere5 at 1/21/2005 3:40:15 PM

Phone Number changed from '' to '972-431-3561' by epere5 at 1/21/2005 3:40:15 PM

Email changed from '' to 'cport4' by epere5 at 1/21/2005 3:40:15 PM

Entry Description changed from '' to 'User is having problems getting application CMS' by epere5 at 1/21/2005 3:40:15 PM

Worklog last updated by ljohns26 of ITCC-SOFT1 at 1/21/2005 3:45:26 PM

Customer Impact changed from '' to 'Critical' by ljohns26 at 1/21/2005 3:45:26 PM

Group Assigned changed from 'ITCC-Soft1' to 'PC/LAN Support-Dallas' by ljohns26 at 1/21/2005 3:45:26 PM

Priority changed from '' to 'High' by ljohns26 at 1/21/2005 3:45:26 PM

Priority changed from '' to 'Critical' by Automation at 1/21/2005 3:45:26 PM

Worklog last updated by cdenney1 of PC/LAN SUPPORT-PARK CENTRAL at 1/21/2005 3:56:47 PM

 

[synapsevampire]

Note that I suggested that you supply expected output, so it's impossible to demonstrate exactly what you want. IS this to search all of the text fields and return one output, or per text field?

Anyway, you can search for specific entries within a text field using various functions, such as INSTR, and if you have a carriage return at the then of each field, you could key off of that, but I'd want more information before I'd start.

-k

 

[roswelltx]

I'm sorry I put in before the text file what I need to pull out, I thought thats what you meant.
here is the expected output for everytime it changes:


Person assigned= epere5
Date/Time assigned= 1/21/2005 3:40:15 PM

The problem is I can split it up but the date and time fields are always going to vary in length to be able to pull them out consistantly right. I hope i am explaining this right, I am new to this. Thanks.

 

[smcnulty2000]

My take: I'm not 100% sure that this was one file but this is an example of how to manipulate data like this:


local stringvar strWorking;
stringvar array strarrWorking;
local stringvar strOutput:="";

strWorking:={@data};

strWorking:=Replace(strWorking, " at ", "###");
strWorking:=Replace(strWorking, " by ", "###");
strWorking:=Replace(strWorking, " PM ", " PM###");
strWorking:=Replace(strWorking, " AM ", " AM###");

strarrWorking:=Split(strWorking,"###");

"Person Assigned= " +strarrWorking[2] + chrw(13)+ "Date/Time Assigned=" +strarrWorking[3];



To explain: The first thing is to load it into a variable to manipulate it. Then you look through the variable for patterns. The 'at' and 'by' patterns are hopefully consistant throughout or this will blow up. You replace these with a set of characters picked for their unlikliness to occur, in this case I picked three pound symbols. Replace the ' at ' (including the spaces or you'll hit the middle of words like 'status'), do the same thing to ' by ', then do the same thing to PM, but retain the time/date designation of PM. Every step does a replace on the remaining set of info in the variable.

Then you can use 'split' to dump it into an array. From there you can manipulate it in some ways, although you might not be able to do what you need to specifically. Every 3rd item is the datetime, every third item (minus 1) is the person assigned. I put one example of how I might display it at the end there, to show how it might work.

You could get a for-loop to load up a big string if you wanted. It might look like this:


local numbervar x;
for x:=1 to ubound(strarrWorking)-3 step 3
do
(
strOutput:=strOutput+
"Person Assigned= " +strarrWorking[x+1] + chrw(13)+ "Date/Time Assigned=" +strarrWorking[x+2]
+chrw(13);

);

strOutput;


Hope some of this helps. Thank Synapse for this BTW, he got the info I needed to make this.

Cool problem.

Scott.

 

[roswelltx]

Scott, Great job. With some fine tuning , this looks like we can use it. One problem i am having though is, when you run it, it is getting an error saying:

"An Array's dimension must be an integer between 1 and
1000"

It is getting it from this line:
strarrWorking:=Split(strWorking,"###");

is it just not liking the ###?
Thanks for all your help and thanks Synapse for have patience with me and getting me help...
Regina

 

[GJParker]

The arrays in CR have a limit of 1000 elements, I would guess the text field conatins too many lines of data to use this approach.

from this sample

Group Assigned changed from '' to 'ITCC-Soft1' by epere5 at 1/21/2005 3:40:15 PM

Status changed from '' to 'Open' by epere5 at 1/21/2005 3:40:15 PM

Ticket Type changed from '' to 'Problem' by epere5 at 1/21/2005 3:40:15 PM

Phone Number changed from '' to '972-431-3561' by epere5 at 1/21/2005 3:40:15 PM

Email changed from '' to 'cport4' by epere5 at 1/21/2005 3:40:15 PM

Entry Description changed from '' to 'User is having problems getting application CMS' by epere5 at 1/21/2005 3:40:15 PM

Worklog last updated by ljohns26 of ITCC-SOFT1 at 1/21/2005 3:45:26 PM

Customer Impact changed from '' to 'Critical' by ljohns26 at 1/21/2005 3:45:26 PM

Group Assigned changed from 'ITCC-Soft1' to 'PC/LAN Support-Dallas' by ljohns26 at 1/21/2005 3:45:26 PM

Priority changed from '' to 'High' by ljohns26 at 1/21/2005 3:45:26 PM

Priority changed from '' to 'Critical' by Automation at 1/21/2005 3:45:26 PM

Worklog last updated by cdenney1 of PC/LAN SUPPORT-PARK CENTRAL at 1/21/2005 3:56:47 PM

which part gives you

Person assigned = epere5
Date/Time assigned = 1/21/2005 3:40:15 PM

?????


Gary Parker
MIS Data Analyst
Manchester, England

 

[roswelltx]

Let me give you a longer text file than that one. This is pulling from a Remedy ticket history field stored on SQL. We need to know every time the assigned person changes to another one and the date and time for that. The parts I need pulled out are bolded and in RED.
I know this is long but this is what i am up against. The object is to be able to see how many people worked this ticket and for how long. For instance, if it got assigned to 3 different people during the course of this ticket, and maybe the same person more than once, how long did each person work on it. Here it is:

Group Assigned changed from '' to 'ITCC-Desktop' by lkizer at 6/29/2004 1:30:03 PM

Status changed from '' to 'Open' by lkizer at 6/29/2004 1:30:03 PM

Ticket Type changed from '' to 'Problem' by lkizer at 6/29/2004 1:30:03 PM

Phone Number changed from '' to '972-431-5968' by lkizer at 6/29/2004 1:30:03 PM

Email changed from '' to 'jmolzahn' by lkizer at 6/29/2004 1:30:03 PM

Entry Description changed from '' to 'Every time something is installed remotely client has to install queryman 6.0 again on his desktop.' by lkizer at 6/29/2004 1:30:03 PM

Worklog last updated by esilv2 of ITCC-SOFT1 at 6/29/2004 1:41:22 PM

Group Assigned changed from 'ITCC-Desktop' to 'PC/LAN Support-Dallas' by esilv2 at 6/29/2004 1:41:22 PM

Priority changed from '' to 'Low' by esilv2 at 6/29/2004 1:41:22 PM

Worklog last updated by ssgardne of PC/LAN SUPPORT-DALLAS at 6/30/2004 7:51:20 AM

Group Assignment made by Auto Assign Application ssgardne of PC/LAN SUPPORT-DALLAS at 6/30/2004 7:51:20 AM

Person Assigned changed from '' to 'ssgardne' by ssgardne at 6/30/2004 7:51:20 AM

Worklog last updated by ssgardne of PC/LAN SUPPORT-DALLAS at 6/30/2004 8:49:04 AM

Status changed from 'Open' to 'Waiting for User Verification' by ssgardne at 6/30/2004 8:49:04 AM

Person Assigned changed from 'ssgardne' to '' by ssgardne at 6/30/2004 12:04:26 PM

Status changed from 'Waiting for User Verification' to 'Open' by ssgardne at 6/30/2004 12:04:26 PM

Ticket reopened by ssgardne at 6/30/2004 12:04:26 PM

Person Assigned changed from '' to 'ssgardne' by ssgardne at 6/30/2004 12:04:45 PM

Worklog last updated by ssgardne of PC/LAN SUPPORT-DALLAS at 6/30/2004 12:38:02 PM

Group Assigned changed from 'PC/LAN Support-Dallas' to 'PC/LAN Support-Dallas/Sfw Asset Mgmt' by ssgardne at 6/30/2004 12:38:02 PM

Person Assigned changed from 'ssgardne' to '' by ssgardne at 6/30/2004 12:38:02 PM

Group Assigned changed from 'PC/LAN Support-Dallas/Sfw Asset Mgmt' to 'PC/LAN Support-Dallas/Client Management' by jmasters at 7/22/2004 9:28:29 AM

Worklog last updated by mwatkins of DESKTOP SERVICES-DEPLOYMENT at 7/22/2004 10:40:59 AM

Group Assigned changed from 'PC/LAN Support-Dallas/Client Management' to 'PC/LAN Support-Dallas' by mwatkins at 7/22/2004 10:40:59 AM

Worklog last updated by mkoonce of PC/LAN SUPPORT-DALLAS at 7/23/2004 8:33:23 AM

Person Assigned changed from '' to 'client' by mkoonce at 7/23/2004 8:33:23 AM

Worklog last updated by kwlewis of PC/LAN SUPPORT-DALLAS at 7/23/2004 10:40:07 AM

Person Assigned changed from 'client' to '' by kwlewis at 7/23/2004 10:40:07 AM

Person Assigned changed from '' to' 'client' by fside1 at '7/23/2004 4:48:37 PM

Person Assigned changed from 'client' to '' by AR_ESCALATOR at 7/26/2004 7:14:45 AM

Worklog last updated by jcgoode of PC/LAN SUPPORT-DALLAS at 7/28/2004 10:25:59 AM

Group Assignment made by Auto Assign Application jcgoode of PC/LAN SUPPORT-DALLAS at 7/28/2004 10:25:59 AM

Person Assigned changed from '' to 'jcgoode' by jcgoode at 7/28/2004 10:25:59 AM

Worklog last updated by jcgoode of PC/LAN SUPPORT-DALLAS at 7/28/2004 10:28:26 AM

Person Assigned changed from 'jcgoode' to 'client' by jcgoode at 7/28/2004 10:28:26 AM

Person Assigned changed from 'client' to '' by AR_ESCALATOR at 7/28/2004 10:31:08 AM

Worklog last updated by mkoonce of PC/LAN SUPPORT-DALLAS at 7/29/2004 11:17:24 AM

Group Assignment made by Auto Assign Application mkoonce of PC/LAN SUPPORT-DALLAS at 7/29/2004 11:17:24 AM

Person Assigned changed from '' to 'mkoonce ' by mkoonce at 7/29/2004 11:17:24 AM

Worklog last updated by mkoonce of PC/LAN SUPPORT-DALLAS at 7/29/2004 11:46:05 AM

Status changed from 'Open' to 'Complete' by mkoonce at 7/29/2004 11:46:05 AM

 

[smcnulty2000]

GJParker is dead-on that the arrays can only be 1000 elements long and that's why this is breaking.

If you change the above statement listed from:

strarrWorking:=Split(strWorking,"###");

to

strarrWorking:=Split(strWorking,"###",1000);

it won't break. However it will no longer give correct information on large entries. It will work fine for some large number of most Remedy system trouble tickets. For the larger fields it will stop in the initial entries. Were I doing this report it would be satisfactory to me. But that's me.

I have to, before I create another formula combo that might or might not solve your problem, discuss in detail what you are actually trying to accomplish compared to what you have with your data.

Remedy diary fields can hold up to 64000 characters. This is a painful fact. Question: is this a project involving old data or future data or both? The problem might be better served with a Remedy change rather than a Crystal kluge.

Second; all you seem to be getting is a sort of abreviated version of the worklog. Is this actually going to tell you what you need to know? I mean, if every entry just says 'assigned' or whatever then it doesn't really say how long a person worked on it, it really only says how long they had it on their desk. Which is a different measure.

And it seems that to find the 'how long on Fred's desk' measure, you are going to have to parse these out into something that shows them as individual transactions, which my formula doesn't do.

Thirdly, how many tickets at a time are you going to run this report on? This could get to be an immense report from the standpoint of output and performance. Is this where you are headed, or do you know yet?

I do have some belief that I can still solve the problem as written, but I'm questioning whether it is what you actually want from your data. I mean, you do have the ability to drag the 'how long has this been WIP', and 'how long has this been PENDING' from the history table for the relevant remedy form. Last machine I looked at these statuses were stored in H171, each User ID and each timestamp (in unix epoch time). Yours would be stored in a table that begins with H and has the same suffix digits as your help desk form (or whatever form this happens to be).

Maybe we should talk about this aspect a little more.

Oh, one more question; do you have the rights and means to write views or stored procedures on the SQL box that you are reporting from? Maybe we can explore in that direction too.

scott.

 

[roswelltx]

Scott,
I do NOT have write access to SQL only read. Someone that had done this report in the past just took the assigned feild in remedy and calculated that time with when it was opened. That is not even close to acurate since the assigned field ONLY gives the last person that was assigned and may miss several people before this. I DO need to pull these times out in a way to make calculations on them. We used to have views that we could use, but the SQL people stopped backing them up for us, so now all we have is the tables. What I am giving you an example of is not the work log but is the history field that just shows changes in assignees, groups ect. We have a autoassign procedure in place and they are only given 1 ticket at a time, so best we can do is assume if it is in their name it is actually being worked on. The purpose of this field that i am trying to get the data on, is to find the average time worked per person, per ticket. I don't know if this is even going to be possible, and i know this is going to make this report EXTREMELY SLOW. I just can't think of a better way to do this. The assignee's that you see that says Client is like a universal wait pool, and I will have to get the average a ticket is in that pool also. This will be pulling records for a time frame probably month at a time, so it will be haveing to parse through history fields of anywahere between 1500 and 2000 records. I am trying to answer all your ???'s and hopefully I haven't missed something, please forgive me if I have.

Thanks,
Regina

 

[GJParker]

Regina

You can use the formula below to parse out the lines of data you are interested in .

//@Assignees
Dim i as Number
Dim n as Number
Dim strNew as String
Dim strTemp as String
Dim strTemp2 as String
Dim strTemp3 as String

strTemp = Right({Table1.Text},(Len({Table1.Text}) - instr({Table1.Text},"Person")+1))
Do While Instr(strTemp,"Person") <> 0
        strTemp2 = ""
        n = 1
        Do Until Right(Mid(strTemp,1,n),2) In Array ("AM","PM")
            strTemp2 = strTemp2 + Mid(strTemp,1 + n-1 ,1)
            n = n + 1
        Loop
            strNew = strNew & chr(13) & strTemp2 & "M"
        i = i + n
        strTemp3 = Right(strTemp,(Len(strTemp)-Len(strTemp2)-1))
        strTemp = Right(strTemp3,(Len(strTemp3) - instr(strTemp3,"Person")+1))
Loop

Formula = strNew

This will return something like

Person Assigned changed from '' to 'ssgardne' by ssgardne at 6/30/2004 7:51:20 AMPerson Assigned changed from 'ssgardne' to '' by ssgardne at 6/30/2004 12:04:26 PMPerson Assigned changed from '' to 'ssgardne' by ssgardne at 6/30/2004 12:04:45 PMPerson Assigned changed from 'ssgardne' to '' by ssgardne at 6/30/2004 12:38:02 PMPerson Assigned changed from '' to 'client'  by mkoonce at 7/23/2004 8:33:23 AMPerson Assigned changed from 'client' to '' by kwlewis at 7/23/2004 10:40:07 AMPerson Assigned changed from '' to' 'client' by fside1 at '7/23/2004 4:48:37 PMPerson Assigned changed from 'client' to '' by AR_ESCALATOR at 7/26/2004 7:14:45 AMPerson Assigned changed from '' to 'jcgoode' by jcgoode at 7/28/2004 10:25:59 AMPerson Assigned changed from 'jcgoode' to  'client' by jcgoode at 7/28/2004 10:28:26 AMPerson Assigned changed from 'client' to '' by AR_ESCALATOR at 7/28/2004 10:31:08 AMPerson Assigned changed from '' to 'mkoonce ' by mkoonce at 7/29/2004 11:17:24 AM

Although this will reduce the amount of data you have to analyse, it would be very difficult if not impossible to extract the data required in a format which would allow you to use the returned values in calculations.

HTH

Gary Parker
MIS Data Analyst
Manchester, England

 

[GJParker]

If we can find a way of extrating the data for calculations, using the example above how would you display the output ?



Gary Parker
MIS Data Analyst
Manchester, England

 

[roswelltx]

Would it be possible to use the first code Scott provided for splitting but do a step before that that splits it up per line first then puts each line into a separate array, then when you start to split, instead of pasing through the entire history file, you parse through each line that is in an array? Because I really need to have the data for each line, maybe not for this particular report but for future ones too. In order to calculate other things like the amount of time set in client and unassigned and all. I really thing if I can get this to parse out each of the things mentioned above for each line, i can convert them into calcuble formula.
Regina

 

[GJParker]

Regina

populating arrays with the data isn't a problem and should be quiet easy to do. The problem arises with how you the create a formula to use elements of these arrays.

Remember crystal arrays only hold 1 level of elements with no keys so you would need to build seperate arrays for the user names and date times. i.e.

UserArray DateTimeArray
ssgardne[1] 6/30/2004 7:51:20 AM [1]
ssgardne[2] 6/30/2004 12:04:45 PM[2]

Also how would you know which array elements you need to calculate the time differences between ?

HTH






Gary Parker
MIS Data Analyst
Manchester, England

 

[smcnulty2000]

Regina, I haven't dropped off thinking about this. You got through the questions fine. After reading this and trying some stuff I do have some ideas. But I don't have a solution quite yet.

Gary's question is pretty pointed- let's say you have an array of names and timestamps, what would you want to do with it?

What I'm visualizing based on what you said is a report that shows a ticket as if it were a group, then it would show each person and how much total time they spent on the ticket.

TICKET NUMBER HD0000001725
---jcgoode 20 hours
---mkoonce 144 hours
---kwlewis 57 hours

But if you are looking for an average per person per ticket;

TICKET NUMBER HD0000001725
jcgood: average 15 hours 4 instances
mkoonce: average 12 hours 5 instances
kwlewis: average 15 hours 6 instances


The second is going to require a different approach than the first of these.

It's too bad you can't just get a Remedy programmer in to make the application track this more properly. I've seen some shops do that. But that doesn't help with old data.

I appreciate how frustrating this must be for you. But Remedy reporting is pretty much like what you are dealing with all the time.

Scott.

 

[roswelltx]

Scott,
If I could get this output:

TICKET NUMBER HD0000001725
---jcgoode 20 hours
---mkoonce 144 hours
---kwlewis 57 hours

That would work, then I could add up all the hours across the varies tickets and get an average of the amount of time worked for the month. Unfortunetly the people who run our remedy SQL DB are not DBA's and don't seem to have much of a clue on how to help me. I am new to it all, so I lack the expierence needed for these in-depth formulas like this one. According to your previous post can you figure out to get the aboved output. Or is it not possible?
Thanks,
Regina

 

[smcnulty2000]

Okay, this is really obnoxious. I will put a commentary after it.


// Note: throughout this each part of a record, like a line, will be referred to as a 'subrecord'
// additionally parts of a subrecord are referred to as 'elements'.
// There should be a 0; at the end of each step for benefit of code readability. It isn't really part of the working code.
//Part 1; Setting up the variables
stringvar array strarrWorking1st;
stringvar array strarrWorking2nd;
local stringvar array strarrLoopArray1;
local stringvar array strarr01Event;
local stringvar array strarr02From;
local stringvar array strarr03To;
local stringvar array strarr04By;
local datetimevar array dtarr05When;
local numbervar array numarr06Howlong;
stringvar array strarrNames;
numbervar array numarrHours;
numbervar numNames;
local stringvar strWorking;
local numbervar i;
local numbervar j;
local numbervar numUpperLimit;
local numbervar numUpperLimit2;
local numbervar numCounter2:=1;
strWorking:={@data};
redim strarrLoopArray1[5]; //this sets us to five elements,
0;

//Part 2; separating the lines out.
strWorking:=Replace(strWorking, " PM ", " PM###");
strWorking:=Replace(strWorking, " AM ", " AM###");
strWorking:=Replace(strWorking, "'", " ");
strarrWorking1st:=Split(strWorking,"###",1000); //this puts each line into individual array slots

numUpperLimit:=Ubound(strarrWorking1st); //This finds the size of the array

redim strarrWorking2nd[numUpperLimit]; //This makes the second array a little smaller and clears it out.

For i := 1 To numUpperLimit Do //This starts a loop, looking through the array.
(
if instr(strarrWorking1st,"Person Assigned changed")>0 or i=numUpperLimit then //Check for the right record type
( //We will want the last element in the whole log to go in as well.
strarrWorking2nd[numCounter2]:=strarrWorking1st; //If the right record type, copy into 2nd array
numCounter2:=numCounter2+1; //increment the counter on the second array.
)

); //end loop

//At this point the second array should contain only the 'lines' we want from the original
0;

//Part 3; Making each line go into component parts, and put them into arrays.
//Next we need to separate the events into their component parts. And keep them there.
// This requires that each line be looked at, chunked into a mini array of five elements and then
// those five elements need to go into their five arrays.

redim preserve strarrWorking2nd[numCounter2-1]; //Shrings the array again, without cleaning it out.
local numbervar numUpperLimit2:=Ubound(strarrWorking2nd);
redim strarr01Event[numUpperLimit2];
redim strarr02From[numUpperLimit2];
redim strarr03To[numUpperLimit2];
redim strarr04By[numUpperLimit2];
redim dtarr05When[numUpperLimit2];
strWorking:="";

//for each element then we replace ' from ', ' to ', ' by ' and ' at '
For i := 1 To numUpperLimit2 Do (
strWorking:=strarrWorking2nd;
if i=numUpperLimit2 then //Checks to see if we are at the last element, which won't match the others in type.
(
strWorking:=Replace(strWorking, " at ", "###");
strarrLoopArray1:=split(strWorking,"###"); //breaks it into two elements if last record.
dtarr05When=cdatetime(strarrLoopArray1[2]); //loads only the last array, the date field.
strarr01Event="LAST RECORD";
); //ends last record handling.

if i<>numUpperLimit2 then //Normal subrecords follow.
(
strWorking:=Replace(strWorking, " from ", "###");
strWorking:=Replace(strWorking, " to ", "###");
strWorking:=Replace(strWorking, " by ", "###");
strWorking:=Replace(strWorking, " at ", "###");
strarrLoopArray1:=split(strWorking,"###"); //breaks it into five elements if normal subrecord
//loads each element into five arrays.
strarr01Event:=strarrLoopArray1[1];
strarr02From:=strarrLoopArray1[2];
strarr03To:=strarrLoopArray1[3];
strarr04By:=strarrLoopArray1[4];
dtarr05When:=cdatetime(strarrLoopArray1[5]);
); //ends the normal subrecord handling.


); //ends the loop
0;


//Part 4; Get the time/date difference.
// Next we can determine how long it is from one event to the next.
// We know what a given time is and where it is in the array, we know what the next element is.
// So we do a datediff on them. We have to know when the ticket ends, of course, to get the last element to come out properly.
// This gets stored in yet another arrray. Each element in this array corresponds to the
// element number we are using for each of the above arrays. All of them are in lock-step together.
redim numarr06Howlong[numUpperLimit2];
For i := 1 To numUpperLimit2-1 Do //Don't need the last element.
(
numarr06Howlong:=datediff("h",dtarr05When,dtarr05When[i+1]); //gets the datediff in hours between element and next element.
);
0;

//Part 5. Totals per person.
//Next we need to total these values up, but we have to do it per person.
// In order to do this we have two more arrays, one for each name, and one for the current total.

numNames:=distinctcount(strarr03To)+1; //This figures out how many elements we need. We need an extra for blanks.
redim strarrNames[numNames];
redim numarrHours[numNames];
numCounter2:=1;

For i:= 1 to numUpperLimit2 Do //This puts unique names into the array.
(
if strarr02From<>"" and strarr02From <> strarrNames //This checks the whole array for presence of the name.
then (strarrNames[numCounter2]:=strarr02From;
numCounter2:=numCounter2+1);
);

//For each subrecord we will find the appropriate bin and put it in.

For i := 1 To numUpperLimit2 Do
(
For j := 1 to numNames Do
(
if strarrNames[j]=strarr02From then //if the names are the same, dump it in the bin
(numarrHours[j]:=numarrHours[j]+numarr06Howlong);
)
);
0;


and to display


local numbervar j;
numbervar numNames;
stringvar array strarrNames;
numbervar array numarrHours;
local stringvar strDisplay:="";

For j := 1 to numNames Do
(
if strarrNames[j]<>"" then strDisplay:=strDisplay+strarrNames[j] +" "+totext(numarrHours[j])+chrw(13);
);
strDisplay;

 

[smcnulty2000]

I think that was about 11 arrays, all told.

You are going to have to go through this and understand it pretty well to use it in your reporting. But this does seem to work.

What it does is it chops the whole diary field into lines. It checks each line to see if it wants to use it and either loads it into another array or discards it. Then it takes the keepers, and it minces those further into five pieces, of which probably you'll only need two for this report (but I put them all in in case you need them later). Those five pieces each go into separate arrays. They are all treated as a single array by the remaining processes though.

Then each line's date-time is compared to the next line's date-time to see what the difference is in hours. These are raw hours and don't disinclude weekends or evenings because that would complicate things needlessly at this point. Most bosses are okay with raw hours because it represents a customer's perception of how long things have lasted (and every worker is on the same standard as every other worker, making it an apples to apples compare).

Okay, that leaves you with a sixth element being the hours. The last part builds two new arrays, one to contain distinct names in the record, the other to hold hours as they accumulate through the diary field. This adds the hours to the current hours but does it on a per-person basis.

The display formula is just there to show the final record. You'll note that some of the lines in the names/hours array(s) are blank, and this is because although the distinct count tried to guess at how many it would need, it didn't find as many distinct names as it gets from a 'distinctcount'.

So, the obvious question you'll come across next is how to do an average per month or day or some such. This may take something I don't like but I'm not sure how else to do it at this instant (big and complex problems are brain frying). And that is, you may use the last section of the main mega-formula to load the elements of the names and hours arrays into global variables which you can then use to report on. The part I don't like there is that you might have to hard-code some names.

That's all for now. If it breaks, let me know how it broke. It worked on my desk top but I don't have a full setup so I couldn't test it fully.

Good luck with it.

Scott.

 

[roswelltx]

Scott,
Thank you so much, I will be working heavily on this Monday and I will let you know what happens. I appreciate all the work you put into this.
Thanks,
Regina