All Records from Left Table

[eric333]

I would this what I am trying to do is fairly straightforward, yet the answer eludes me.

I have a list of employees in one table, and a series of records they entered in another table. The common field between the two tables is Employee ID. I have linked the fields using a Left Outer Join.

I want a listing of ALL employees and the total number of records they entered. So, if the employees did not enter any records during the timeframe, their total should be zero.

Does anyone have any suggestions?

 

[Turkbear]

Hi,
As you have done, link using a left outer join on the ID.

Group on the Employee.
Create a formula like :
If IsNull(recordtable.field) or Trim(recordtable.field) = "" then
0
Else
1

Place this formula in the detail section ( you can suppess its display and the section) and insert a Summary(distinct count) of it in the GH.

This should result in:

EMPLOYEE Count_of_Records





To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[eric333]

Good morning,

Thank you for the advice. Unfortunately, it did not work. I ended up with just the four employees that had records, a listing of their records, and the total count.

I don't think this matters, but from the Employee table, I am selecting just a specific set of employees (based on their assignment) and in the Records table, I am selecting just a specific set of records (based on the what the record number starts with).

So, my goal is to list all employees that have this specific assignment, and whether or not they had records that start with this record number.

Thanks in advance for any further suggestions or advice.

 

[lbass]

You can use selection criteria on the left-hand table (the employee table), but NOT on the records table, as this will effectively undo the left join. So remove the selection criteria and instead build it into your formula:

If IsNull(recordtable.field) or
Trim(recordtable.field) = "" or
left(recordtable.field, 2) <> "AB" then //if counting AB records
0
Else
1

Then insert a sum, not a count, on this formula at the employee group level.

There is another approach using a command as your sole datasource where you can select on the right hand table by building the criteria into the From statement instead of the where clause:

select employee.ID, recordtable.field
from employee left outer join recordtable on
employee.key = recordtable.key and
employee.ID in (1,2,3) and
left(recordtable.field,2) = 'AB'

-LB

 

[eric333]

Good morning,

Going with the first approach, I am unfortunately, still experiencing problems.

My Formula is as follows:

If IsNull({RecordTable}) or
Trim({RecordTable}) = ""
or left ({RecordTable},2) startswith "S0" then
0
else
1

The result is that every record in the database is listed and counted in the Sum.

Did I miss something with the formula?

 

[lbass]

Are you trying to count records that startwith "SO"? If so, then the formula should be:

If IsNull({RecordTable}) or
Trim({RecordTable}) = "" or
left ({RecordTable},2)[red]<>[/red]"S0" then
0
else
1

 

[eric333]

That worked perfectly! Thank you.

One last question, if I want the details to be visible, but only include records where the count is 1, would I include a Suppress formula in the Details section and if so, what would it look like?

 

[lbass]

Assuming you have a group on employee, you can suppress the details by using a formula like this:

{@yourformula} = 0

-LB

 

[eric333]

That worked perfectly, and the report looks great. Exactly what I had envisioned (and more importantly, what my manager wants). Thank you very much.

I am trying to understand exactly how this works, versus just making it work. I understand the concept, but it's not entirely clearly to me how the Trim function and Left function come into play here. I've used both of those in the past, but not in this manner.

And, although the following is not a requirement for the report, I expect it may be in the future. And, it may help me to better understand why this worked:

What if I wanted to add a date range or a date statement such as LastFullWeek? There is a field in the Record table for the record date.

 

[lbass]

If
(
IsNull({Record.field}) or
Trim({Record.field}) = "" or
left ({Record.field},2)<>"S0"
) and
not({record.date} in lastfullweek) then
0
else
1

Basically you are assigning a 0 to all records that don't meet your criteria and 1 to those that do. Because null checks must come first, the zero assignment is first in the formula.

-LB

 

[eric333]

Thanks - I see how that works.

I understand most of the formula. Is the purpose of the Trim and Left functions as used in the fomrula to isolate the first two characters of the record number (in this case, those that start with "S0"?

Also, adding the not statement regarding the date (and other modifications to the formula, such as the parenthesis) causes all records during the tiemframe of last full week to appear on the report, not just those that begin with S0.

 

[lbass]

Okay, sorry, my error. I think it should be:

if
IsNull({Record.field}) or
Trim({Record.field}) = "" or
left ({Record.field},2)<>"S0" [red]or[/red]
not({record.date} in lastfullweek) then
0
else
1

The left function is returning the first two characters.

The trim function is being used in case the string field is empty or contains spaces as opposed to being truly null. The trim would eliminate records with potentially varying numbers of spaces and make them all = "".

-LB

 

[eric333]

Thank you sir, for both catching the fish, and teaching me how to fish.

 

[Turkbear]

Hi,
Just a note: LB is a woman.





To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[eric333]

Of course! I apologize LB. Nonetheless, your assistance is greatly appreciated.