TIPS AND TRICKS TIPS AND TRICKS WITH POPSELS!WITH POPSELS!

NANCY LARSONNANCY LARSON

FINANCIAL AID FINANCIAL AID COORDINATORCOORDINATOR

FULLERTON COLLEGEFULLERTON COLLEGE

nlarson@fullcoll.edunlarson@fullcoll.edu

GLRSLCTGLRSLCT Nancy Larson Fullerton College Financial Aid Coordinator

321 E. Chapman Ave. , Fullerton, Calif. 92832 (714) 992-7057

HANDOUT #1

GLRSLCT FORM Application: FINAID Selection ID:

Creator ID: NLARSON

Description:

Select: TABLE Name (Can search on RORRULE – flashlight)

From: Column Name (Can search on RORULE – flashlight)

Data Element: Operator Value

Sometimes can search on Dynamic parameters can be used whenever

your form by clicking in field possible by preceding with a &______ and

you want to “find” and then then typing the item you need to enter (which

go to HELP Dynamic Help will show as a 88 field after saving the

query to see column name. GLBDATA). Helpful for aid-year changes in

order not to have to remember to change

popsels each year because of hard-coding.

The form used to The form used to create, edit or create, edit or delete popsels. See delete popsels. See handout #1.handout #1.

Best to use existing Best to use existing popsels and edit to popsels and edit to start.start.

The form was The form was designed to give designed to give users a way of users a way of writing SQL queries.writing SQL queries.

GLISLCTGLISLCT Is the form that you can Is the form that you can

view all the popsels view all the popsels created for certain created for certain applications. Ours are applications. Ours are FINAID for financial aid. FINAID for financial aid. Student, AR, Finance Student, AR, Finance and other offices may and other offices may have some created as have some created as well. Look at theirs! Use well. Look at theirs! Use theirs! Copy them for theirs! Copy them for yourself!yourself!

Use this form to search Use this form to search for existing popsels you for existing popsels you may want to use to may want to use to create your own.create your own.

SOME FC POPSELSSOME FC POPSELS• Will review and give out a small sample of some of our best

used popsels. Handouts 2 thru 7.• HAS_FUND – Handout #2, can run and get a listing of any fund

we have on RPAAWRD in the accepted column by designating (with dynamic parameter) the aidyear and fund code.

• HAS_SAP – Handout #3 can run any SAP code status (New status field on ROASTAT). Good for running list of disqualified students, etc and can be used to run letters, etc.

• HAS_REQUIREMENT – Handout # 4, list of students for a aidyear who have specific tracking requirement.

• FA_BAL_OWED – Handout # 5 to find student who owe financial aid money. Note the table-PIDM ties here.

• GET_POPSEL – Handout # 6 to be described later.• MANUAL – Handout # 7. To put students in a popsel by hand

on the GLAEXTR form.

CREATING A NEW POPSELCREATING A NEW POPSEL Nancy Larson Fullerton College Financial Aid Coordinator

321 E. Chapman Ave. , Fullerton, Calif. 92832 (714) 992-7057

HANDOUT #2

GLRSLCT FORM Application: FINAID Selection ID: HAS_FUND

Creator ID: NLARSON

Description: Get PIDM’s/List of students awarded for a designated fund

Select: RPRAWRD_PIDM

From: RPRAWRD

Data Element: Operator Value

RPRAWRD_AIDY_CODE = &FUND_AIDY_CODE AND

RPRAWRD_FUND_CODE = &FUND_CODE AND

RPRATRM_ACCEPT_AMT > ‘0’

