3C Communications: Custom Extract Data Queries (CEDQs)
Purpose: The purpose of this document is to provide information about Custom Extract Data Queries (CEDQs) that are available within Communication Data Sources.
Audience: CS Support Staff
Custom Extract Data Queries (CEDQs) are added to Communication Data Sources to pull data to populate live fields in 3C Communications. By default, Communication Data Sources provide bio demo information, such as names and addresses, of communication recipients. CEDQs are used to pull other data that are not already included in the Communication Data Source. Data examples include, but are not limited to, the following:
- Student residency statuses
- Plan code descriptions
- Term and class descriptions
- Information about enrollment appointments
- Aid awarded and aid disbursed
- Balance owed and balanced paid
- Significant term dates
Any information in ctcLink that can be queried can also be pulled by a CEDQ.
CEDQs are run by ctcLink when a 3C Communication is generated. The results from the query can then be added to live fields in 3C Communications. CEDQs can create highly personalized and detailed communications and are an essential tool for colleges that use the 3C Communications functionality.
Queries must be built in a very specific way to be used as CEDQs. The following describes how CEDQs must be built to be used by a Communication Data Source; CEDQ conventions used in ctcLink; using CEDQs in 3C Communications; and CEDQ best practices.
Building Custom Extract Data Queries (CEDQs)
The primary record in a CEDQ is a bind record. The bind record corresponds with the Administrative Function of the Communication Data Source that the CEDQ will be added. A full list of bind records is found at the end of the 3C Communications: Administrative Functions QRG.
Once the bind record is selected, additional records can be joined to create the CEDQ.
The prompts used in a "regular" query are very different from those used in a CEDQ. In ctcLink, queries normally contain a lookup prompt for Institution. They may also contain prompts for other criteria to further narrow search results. CEDQs, on the other hand, only use prompts for the variable data values associated with the query's bind record.
CEDQ prompts are not "user friendly" in the way that query prompts usually are. This is because CEDQs are intended to be run by ctcLink when a 3C Communication is generated and not by an average Query Viewer user. Behind the scenes, CEDQ prompts must meet the following criteria to function as intended:
- The Edit Type is always No Table Edit. Prompt Tables are never used for CEDQ prompts. Using a Prompt Table will prevent the query from being used as a CEDQ.
- The prompt is never optional.
- The Unique Prompt Name exactly matches the variable data value in the query's bind record. Using default Unique Prompt Name values will prevent the query from being used as a CEDQ.
- The prompts included in a CEDQ exactly match the variable data values used by the CEDQ's bind record. Additional prompts, such as prompts for Institution, are never added to a CEDQ.
Query security is often enforced through query prompts. A common example is a prompt for an Institution with selectable values that match the user's SACR security. Because CEDQ queries cannot use lookup prompts and prompts must match the variable data found in the CEDQ bind record, query security is enforced using an inner join to GRV_INSTITU_TBL.
CEDQs are intended to be run by ctcLink when a 3C Communication is generated, but they can also be run in Query Viewer. Users will encounter two significant differences when running a CEDQ compared to a "normal" query:
- The entered prompt values must exactly match data that exists in ctcLink. For a SENR-based query, this would mean entering a real combination of EMPLID, ACAD_CAREER, STRM, and CLASS_NBR as it exists in ctcLink. Results would be returned if the student has activity within the specified class within the specified term. If the student does not have enrollment activity associated with that specific class number in that specific term, no results will be found.
- Data will only be returned for one student at a time. Most CEDQs are configured to only return one line of results, though some are configured to return more than one line.
CEDQs are named using the following convention to be easily identified:
CEDQs are also stored within the COMMUNICATIONS folder. CEDQ descriptions provide a short, detailed description of the CEDQ's function, note that the query is a CEDQ, and list the Administrative Function associated with the query's bind record at the end of the query description. For example, Class Detail CEDQ (SENR) and Shopping Cart CEDQ (STRM).
The query long description, which is visible in Query Manager and metaLink, lists the general type of data that is being pulled as well as the Administrative Function that is associated with the CEDQ. The long description should also note that the query is a CEDQ, describe what CEDQs are used for, and include a warning that the CEDQ should not be modified without builder consent and without first researching to determine how the modifications would impact current 3C Communications.
Query developers should consider the following when building and modifying CEDQs:
- Prompts must exactly match the variable data of the bind record used to build the query. Including additional prompts, leaving off a prompt, or using prompt lookup tables will prevent the CEDQ from functioning as intended. In addition, the Unique Prompt Name must exactly match the variable data value in the query's bind record.
- Query security is enforced with a subquery that uses the SCRTY_TBL_INST record to limit query results based on the user's SACR security.
- Build simple CEDQs that consist of a bind record and a few other select records. The most versatile and easy-to-use CEDQs function as a "data dump" of certain types of data. The more complex a CEDQ becomes, the harder it is to test and maintain. It is better to use multiple CEDQs to return multiple types of data than it is to try and create a single CEDQ that returns all of the data that needs to be included in a communication. It is also better to build simple CEDQs that can be used for multiple communications and in multiple Communication Data Sources than a single CEDQ that has limited use.
- Do not build a CEDQ for bio demo data such as names or addresses. Communication Data Sources already provide recipient names and contact information in multiple formats.
- It is worthwhile to add descriptive Field Names so that 3C Communication builders can more easily identify what type of data is returned by certain CEDQ fields, especially if those fields are expressions or contain long descriptions.
- Build and test CEDQs thoroughly in PCD before migrating them to Production. Testing should include adding the CEDQ to a Communication Data Source and generating 3C Communications to ensure that the communication output looks as expected.
- Do not modify CEDQs without first thoroughly researching how the modification will impact 3C Communications that use results from the CEDQ. Adding additional fields to the end of the query is generally a safe modification with little to no negative impact. Renaming or removing fields, adding or removing records, or adjusting query criteria has the potential to significantly impact 3C Communications for multiple colleges. Submit a ticket to the CS Support team if you are unsure how to modify a CEDQ query or what communications will be impacted by the modification.
CEDQs are added to Communication Data Sources by the CS Support team. Submit a ticket to have a Communication Data Source updated with a CEDQ.
CEDQs usually are built to return a single line of results, but some are built to return more than one line when applicable. The Max Nbr field determines how many of the lines returned by a CEDQ will be made available for use in a 3C Communication. A blank Max Nbr field does not limit CEDQ results, whereas a value of "1" would limit useable results to the first line returned by the query.
Multiple 3C Communications can use the same Communication Data Source. These communications do not need to use live fields from all (or even any) of the CEDQs added to the Communication Data Source. A Communication Data Source may include multiple CEDQs that are only used by some 3C Communications.