*** This version of Confluence is for testing only and contains a copy of content from June 29th 2026. No changes will be preserved. ***
Why
...
would
...
you
...
need
...
to
...
include
...
number
...
of
...
pages
...
in
...
a
...
report?
physical
collections
inventory
planning
collection
shifts,
especially
when
books
are
not
located
together
and
direct
measurement
(linear
feet)
is
not
feasible
estimating
the
number
of
trucks
needed
to
move
materials
to
the
Annex
or
another
location
doing
tracers;
height
of
volume
and
number
of
pages
give
an
approximation
of
the
size
of
the
item
being
searched
for
counting
the
number
of
exposures
needed
to
scan
or
copy
a
book
Where
...
are
...
page
...
number
...
data
...
fields
...
stored?
in
the
MARC
record,
field
300
is
the
physical
description,
and
subfield
"a"
contains
the
page
numbers
or
"leaves"
(note
that
the
"300"
field
and
the
"a"
subfield
are
both
repeatable
in
a
record).
See Marc21
for
details.
What
is
a
leaf?
A
leaf
is
a
piece
of
paper
printed
on
one
side.
A
"page"
is
a
piece
of
paper
printed
on
2
sides
(e.g.
100
pages
is
50
pieces
of
paper
printed
on
2
sides)
The
"folio_derived.instance_physical_descriptions"
derived
table
is
created
from
the
instance
physical
descriptions
array,
but
shows
the
entire
300
field,
without
subfield
indicators. See
for
this
table.
(Note
that
instance
records
that
do
not
have
a
physical
descriptions
array
will
not
be
in
the
derived
table.)
NOTE:
Some
electronic
resources
have
a
300
field
showing
page
numbers,
if
the
original
form
of
the
work
was
print
material
The
...
example
...
below
...
shows
...
an
...
online
...
resource
...
with
...
"236
...
pages."
...
If
...
your
...
query
...
is
...
only
...
pulling
...
items
...
from
...
a
...
physical
...
location,
...
you
...
should
...
not
...
get
...
any
...
electronic
...
resources.
...
However,
...
it
...
is
...
good
...
to
...
be
...
aware
...
that
...
electronic
...
resources
...
may
...
have
...
physical
...
descritptions
...
inherited
...
from
...
the
...
source
...
material. 
Note
...
that
...
physical
...
descriptions
...
may
...
be
...
entered
...
in
...
many
...
different
...
ways
...
and
...
have
...
a
...
high
...
degree
...
of
...
complexity;
...
parsing
...
out
...
the
...
page
...
count
...
through
...
regular
...
expressions
...
(the
...
methods
...
used
...
here)
...
will
...
never
...
be
...
completely
...
accurate.
Code
...
to
...
extract
...
the
...
physical
...
description
...
using
...
derived
...
tables
This
...
method
...
finds
...
an
...
estimated
...
page
...
count
...
from
...
the
...
instance_physical_descriptions
...
derived
...
table.
...
Because
...
the
...
physical
...
description
...
field
...
of
...
the
...
instance
...
record
...
is
...
a
...
text-entry
...
field
...
that
...
has
...
no
...
enforced
...
data
...
entry
...
format,
...
the
...
parsing
...
statement
...
will
...
not
...
yield
...
highly
...
accurate
...
results.
...
It
...
works
...
in
...
cases
...
where
...
a
...
page
...
count
...
number
...
is
...
followed
...
by
...
"p."
...
or
...
"pages"
...
or
...
"leaves"
...
or
...
"l."
...
and
...
when
...
the
...
first
...
number
...
in
...
the
...
description
...
field
...
(reading
...
from
...
left
...
to
...
right)
...
is
...
the
...
actual
...
number
...
of
...
pages
...
or
...
leaves
...
and
...
not
...
some
...
other
...
value.
--define
...
a
...
parameters
...
Common
...
Table
...
Expression
...
(subquery
...
or
...
CTE)
...
so
...
the
...
location
...
filter
...
can
...
be
...
easily
...
changed
...
in
...
one
...
place
--enter
...
a
...
location
...
code
...
between
...
the
...
quote
...
marks
...
below
--if
...
left
...
blank,
...
the
...
query
...
will
...
get
...
all
...
locations
...
except
...
for
...
'serv,remo'
WITH
...
parameters
...
AS
...
...
(
...
...
SELECT
...
...
...
...
'law,ref'
...
AS
...
location_code_filter
...
...
)
--Select
...
distinct
...
rows
...
to
...
reduce
...
duplicates
...
that
...
may
...
result
...
from
...
the
...
joins
--and
...
bringing
...
in
...
data
...
fields
...
from
...
holdings
...
records
...
(in
...
this
...
case)
SELECT
...
DISTINCT
...
...
ipd.instance_id,
...
...
...
...
...
...
...
...
...
...
...
--Instance
...
UUID
...
from
...
the
...
physical
...
description
...
table
...
...
ipd.instance_hrid,
...
...
...
...
...
...
...
...
...
...
--Instance
...
HRID
...
from
...
the
...
physical
...
description
...
table
...
...
instext.title,
...
...
...
...
...
...
...
...
...
...
...
...
--Title
...
from
...
the
...
instance
...
extension
...
table
...
...
ll.location_name,
...
...
...
...
...
...
...
...
...
...
--Human-readable
...
location
...
name
...
...
he.call_number,
...
...
...
...
...
...
...
...
...
...
...
--Call
...
number
...
from
...
the
...
holdings
...
extension
...
table
...
...
ipd.physical_description,
...
...
...
...
...
...
--Raw
...
physical
...
description
...
text
...
...
CASE
...
...
...
...
--Check
...
whether
...
the
...
physical
...
description
...
contains
...
a
...
1-
...
to
...
4-digit
...
number
...
...
...
...
WHEN
...
substring(ipd.physical_description,
...
'\d{1,4}')
...
IS
...
NOT
...
NULL
...
...
...
...
--Check
...
whether
...
the
...
description
...
looks
...
like
...
it
...
refers
...
to
...
pages
...
or
...
leaves
...
...
...
...
AND
...
(
...
...
...
...
...
...
ipd.physical_description
...
LIKE
...
'%page%'
...
...
...
...
...
...
OR
...
ipd.physical_description
...
LIKE
...
'%p.%'
...
...
...
...
...
...
OR
...
ipd.physical_description
...
LIKE
...
'%leaves%'
...
...
...
...
...
...
OR
...
ipd.physical_description
...
LIKE
...
'%
...
l.%'
...
...
...
...
)
...
...
...
...
--If
...
both
...
conditions
...
are
...
true,
...
extract
...
the
...
first
...
1-
...
to
...
4-digit
...
number
...
...
...
...
--and
...
cast
...
it
...
to
...
an
...
integer
...
as
...
the
...
page
...
count
...
estimate
...
...
...
...
THEN
...
substring(ipd.physical_description,
...
'\d{1,4}')::INT
...
...
...
...
--If
...
no
...
usable
...
page
...
or
...
leaf
...
count
...
is
...
found,
...
return
...
null
...
...
...
...
ELSE
...
NULL
...
...
END
...
AS
...
page_count_estimate,
...
...
--ordinality
...
shows
...
the
...
order
...
of
...
the
...
physical
...
description
...
entry
...
from
...
the
...
record
...
...
--in
...
cases
...
where
...
there
...
is
...
more
...
than
...
one
...
...
ipd.physical_description_ordinality
...
--Start
...
with
...
the
...
instance
...
extension
...
table
...
to
...
get
...
title-level
...
data
FROM
...
folio_derived.instance_ext
...
AS
...
instext
...
...
--Join
...
to
...
holdings
...
to
...
bring
...
in
...
call
...
number
...
and
...
location
...
links
...
...
LEFT
...
JOIN
...
folio_derived.holdings_ext
...
AS
...
he
...
...
...
...
ON
...
instext.instance_id
...
=
...
he.instance_id
...
...
...
--Join
...
to
...
locations/libraries
...
to
...
get
...
location
...
names
...
and
...
location
...
codes
...
...
LEFT
...
JOIN
...
folio_derived.locations_libraries
...
AS
...
ll
...
...
...
...
ON
...
he.permanent_location_id
...
=
...
ll.location_id
...
...
...
--Join
...
to
...
instance
...
physical
...
descriptions
...
to
...
get
...
the
...
physical
...
description
...
text
...
...
LEFT
...
JOIN
...
folio_derived.instance_physical_descriptions
...
AS
...
ipd
...
...
...
...
ON
...
instext.instance_id
...
=
...
ipd.instance_id
--Apply
...
the
...
location
...
filter
WHERE
...
...
CASE
...
...
...
...
--If
...
the
...
location
...
filter
...
parameter
...
is
...
blank,
...
return
...
all
...
locations
...
except
...
'serv,remo'
...
...
...
...
WHEN
...
(SELECT
...
location_code_filter
...
FROM
...
parameters)
...
=
...
''
...
...
...
...
...
...
THEN
...
ll.location_code
...
!=
...
'serv,remo'
...
...
...
...
--Otherwise,
...
return
...
only
...
the
...
specific
...
location
...
code
...
entered
...
in
...
the
...
parameters
...
CTE
...
...
...
...
ELSE
...
ll.location_code
...
=
...
(SELECT
...
location_code_filter
...
FROM
...
parameters)
...
...
END
;
Result:
Code to
...
extract
...
the
...
physical
...
description
...
from
...
the
...
instance
...
table
...
using
...
a
...
data
...
array
...
extraction
...
function
This
...
method
...
extracts
...
the
...
"physical
...
descriptions"
...
array
...
from
...
the
...
instance
...
record;
...
records
...
that
...
do
...
not
...
have
...
a
...
physical
...
descriptions
...
array
...
will
...
NOT
...
drop
...
out,
...
because
...
the
...
"left
...
join
...
lateral"
...
function
...
used
...
in
...
this
...
query
...
will
...
keep
...
all
...
instance
...
records
...
in
...
the
...
results
...
set,
...
including
...
those
...
that
...
do
...
not
...
have
...
array
...
values
...
for
...
physical
...
descriptions.
As
...
stated
...
above,
...
because
...
the
...
physical
...
description
...
field
...
of
...
the
...
instance
...
record
...
is
...
a
...
text-entry
...
field
...
that
...
has
...
no
...
enforced
...
data
...
entry
...
format,
...
the
...
parsing
...
statement
...
will
...
not
...
yield
...
highly
...
accurate
...
results.
...
It
...
works
...
in
...
cases
...
where
...
a
...
page
...
count
...
number
...
is
...
followed
...
by
...
"p."
...
or
...
"pages"
...
or
...
"leaves"
...
or
...
"l."
...
and
...
when
...
the
...
first
...
number
...
in
...
the
...
description
...
field
...
(reading
...
from
...
left
...
to
...
right)
...
is
...
the
...
actual
...
number
...
of
...
pages
...
or
...
leaves,
...
and
...
not
...
some
...
other
...
value.
--define
...
a
...
parameters
...
Common
...
Table
...
Expression
...
(subquery
...
or
...
CTE)
...
so
...
the
...
location
...
filter
...
can
...
be
...
easily
...
changed
...
in
...
one
...
place
--enter
...
a
...
location
...
code
...
between
...
the
...
quote
...
marks
...
below
--if
...
left
...
blank,
...
the
...
query
...
will
...
get
...
all
...
locations
...
except
...
for
...
'serv,remo'
WITH
...
parameters
...
AS
...
...
(
...
...
SELECT
...
...
...
...
'law,ref'
...
AS
...
location_code_filter
...
...
...
...
--Enter
...
a
...
location
...
code
...
between
...
the
...
quote
...
marks
...
...
...
...
--If
...
left
...
blank,
...
the
...
query
...
will
...
return
...
all
...
locations
...
except
...
'serv,remo'
...
...
)
--Select
...
distinct
...
rows
...
to
...
reduce
...
duplicates
...
that
...
may
...
result
...
from
...
the
...
joins
--and
...
bringing
...
in
...
data
...
fields
...
from
...
holdings
...
records
...
(in
...
this
...
case)
SELECT
...
DISTINCT
...
...
...
AS
...
instance_id,
...
...
...
...
...
...
...
...
...
...
...
...
...
--Instance
...
UUID
...
...
instance.jsonb#>>'{hrid}'
...
AS
...
instance_hrid,
...
...
...
...
...
...
--Extract
...
instance
...
HRID
...
from
...
JSONB
...
...
instance.jsonb#>>'{title}'
...
AS
...
title,
...
...
...
...
...
...
...
...
...
--Extract
...
title
...
from
...
JSONB
...
...
...
AS
...
location_name,
...
...
...
...
...
...
...
...
...
...
--Human-readable
...
location
...
name
...
...
holdings_record__t.call_number,
...
...
...
...
...
...
...
...
...
...
...
--Call
...
number
...
from
...
holdings
...
...
phys.jsonb#>>'{}'
...
AS
...
field_300,
...
...
...
...
...
...
...
...
...
...
...
--Full
...
text
...
of
...
each
...
physical
...
description
...
(MARC
...
300
...
equivalent)
...
...
CASE
...
...
...
...
--Check
...
whether
...
the
...
physical
...
description
...
contains
...
a
...
1-
...
to
...
4-digit
...
number
...
...
...
...
WHEN
...
substring(phys.jsonb#>>'{}',
...
'\d{1,4}')
...
IS
...
NOT
...
NULL
...
...
...
...
--Check
...
whether
...
the
...
description
...
appears
...
to
...
refer
...
to
...
pages
...
or
...
leaves
...
...
...
...
AND
...
(
...
...
...
...
...
...
phys.jsonb#>>'{}'
...
LIKE
...
'%page%'
...
...
...
...
...
...
OR
...
phys.jsonb#>>'{}'
...
LIKE
...
'%p.%'
...
...
...
...
...
...
OR
...
phys.jsonb#>>'{}'
...
LIKE
...
'%leaves%'
...
...
...
...
...
...
OR
...
phys.jsonb#>>'{}'
...
LIKE
...
'%
...
l.%'
...
...
...
...
)
...
...
...
...
--If
...
both
...
conditions
...
are
...
true,
...
extract
...
the
...
first
...
number
...
and
...
cast
...
to
...
integer
...
...
...
...
THEN
...
substring(phys.jsonb#>>'{}',
...
'\d{1,4}')::INT
...
...
...
...
--Otherwise
...
return
...
null
...
when
...
no
...
usable
...
page
...
count
...
is
...
found
...
...
...
...
ELSE
...
NULL
...
...
END
...
AS
...
page_count_estimate,
...
...
--ordinality
...
shows
...
the
...
order
...
of
...
the
...
physical
...
description
...
entry
...
from
...
the
...
record
...
--in
...
cases
...
where
...
there
...
is
...
more
...
than
...
one
...
...
phys.ordinality
...
AS
...
record_sequence_ordinality
...
...
...
...
--Start
...
from
...
the
...
instance
...
table,
...
which
...
stores
...
JSONB
...
records
...
for
...
bibliographic
...
instances
FROM
...
folio_inventory.instance
--LEFT
...
JOIN
...
LATERAL
...
allows
...
you
...
to
...
extract
...
the
...
physical
...
descriptions
...
array
--for
...
each
...
instance
...
record
...
whether
...
or
...
not
...
it
...
has
...
values
...
in
...
that
...
array
...
...
LEFT
...
JOIN
...
LATERAL
...
...
...
...
jsonb_array_elements(
...
...
...
...
...
...
jsonb_extract_path(instance.jsonb,
...
'physicalDescriptions')
...
...
...
...
)
...
WITH
...
ORDINALITY
...
AS
...
phys(jsonb)
...
...
...
...
ON
...
true
...
...
--Join
...
to
...
holdings
...
to
...
bring
...
in
...
call
...
number
...
and
...
location
...
reference
...
...
LEFT
...
JOIN
...
folio_inventory.holdings_record__t
...
...
...
...
ON
...
...
=
...
holdings_record__t.instance_id
...
...
--Join
...
to
...
location
...
table
...
to
...
get
...
location
...
names
...
and
...
codes
...
...
LEFT
...
JOIN
...
folio_inventory.location__t
...
...
...
...
ON
...
holdings_record__t.permanent_location_id
...
=
...
location__t.id
--Apply
...
the
...
location
...
filter
...
based
...
on
...
the
...
parameter
...
value
WHERE
...
...
CASE
...
...
...
...
--If
...
the
...
parameter
...
is
...
blank,
...
return
...
all
...
locations
...
except
...
'serv,remo'
...
...
...
...
WHEN
...
(SELECT
...
location_code_filter
...
FROM
...
parameters)
...
=
...
''
...
...
...
...
...
...
THEN
...
location__t.code
...
!=
...
'serv,remo'
...
...
...
...
--Otherwise
...
return
...
only
...
rows
...
matching
...
the
...
specified
...
location
...
code
...
...
...
...
ELSE
...
location__t.code
...
=
...
(SELECT
...
location_code_filter
...
FROM
...
parameters)
...
...
END
;
Result:
Physical Descriptions array example from the instance table:
...