Calculated Fields - Non deterministic functions

1

I’ve been exploring Calculated Fields with some success and failures :grinning::man_facepalming: hopefully you can help :grinning:

I’m attempting to create a virtual field that calculates the remaining number of days any one ‘subscription’ has before it expires. When I do I get the error msg ‘Non deterministic functions are not supported in computed fields’. The docs say otherwise: Data Builder - 8base Documentation

I’m attempting to create the calculated field pictured. Please can you review and see where I’m going wrong.

00
Screenshot 2021-01-27 at 11.07.00.png1245×806 83.4 KB

Thank you, :slight_smile:

2

Hey Theodore - I checked with our team and it does look like there is a misalignment between the docs and what’s available here.

8base currently only supports deterministic functions: https://docs.microsoft.com/en-us/sql/relational-databases/user-defined-functions/deterministic-and-nondeterministic-functions?view=sql-server-ver15

So your approach isn’t going to work. However! There is hope. I’d suggest this approach.

Add a field called today on your Tickets table and then create a scheduled task function that runs once every 24-hours. Inside of it, run a ticketUpdateByFilter(...) that updates ALL tickets with the current date.

Then in your calculated field, you’d simply do DATEDIFF(end, today) and be able to get back your result.

PS: With this approach to, you could also just skip the calculated field and update a daysLeft on your ticket by substracting 1 day from the specified date:

mutation {
  messageUpdateByFilter(data: {
    daysLeft: {
      sub: {
        days: 1
      }
    }
  }) {
    items {
      id
      daysLeft
      updatedAt
    }
  }
}