HOW TO do Next(Next(field)

[biteside]

What I would like is to know if it is possible to ask not only the next in a cursor, but also the next/next value in a cursor.

Let me explain.
If I have three value in 3 different rows
row 1 contains a
row 2 contains b
row 3 contains a.

If values 1 and 3 are equal I want to show the value of 1, 2 and 3 (being a , b ,c). So if row 1 and 3 are equal I do no supress rows 1, 2 and 3.

I have tried to use Next(Next(fieldname)) but that doesnot work. Is there another possibility to do this?

Thanx in advance...
B.

 

[lbass]

Please provide more information about your report structure. Are these three values within a group? On what field are they sorted? Are there always only three values? You could potentially use something like this:

maximum({table.sortfield},{table.groupfield}) <> minimum({table.sortfield},{table.groupfield})

...as a suppression formula, but that depends on whether your report structure meets the above criteria.

-LB

 

[biteside]

More info. OK here we go. ohw, BTW I use CR 10.

What I have is four fields.

1) a Clientnumber ({TBLKLANT.KLAID})
2) a startdate ({TBLSELECTIE.SEEAFNAMEDATUM})
3) a text field (not in function)
4) a number field ({TBLUITSLAGSELECTIE.SELKLANTUITGESTROOMD})(1 means that something has changed in the DB after insert).

The data is orderded on Clientnumber then the startdate. The date should be between to other dates (in {?Begindatum} to {?Einddatum}).

1 1-1-2005 "Text1" 1
1 1-1-2005 "Text1" 0
1 1-2-2005 "Text2" 0
1 1-3-2005 "Text3" 1
1 1-4-2005 "Text4" 0
1 1-5-2005 "Text5" 0
2 1-5-2005 "Text6" 0
2 1-6-2005 "Text7" 0
2 1-7-2005 "Text8" 1
3 1-8-2005 "Text9" 0

I have no groups.

What I want is the following.

1) Show the row where number is 1 if the next row is same client.
2) show the row after the row where the number is 1 if the client is same client.
3) Show the row before the row where number is 1 if the next client is same client but also !!where te next next client is same client!! (The part between !! is the part missing in my formula.)
(for al three also a date is in is used, but that is not the problem here)

So my result should be:
1 1-1-2005 "Text1" 1 (rule 1)
1 1-1-2005 "Text1" 0 (rule 2)
1 1-2-2005 "Text2" 0 (rule 3)
1 1-3-2005 "Text3" 1 (rule 1)
1 1-4-2005 "Text4" 0 (rule 2)

See below for the complete code I have so far. There is 1 problem with the code and that is in this part:
OR
Next ({TBLKLANT.KLAID}) = {TBLKLANT.KLAID}
AND Next({TBLUITSLAGSELECTIE.SELKLANTUITGESTROOMD}) = 1
AND Next({TBLSELECTIE.SEEAFNAMEDATUM}) in {?Begindatum} to {?Einddatum}

What I need here is a check to see if the next,next record is also of the same client cause if I do not check this I will also get to see the row

2 1-5-2005 "Text7" 0

Any idea's how to do this? (Hopes this clarifies anthing )

Thanx in advance,
B.