From an existing From an existing popsel, using: popsel, using: HAS_FUND (your HAS_FUND (your handout # 2)handout # 2)

Using an existing Using an existing popsel is the best popsel is the best way to start to way to start to learn to create learn to create popsels on your popsels on your own.own.

USING RORRULEUSING RORRULE Perhaps we want to change Perhaps we want to change

the HAS_FUND popsel because the HAS_FUND popsel because instead of looking for instead of looking for accepting award amount, we accepting award amount, we want to find the offered award want to find the offered award amount.amount.

What is the name of the What is the name of the column that holds the offer column that holds the offer information?information?

We see that the accepted is a We see that the accepted is a RPRTRM table (from GLRSLCT RPRTRM table (from GLRSLCT RPRATRM_ACCEPT_AMT), so RPRATRM_ACCEPT_AMT), so perhaps offered is the same.perhaps offered is the same.

We will use RORRULE to find We will use RORRULE to find this column. RORRULE is a this column. RORRULE is a great search tool!great search tool!

Put in the table RPRATRMPut in the table RPRATRM Hit the search button to view Hit the search button to view

the columns for the RPRATRM the columns for the RPRATRM table. Is there one for offered?table. Is there one for offered?

Text box will pop up.Text box will pop up. Search for O% or Search for O% or

offered, or scroll thru to offered, or scroll thru to see if there is a offered see if there is a offered column.column.

Once we find the Once we find the column name, we can column name, we can now replace into now replace into existing popsel.existing popsel.

RORRULE can be used RORRULE can be used for any Table – column for any Table – column search if needed. It lets search if needed. It lets us view all the columns us view all the columns available for certain available for certain tables.tables.

RORRULERORRULE

Can be used to look at Tables and columns Can be used to look at Tables and columns and is a wonderful financial aid SEARCH form.and is a wonderful financial aid SEARCH form.

Columns are what we use for data element Columns are what we use for data element values to develop popsels on the GLRSLCT values to develop popsels on the GLRSLCT form.form.

You can also sometimes use your RORRULE You can also sometimes use your RORRULE rules as popsels! Look at them!rules as popsels! Look at them!

COPYING POPSELSCOPYING POPSELS In order to use an existing popsel, In order to use an existing popsel,

created by someone else, you created by someone else, you must first copy it and save it to must first copy it and save it to make it your popsel .make it your popsel .

If you are wanting to use an If you are wanting to use an existing popsel and change a existing popsel and change a value-field, you would also copy value-field, you would also copy and rename it as well.and rename it as well.

For many processes you will need For many processes you will need to be the creator and user in order to be the creator and user in order to run processes.to run processes.

REMEMBER GLISLCT!REMEMBER GLISLCT!

Use this form to find Use this form to find popsels (use key words popsels (use key words to search) that you may to search) that you may want to use, or to copy want to use, or to copy and edit !and edit !

MODIFYING THE MODIFYING THE ‘HAS_FUND’ POPSEL‘HAS_FUND’ POPSEL

Go to GLRSLCT (for Go to GLRSLCT (for the popsel you want to the popsel you want to use to edit) and navigate use to edit) and navigate to the 4to the 4thth block. block.

Go to options and Go to options and choose the copy optionchoose the copy option

You will see a new You will see a new screen/block pop up.screen/block pop up.

Fill out the copy to Fill out the copy to information, a new information, a new description of what the description of what the popsel will do, name popsel will do, name your new popsel, and your new popsel, and SAVE.SAVE.

Now you will see a new Now you will see a new “revised” GLRSLCT “revised” GLRSLCT with a new name, new with a new name, new description and you description and you indicated as the creator.indicated as the creator.

Now the data element Now the data element that you want to replace that you want to replace needs to be edited from: needs to be edited from: RPRATRM_ACCEPT_AMRPRATRM_ACCEPT_AMTo the one you just found on To the one you just found on RORRULE which isRORRULE which is

RPRATRM_OFFER_AMT RPRATRM_OFFER_AMT and SAVED.and SAVED.

Once you have SAVED Once you have SAVED the form will go thru a the form will go thru a compile process.compile process.

You can now go back to You can now go back to the GLRSLCT form to the GLRSLCT form to view your new popsel view your new popsel and can begin testing it and can begin testing it by running GLBDATA!by running GLBDATA!

SEARCHING FOR A COLUMN SEARCHING FOR A COLUMN NAME ON A FORMNAME ON A FORM

Another way to search a column Another way to search a column is to click the field (on a is to click the field (on a particular form) that you would particular form) that you would like to capture in a popsel. In like to capture in a popsel. In this case disbursed amount from this case disbursed amount from RPAAWRD.RPAAWRD.

Then go to HELP and Dynamic Then go to HELP and Dynamic Help query.Help query.

At this new dialog box you will At this new dialog box you will see the column name.see the column name.

NOTE: You can not always use NOTE: You can not always use this process as sometimes the this process as sometimes the field is just a “view” NOT a field is just a “view” NOT a column .column .

COMBINING POPSELSCOMBINING POPSELS Running the Running the

GLBDATA process.GLBDATA process. How to use the How to use the

Union, Intersection, Union, Intersection, and Minus and Minus (parameter 05 on (parameter 05 on GLBDATA).GLBDATA).

Running Running lists/spreadsheets lists/spreadsheets from GLAEXTR and from GLAEXTR and GLIEXTR and other GLIEXTR and other forms).forms).

