Count From Another Table if field is Null

[austin22]

Hi, I am using CR 8.5 and I want to be able to count data from one table that may not be in another. I have two tables linked, one named 'table.WarList'(primary table) and the other named 'table.ActionList' and they are linked by it's common field the case number. I have chosen the option to 'left outer join' to retrieve everything that I need regardless of the activity on the case.

Here is my setup:

CaseNumber     DocID (field from the table.WarList)122            W2
894            W1
987
471            W1

Number of W1 = 2
Number of W2 = 1

Notice that case number 987 DocID is missing, however, the DocID is found on the table.ActionList. I want my report to say if DocID is missing from the table.WarList then count from the table.ActionList.

Please advise.

 

[austin22]

Can someone help me with this? Please?
I may not understand the null statements.

Any help will be greatly appreciated.

austin22

 

[Tatertot45]

You could create an Ifstatement that fills in the data you need. If would be something like...

"If isnull('table.WarList') then 'table.ActionList' else "other"

Then you can take that formula and do your counting.

--OR--

In this case, i like to keep things simple. use 2 seperate IF formulas, one to populate the table.warlist and one to populate table.actionlist. (If table.field isnot null then table.field else ""). Then, you can total each formula in the footer with a summary or running total. I can't tell you for sure the formula's, becuase i did this off the top of my head, but this should get you going in the right direction. Just think about using an IF statement to populate the field, so yor formula is the field, and you don't actually have the field in the details.

good luck.

 

[lbass]

Try this:

if isnull({warlist.doc}) and
isnull({actionlist.doc}) then 0 else 1

Then right click on the formula and insert a SUM, not a count, on it. This assumes there is no record inflation.

Note that if there is always a doc in one of the two tables for each case number, all you have to do is insert a count on {warlist.caseno}.

-LB