LDP Known Issues

LDP not showing invoice charge

Poppy (one problem) and Quesnalia (another problem) may fix invoice input issues so there will be better data quality going forward

po_organization derived table

Fiscal Year field on Invoice

• New feature allows approval of invoices without fiscal year associated with it
• CUL will not use the feature
• Does not default to the current fiscal year - now it does
• not mandatory field - 
• only appears if permission is assigned, if you don't have the permission, the current fiscal year is assigned by default

Problems with patron data

Graduate students have been reverted to Undergraduate in the Patron Groups, except for about 300 records; this may impact all queries and dashboards using patron data

Unresolved

Fund 9

An invoice was inadvertently deleted out of the Fund screen in FOLIO, which means balances for fund 9 will be off by $18,000 until this is fixed.

Fixed as of 1/31/24

Resolved

Publisher

There have been some inconsistencies in the availability of the publication date and publisher on tables with this data. Advised to get this data from the instance, not the PO for now

Unresolved

Fully paid order still shows encumbrance

In orders reports, even though payment status is showing the “Fully Paid” value, there are still encumbrances showing

Unresolved

FY23-FY24 Fiscal Year Rollover Changes to Fiscal Year Dates

FY23-FY24 Fiscal Year Rollover Changes

• End date of FY2023 is 7/3/2023 and start date for FY2024 is 7/4/2023
• Cannot use 7/1/23 or 7/2/23 as FY24 start date
• Change needed to accommodate a lost book cost invoice under Fund 310 that was not attached to a PO
• Impacts on queries – will impact everything with a transaction
• Impacts on dashboards
• Date range and Fiscal Year may not match
• Add note to show changed date range
• No impacts to collection counts
• Notes on FY2024 change have been added to finance dashboards 

None

Date/Time in LDP not matching Folio

This issue has been found when using the folio_reporting.feesfines_account_actions. It may very well affect many other tables. The transaction date result from an SQL query using LDP is showing a timestamp difference of 4 hours.

If the query is referring to an original table where the time was recorded as Timestamptz, then the date in the derived table needs to be casted as Timestamptz, otherwise the system will add 4 hours automatically.

It will affect the query results when the date/time was recorded after 8PM UTC. Examples are Fees, Fines, loans etc. A review of all derived tables using dates should be done.

Example:

Existing code: json_extract_path_text(ff.data, 'dateAction') AS transaction_date,

Replace by: json_extract_path_text(fa.data, 'dateCreated') ::timestamptz AS transaction_date

As a temporary fix, if your query is affected by a date not casted as Timestamptz, update your query by changing the data type to Timestamptz. The results should show the correct date/time. Derived tables will need to be updated by the Folio Community.

Unresolved

This issue (along with several others) is scheduled to be fixed in https://github.com/folio-org/folio-analytics/issues/501

feesfines_comments table

feesfines-comments table not populating with comments

-not requested in reports yet

-not high priority, but good to know

Unresolved

The derived table "feesfines_accounts_actions" is no longer correct because the source table (public.feesfines_accounts) has changed

The derived table "feesfines_accounts_actions" is no longer correct because the source table (public.feesfines_accounts) has changed. Several fields in feesfines_accounts_actions are now blank.

We discussed this issue in the reporting development meeting. Angela will update the feesfines_accounts_actions derived table so that these fields populate from the public.feesfines_accounts table. She will also bring up the problem of not finding out about field changes to Product Council on 4/20/23.

• payment_status (object)
• payment_method
• fine_updated_date
• fine_date

Unresolved, but plan in place

srs_marctab instance count is low in LDP Production

reported to EBSCO

-The number of srs_marctab instances in the LDP Production instance is down by a factor of 10 compared to LDP Test (600 million down to 60 million)

-The number of srs_marctab instances in LDP Test is now down by 318624 records compared to 3/14/23

Resolution: Setting statement_timeout = 7200000 parameter for postgres addressed issue with srs_marc table not getting updated fully in LDP Test and LDP Prod

Unresolved

-still seeing intermittent problems

LDP system configuration setting addressed issue

Preserving Circulation Demographic data

Preserving Circulation Demographic data

• patron id is wiped from circulation tables when items are checked in, which results in losing connection to demographic data in custom fields for reporting
• cul derived local table to grab and save demographic data daily; does not get items checked out and returned in the same day
• need something like the circulation_transaction_stats table we had in Voyager
• take snapshot of loans_items, then save demographic (custom fields) data before the patron id is wiped
• may need to ask for a feature working with RA SIG
• needs to be discussed at FOLIO Governance
• Joanne has submitted a FOLIO Application enhancement request to create a way to retain patron demographics (department, college) in circulation loan history
• Tom Trutt submitting an enhancement request for this issue

Note: Metadb will include history for tables, so patron demographics could be gathered this way

Workaround in place

local_core.circ_snapshot3 updated automatically daily at 7am 

Call Number Order on Holdings-Level Call Numbers 

