Mastering Valuable Information Queries
I
n
t
r
o
t
o
M
I
S
-
M
G
S
3
5
1
Obtain Valuable Information
Using Queries
Chapter 4
C
h
a
p
t
e
r
O
v
e
r
v
i
e
w
Queries
List Views
Formula Fields
Mathematical Operators and Functions
(+,-,*,/)
Text Operators (Concatenation)
Logical Operators and Functions (IF)
Salesforce Object Query Language
Q
u
e
r
i
e
s
Allow you to ask questions (queries)
about data in the database.
Accomplished through the use of:
List Views
Limit fields displayed
Sort and filter results
Apply filter logic
Salesforce Object Query Language
List View Examples
W
h
o
i
s
i
n
M
G
S
3
5
1
s
o
r
t
e
d
b
y
p
e
r
s
o
n
n
u
m
b
e
r
?
W
h
o
a
r
e
J
u
n
i
o
r
s
i
n
M
G
S
3
5
1
?
W
h
o
a
r
e
F
r
,
S
o
,
a
n
d
J
r
s
t
u
d
e
n
t
s
i
n
M
G
S
3
5
1
?
W
h
o
d
o
e
s
n
’
t
h
a
v
e
a
g
r
a
d
e
y
e
t
?
W
h
o
h
a
s
a
l
a
s
t
n
a
m
e
b
e
g
i
n
n
i
n
g
w
i
t
h
t
h
e
l
e
t
t
e
r
M
?
W
h
o
h
a
s
a
G
P
A
g
r
e
a
t
e
r
t
h
a
n
3
.
5
?
W
h
o
h
a
s
a
G
P
A
b
e
t
w
e
e
n
3
.
2
a
n
d
3
.
5
?
W
h
o
i
s
a
S
e
n
i
o
r
O
R
A
c
c
o
u
n
t
i
n
g
m
a
j
o
r
?
W
h
o
i
s
a
S
e
n
i
o
r
A
N
D
A
c
c
o
u
n
t
i
n
g
m
a
j
o
r
?
W
h
o
i
s
a
J
r
o
r
S
r
a
n
d
a
M
g
m
t
(
M
G
)
m
a
j
o
r
?
W
h
o
i
s
a
J
r
o
r
S
r
a
n
d
a
(
M
G
)
m
a
j
o
r
w
i
t
h
a
g
r
a
d
e
o
f
A
o
r
B
?
W
h
o
i
s
a
n
o
t
a
F
r
e
s
h
m
a
n
?
F
o
r
m
u
l
a
F
i
e
l
d
s
Can perform simple calculations and
complex operations to display
calculated results.
Number of days since last sales contact
Profit margin calculation
Volume discount calculation
Account rating
Math, Text, Logical, Date, Summary
and Advanced Functions
F
o
r
m
u
l
a
F
i
e
l
d
-
M
a
t
h
E
x
a
m
p
l
e
s
Add, Subtract, Multiply and Divide
Exponents
Order of Operations using ( )
MIN, MAX, SQRT, ABS, MOD
ROUND
M
a
t
h
C
a
l
c
u
l
a
t
i
o
n
-
A
v
e
r
a
g
e
E
x
a
m
( Midterm__c + Final__c ) / 2
F
o
r
m
u
l
a
F
i
e
l
d
-
T
e
x
t
E
x
a
m
p
l
e
s
Concatenate (&)
LEN
LEFT, MID, RIGHT
LOWER, UPPER
VALUE, TEXT
BEGINS, CONTAINS
FIND, SUBSTITUTE
TRIM
C
o
n
c
a
t
e
n
a
t
i
o
n
Used to combine multiple fields
together or to add extra formatting in
a formula field.
The ampersand sign
&
connects
multiple fields and strings of text.
Fields are referenced by their
field
name
and
text strings
are enclosed
in double quotes.
C
o
n
c
a
t
e
n
a
t
i
o
n
-
F
u
l
l
N
a
m
e
First_Name__c
&
" "
&
Last_Name__c
C
o
n
c
a
t
e
n
a
t
i
o
n
-
N
a
m
e
M
a
j
o
r
Full_Name__c
&
" ("&
Major__c
&
")"
C
o
n
c
a
t
e
n
a
t
i
o
n
-
G
P
A
G
r
a
d
e
"GPA: "
&
TEXT(GPA__c)
&
" - Grade: "
&
Grade__c
Space
Space
F
o
r
m
u
l
a
F
i
e
l
d
-
L
o
g
i
c
a
l
E
x
a
m
p
l
e
s
=, ==, !=, <>, <, >, <=, >=, &&, ||
IF
AND, OR , NOT
ISBLANK, ISNULL, ISNUMBER
CASE
I
F
E
x
p
r
e
s
s
i
o
n
Use to conditionally evaluate data, and
dynamically generate output based on it.
IF(
logical_test
,
output_if_true
,
output_if_false
)
In other words, a formula field can be
created to display “Graduating” for seniors
and “Continuing” for all other students.
I
F
(
C
l
a
s
s
i
s
e
q
u
a
l
t
o
S
R
,
d
i
s
p
l
a
y
G
r
a
d
u
a
t
i
n
g
,
o
t
h
e
r
w
i
s
e
d
i
s
p
l
a
y
C
o
n
t
i
n
u
i
n
g
)
I
F
E
x
p
r
e
s
s
i
o
n
-
S
t
a
t
u
s
IF(
Class__c = "SR"
, "
Graduating
", "
Continuing
")
I
F
E
x
p
r
e
s
s
i
o
n
-
L
e
v
e
l
Display “
Underclassman
” for FR and SO students
and “
Upperclassman
” for JR and SR students
IF(
OR (Class__c ="FR", Class__c ="SO")
,
"
Underclassman
", "
Upperclassman
")
N
e
s
t
e
d
I
F
E
x
p
r
e
s
s
i
o
n
-
P
e
r
f
o
r
m
a
n
c
e
Display the performance rating for each
student based on their course grade.
Satisfactory
[A, A-, B+, B, B-, C+, C]
Marginal
[C-, D]
Unsatisfactory
[F]
IF(
Grade__c = "F"
, "
Unsatisfactory
",
IF(
OR (Grade__c ="D", Grade__c = "C-"
),
"
Marginal
", "
Satisfactory
"))
N
e
s
t
e
d
I
F
E
x
p
r
e
s
s
i
o
n
-
P
e
r
f
o
r
m
a
n
c
e
IF(
Grade__c = "F"
, "
Unsatisfactory
",
IF(
OR (Grade__c ="D", Grade__c = "C-"
),
"
Marginal
", "
Satisfactory
"))
Who has a last name exactly six
characters long?
Sort a list view by Class order - Freshman,
Sophomore, Junior, Senior
C
h
a
l
l
e
n
g
e
P
r
o
b
l
e
m
s
S
a
l
e
s
f
o
r
c
e
O
b
j
e
c
t
Q
u
e
r
y
L
a
n
g
u
a
g
e
-
S
O
Q
L
Delve into the world of information queries in MIS, unlocking the power to extract valuable insights using various operators and functions, including mathematical and logical operations, text concatenation, and more. Explore the Salesforce Object Query Language to formulate precise queries, optimize list views, and filter data effectively for enhanced decision-making.
Download Presentation
Please find below an Image/Link to download the presentation.
The content on the website is provided AS IS for your information and personal use only. It may not be sold, licensed, or shared on other websites without obtaining consent from the author.If you encounter any issues during the download, it is possible that the publisher has removed the file from their server.
You are allowed to download the files provided on this website for personal or commercial use, subject to the condition that they are used lawfully. All files are the property of their respective owners.
The content on the website is provided AS IS for your information and personal use only. It may not be sold, licensed, or shared on other websites without obtaining consent from the author.
E N D
Presentation Transcript
Intro to MIS - MGS351 Obtain Valuable Information Using Queries Chapter 4
Chapter Overview Queries List Views Formula Fields Mathematical Operators and Functions (+,-,*,/) Text Operators (Concatenation) Logical Operators and Functions (IF) Salesforce Object Query Language
Queries Allow you to ask questions (queries) about data in the database. Accomplished through the use of: List Views Limit fields displayed Sort and filter results Apply filter logic Salesforce Object Query Language
Who has a last name beginning with the letter M?
Who is a Jr or Sr and a (MG) major with a grade of A or B?
Formula Fields Can perform simple calculations and complex operations to display calculated results. Number of days since last sales contact Profit margin calculation Volume discount calculation Account rating Math, Text, Logical, Date, Summary and Advanced Functions
Formula Field - Math Examples Add, Subtract, Multiply and Divide Exponents Order of Operations using ( ) MIN, MAX, SQRT, ABS, MOD ROUND
Math Calculation - Average Exam ( Midterm__c + Final__c ) / 2
Formula Field - Text Examples Concatenate (&) LEN LEFT, MID, RIGHT LOWER, UPPER VALUE, TEXT BEGINS, CONTAINS FIND, SUBSTITUTE TRIM
Concatenation Used to combine multiple fields together or to add extra formatting in a formula field. The ampersand sign & connects multiple fields and strings of text. Fields are referenced by their field name and text strings are enclosed in double quotes.
Concatenation - Full Name First_Name__c & " " & Last_Name__c
Concatenation - Name Major Full_Name__c & " (" & Major__c & ")"
Concatenation - GPA Grade "GPA: " & TEXT(GPA__c) & " - Grade: " & Grade__c Space Space
Formula Field - Logical Examples =, ==, !=, <>, <, >, <=, >=, &&, || IF AND, OR , NOT ISBLANK, ISNULL, ISNUMBER CASE
IF Expression Use to conditionally evaluate data, and dynamically generate output based on it. IF(logical_test, output_if_true, output_if_false) In other words, a formula field can be created to display Graduating for seniors and Continuing for all other students. IF(Class is equal to SR, display Graduating, otherwise display Continuing)
IF Expression - Status IF(Class__c = "SR", "Graduating", "Continuing")
IF Expression - Level Display Underclassman for FR and SO students and Upperclassman for JR and SR students IF(OR (Class__c ="FR", Class__c ="SO"), "Underclassman", "Upperclassman")
Nested IF Expression - Performance Display the performance rating for each student based on their course grade. Satisfactory Marginal Unsatisfactory [A, A-, B+, B, B-, C+, C] [C-, D] [F] IF(Grade__c = "F", "Unsatisfactory", IF(OR (Grade__c ="D", Grade__c = "C-"), "Marginal", "Satisfactory"))
Nested IF Expression - Performance IF(Grade__c = "F", "Unsatisfactory", IF(OR (Grade__c ="D", Grade__c = "C-"), "Marginal", "Satisfactory"))
Challenge Problems Who has a last name exactly six characters long? Sort a list view by Class order - Freshman, Sophomore, Junior, Senior
