8 Replies Latest reply on Feb 5, 2016 1:30 PM by Fahri Akar

# Operator for "Includes" in a calculation

Hello all!

In my solution, I track student attendance from several sites.  Right now, I am able to filter a portal which will show me all student attendance across all sites based on the event date OR I can filter the portal to only show attendance based on a site that is selected in the Event_Dates table (it corresponds to the site in Student_Information_Attendance, which was copied from the Student Information table when I run a generate attendance script).

The filter is as so:

If (Event_Dates::Filter_by_all  =  "All"; Event_Dates::Date = Student_Attendance_ED::Date;

Event_Dates::Site  =  Student_Information_Attendance::Site)

This has been working great, but now I have a student that is in two sites. This student has two checkboxes checked in Student Information, so when the Site information is copied to Student_Information_Attendance::Site it has two site names with a line break in between.  For instance:

Doral

SFCPA

How would I change the filter records calculation to show the attendance record if Student_Information_Attendance::Site includes any of the sites listed in Event_Dates::Site?

Thanks,

Rey Ramirez

• ###### 1. Re: Operator for "Includes" in a calculation

The formula for the If Statement is as follows:  If ( test ; resultOne ; resultTwo )

It appears you have an If followed by a test, and a second test, and a third test.  You get one test followed by a true result (resultOne) and a false result (result Two).  Your test can be strung together with "and" and "or" statements to have multiple tests.  Just remember there is only one test and if the test is 0 or empty, Result Two will be displayed.  If the test is 1 or not empty, then Result One will be displayed.

• ###### 2. Re: Operator for "Includes" in a calculation

Thank you.  I understand the If calculation, I'm just wondering if there is another operator I can use rather than using the equals sign in the resultTwo test to bring the desired "includes" result.

I guess I could use a case statement for every possible result, but sites are added each year and I don't want to go back every year and account for every possible combination.

• ###### 3. Re: Operator for "Includes" in a calculation

I think your If() looks fine for a portal filter.

You may want to consider using a separate tables to hold Students and their Sites if it becomes common to have more than one. Until then try,

//this is the same

If (Event_Dates::Filter_by_all  =  "All"; Event_Dates::Date = Student_Attendance_ED::Date ;

//use FilterValues() here:

not isEmpty ( FilterValues ( Student_Information_Attendance::Site ) ; Event_Dates::Site ) )

• ###### 4. Re: Operator for "Includes" in a calculation

My bad, Reynaldo, for not understanding what you wanted.  You want the Test Two to see if there multiple ones to include.  How about Test Two be a Let with SQL like this:

Let ( [

SQL = "SELECT Site FROM Event_Dates WHERE  [not sure what to search on, but whatever gets Doral, SFCPA]" ;

Result = ExecuteSQL ( SQL ; "" ; "', " ) ;

Result = If ( Not IsEmpty ( Result ) ; "'" & Result & "'" ) ;

SQL = "SELECT COUNT ( * ) FROM Student_Information_Attendance WHERE Site IN ( " & Result & " )" ;

Result = ExecuteSQL ( SQL ; ¶ ; ¶ )

] ;  Result )

Note the first WHERE will be whatever the relationship is between the layout's table occurrence and the portal.

• ###### 5. Re: Operator for "Includes" in a calculation

PatternCount ( text ; searchString ) > 0

this works like include

not IsEmpty ( Filter ( textToFilter ; filterText ) )

this too.

Fahri Akar

• ###### 6. Re: Operator for "Includes" in a calculation

I would recommend against using even one ExecuteSQL() on a whole table in a portal filter. For each related record, ExSQL() will evaluate. It's a big performance hit.

• ###### 7. Re: Operator for "Includes" in a calculation

This did it!  Thank you!

• ###### 8. Re: Operator for "Includes" in a calculation

Because my post closed to modification, i correct it here.

PatternCount ( text ; searchString ) > 0

this works like include

not IsEmpty ( Filter ( textToFilter ; filterText ) )

this too.

Fahri Akar