PS
I have come up with the following code:
NOT
(
If NOT PreviousIsNull ({TBLKLANT.KLAID}) AND NOT NextIsNull ({TBLKLANT.KLAID}) then
(
(
(
{TBLUITSLAGSELECTIE.SELKLANTUITGESTROOMD}=1
AND Next ({TBLKLANT.KLAID}) = {TBLKLANT.KLAID}
AND {TBLSELECTIE.SEEAFNAMEDATUM} in {?Begindatum} to {?Einddatum}
)
OR
(
Previous ({TBLKLANT.KLAID}) = {TBLKLANT.KLAID}
AND Previous({TBLUITSLAGSELECTIE.SELKLANTUITGESTROOMD}) =1
AND Previous({TBLSELECTIE.SEEAFNAMEDATUM}) in {?Begindatum} to {?Einddatum}
)
OR
Next ({TBLKLANT.KLAID}) = {TBLKLANT.KLAID}
AND Next({TBLUITSLAGSELECTIE.SELKLANTUITGESTROOMD}) = 1
AND Next({TBLSELECTIE.SEEAFNAMEDATUM}) in {?Begindatum} to {?Einddatum}
)
)
else if PreviousIsNull ({TBLKLANT.KLAID}) then
(
Next ({TBLKLANT.KLAID}) = {TBLKLANT.KLAID} AND Next({TBLUITSLAGSELECTIE.SELKLANTUITGESTROOMD}) =1 AND Next({TBLSELECTIE.SEEAFNAMEDATUM}) in {?Begindatum} to {?Einddatum}
)
else if NextIsNull({TBLKLANT.KLAID}) then
(
Previous ({TBLKLANT.KLAID}) = {TBLKLANT.KLAID} AND Previous({TBLUITSLAGSELECTIE.SELKLANTUITGESTROOMD}) =1 AND Previous({TBLSELECTIE.SEEAFNAMEDATUM}) in {?Begindatum} to {?Einddatum}
)
)

 

[lbass]

You've done a great job of showing what the rules are in the abstract, but it might be that there is a different way of defining the rules using other fields--which might allow a simpler solution. I'm guessing that the rules relate to something meaningful, e.g., some status field. It would help if there is such a field and if you could translate your rules using that. In other words, WHY do you want these particular rows to show, and what field is there that relates to that?

-LB

 

[biteside]

What is happening is the following:
I work for a department that tries to help people find a new job. We have different tools to do so (give course, help write letter etc.)
Each time we use a tool a new row is inserted in the database. There is 1 tool that is different from all the other tools. That is the tool 'Has found job'. For this tool the number field = 1, for all the other tools the field = 0.

So the number field ({TBLUITSLAGSELECTIE.SELKLANTUITGESTROOMD}) becomes 1 it means that the client found a job.

Sadly some people don't hold their job and return to us for more help. That means that we enter a new row in the database with a new tool we use.

What we want to see is which tool was used before the person got a job. When did he get the job. And which tool we are using next to try and help the person get a job. It is possible that more tools are used after this, but those we do not need to see. Only the last tool before the job, the job itself and the first tool after the job is needed.

Though job trying to explain what the problem is. So thanx for your patience.

Kind regards,
Bob

 

[lbass]

Try the following suppression formula:

(
previousisnull({TBLKLANT.KLAID}) and
next({TBLKLANT.KLAID}) <> {TBLKLANT.KLAID}
) or
(
nextisnull({TBLKLANT.KLAID}) and
previous({TBLKLANT.KLAID}) <> {TBLKLANT.KLAID}
) or
not(
(
(
(previousisnull({TBLKLANT.KLAID}) and
next ({TBLKLANT.KLAID}) = {TBLKLANT.KLAID})
or
(nextisnull({TBLKLANT.KLAID}) and
previous({TBLKLANT.KLAID}) = {TBLKLANT.KLAID})
) and
{TBLUITSLAGSELECTIE.SELKLANTUITGESTROOMD} = 1
) or
(
(previous({TBLKLANT.KLAID}) = {TBLKLANT.KLAID} or
next({TBLKLANT.KLAID}) = {TBLKLANT.KLAID}) and
{TBLUITSLAGSELECTIE.SELKLANTUITGESTROOMD} = 1
) or
(
next({TBLKLANT.KLAID}) = {TBLKLANT.KLAID} and
next({TBLUITSLAGSELECTIE.SELKLANTUITGESTROOMD}) = 1
) or
(
previous({TBLKLANT.KLAID}) = {TBLKLANT.KLAID} and
previous({TBLUITSLAGSELECTIE.SELKLANTUITGESTROOMD}) = 1
)
)

However, this doesn't really check for next next--that didn't appear to be necessary, but maybe I'm not following exactly.

-LB

 

[biteside]

Hi and thanx for the solution.

I have tried it, but is has the same 'flaw' as my formula. The problem is that I don't want to see the clients which have a job and did not return to us.

That's why I think that on of the checks I need to do on the first supression (or not) if the next-next client is the same as the client I am working with at that moment. Otherwise I will never know if I should or shouldnot surpress.

