Matching Billing Names with matching Elderly and or Disabled status

[nathanwiley]

Hello,

I have a challenge again...

I have the following tables.

{vwAVMembersAll}
{vwAVEAPApplications}
{vwAVEAPClaims}

I need to pull specific data, let me explain... I work for the Energy Assistance Program that helps pay on qualifying families on their utility bills.

I have new funding that can only be applied to either Elderly 60+ individuals and or individuals who are disabled with the utility bills are in their name.

(It can be elderly 60+ or someone who is disabled with the utilities in their name – if they are both disabled and elderly this is ok)

I Need to see in the house is disabled or elderly with the utilities in their name, so the billing name on the claim table should match a household members name.

We have {vwAVEAPClaims.BillToFirstName} showing the first name of the bill and we have {vwAVEAPClaims.BillToLastName} showing the last name of the bill. These need to match one or both of these tables {vwAVMembersAll.LastName} and {vwAVMembersAll.FirstName} while matching either Eldery 60+ or disabled 18 or older or both elderly and disabled.

I had the following forula's but they don't work because I don't know the coding to make the information look for each other.


{vwAVEAPApplications.MatrixPoints} >= 12 and
{vwAVEAPApplications.EAPApplicationStatus} = "Approved" and
{@Age} >= 18 and
{vwAVMembersAll.Disabled}
{vwAVEAPApplications.ProgramYear} >= 2011 and
{vwAVEAPClaims.BillToFirstName} = ""



{vwAVApplications.ApprovedForEAP} and
{vwAVEAPApplications.ProgramYear} = 2011 and
(
(TRIM({{vwAVMembersAll.FirstName}}) = "" OR
ISNULL({{vwAVEAPClaims.BillToFirstName}}))
OR
(TRIM({{vwAVMembersAll.LastName}}) = "" OR
ISNULL({{vwAVEAPClaims.BillToLastName}}))
OR
(TRIM({{vwAVMembersAll.Disabled}}) = "true" OR
ISNULL({{vwAVMembersAll.Disabled}}))
)


Thanks for any help you can offer, I hope I was clear in what I need.

 

[lbass]

Since you are looking only for matches, I think you could join the claims and membersall tables on the two name fields. Then you could add criteria like this to your other criteria:

(
{@age} >= 60 or
{vwAVMembersAll.Disabled} = "true"
)

-LB

 

[nathanwiley]

thank you!

We have over 1400 families already todate, is it possible to formulate it to just show the ones that match with the formula you provide?

 

[nathanwiley]

I was checking, and our criteria can be either elderly or disabled or they can be both, they just have to be older than 18.

 

[lbass]

If you link the two fields in each table with an equal join and enforce both joins in the linking options (or use fields from each table in the report), the report will ONLY show people who are in the billing file. Then the selection formula I showe will limit the records to only those people who meet one or both criteria. Did you try this?

The criteria I showed don't rule out people younger than 60. They would allow people younger than 60 if they were disabled.

-LB

 

[nathanwiley]

I will try, thank you!

 

[nathanwiley]

Ok I tried this and I have good success until I enter OR instead of AND after age.

{vwAVEAPApplications.EAPApplicationStatus} = "Approved" and
{vwAVEAPClaims.VendorName} = "Duke Energy" and
{vwAVEAPApplications.ProgramYear} = 2011 and
{vwAVEAPApplications.MatrixPoints} >= 12 and
{@AGE} >= 18.00 or
{vwAVMembersAll.Disabled}

I can't enter = "TRUE" after {vwAVMembersAll.Disabled} because it errors out, also running the report with OR alters current data into old data. Meaning instead of pulling 2011 data it pulls all years back to 1996 which isn't what I am needing.

Also when the value is false in the select expert it enters a NOT infront - resulting in this "not {vwAVMembersAll.Disabled}" without ""'s

Again I apprecite your advise.

 

[lbass]

The parens that I showed are not optional. Use:

{vwAVEAPApplications.EAPApplicationStatus} = "Approved" and
{vwAVEAPClaims.VendorName} = "Duke Energy" and
{vwAVEAPApplications.ProgramYear} = 2011 and
{vwAVEAPApplications.MatrixPoints} >= 12 and
(
{@AGE} >= 60 or
{vwAVMembersAll.Disabled}
)


I only used "true" because you did.

Don't use the select expert--just go to report->selection formula->record and enter the formula there.

-LB

 

[nathanwiley]

Sorry about the "True" I am still learning...

I placed the code you sent and it didn't show errors, but you knew that cause you are a pro , that aside it is still pulling households who are not disabled. Resulting in more data than needed.

I will call it a night, I have gotten further with your help and I greatly appreciate everything you have offered.

If you have any more ideas, please share.

Nathan

 

[lbass]

If you changed the age to 60, it should only be pulling households where someone is 60 or over or who where someone who is disabled is the billed party or both.

-LB