Record selection criteria

[GPowers]

I have the following database table and am trying to select records where the like nodeid existed yesterday, but does not exist today. The following is example data in the table. Both fields are text.

NODEID DATE
110011 2003-06-02
110011 2003-06-03
111100 2003-06-02

In this scenario nodeid 111100 does not exist today. I need to report that nodeid 111100 does not exist. I have tried using Grouping, but have not been successful. Any help would be appreciated.

 

[synapsevampire]

You might use SQL to do this, but not knowing your version of CR or the database, I'll stick with a quick cheat:

Record Selection Formula:

{table.date} >= currentdate-1

Group by the node and sort on the date.

Right click the group footer and select format section->X-2 next to suppress and place something like:

{table.date} = currentdate

Now whatever fields you palce in the group footer will only display if it's yesterdays node and it doesn't exist today.

-k

 

[lbass]

I think SV's suggestion will still return a nodeID at the group level if it appears on both days, i.e., I think the suppress only works if all dates in the group meet the criteria.

Use a record select as SV suggested of:

{table.date} >= CurrentDate - 1

Group on {table.nodeID}

Create a formula {@currentdate}:

if {table.date} = CurrentDate then 1 else 0

Place this in the detail section and insert a sum on the formula.

Then do a group select:

sum({@currentdate}, {table.nodeID}) = 0

-LB