Newest Record 3

Pandal · Oct 6, 2003

I'm trying to create a report to show newest date of sidewalk inspections.If I group by date descending this will show the data I need. My problem is I have other fields I need the report to sort by first . For example rating summary and unit id
when I group these first they are sorted first and all the inspections now show
I was hoping there might be a way of creating a formula etc and making the date field = the formula
any help will be appreciated
thanks

Pandal · Oct 9, 2003

Jim
thanks for the quick response
after a few quick meetings I'm going to give it a go
we currently have 6500 unitids
With over 15000 Inspection/ratings

Will keep you posted

cheers
Rod

Ngolem · Oct 9, 2003

ok then you would modify the formulas as follows:

//@Initialize (placed suppressed in the Report Header)

WhilePrintingRecords;
//Create 1000 values of "" in this array.
//it seems tedious but with copy and paste it is relatively easy
StringVar Array UsedIDS1 := ["","","",...,"","",""]
StringVar Array UsedIDS2 := ["","","",...,"","",""]
StringVar Array UsedIDS3 := ["","","",...,"","",""]
StringVar Array UsedIDS4 := ["","","",...,"","",""]
StringVar Array UsedIDS5 := ["","","",...,"","",""]
StringVar Array UsedIDS6 := ["","","",...,"","",""]
StringVar Array UsedIDS7 := ["","","",...,"","",""]
StringVar Array UsedIDS8 := ["","","",...,"","",""]
StringVar Array UsedIDS9 := ["","","",...,"","",""]
StringVar Array UsedIDS10 := ["","","",...,"","",""]
NumberVar Pointer := 0;

We increase the number of arrays to 10...ie capable of handling 10,000 unitID's.....roughly 100% more than currently required...Note I forgot the [] in the array definitions before.

now in the Section Expert for conditional suppress in the section for UnitID Group header place the following formula

WhilePrintingRecords;
StringVar Array UsedIDS1;
StringVar Array UsedIDS2;
StringVar Array UsedIDS3;
StringVar Array UsedIDS4;
StringVar Array UsedIDS5;
StringVar Array UsedIDS6;
StringVar Array UsedIDS7;
StringVar Array UsedIDS8;
StringVar Array UsedIDS9;
StringVar Array UsedIDS10;
NumberVar Pointer ;
NumberVar Temp;
BooleanVar Result;

