Newest Record 3

[Pandal]

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

 

[Ngolem]

did you re-place the formulas as I told you??? They were not placed correctly

Jim Broadbent

 

[Pandal]

Jim
Yes I did
group 1 "Rating" - no suppress at all
group 2 "Unitid" - big formula in the conditional suppress
group 3 "date" - small formula in the conditional suppress

Rod

 

[Pandal]

Morning
to add on to my last statement
details and all the group footers are suppressed no drill down

 

[Ngolem]

Well it should work as you have described it....unless there is something else at work that I am not aware of

put the following forumla in the Group 3 header

//@Debug

WhilePrintingRecords;
BooleanVar Result;
StringVar Array UsedIDS1;
StringVar test;

if Result then
test := "True"
else
test := "False";

test + " First 4 values of Array: " + UsedIDS1[1] + ", " + UsedIDS1[2] + ", " + UsedIDS1[3] + ", " + UsedIDS1[4] ;


When you run the report again look at these values. Each string should be headed by the word "False" and the unitid's in this string should never repeat for other ratings.

tell me what you see....

Jim Broadbent

 

[Pandal]

HI JIM

says false first 4 values of the array: RW001904,RW000462,RW001906,RW001889

WHICH ARE THE FIRST 4 UNITID'S LISTED WHEN THE REPORT BUILDS
ROD

 

[Ngolem]

Ok...that is what is expected...Yes?

Do those UnitID's repreat themselves later???they shouldn't

Jim Broadbent

 

[Pandal]

if there is more than 1 inspection for that unitid they are repeating
there are 4500 unitids so I would like our report to show show the 4500 newest inspection/ratings
right now its shows all the inspections ..
the first four records are not the newest inspection , they are just the worst rated inpection

notice how I used our ....With all the work you've done on it I feel its part yours hehe

I don't know if this is a factor but the three groups in the report are from 3 different fields

1-INLAIR.RATING
2-COMPSW.UNITID
3-INLAI.COMPDTTM

 

[Ngolem]

************************
if there is more than 1 inspection for that unitid they are repeating
there are 4500 unitids so I would like our report to show show the 4500 newest inspection/ratings
right now its shows all the inspections ..
the first four records are not the newest inspection , they are just the worst rated inpection
************************

I see.....You want the report to be ranked by "The Worst Rated" but the last inspection is not necessarily the worst....Hmmmmm...that is not what you said to me earlier when I asked about this...and I quote

My Question:
Is there a case where a unitID was rated badly...then improved its rating....or is each successive inspection revealing a worse/equal rating.

Your response:
Jim
Thanks for your reply
yes, each successive inspection reveals a worse/equal rating.

Now you are telling me something different.

This makes the report infinately more difficult to do. And I don't believe you will be successful doing it with your proposed groupings...perhaps something could be done using subreports....but frankly I have spent enough time being led down a garden path.

The approach I gave you should work as to getting you 4500 unitID's based on worst ratings...why it doesn't I am not sure but it should work....However that is not your reporting problem

I believe I have spent enough time on this ...good luck with your project




Jim Broadbent

 

[Pandal]

Thanks for your patience ..I'm suprised you put up with me
for as long as you did.
I've got a lot of energy so I'll get this report workin or die tryin
regardless of the outcome I did learn a few things
All the best to you

Rod

 

[lbass]

I still think using a subselect within the SQL is the answer. You might try the following. I'm not sure about the quotes around the table names--I added them since your tables have them in your SQL, while my SQL doesn't. I'm not sure whether the fact that you're using a stored procedure is a factor. This works for a test I did, but I'm not an expert at this. This assumes you have a field in INLAI (UNIT ID) that matches the field you are grouping on (COMPSW.UNITID). Add this to the end of your WHERE clause:

AND "INLAI"."COMPDTTM" = (SELECT MAX("INLAI"."COMPDTTM&quot FROM "INLAI" WHERE "INLAI"."UNITID" = "COMPSW"."UNITID&quot

If this still doesn't work, I would recommend reposting with a request for help on writing a subselect (maximum) in SQL in the subject line.

-LB

 

[Pandal]

hi LB
I think we've broken the record for posts on this topic
COMPSW TABLE is where the unitid field is
INLAI TABLE is where the DATE FIELD is, and compsw and inlai are linked thru a common compkey field .There isn't a unitid field in Inlai
INLAIR TABLE is where RATING FIELD is, inlair and inlai are linked thru a common insp.key

these .key fields are the only common link between the tables
I really don't know if this helps
thanks again for your reply
I truly am amazed at the support of tek-tips and I hope to be able to help in the future

thanks again for your post
Rod

 

[lbass]

It's worth one more try, isn't it? Give this a try:

AND "INLAI"."COMPDTTM" = (SELECT MAX("INLAI"."COMPDTTM&quot FROM "INLAI" WHERE "INLAI"."COMPKEY" = "COMPSW"."COMPKEY&quot

Even though I'm not totally confident about how to write this doesn't mean that it's not the correct approach--someone else could probably put on the finishing touches, if you asked.

-LB

 

[Pandal]

lbass
I'm going to nickname my first grandchild (lbass)

that sql statement worked perfectly as you typed it
my report is now showing the latest inspections in the sort order worst rated to best

I think its time to ask my boss for a raise

thankyou all for your effort and support
Rod