Filtering On a Computed Column in a Non-Primary File

You cannot filter on a computed column from a normal simple condition unless the computed column is in the primary file. However, simple conditions within a Select / Returning block treat the Select / Returning file as if it were a primary file. So, as long as you can link from your computed column’s file to your primary file, you can filter on it:

This example illustrates not only filtering on non-primary file computed columns, but also choosing the best primary file. In this report, the goal is to return a list of IDs for parents of current students, excluding town audits and high school students. Because X.STU.CURRENT.STUDENT and X.STU.ENROLL.STATUS are computed columns in STUDENTS, we can’t filter on them from just a simple condition. However, STUDENTS links to PERSON, and PERSON links to PERSON via a link called “PERSON (Parent Mail Id)” which uses the foreign key of X.PERSON.PARENT.MAILING.ID. Since we can link from STUDENTS to the PERSON record of the parents, we can use two Select / Returning blocks and use STUDENTS like a primary file, including filtering on its computed columns. STUDENTS will be filtered on X.STU.CURRENT.STUDENT and X.STU.ENROLL.STATUS, and the foreign key from STUDENTS to PERSON (the @ID field in STUDENTS) will be returned. These foreign keys in STUDENTS will be used to select the linked PERSON records, and then the foreign key for the “PERSON (Parent Mail Id)” link (which is X.PERSON.PARENT.MAILING.ID) will be returned and used to select from PERSON again. These will be our parent IDs. If the primary file of this report was STUDENTS, you could filter on the computed columns in STUDENTS without using the Select / Returning blocks. However, the query would then return a list of STUDENTS IDs rather than the PERSON IDs of the parents. While we could still link from the STUDENTS IDs to return the data we need on the parents, there would be a problem, because parents with more than one student would be listed more than one time. In this case, we want to end up with a list of parent PERSON IDs, and we can still get access to the STUDENTS computed columns by using the Select / Returning blocks.

Filtering on a File Linked Via a Multivalued Key

If you are filtering on more than one field in a file linked via a multi-valued key, you may not get the correct results without using a Select / Returning block. This is because when you have multiple conditions for a file linked via a multi-valued key, you probably intend for all your conditions to be met completely in one record, but the query generated from your criteria will not require this. For example, say your primary file is PERSON, and you want to select anyone who has a current address of Cleveland, OH. To find current addresses, you go from PERSON to ADDRESS via PERSON.ADDRESSES. This field is multi-valued, so a person can have more than one address. If you were to use the following criteria this would look for anyone with the following:

• an ADDRESS record linked via PERSON.ADDRESSES with a city of Cleveland.
• an ADDRESS record linked via PERSON.ADDRESSES with a state of OH.

But the query does not specify that these ADDRESS records must be the same. If a person had one address in Wooster, OH and another in Cleveland, GA, this query would select them. All the fields in ADDRESS become like multi-valued associated fields, but there is no way to use a limit of “When” on them like normal multi-valued associated fields. To solve this problem, you can use a Select / Returning block as long as the file you need to filter on can link back to your primary file. In this case, we can do a Select / Returning block on ADDRESS and return to PERSON using RESIDENTS:

Now these to conditions are required to be true for a single ADDRESS record. If they are, the RESIDENTS field will be returned to select on PERSON.

Excluding a Set of IDs

Similar to the previous example, it is probably necessary to use a Select / Returning block when excluding records based on criteria in a file which is linked via a multi-valued foreign key. In this case, though, the Select / Returning block must be nested inside a compound condition set to None.

In this filter criteria, we are looking for students who have an invoice item for 1011FA with a code of 5STIN and a positive charge amount. They also must NOT have an invoice item for 1011FA with a code of 5STIN and a positive credit amount. Each half of this criteria is done using a Select / Returning block. To find students with the required AR.INVOICE.ITEMS record, the first select / returning block must be used for the reasons discussed in Filtering on a File Linked Via a Multivalued Key (all three criteria must be met for the same AR.INVOICE.ITEM record). AR.INVOICE.ITEMS is searched for records with a term of 1012FA, code of 5STIN, and charge amount, and a list of IDs back to AR.ACCTS is returned. The same must be done for the second part of the query – we want to find all AR.INVOICE.ITEMS records with a term of 1011FA, code of 5STIN, and credit amount – but we want to exclude the AR.ACCTS IDs returned. To do this, nest the Select / Returning block inside a compound condition and change the compound condition’s type from All to None. Therefore, the AR.ACCTS IDs must be in the first returned list but not in the second returned list.