GLBDATAGLBDATA Nancy Larson Fullerton College Financial Aid Coordinator

321 E. Chapman Ave. , Fullerton, Calif. 92832 (714) 992-7057

PROCESS: GLBDATA

PARAMETERS Values

01 Selection Identifier 1

02 Selection Identifier 2

03 New Selection Identifier NAME OF POPSEL USING 01 & 02

04 Description of new selection DESCRIPTION OF 03 POPSEL

05 Union/Intersection/Minus U- UNION, I – INTERSECT, M – MINUS**

06 Application Code FINAID (FROM GLRSLCT)

07 Creator ID of Selection ID CREATOR NAME (FROM GLRSLCT)

08 Detail Execution Report

Parameter 05 Description:

U – UNION = To take 01 and 02 popsels and combine them to get an “unduplicated count” for 03 popsel.

I – INTERSECT = To take 01 and 02 popsels and combine them to get a list of

people that are NOT in both popsels. Tell me (in popsel 03) which students are not found in 01 AND 02.

M – MINUS = Takes popsel 01 and subtracts students in popsel 02.

GLBDATA is the process that GLBDATA is the process that runs your popsels.runs your popsels.

Refer to GBLDATA handout Refer to GBLDATA handout # 8.# 8.

UnionUnion – To take two popsels – To take two popsels and combine them to create and combine them to create one unduplicated list/popsel.one unduplicated list/popsel.

IntersectionIntersection – To take two – To take two popsels and combine them popsels and combine them to get a list/popsels of to get a list/popsels of people that are NOT in both people that are NOT in both popsels. “Show me the popsels. “Show me the students that do not meet students that do not meet the popsel 1 and popsel 2 the popsel 1 and popsel 2 criteria.”criteria.”

MinusMinus – To subtract popsel # – To subtract popsel # 2 from popsel # 1 to create 2 from popsel # 1 to create a list/popsel.a list/popsel.

GLBDATA – EXAMPLE GLBDATA – EXAMPLE #1#1

Nancy Larson Fullerton College Financial Aid Coordinator

321 E. Chapman Ave. , Fullerton, Calif. 92832 (714) 992-7057

PROCESS: GLBDATA

PARAMETERS Values

01 Selection Identifier 1 HAS_FUND

02 Selection Identifier 2 HAS_REQUIREMENT

03 New Selection Identifier PELL_90U

04 Description of new selection PELL STUDENTS W/90U REQUIREMENT

05 Union/Intersection/Minus I

06 Application Code FINAID

07 Creator ID of Selection ID NLARSON

08 Detail Execution Report

EXAMPLE 1 : BSTUDENT wants to run two popsels that have been created by NLARSON (HAS_FUND and HAS_REQUIREMENT) to get a list of students who have a 90U tracking requirement and have Pell grant. By using parameters (above) BSTUDENT will be “creating” a new popsel named: PELL_90U (parameter 03 above). If she wants to use this popsel to run a process, she would then put : APPLICATION: FINAID SELECTION ID: PELL_90U CREATOR: BSTUDENT because SHE has created this when she ran with the parameters above. Many times it is confusing and the original creator is used in error, and the popsel will not work.

Go thru example 1 to show Go thru example 1 to show how to combine popsels how to combine popsels and use the new popsel and use the new popsel created in a process.created in a process.

Same person must be the Same person must be the creator of both popsels creator of both popsels used.used.

Remember that the user Remember that the user who is running the process who is running the process (who does not have to be (who does not have to be the creator) will now the creator) will now BECOME the creator of the BECOME the creator of the new popsel (named in new popsel (named in parameter 03) that is parameter 03) that is being created by this being created by this combining process. combining process.

GLBDATA – EXAMPLE GLBDATA – EXAMPLE #2#2

