I have a search action that returns a list of courses that teachers have
taken
The values
Staff_ID
Course_ID
Title
First_Name
Last_Name
Location_ID
Location
Compensation
I would like to return a report that looks like this
Building ( location)
Teacher
Course Number Course name Compensation
Teacher
Course Number Course name Compensation
Etc.
For now I can have them do one building at a time but the list would look
best if teacher names only showed when there was a new teacher in the list.
I've attached what I have so far. I think I am close but just can't grasp
the logic for the teacher loop.
--
Dan Stein
Digital Software Solutions
799 Evergreen Circle
Telford PA 18969
Land: 215-799-0192
Mobile: 610-256-2843
Fax 413-410-9682
FMP, WiTango, EDI,SQL 2000
[EMAIL PROTECTED]
www.dss-db.com
<?xml version="1.0" encoding="ISO-8859-1" ?>
<!DOCTYPE TAF SYSTEM "tango.dtd" >
<TAF Version="0x02000002">
<Comments />
<!--Main program-->
<Program>
<ActionRef Ref="If">
<ActionRef Ref="Create_Staff_DevLists" />
<ActionRef Ref="Departments_Array" />
<ActionRef Ref="Locations" />
<ActionRef Ref="Semester_Years" />
<ActionRef Ref="Compensation" />
<ActionRef Ref="Providers" />
<ActionRef Ref="Results" />
</ActionRef>
<ActionRef Ref="Elseif">
<ActionRef Ref="Results1" />
</ActionRef>
<ActionRef Ref="Elseif1">
<ActionRef Ref="Search" />
</ActionRef>
</Program>
<!--Program actions-->
<Actions>
<IfAction ID="If" Sig="if " Expanded="True">
<Expression><![CDATA[('<@ARG
_function>'='start')]]></Expression>
<ExpressionInfo>
<ExpLeft><![CDATA[<@ARG _function>]]></ExpLeft>
<ExpRight>start</ExpRight>
<Operator>iseq</Operator>
</ExpressionInfo>
</IfAction>
<CreateObjectAction ID="Create_Staff_DevLists" Sig="cobj">
<ObjectIdentity>TCF://Staff_DevLists.tcf</ObjectIdentity>
<ObjectName>Staff_DevLists</ObjectName>
<ObjectInstance ConnectToExisting="False">
<Name>Courses_Arrays</Name>
<Scope>Local</Scope>
</ObjectInstance>
<UserInfo Encrypted="True">
<UserName />
<Password />
</UserInfo>
</CreateObjectAction>
<CallMethodAction MethodID="Departments" ID="Departments_Array"
Sig="calm">
<ObjectIdentity>TCF://Staff_DevLists.tcf</ObjectIdentity>
<ObjectName>Staff_DevLists</ObjectName>
<ObjectInstance>
<Name>Courses_Arrays</Name>
<Scope>Local</Scope>
</ObjectInstance>
<MethodResult Type="Void" Description="Array"
NativeType="_ARR" />
<CallParameterList>
<CallParameterItem Type="Void" Description="Any"
NativeType="_ANY">
<Name>Departments</Name>
<Direction>InBound</Direction>
<CallParameterValue>
<Value />
</CallParameterValue>
</CallParameterItem>
<CallParameterItem Type="Void" Description="Array"
NativeType="_ARR">
<Name>Departments_List</Name>
<Direction>OutBound</Direction>
<CallParameterVariable>
<Name>Departments_List</Name>
<Scope>Local</Scope>
</CallParameterVariable>
</CallParameterItem>
</CallParameterList>
</CallMethodAction>
<CallMethodAction MethodID="Locations" ID="Locations" Sig="calm">
<ObjectIdentity>TCF://Staff_DevLists.tcf</ObjectIdentity>
<ObjectName>Staff_DevLists</ObjectName>
<ObjectInstance>
<Name>Courses_Arrays</Name>
<Scope>Local</Scope>
</ObjectInstance>
<MethodResult Type="Void" Description="Array"
NativeType="_ARR" />
<CallParameterList>
<CallParameterItem Type="Void" Description="Any"
NativeType="_ANY">
<Name>Location_Codes</Name>
<Direction>InBound</Direction>
<CallParameterValue>
<Value />
</CallParameterValue>
</CallParameterItem>
<CallParameterItem Type="Void" Description="Array"
NativeType="_ARR">
<Name>Locations_List</Name>
<Direction>OutBound</Direction>
<CallParameterVariable>
<Name>Locations_List</Name>
<Scope>Local</Scope>
</CallParameterVariable>
</CallParameterItem>
</CallParameterList>
</CallMethodAction>
<CallMethodAction MethodID="Semester_Years" ID="Semester_Years"
Sig="calm">
<ObjectIdentity>TCF://Staff_DevLists.tcf</ObjectIdentity>
<ObjectName>Staff_DevLists</ObjectName>
<ObjectInstance>
<Name>Courses_Arrays</Name>
<Scope>Local</Scope>
</ObjectInstance>
<MethodResult Type="Void" Description="Array"
NativeType="_ARR" />
<CallParameterList>
<CallParameterItem Type="Void" Description="Any"
NativeType="_ANY">
<Name>Years</Name>
<Direction>InBound</Direction>
<CallParameterValue>
<Value />
</CallParameterValue>
</CallParameterItem>
<CallParameterItem Type="Void" Description="Array"
NativeType="_ARR">
<Name>S_Y_List</Name>
<Direction>OutBound</Direction>
<CallParameterVariable>
<Name>S_Y_List</Name>
<Scope>Local</Scope>
</CallParameterVariable>
</CallParameterItem>
</CallParameterList>
</CallMethodAction>
<CallMethodAction MethodID="Compensation" ID="Compensation"
Sig="calm">
<ObjectIdentity>TCF://Staff_DevLists.tcf</ObjectIdentity>
<ObjectName>Staff_DevLists</ObjectName>
<ObjectInstance>
<Name>Courses_Arrays</Name>
<Scope>Local</Scope>
</ObjectInstance>
<MethodResult Type="Void" Description="Any" NativeType="_ANY"
/>
<CallParameterList>
<CallParameterItem Type="Void" Description="Any"
NativeType="_ANY">
<Name>Compensation</Name>
<Direction>InBound</Direction>
<CallParameterValue>
<Value />
</CallParameterValue>
</CallParameterItem>
<CallParameterItem Type="Void" Description="Array"
NativeType="_ARR">
<Name>Compensation_List</Name>
<Direction>OutBound</Direction>
<CallParameterVariable>
<Name>Compensation_List</Name>
<Scope>Local</Scope>
</CallParameterVariable>
</CallParameterItem>
</CallParameterList>
</CallMethodAction>
<CallMethodAction MethodID="Providers" ID="Providers" Sig="calm">
<ObjectIdentity>TCF://Staff_DevLists.tcf</ObjectIdentity>
<ObjectName>Staff_DevLists</ObjectName>
<ObjectInstance>
<Name>Courses_Arrays</Name>
<Scope>Local</Scope>
</ObjectInstance>
<MethodResult Type="Void" Description="Any" NativeType="_ANY"
/>
<CallParameterList>
<CallParameterItem Type="Void" Description="Any"
NativeType="_ANY">
<Name>providers</Name>
<Direction>InBound</Direction>
<CallParameterValue>
<Value />
</CallParameterValue>
</CallParameterItem>
<CallParameterItem Type="Void" Description="Array"
NativeType="_ARR">
<Name>Providers_List</Name>
<Direction>OutBound</Direction>
<CallParameterVariable>
<Name>Providers_List</Name>
<Scope>Local</Scope>
</CallParameterVariable>
</CallParameterItem>
</CallParameterList>
</CallMethodAction>
<ResultAction ID="Results" Sig="null">
<ResultsOutput Ref="Results.Results" />
</ResultAction>
<ElseIfAction ID="Elseif" Sig="elif" Expanded="True">
<Expression><![CDATA[('<@ARG
_function>'='by_building')]]></Expression>
<ExpressionInfo>
<ExpLeft><![CDATA[<@ARG _function>]]></ExpLeft>
<ExpRight>by_building</ExpRight>
<Operator>iseq</Operator>
</ExpressionInfo>
</ElseIfAction>
<ResultAction ID="Results1" Sig="null">
<ResultsOutput Ref="Results1.Results" />
</ResultAction>
<ElseIfAction ID="Elseif1" Sig="elif" Expanded="True">
<Expression><![CDATA[('<@ARG
_function>'='building_list')]]></Expression>
<ExpressionInfo>
<ExpLeft><![CDATA[<@ARG _function>]]></ExpLeft>
<ExpRight>building_list</ExpRight>
<Operator>iseq</Operator>
</ExpressionInfo>
</ElseIfAction>
<SearchAction UseDevelopmentDataSource="TRUE" DSID="Staff_Dev"
DeploymentDSID="" ID="Search" Sig="srch">
<Tables>
<TableName>dbo.Staff_Records</TableName>
<TableName>dbo.Courses</TableName>
<TableName>dbo.Staff</TableName>
<TableName>dbo.Location</TableName>
</Tables>
<DataDictionary>
<Column DataType="deci" ColumnType="0">
<TableName>Courses</TableName>
<ColumnName>Course_ID</ColumnName>
</Column>
<Column DataType="deci" ColumnType="0">
<TableName>Staff_Records</TableName>
<ColumnName>Course_ID</ColumnName>
</Column>
<Column DataType="deci" ColumnType="0">
<TableName>Staff</TableName>
<ColumnName>Staff_ID</ColumnName>
</Column>
<Column DataType="deci" ColumnType="0">
<TableName>Staff_Records</TableName>
<ColumnName>Staff_ID</ColumnName>
</Column>
<Column DataType="deci" ColumnType="0">
<TableName>Staff</TableName>
<ColumnName>Location_ID</ColumnName>
</Column>
<Column DataType="long" ColumnType="0">
<TableName>Location</TableName>
<ColumnName>Location_ID</ColumnName>
</Column>
<Column DataType="vcha" ColumnType="0">
<TableName>Courses</TableName>
<ColumnName>Title</ColumnName>
</Column>
<Column DataType="text" ColumnType="0">
<TableName>Staff</TableName>
<ColumnName>First_Name</ColumnName>
</Column>
<Column DataType="text" ColumnType="0">
<TableName>Staff</TableName>
<ColumnName>Last_Name</ColumnName>
</Column>
<Column DataType="text" ColumnType="0">
<TableName>Location</TableName>
<ColumnName>Location</ColumnName>
</Column>
<Column DataType="long" ColumnType="0">
<TableName>Courses</TableName>
<ColumnName>SY_ID</ColumnName>
</Column>
</DataDictionary>
<SearchColumns>
<ColumnReference>
<TableName>Staff_Records</TableName>
<ColumnName>Staff_ID</ColumnName>
</ColumnReference>
<ColumnReference>
<TableName>Courses</TableName>
<ColumnName>Course_ID</ColumnName>
</ColumnReference>
<ColumnReference>
<TableName>Courses</TableName>
<ColumnName>Title</ColumnName>
</ColumnReference>
<ColumnReference>
<TableName>Staff</TableName>
<ColumnName>First_Name</ColumnName>
</ColumnReference>
<ColumnReference>
<TableName>Staff</TableName>
<ColumnName>Last_Name</ColumnName>
</ColumnReference>
<ColumnReference>
<TableName>Staff</TableName>
<ColumnName>Location_ID</ColumnName>
</ColumnReference>
<ColumnReference>
<TableName>Location</TableName>
<ColumnName>Location</ColumnName>
</ColumnReference>
</SearchColumns>
<OrderColumns>
<OrderColumn>
<Ascending>True</Ascending>
<TableOwner />
<TableName />
<ColumnName>5</ColumnName>
</OrderColumn>
</OrderColumns>
<Criteria>
<CriteriaItem>
<TableName>Courses</TableName>
<ColumnName>SY_ID</ColumnName>
<Operator>iseq</Operator>
<Value><![CDATA[<@ARG SY_ID>]]></Value>
<QuoteValue>false</QuoteValue>
<IncludeIfEmpty>false</IncludeIfEmpty>
</CriteriaItem>
<CriteriaItem>
<Conjunction>and </Conjunction>
<TableName>Staff</TableName>
<ColumnName>Location_ID</ColumnName>
<Operator>iseq</Operator>
<Value><![CDATA[<@ARG location_ID>]]></Value>
<QuoteValue>false</QuoteValue>
<IncludeIfEmpty>false</IncludeIfEmpty>
</CriteriaItem>
</Criteria>
<JoinCriteria>
<JoinItem JoinType="Equal">
<FirstTable>Courses</FirstTable>
<FirstColumn>Course_ID</FirstColumn>
<SecondTable>Staff_Records</SecondTable>
<SecondColumn>Course_ID</SecondColumn>
</JoinItem>
<JoinItem JoinType="Equal">
<FirstTable>Staff</FirstTable>
<FirstColumn>Staff_ID</FirstColumn>
<SecondTable>Staff_Records</SecondTable>
<SecondColumn>Staff_ID</SecondColumn>
</JoinItem>
<JoinItem JoinType="Equal">
<FirstTable>Staff</FirstTable>
<FirstColumn>Location_ID</FirstColumn>
<SecondTable>Location</SecondTable>
<SecondColumn>Location_ID</SecondColumn>
</JoinItem>
</JoinCriteria>
<StartRow>1</StartRow>
<ResultsOutput Ref="Search.Results" />
</SearchAction>
</Actions>
<!--Data sources-->
<DataSources>
<DataSource ID="Staff_Dev">
<DSType>ODBC</DSType>
<DSN>Staff_Dev</DSN>
<DBMS />
<Host>Staff_Dev</Host>
<Database />
<UserInfo Encrypted="True">
<UserName>%7C%81%20Vf%F2%23%B7</UserName>
<Password>%F4%AA6p%9D%3E%23%EC</Password>
</UserInfo>
</DataSource>
</DataSources>
<!--Output blocks-->
<Outputs>
<Output ID="Results.Results" Type="HTML"><![CDATA[<!DOCTYPE HTML
PUBLIC "-//W3C//DTD HTML 3.2//EN">
<HTML>
<HEAD>
<TITLE>Reports</TITLE>
</HEAD>
<BODY>
<A HREF="<@CGI><@APPFILE>?_function=by_building&<@UserReferenceArgument>&nc=<@RANDOM
Low='1000' HIGH='2000000' ENCODING=URL>">By Building</A>
</BODY>
</HTML>]]></Output>
<Output ID="Results1.Results" Type="HTML"><![CDATA[<!DOCTYPE HTML
PUBLIC "-//W3C//DTD HTML 3.2//EN">
<HTML>
<HEAD>
<TITLE></TITLE>
</HEAD>
<BODY>
<FORM METHOD=POST
ACTION="<@CGI><@APPFILE>?_function=building_list&<@UserReferenceArgument>&nc=<@RANDOM
Low='1000' HIGH='2000000' ENCODING=URL>">
<SELECT NAME="Location_ID" SIZE=1>
<OPTION VALUE="">Please Select
<OPTION VALUE="">-------------
<@ROWS START=1 STEP=1 ARRAY="user$Locations_List">
<OPTION VALUE="<@COL NUM='1'>"><@COL NUM="2">
</@ROWS>
</SELECT>
<TR ALIGN="LEFT" VALIGN="TOP">
<TD>
<B>School Year: </B>
</TD>
<TD>
<SELECT NAME="SY_ID" SIZE=1>
<OPTION VALUE="">Please Select
<OPTION VALUE="">-------------
<@ROWS START=1 STEP=1 ARRAY="user$S_Y_List">
<OPTION VALUE="<@COL NUM='1'>"><@COL NUM="2">
</@ROWS>
</SELECT>
</TD>
</TR>
<P>
<INPUT TYPE=SUBMIT NAME="Submit" VALUE="Submit">
<INPUT TYPE=RESET VALUE=Reset>
</FORM>
</BODY>
</HTML>]]></Output>
<Output ID="Search.Results" Type="HTML"><![CDATA[
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<title>By Building Report</title>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
</head>
<body>
<@Assign Array="local$Staff_ID" Value="@@resultset[*,1]">
<h2 align="center"><@COLUMN 'Location.Location'> </h2>
<h2 align="center"><@CURRENTDATE FORMAT="datetime:%A, %B %d, %Y"> </h2>
<table width="100%" border="0">
<@COMMENT>
This would be the teacher loop if I can check to see if the staff_ID of the current
row is the same as the one of the previous row then I should be able to leave out this
table row and only include it if the are different.
</@COMMENT>
<@ROWS Array=local$staff_ID>
<@IF Expr="local$staff_ID=">
<tr bgcolor="#CCCCCC">
<td colspan="4"><strong><font size="2" face="Arial, Helvetica,
sans-serif"><@COLUMN 'Staff.First_Name'> <@COLUMN
'Staff.Last_Name'></font></strong></td>
</tr>
</@ROWS>
<@COMMENT>
This woyuld be the course loop showing all the courses the teacher took.
</@COMMENT>
<@ROWS>
<tr>
<td> </td>
<td><strong><font size="2" face="Arial, Helvetica, sans-serif">Course
Number</font></strong></td>
<td><strong><font size="2" face="Arial, Helvetica,
sans-serif">Name</font></strong></td>
<td><strong><font size="2" face="Arial, Helvetica,
sans-serif">Compensation</font></strong></td>
</tr>
<@ROWS>
<tr>
<td><font size="2" face="Arial, Helvetica, sans-serif"> </font></td>
<td><font size="2" face="Arial, Helvetica, sans-serif"><@COLUMN
'Courses.Course_ID'> </font></td>
<td><font size="2" face="Arial, Helvetica, sans-serif"><@COLUMN
'Courses.Title'> </font></td>
<td><font size="2" face="Arial, Helvetica, sans-serif"> </font></td>
</tr>
</@ROWS>
</table>
</body>
</html>
]]></Output>
</Outputs>
</TAF>