Any other ideas?

BTW thank you for all the help you give me.

Kind regards,
B...

 

[GJParker]

If you have access to the database one way to solve this problem would be to create a View or Stored Procedure to show the last tool entry per client. This could then be linked in to the report to filter out all clients whos last entry was 1 i.e. all clients who are currently in work.

HTH

Gary Parker
MIS Data Analyst
Manchester, England

 

[lbass]

Hmm. When I tested this I thought it did suppress those with a job but no next activity. I'll test again later today.

-LB

 

[biteside]

GJParker, I'll look into your solution. Although I am nog sure whether I can get the right view for this. But I can always try.

Thanx for the suggestion.

Lbass, I have tried it again (sanity check) but I really see lines where there is no new tool after a 'found a job' tool. I am curious what your findings are.

Greets,
Bob...

 

[lbass]

Try:

(
onfirstrecord and
(
(
{TBLUITSLAGSELECTIE.SELKLANTUITGESTROOMD} = 1 and
next({TBLUITSLAGSELECTIE.SELKLANTUITGESTROOMD}) <> 0
) or
(
{TBLUITSLAGSELECTIE.SELKLANTUITGESTROOMD} = 0 and
next({TBLUITSLAGSELECTIE.SELKLANTUITGESTROOMD}) <> 1
)
)
) or
(
previousisnull({TBLKLANT.KLAID}) and
next({TBLKLANT.KLAID}) <> {TBLKLANT.KLAID}
) or
(
nextisnull({TBLKLANT.KLAID}) and
previous({TBLKLANT.KLAID}) <> {TBLKLANT.KLAID}
) or
(
previous({TBLKLANT.KLAID}) <> {TBLKLANT.KLAID} and
next({TBLKLANT.KLAID}) <> {TBLKLANT.KLAID} and
{TBLUITSLAGSELECTIE.SELKLANTUITGESTROOMD} = 1
) or
(
previous({TBLUITSLAGSELECTIE.SELKLANTUITGESTROOMD}) <> 0 and
{TBLUITSLAGSELECTIE.SELKLANTUITGESTROOMD} = 1 and
(
next({TBLUITSLAGSELECTIE.SELKLANTUITGESTROOMD}) <> 0 or
next({TBLKLANT.KLAID}) <> {TBLKLANT.KLAID}
)
) or

not(
(
(
(previousisnull({TBLKLANT.KLAID}) and
next ({TBLKLANT.KLAID}) = {TBLKLANT.KLAID})
or
(nextisnull({TBLKLANT.KLAID}) and
previous({TBLKLANT.KLAID}) = {TBLKLANT.KLAID})
) and
{TBLUITSLAGSELECTIE.SELKLANTUITGESTROOMD} = 1
) or
(
(previous({TBLKLANT.KLAID}) = {TBLKLANT.KLAID} or
next({TBLKLANT.KLAID}) = {TBLKLANT.KLAID}) and
{TBLUITSLAGSELECTIE.SELKLANTUITGESTROOMD} = 1
) or
(
next({TBLKLANT.KLAID}) = {TBLKLANT.KLAID} and
next({TBLUITSLAGSELECTIE.SELKLANTUITGESTROOMD}) = 1
) or
(
previous({TBLKLANT.KLAID}) = {TBLKLANT.KLAID} and
previous({TBLUITSLAGSELECTIE.SELKLANTUITGESTROOMD}) = 1
)
)

I am also concerned about the dates--if the record does not meet the date range, then the record will still appear even if it does meet other suppression criteria. I think it would be simpler to test this without the date criteria. If possible, build the date criteria into your record selection formula to begin with so there is no need to complicate the formula with the dates.

-LB

 

[biteside]

Hi there,

Thank you for all the suggestions you have made, but believe it or not... Today my boss came in with the message the report is not needed anymore in this form. So I can quit trying to do this...
(Nevertheless, I am still curious how to do the things I wanted, but I won't bother you with it ;-) )

Again thanks for you time and effort...
B....

PS
Bosses *sigh*