Effective Dated Records

[Samar A]

How do you create/manage effective dated custom tables in SNow?

For example, I have a tutor table to track availability.

Tutor Table:

Tutor-Name Effective-Date Effective-Status Days

Tutor-1         01/01/2024    Active                  Monday

Tutor-1         05/01/2024    Inactive

Tutor-1         08/01/2024    Active                  Friday

Tutor-2        02/01/2024    Active                   Tuesday

Tutor-2       07/01/2024    Inactive

Tutor View should only list the current records [the tutor record with max(effective date) <= today], in this example:

Tutor-1        08/01/2024    Active                  Friday

Tutor-2       07/01/2024    Inactive

When I select a tutor then the tutor page would list all the historic records.

How SNow allows you to build current records vs history records?

Thanks.

[Manoj89]

Hi Samar,

Not sure what are you trying to achieve here, your requirement more or less sounds like the requirement for dashboard and drilldown reports! 

[jasonmarvinsc]

• Create the Tutor Table: Define fields like Tutor-Name, Effective-Date, Effective-Status, and Days.

• Define a Business Rule for Current Records: To display only current records (the latest record with an Effective-Date less than or equal to today), create a Business Rule or a Script Include with a GlideRecord query. Use a max() function to get the latest effective date for each tutor.

  Example query:

  var gr = new GlideRecord('x_custom_tutor_table'); gr.addQuery('effective_date', '<=', gs.nowDateTime()); gr.orderByDesc('effective_date'); gr.query();

• Create Views:

  • Current View: Use a GlideRecord query to fetch only the latest effective records, similar to the example above.
  • Historic View: When a tutor is selected, retrieve all records by querying without date filters.

• UI Actions for Navigation: Create UI actions or buttons to switch between current and historic views for easy navigation.

• Client Scripts or UI Policies: Implement these to update the tutor view dynamically based on the selected tutor, ensuring it lists all historic records on the detailed page.

[Samar A]

Thank you Jason for taking the time to describe your suggestion which will be taken in consideration and let you know.