Nancy Larson Fullerton College Financial Aid Coordinator

321 E. Chapman Ave. , Fullerton, Calif. 92832 (714) 992-7057

PROCESS: GLBDATA

PARAMETERS Values

01 Selection Identifier 1 GET_POPSEL

02 Selection Identifier 2 HAS_SAP

03 New Selection Identifier PELL_90U_GOOD

04 Description of new selection PELL STUDENTS W/90U REQ NOT DISQ

05 Union/Intersection/Minus M

06 Application Code FINAID

07 Creator ID of Selection ID BSTUDENT

08 Detail Execution Report

EXAMPLE 2 : BSTUDENT wants to NOW take the popsel just created by intersecting the two popsels (in example 1) , and wants to know, out of the PELL_90U students, pull out the disqualified ones. Banner does not recognize the PELL_90U as a valid popsel. If BSTUDENT took the PELL_90U and put that in parameter 01, then put the popsel HAS_SAP in parameter 02 to minus, there would be an error, Banner can not “find” the created popsel. In order to DO this, instead of putting PELL_90U in parameter 01, you can use the GET_POPSEL selection (which I have given you a copy of) and then, you will get dynamic 88 parameters after saving which will ask you to enter the application (FINAID), selection (PELL_90U), creator (BSTUDENT) and user which would be BSTUDENT again, but others could run this as well (could be different users for this). THEN it will pull those student and minus the 02 selection. HAS_SAP will also give you a dynamic in order to enter your ROASTAT disqualified code. Note: BSTUDENT must have a GET_POPSEL that she created on file. Both popsels used in 01 and 02 must have the same creator. Students from this popsel can be used to run a process and the information would be: APPLICATION: FINAID SELECTION ID: PELL_90U_GOOD CREATOR: PERSON RUNNING THE PROCESS (USER)

Go thru example 2 Go thru example 2 to show how to to show how to “use” the popsel “use” the popsel created by the user created by the user in example 1, in in example 1, in another combined another combined popsel.popsel.