if {Table.UnitID} in UsedIDS1 or
{Table.UnitID} in UsedIDS2 or
{Table.UnitID} in UsedIDS3 or
{Table.UnitID} in UsedIDS4 or
{Table.UnitID} in UsedIDS5 or
{Table.UnitID} in UsedIDS6 or
{Table.UnitID} in UsedIDS7 or
{Table.UnitID} in UsedIDS8 or
{Table.UnitID} in UsedIDS9 or
{Table.UnitID} in UsedIDS10 then
Result := True
else
(
Pointer := Pointer + 1;
if Pointer <= 1000 then
(UsedIDS1[Pointer] := {Table.UnitID}
else if Pointer <= 2000 then
(
Temp := Pointer - 1000;
(UsedIDS2[Temp] := {Table.UnitID};
)
else if Pointer <= 3000 then
(
Temp := Pointer - 2000;
(UsedIDS3[Temp] := {Table.UnitID};
)
else if Pointer <= 4000 then
(
Temp := Pointer - 3000;
(UsedIDS4[Temp] := {Table.UnitID};
)
else if Pointer <= 5000 then
(
Temp := Pointer - 4000;
(UsedIDS5[Temp] := {Table.UnitID};
)
else if Pointer <= 6000 then
(
Temp := Pointer - 5000;
(UsedIDS6[Temp] := {Table.UnitID};
)
else if Pointer <= 7000 then
(
Temp := Pointer - 6000;
(UsedIDS7[Temp] := {Table.UnitID};
)
else if Pointer <= 8000 then
(
Temp := Pointer - 7000;
(UsedIDS8[Temp] := {Table.UnitID};
)
else if Pointer <= 9000 then
(
Temp := Pointer - 8000;
(UsedIDS9[Temp] := {Table.UnitID};
)
else if Pointer <= 10000 then
(
Temp := Pointer - 9000;
(UsedIDS10[Temp] := {Table.UnitID};
);

Result := False;
);
Result;

NOTE: I found a bug and this next formula is much simpler anyways....since the formula above establishes whether or not the reset should be suppressed this is the formula you should use in subsequent sections that may or may not be suppressed

WhilePrintingRecords;

BooleanVar Result;
Result;

This should work now for your situation

Jim Broadbent

Pandal · Oct 9, 2003

HI jIM
ERROR MESSAGE
"SUBSCRIPT MUST BE BETWEEN 1 AND THE SIZE OF THE ARRAY
I tried to to a test with 10 records
unitids -RW000001 THRU RW000010

this is what I put in the untid group conditional suppress

WhilePrintingRecords;
StringVar Array UsedIDS1;

NumberVar Pointer ;
NumberVar Temp;
BooleanVar Result;

if {COMPSW.UnitID} in UsedIDS1
then
Result := True
else
(
Pointer := Pointer + 1;
if Pointer <= 10 then
(UsedIDS1[Pointer] := {COMPSW.UnitID}

);

Result := False;
);
Result;

I maually added the first 10 unitids in the formula @initialize

WhilePrintingRecords;
//Create 10 values of "" in this array.
//it seems tedious but with copy and paste it is relatively easy
StringVar Array UsedIDS1 := "RW000001";"RW000002";"RW000003";"RW000004";"RW000005";"RW000006";"RW000007";"RW000008";"RW000009";"RW000010";
NumberVar Pointer := 0;

I also added in the other 2 groups conditional suppress

WhilePrintingRecords;

BooleanVar Result;
Result;

thanks again
Rod

Ngolem · Oct 9, 2003

WhilePrintingRecords;
//Create 10 values of "" in this array.
//it seems tedious but with copy and paste it is relatively easy
StringVar Array UsedIDS1 := ["RW000001";"RW000002";"RW000003";"RW000004";"RW000005";"RW000006";"RW000007";"RW000008";"RW000009";"RW000010"];
NumberVar Pointer := 0;

You must surround the assignment of values with Square brackets....I am not sure why you filled these with values....they should be filled with nulls...ie. ""

Also...there should be a semicolon here after {COMPSW.UnitID} in the first formula

(
Pointer := Pointer + 1;
if Pointer <= 10 then
(UsedIDS1[Pointer] := {COMPSW.UnitID};

Jim Broadbent

Pandal · Oct 10, 2003

HI jIM
Thanks again for your time and Patience
I still get the same error message when I refresh the report
"SUBSCRIPT MUST BE BETWEEN 1 AND THE SIZE OF THE ARRAY "
If I remove the suppression for unitid the report runs,it
only shows up after I add the formula in the unitid group suppress. Crystal says no errors found after I have added both formulas . Just on refresh

Here's what Ive Done so far
@Initialize
WhilePrintingRecords;
//Create 1000 values of "" in this array.
//it seems tedious but with copy and paste it is relatively easy
StringVar Array UsedIDS1 := ["","...",""];
StringVar Array UsedIDS2 := ["","...",""];
StringVar Array UsedIDS3 := ["","...",""];
StringVar Array UsedIDS4 := ["","...",""];
StringVar Array UsedIDS5 := ["","...",""];
StringVar Array UsedIDS6 := ["","...",""];
StringVar Array UsedIDS7 := ["","...",""];
StringVar Array UsedIDS8 := ["","...",""];
StringVar Array UsedIDS9 := ["","...",""];
StringVar Array UsedIDS10 := ["","...",""];
NumberVar Pointer := 0;

THIS WAS ACCEPTED AS NO ERRORS FOUND

WhilePrintingRecords;
StringVar Array UsedIDS1;
StringVar Array UsedIDS2;
StringVar Array UsedIDS3;
StringVar Array UsedIDS4;
StringVar Array UsedIDS5;
StringVar Array UsedIDS6;
StringVar Array UsedIDS7;
StringVar Array UsedIDS8;
StringVar Array UsedIDS9;
StringVar Array UsedIDS10;
NumberVar Pointer ;
NumberVar Temp;
BooleanVar Result;

if {COMPSW.UNITID} in UsedIDS1 or
{COMPSW.UNITID} in UsedIDS2 or
{COMPSW.UNITID} in UsedIDS3 or
{COMPSW.UNITID} in UsedIDS4 or
{COMPSW.UNITID} in UsedIDS5 or
{COMPSW.UNITID} in UsedIDS6 or
{COMPSW.UNITID} in UsedIDS7 or
{COMPSW.UNITID} in UsedIDS8 or
{COMPSW.UNITID} in UsedIDS9 or
{COMPSW.UNITID} in UsedIDS10 then
Result := True
else
(
Pointer := Pointer + 1;
if Pointer <= 1000 then
(UsedIDS1[Pointer] := {COMPSW.UNITID};
)
else if Pointer <= 2000 then
(
Temp := Pointer - 1000;
(UsedIDS2[Temp] := {COMPSW.UNITID}

)
else if Pointer <= 3000 then
(
Temp := Pointer - 2000;
(UsedIDS3[Temp] := {COMPSW.UNITID}

)
else if Pointer <= 4000 then
(
Temp := Pointer - 3000;
(UsedIDS4[Temp] := {COMPSW.UNITID}

)
else if Pointer <= 5000 then
(
Temp := Pointer - 4000;
(UsedIDS5[Temp] := {COMPSW.UNITID}

)
else if Pointer <= 6000 then
(
Temp := Pointer - 5000;
(UsedIDS6[Temp] := {COMPSW.UNITID}

)
else if Pointer <= 7000 then
(
Temp := Pointer - 6000;
(UsedIDS7[Temp] := {COMPSW.UNITID}

)
else if Pointer <= 8000 then
(
Temp := Pointer - 7000;
(UsedIDS8[Temp] := {COMPSW.UNITID}

)
else if Pointer <= 9000 then
(
Temp := Pointer - 8000;
(UsedIDS9[Temp] := {COMPSW.UNITID}

)
else if Pointer <= 10000 then
(
Temp := Pointer - 9000;
(UsedIDS10[Temp] := {COMPSW.UNITID}

);

Result := False;
);
Result;

THIS WAS ACCEPTED AS NO ERRORS FOUND
thanks again
Rod

Ngolem · Oct 10, 2003

Here's what Ive Done so far
@Initialize
WhilePrintingRecords;
//Create 1000 values of "" in this array.
//it seems tedious but with copy and paste it is relatively easy
StringVar Array UsedIDS1 := "","...","";
StringVar Array UsedIDS2 := "","...","";
StringVar Array UsedIDS3 := "","...","";
StringVar Array UsedIDS4 := "","...","";
StringVar Array UsedIDS5 := "","...","";
StringVar Array UsedIDS6 := "","...","";
StringVar Array UsedIDS7 := "","...","";
StringVar Array UsedIDS8 := "","...","";
StringVar Array UsedIDS9 := "","...","";
StringVar Array UsedIDS10 := "","...","";
NumberVar Pointer := 0;

THIS WAS ACCEPTED AS NO ERRORS FOUND

This formula is NOT correct...though Crystal may have accepted it.

First (I have said this twice now) Array definitions require square brackets.

StringVar Array UsedIDS1 := ["","...",""];

Second this notation - "..." - is not a valid form in Crystal...it is just a short form for saying that many many more values are inserted here.

Here is how the statement should look for one of these arrays if I initialize it for 100 elements (you must do it for 1000 elements)

StringVar Array UsedIDS1 := ["","","","","","","","","","",
"","","","","","","","","","",
"","","","","","","","","","",
"","","","","","","","","","",
"","","","","","","","","","",
"","","","","","","","","","",
"","","","","","","","","","",
"","","","","","","","","","",
"","","","","","","","","","",
"","","","","","","","","",""];

You also have some useless (though non-fatal) brackets in the second formula

else if Pointer <= 4000 then
(
Temp := Pointer - 3000;
UsedIDS4[Temp] := {COMPSW.UNITID};
NOT
(UsedIDS4[Temp] := {COMPSW.UNITID}

)
else if Pointer <= 5000 then

You do this many times in the formula

Also
this first part ....while it isn't wrong...is hard to read at first glance...it is better written as follows

Pointer := Pointer + 1;
if Pointer <= 1000 then
(
UsedIDS1[Pointer] := {COMPSW.UNITID};
)

Hope this helps

Jim Broadbent

Ngolem · Oct 10, 2003

sorry....this seems to be a Tek-tip problem as far as printing square brackets

StringVar Array UsedIDS1 := [ "","","","","","","","","","",
"","","","","","","","","","",
"","","","","","","","","","",
"","","","","","","","","","",
"","","","","","","","","","",
"","","","","","","","","","",
"","","","","","","","","","",
"","","","","","","","","","",
"","","","","","","","","","",
"","","","","","","","","","" ];

I put a space between the bracket and it seems to show now

Jim Broadbent

Pandal · Oct 10, 2003

ok thanks Jim
I had square brackets in also they just didnt show
I think I grasp what your doing now with the last example
<<this newbie is learning a lot from this and I really appreciate the time and great patience you've shown

cheers (tgif)
Rod

Ngolem · Oct 10, 2003

good...let me know when it works...it should....

this is quite complicated for a newbie....but that is how we all learned here....Crystal Decisions isn't a whole lot creative sometimes.

Jim Broadbent

Pandal · Oct 10, 2003

Jim
Getting closer to the end now
Report took all the changes and generated
problem is it still shows all the inspection/ratings

didn't take too long to create the ten string vars

heres how I grouped as per your intructions

Group 1 header - {table.Rating} descending (enabled with data)
Group 2 Header - {table.unitId) descending (enabled with data)
Group 3 header - {Table.date} descending (enabled showing the date and important information)
Details - suppressed
Group 3 footer - suppressed (if enabled you would show the oldest date data)
Group 2 footer - suppressed
Group 1 footer - suppressed

so close..
have a great weekend
happy thanksgiving if your from Canada
Rod

Ngolem · Oct 10, 2003

well....having only 10 elements/array is very limited considering all the UnitID's you have to deal with.

Now in the @initialize increase the array sizes to 1000 each for a real test.

Sounds hard to do but just create a line of 100 values of "" each separated by commas and clone them to make 10 lines of 100 each then once it is done for one Array the copy the whole array and paste it changing the array name, then do this again til all arrays are 1000 elements

not hard...

StringVar Array UsedIDS1 := [ "","","",..94 more..,"","","",
"","","",.. 94 more..,"","","",
"","","",.. 94 more..,"","","",
"","","",.. 94 more..,"","","",
"","","",.. 94 more..,"","","",
"","","",.. 94 more..,"","","",
"","","",.. 94 more..,"","","",
"","","",.. 94 more..,"","","",
"","","",.. 94 more..,"","","",
"","","",.. 94 more..,"","","" ];
then copy/paste the above and change the array name to
UsedIDS2... then repeat for the others.

Yes...I will have some turkey for you

Jim Broadbent

Pandal · Oct 10, 2003

Jim
I also will be eating turkey
I had incresed the array size to 1000 per
10,000 in all
but as I said the report still shows all the inspection/ratings

<<<Is almost at one with crystal

hehe
I'm off in one hour so if your still up for it I'd love to solve this problem on tuesday

have a good one Jim
thanks
Rod

Ngolem · Oct 10, 2003

you probably forgot to update the other formula as well

Pointer := Pointer + 1;
if Pointer <= 1000 then
(UsedIDS1[Pointer] := {Table.UnitID}
else if Pointer <= 2000 then
(
Temp := Pointer - 1000;
(UsedIDS2[Temp] := {Table.UnitID};
)
else if Pointer <= 3000 then
(
Temp := Pointer - 2000;
(UsedIDS3[Temp] := {Table.UnitID};
)
.
.
.

as well as the rest of the formula

Jim Broadbent

Pandal · Oct 14, 2003

Morning Jim
Hope your weekend was a good 1

the formula I used in the Unitid group suppress was :

WhilePrintingRecords;
StringVar Array UsedIDS1;
StringVar Array UsedIDS2;
StringVar Array UsedIDS3;
StringVar Array UsedIDS4;
StringVar Array UsedIDS5;
StringVar Array UsedIDS6;
StringVar Array UsedIDS7;
StringVar Array UsedIDS8;
StringVar Array UsedIDS9;
StringVar Array UsedIDS10;
NumberVar Pointer ;
NumberVar Temp;
BooleanVar Result;

if {COMPSW.UNITID} in UsedIDS1 or
{COMPSW.UNITID} in UsedIDS2 or
{COMPSW.UNITID} in UsedIDS3 or
{COMPSW.UNITID} in UsedIDS4 or
{COMPSW.UNITID} in UsedIDS5 or
{COMPSW.UNITID} in UsedIDS6 or
{COMPSW.UNITID} in UsedIDS7 or
{COMPSW.UNITID} in UsedIDS8 or
{COMPSW.UNITID} in UsedIDS9 or
{COMPSW.UNITID} in UsedIDS10 then
Result := True
else
(
Pointer := Pointer + 1;
if Pointer <= 1000 then
(
UsedIDS1[Pointer] := {COMPSW.UNITID};
)
else if Pointer <= 2000 then
(
Temp := Pointer - 1000;
UsedIDS2[Temp] := {COMPSW.UNITID};
)
else if Pointer <= 3000 then
(
Temp := Pointer - 2000;
UsedIDS3[Temp] := {COMPSW.UNITID};
)
else if Pointer <= 4000 then
(
Temp := Pointer - 3000;
UsedIDS4[Temp] := {COMPSW.UNITID};
)
else if Pointer <= 5000 then
(
Temp := Pointer - 4000;
UsedIDS5[Temp] := {COMPSW.UNITID};
)
else if Pointer <= 6000 then
(
Temp := Pointer - 5000;
UsedIDS6[Temp] := {COMPSW.UNITID};
)
else if Pointer <= 7000 then
(
Temp := Pointer - 6000;
UsedIDS7[Temp] := {COMPSW.UNITID};
)
else if Pointer <= 8000 then
(
Temp := Pointer - 7000;
UsedIDS8[Temp] := {COMPSW.UNITID};
)
else if Pointer <= 9000 then
(
Temp := Pointer - 8000;
UsedIDS9[Temp] := {COMPSW.UNITID};
)
else if Pointer <= 10000 then
(
Temp := Pointer - 9000;
UsedIDS10[Temp] := {COMPSW.UNITID};
);

Result := False;
);
Result;

The Formula @Initialize (which I put in the report Header)
was 10 groups of 1000 as you described to me

I put this in the other 2 group supress's :

WhilePrintingRecords;

BooleanVar Result;
Result;

The report has 3 groups :
1= Rating descending
2= Unitid descending
3= Date descending
Thanks again for your time and if you're like me your all turkey'd out
Rod

Ngolem · Oct 14, 2003

I take it from this message that everything works ok....good

Jim Broadbent

Pandal · Oct 14, 2003

OOPs
Jim
actually all the ratings still show.
What we did doesn't seem to filter out the old rating/inspections

Ngolem · Oct 14, 2003

layout where you have placed the suppress formulas

show me exactly which sections of the report are permanently suppressed, which are conditionally suppressed and which formula is used to conditionally suppress them....just refer to the suppress formulas as "long suppress" and "short suppress"

Jim Broadbent

Pandal · Oct 14, 2003

Group 1 header - {table.Rating} descending (enabled with data)
Group 2 Header - {table.unitId) descending (enabled with data)
Group 3 header - {Table.date} descending (enabled showing the date and important information)
Details - suppressed
Group 3 footer - suppressed
Group 2 footer - suppressed
Group 1 footer - suppressed

GROUP 1 AND 3 - IN THE SUPPRESSED - FORMAT FORMULA EDITOR: SELECTION VISIBILTY - I put

WhilePrintingRecords;

BooleanVar Result;
Result;

gROUP 2 (unitid) In the Suppressed -Format Formula Editor: Selection Visibility - I put

WhilePrintingRecords;
StringVar Array UsedIDS1;
StringVar Array UsedIDS2;
StringVar Array UsedIDS3;
StringVar Array UsedIDS4;
StringVar Array UsedIDS5;
StringVar Array UsedIDS6;
StringVar Array UsedIDS7;
StringVar Array UsedIDS8;
StringVar Array UsedIDS9;
StringVar Array UsedIDS10;
NumberVar Pointer ;
NumberVar Temp;
BooleanVar Result;

if {COMPSW.UNITID} in UsedIDS1 or
{COMPSW.UNITID} in UsedIDS2 or
{COMPSW.UNITID} in UsedIDS3 or
{COMPSW.UNITID} in UsedIDS4 or
{COMPSW.UNITID} in UsedIDS5 or
{COMPSW.UNITID} in UsedIDS6 or
{COMPSW.UNITID} in UsedIDS7 or
{COMPSW.UNITID} in UsedIDS8 or
{COMPSW.UNITID} in UsedIDS9 or
{COMPSW.UNITID} in UsedIDS10 then
Result := True
else
(
Pointer := Pointer + 1;
if Pointer <= 1000 then
(
UsedIDS1[Pointer] := {COMPSW.UNITID};
)
else if Pointer <= 2000 then
(
Temp := Pointer - 1000;
UsedIDS2[Temp] := {COMPSW.UNITID};
)
else if Pointer <= 3000 then
(
Temp := Pointer - 2000;
UsedIDS3[Temp] := {COMPSW.UNITID};
)
else if Pointer <= 4000 then
(
Temp := Pointer - 3000;
UsedIDS4[Temp] := {COMPSW.UNITID};
)
else if Pointer <= 5000 then
(
Temp := Pointer - 4000;
UsedIDS5[Temp] := {COMPSW.UNITID};
)
else if Pointer <= 6000 then
(
Temp := Pointer - 5000;
UsedIDS6[Temp] := {COMPSW.UNITID};
)
else if Pointer <= 7000 then
(
Temp := Pointer - 6000;
UsedIDS7[Temp] := {COMPSW.UNITID};
)
else if Pointer <= 8000 then
(
Temp := Pointer - 7000;
UsedIDS8[Temp] := {COMPSW.UNITID};
)
else if Pointer <= 9000 then
(
Temp := Pointer - 8000;
UsedIDS9[Temp] := {COMPSW.UNITID};
)
else if Pointer <= 10000 then
(
Temp := Pointer - 9000;
UsedIDS10[Temp] := {COMPSW.UNITID};
);

Result := False;
);
Result;

I hope this helps
Rod

Ngolem · Oct 14, 2003

GROUP 1 AND 3 - IN THE SUPPRESSED - FORMAT FORMULA EDITOR: SELECTION VISIBILTY

I have no idea what you are refering to here???
these formulas are placed in the SECTION EXPERT - suppress section....for each section you can conditionally suppress a particular section by placing a formula in the 2-X button

these are not formula fields....they are formulas whose purpose are to suppress or unsuppress sections of the report

A)

DON'T suppress Group 1 header at all

B)

Place the LONG suppress formula (no need to repeat it...the formula is fine) in the Group 2 header "conditional suppress"

C)

Place the SHORT suppress formula in the Group 3 header conditional suppress

it should work then

Jim Broadbent

Pandal · Oct 14, 2003

Jim
I think we were talking about the same place:
section expert when you open the x-2 button to the right
of suppress
this is where ive placed the long and short suppress formulas
In crystal 8 when I open that x-2 button it opens up to an area called Format formula Editor: section_visibility

And I'm afraid I still get all the inspections/ratings

If it helps i can take screen captures and mail them to you
I thank you for your continued support

Rod

Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Newest Record 3

Technical User

Technical User

Programmer

Technical User

Programmer

Technical User

Programmer

Programmer

Technical User

Programmer

Technical User

Programmer

Technical User

Programmer

Technical User

Programmer

Technical User

Programmer

Technical User

Programmer

Technical User

Similar threads

Log in

Part and Inventory Search

Sponsor