| Author |
Message |
Pushrod
Newbie
Joined: 20 Sep 2010
Online Status: Offline
Posts: 13
|
  
Topic: Select records based on field value
Posted: 20 Sep 2010 at 8:56am |
I have a report that selects all of a certain type of records. Now I want to limit that selection to only records that do not have a certain value in one of the fields of a table.
So the ACTIVITY TYPE field has many values. If the value is 15.00 I do not want to show that record. I am using a Formula Field in the details section to show all the values and GH1 has the document name and number.
|
 IP Logged |
|
|
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 20 Sep 2010 at 9:01am |
|
NOT (table.activityType=15)
|
| IP Logged |
|
Pushrod
Newbie
Joined: 20 Sep 2010
Online Status: Offline
Posts: 13
|
Posted: 20 Sep 2010 at 9:07am |
|
I tried to put this in the record selection area but it did not allow the syntax and says a boolean is required.
|
| IP Logged |
|
Pushrod
Newbie
Joined: 20 Sep 2010
Online Status: Offline
Posts: 13
|
Posted: 20 Sep 2010 at 9:11am |
It did allow:
{ACTIVITYLOG.ACTIVITY_TYPE} <> 15.00
but all that does is remove the type 15.00 from the list in the details section, it still shows the document name and number.
|
| IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 20 Sep 2010 at 9:20am |
|
so you need to remove multiple rows based on the condition that if any of the rows under that 'document' have the value of 15 in the {ACTIVITYLOG.ACTIVITY_TYPE} field?
|
| IP Logged |
|
Pushrod
Newbie
Joined: 20 Sep 2010
Online Status: Offline
Posts: 13
|
Posted: 20 Sep 2010 at 9:57am |
Each document has a set of activitytype values (15 is equal to Accessed the document) - these are history metadata. I want my report to show only the documents that were not accessed.
|
| IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 20 Sep 2010 at 10:25am |
If you cannot do this outiside of using something like a SQL view...
group on the document ID
create a formula to flag your 'accessed record' called 'Accessed'
if {ACTIVITYLOG.ACTIVITY_TYPE}=15 then 1
Insert a Summary as a SUM of this formula at the documentid group footer
in the select expert you can do group selection criteria
Open the select expert
clcik on the formula editor
toggle to the Group Selection
insert your criteria here
|
| IP Logged |
|
Pushrod
Newbie
Joined: 20 Sep 2010
Online Status: Offline
Posts: 13
|
Posted: 20 Sep 2010 at 11:53am |
Wow am I confused now.
I followed the steps but no luck.
Each document number (what I used as documentid from your instructions) comes up - but the total under each is 0 (result of ( {@Accessed},PROFILE.DOCNUMBER).
|
| IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 21 Sep 2010 at 3:53am |
You have to have the word SUM in the formula as you are summing the 0 and 1's you created...
place this in the group footer to see the totals for each document
anything that has a 15 in the rows should have a sum>0
so your selection of
should only find groupings with no 15 in it.
Does that help?
|
| IP Logged |
|
Pushrod
Newbie
Joined: 20 Sep 2010
Online Status: Offline
Posts: 13
|
Posted: 21 Sep 2010 at 5:44am |
Thanks for your help. This does seem to work - even though it is hard to get my head around.
One thing I am still having a problem with: when I try to get a count of the results to find out how many documents that were not accessed, I get the total documents.
I did a distinct count of PROFILE.DOCNUM.
|
| IP Logged |
|
|
|