Review the Review the GET_POPSEL GET_POPSEL (handout # 6) used (handout # 6) used for this purpose.for this purpose.

PROBLEMSPROBLEMS Many times, confusion arises because Many times, confusion arises because

staff will use the “creator” instead of the staff will use the “creator” instead of the “user” when popsels are combined and “user” when popsels are combined and used to run a process.used to run a process.

Important to remember – If you are Important to remember – If you are combining two popsels created by combining two popsels created by BSTUDENT and BSTAFF is the user that is BSTUDENT and BSTAFF is the user that is running the GLBDATA process and giving running the GLBDATA process and giving it a new name (parameter 03 from it a new name (parameter 03 from GLBDATA) then you need to remember GLBDATA) then you need to remember that BSTAFF is now the CREATOR of the that BSTAFF is now the CREATOR of the 03 parameter popsel and when using in a 03 parameter popsel and when using in a process needs to be the creator used, not process needs to be the creator used, not BSTUDENT.BSTUDENT.

Trying to use a parameter 03 “created” Trying to use a parameter 03 “created” popsel to combine with popsel to combine with anotheranother, will not , will not work and you will get a “has not been work and you will get a “has not been compiled” error message. If you want to compiled” error message. If you want to do this, use the GET_POPSEL popsel which do this, use the GET_POPSEL popsel which will then allow you to do this.will then allow you to do this.

GET_POPSELGET_POPSEL Nancy Larson Fullerton College Financial Aid Coordinator

321 E. Chapman Ave. , Fullerton, Calif. 92832 (714) 992-7057

HANDOUT # 6

GLRSLCT FORM Application: FINAID Selection ID: GET_POPSEL

Creator ID: NLARSON

Description: Get PIDMs from existing/created popsel

Select: SPRIDEN_PIDM

From: SPRIDEN, GLBEXTR

Data Element: Operator Value

SPRIDEN_PIDM = GLBEXTR_KEY AND

GLBEXTR_APPLICATION = &APPLICATION AND

GLBEXTR_SELECTION = &SELECTION AND

GLBEXTR_CREATOR_ID = &CREATOR AND

GLBEXTR_USER_ID = &USER

This popsel can be used This popsel can be used in parameter 01 or 02 in parameter 01 or 02 so that you can use the so that you can use the popsel you just created popsel you just created (by combining 2 (by combining 2 popsels) in another popsels) in another popsel.popsel.

This popsel will provide This popsel will provide you with “dynamic you with “dynamic parameters” once you parameters” once you have saved that will ask have saved that will ask you for all the you for all the information from the information from the popsel you created so popsel you created so that you can pull those that you can pull those students into your students into your popsel.popsel.

Dynamic parameters should always be used in popsel Dynamic parameters should always be used in popsel creation if possible.creation if possible.

Dynamic parameters are set up on GLRSLCT in the Dynamic parameters are set up on GLRSLCT in the value field as &WHATVALUEYOUWANT. GET_POPSEL value field as &WHATVALUEYOUWANT. GET_POPSEL (handout 6) is mostly comprised of dynamic values.(handout 6) is mostly comprised of dynamic values.

These then, pop up after the GLBDATA save as a 88 These then, pop up after the GLBDATA save as a 88 parameter that needs to be submitted before process parameter that needs to be submitted before process is run.is run.

This allows you to use the same popsel year after This allows you to use the same popsel year after year.year.

VIEWING GLBDATA VIEWING GLBDATA STUDENTSSTUDENTS

The two forms that can be used for The two forms that can be used for this are GLIEXTR and GLAEXTR.this are GLIEXTR and GLAEXTR.

GLAEXTR can be modified, deleted GLAEXTR can be modified, deleted and used for Manual popsel and used for Manual popsel entries.entries.

GLIEXTR students can be viewed in GLIEXTR students can be viewed in alphabetically and is a view screen alphabetically and is a view screen only.only.

Using the GUAOBJS set up, you can Using the GUAOBJS set up, you can pull spreadsheet lists from these pull spreadsheet lists from these screens.screens.

You can also run RORAPLT which is You can also run RORAPLT which is a report of GLBDATA students that a report of GLBDATA students that were selected. This report shows were selected. This report shows an alpha listing and ID numbers.an alpha listing and ID numbers.

GUAOBJS FORMGUAOBJS FORM In order to be able to pull a In order to be able to pull a

spreadsheet from a form, the spreadsheet from a form, the form needs to be set up here form needs to be set up here first.first.

The Data Extract option needs The Data Extract option needs to be selected and saved for to be selected and saved for the forms you wish for this the forms you wish for this option.option.

Search and find your form, Search and find your form, select the option and save.select the option and save.

NOTE: Not all will have modify NOTE: Not all will have modify access to this form and, not all access to this form and, not all forms can be set up for forms can be set up for spreadsheets.spreadsheets.

GLIEXTR, GLAEXTR and GLIEXTR, GLAEXTR and FGIBDST are wonderful forms FGIBDST are wonderful forms to use this process for.to use this process for.

FGIBDST can be pulled to FGIBDST can be pulled to spreadsheet and budgets can spreadsheet and budgets can be reconciled, great trick!be reconciled, great trick!

CREATING A CREATING A SPREADSHEET FROM A SPREADSHEET FROM A FORMFORM

From the GLIEXTR From the GLIEXTR or GLAEXTR form or GLAEXTR form for the popsel you for the popsel you want to have a want to have a listing for, go to listing for, go to HELP.HELP.

Choose the Extract Choose the Extract Data option. Data option.

You will see this You will see this pop up window pop up window and you select and you select

At this point you At this point you get another get another window.window.

Here you can Here you can choose SAVE or choose SAVE or OPEN. OPEN.

Choosing the Choosing the open open option:option:

You will have all You will have all

your data pulled your data pulled into a into a spreadsheet!spreadsheet!

This is done very This is done very quickly and now quickly and now you have a you have a spreadsheet to spreadsheet to edit and sort and edit and sort and print!print!

RORAPLTRORAPLT

The RORAPLT report The RORAPLT report will present you with will present you with a listing of students a listing of students in a popsel as well.in a popsel as well.

This process takes a This process takes a few minutes to run few minutes to run but will give you a but will give you a report of students report of students names and ID names and ID numbers.numbers.

MORE TIPS OR MORE TIPS OR QUESTIONS?QUESTIONS?

THANK YOU!THANK YOU!