Link Fields in same Table 1

[alexlc]

Hi,
I'm using Crytal Reports 9 to query Notes 6.5
All the data I'm querying is in one table - Notes not being relational 'n all. Notes date/time stamps every edit of the form in one field & records the action & user in another. Each entry is finished with a carriage return, so it looks great when you look at the front end database, however it is rubbish for creating reports on productivity.

What I need help on, is if anyone knows of any way of getting these two fields to link, so I can get the action on line 3 to match with the date/time on line 3 (for example)for instance. Is there a way of numbering each line & linking it that way?

Personally I think I am chasing sunbeams here & we have to get our developer to change the function, but as always any help is much appreciated.

Cheers Alex

 

[dgillz]

If all the data is in one table, there should be no links required. Linking is when there is data in 2 or more tables.

If you are looking to do a self-join of tables, you need to add the table to the report again, and give it an alias name, then link the alians table to the original table.

Software Sales, Training, Implementation and Support for Exact Macola, eSynergy, and Crystal Reports
askdon@srhconsulting.com

http://www.srhconsulting.com

 

[lbass]

I think you could break these into different formulas where each formula has a different N value (where N is the number of the element in the array created by splitting the formulas):

if ubound(split({@datetime},chr(13))) > N then
split({@datetime},chr(13))[N] + " "+split({@action},chr(13))[N]

Substitute the field with datetimes for {@datetime} and the field with actions and users for {@action}

-LB

 

[alexlc]

Cheers lbass, that gets me halfway there.
I'm assuming that I need to do a formula for each line?
This is where for my reporting purposes it could get messy, as I don't know how many lines may appear in each document (programmer used RTF!!!!). I also don't know on which line the action I need will appear, as the programme doesn't require a fixed route to get to the end of the job.
Is there anyway I could find the line of my action e.g "selected by" and get that result into your split formula.
I am currently trying various things I have found on this site, but to no avail.
Thanks in advance.
Alex

 

[lbass]

You haven't explained what you are trying to do overall, so it is hard to respond. Yes, you would create separate formulas for each, since you want to treat them as separate "lines". To determine if an action is in one of the new formulas, you could use:

if instr({@splitN3},"Selected by") > 0 then 1//or {table.amt} or whatever

//where {@splitN3} is the earlier formula when N = 3

-LB

 

[alexlc]

Hi,
Sorry!!!
The Datetime field & action fields record when & who has edited the document & performed a measurable function.
So the string "selected by" is part of an action where the named user has placed a member of staff in a shift, and is date time stamped in the date time field. When these fields are placed next to each other in the document - or in details on the Crystal report, it looks lovely, but as I said, as there is no link between the fields you can't report on it.
My manager needs to be able to see which users are doing what when to improve the staffing levels in the office.
This is why I was wondering if there was a way to count the Chr(13), get it to return the line the action is on & then pull the corresponding date time stamp.
However I'll have a look at your above thingy & let you know how I get on.
Thanks.
Alex

 

[alexlc]

Hi,
Got it to work - I managed to extract the data up to the action I required & count the carriage returns to that line, & used that as the line counter in the formula lbass posted.
Thanks again for your help, and all the other postings that got me to the result I needed.
Cheers Alex