-see https://issues.folio.org/browse/UXPROD-3496

-this is a feature that allows you to order your lists of items in correct call number order with the holdings level call number

-Currently, we are unable to sort holdings record results by correct LC call number order. For item records, we can do this through the “effective_shelving_order” field in the inventory_items table (and with applying the “collate “C”” function in the ORDER BY part of the query). We can’t do this with holdings records.  It never comes out right. We had a JIRA ticket for this work, but we can’t find it.

Unresolved

Item Level Call Number no longer on some derived tables

-derived tables loans_items and items_holdings_instances no longer capture the item level call number, because the inventory_items table (which they reference)  has changed

-The derived tables “items_holdings_instances” and “loans_items” no longer contain the item call number, because the source table (inventory_items) no longer has that field. Instead, inventory_items has the call number components parsed out of the data blob (prefix, suffix and call number core). These components need to be included in a revision of the scripts for the two derived tables. Also, the item_ext derived table has been updated to include the parsed-out call number components, but still retains the non-functional item_level_call_number field.

Resolved

-Natalya has updated items_holdings_instances for Orchid 1.6 

-Natalya has updated loans_items for Orchid 1.6 

Orchid Breaking Changes for Subjects and Series

Breaking changes for Orchid release: updates to instance records (subjects and series) to capture changes associated with authority control

Cornell goes to Nolana - end of Feb, beginning of March

Cornell goes to Orchid - hopefully before Fiscal Year Rollover

• • https://folio-project.slack.com/archives/C210RP0T1/p1672757167354339
  • https://issues.folio.org/browse/MODQM-276

• subjects field will be converted from an array of strings to an array of objects.
• series field will be converted from an array of strings to an array of objects.
• alternativeTitles field object will be extended with authorityId field

The changes are mainly related to two fields: 
subjects field is now an array of objects: 
 

"properties": { 
  "value": { 
"type": "string", 
"description": "Subject heading value" 
  }, 
  "authorityId": { 
"type": "string", 
"description": "UUID of authority record that controls a subject heading", 
"$ref": "uuid.json" 
  } 
} 

series field is now an array of objects: 
 

"properties": { 
  "value": { 
"type": "string", 
"description": "Series title value" 
  }, 
  "authorityId": { 
"type": "string", 
"description": "UUID of authority record that controls an series title", 
"$ref": "uuid.json" 
  } 
} 

Resolved

-Natalya has updated these derived tables for Orchid 1.6 Folio Analytics release

-see more information in Subjects and Series documentation

Finance_Budgets table issue

• on the public.finance_budgets table for LDP1, several fields are not getting calculated properly due to the way the calculations are done in the FOLIO application fields

Workaround in place

Bug submitted, to be fixed in Poppy release

• discrepancy is between data in FOLIO and data shown the Fund Detail Tableau dashboard
• The issue is when the FYRO was run at the end of FY22 – the balances were moved forward under the transaction type Rollover transfer.  When capturing the Group view, the programmers didn’t take into account the Rollover transfer within the Net transfer on the Group view.  This has been submitted as a bug.  Unfortunately, this will not be addressed until the Orchid release.
• see https://issues.folio.org/browse/MODFIN-299
• Until FOLIO is updated – When looking at your Team information, please rely on the Tableau dashboard created by Nancy Bolduc.

• Cornell goes to Nolana - end of Feb, beginning of March

  Cornell goes to Orchid - hopefully before Fiscal Year Rollover

Unresolved 

Bug submitted, to be fixed in Orchid release

Cast all ids from tables that connect to srs_marctab as "::uuid"

Example code when joining srs_marctab AS sm to srs_records AS sr

        LEFT JOIN srs_marctab AS sm ON sr.id::uuid = sm.srs_id 

The srs_marctab table can be connected to the public.inventory_instances table via hrid, and the instance_id can be brought in from the inventory_instances table.

Example code:

SELECT
    ii.id AS instance_id,
    ii.title AS title,
    substring(sm."content", 7, 2) AS "format_type"
FROM 
    public.inventory_instances AS ii
LEFT JOIN srs_marctab AS sm
    ON sm.instance_hrid =ii.hrid

WHERE sm.field = '000'

Another way to get data from the srs_marctab table and insert a correct instance_id is via the srs_records table.

Example code:

SELECT
        sr.external_id AS instance_id,    
        substring (sm.content,7,2) AS format_code
FROM
        srs_records AS sr
        LEFT JOIN srs_marctab AS sm ON sr.id::uuid = sm.srs_id 
 WHERE sm.field = '000'     

  --NOTE that all ids in the srs_marctab table have been cast as uuids, so cast the OTHER table's id as ::uuid 
        

Resolved, but still has the contents field data that includes HTML tags

Upgrade 1.8 will solve this issue (update date to be determined). As of 10-17-22, LDP Test (1.8.2) shows the return of the Notes table.

-Sharon to ask Nassib