topic Re: Calculated Field in Records List Not Showing in Fusion Manage Forum

Calculated Field in Records List Not Showing

ThomasRambach — Wed, 24 Jun 2020 10:58:48 GMT

I have a calculated field in a workspace that subtracts 2 dates and adds the result to a text field in a workspace. This works and shows the value on the records details, but the records list is always empty when viewing the records list. Why?

Re: Calculated Field in Records List Not Showing

philipfrench — Wed, 24 Jun 2020 11:43:25 GMT

1. have you tried explicitly casting the result to text, in your formula:

(your formula in brackets)::text

2. (I hope you have tried logging out/on again, because some data types etc are cached)

3. Try deleting and re-creating the field, specify the type as Single Line Text, save, and then add your formula with the casting, so the system is in no doubt what the data type of the result is (and should render).

4. Try an on-edit script, to populate a (non-computed) read only field. Not sure if that would work for you.

Note these are guesses, I have not seen exactly the symptoms you describe.

Re: Calculated Field in Records List Not Showing

ThomasRambach — Wed, 24 Jun 2020 12:14:37 GMT

@philipfrench (your formula in brackets)::text gives an error. Is that syntax correct?

My formula looks like:

ROUND(TOTAL_ESTIMATED_HOURS - ACCRUED_HOURS)

Both fields are dates. It would also be nice to remove the "Days" text from the result to just get the value.

Re: Calculated Field in Records List Not Showing

sven.dickmans — Wed, 24 Jun 2020 13:06:21 GMT

Are the fields TOTAL_ESTIMATED_HOURS and ACCRUED_HOURS really date fields or numeric/integer/float fields? The field names indicate that you just want to store a given amount of hours in the fields - so why do you define them to be date fields?

Similar question for the computed field: I would assume that you want to calculate the difference in hours. And you may want to use this value for reporting in the dashboard. So why do you define the field as string field instead of Integer? The latter one would enable you to let the system calculate totals / averages / minimums for you in reports.

I tried to create the same setup, but I am not even able to create a computed field of type string with your formula (the given fields have been added before, of course). So I changed the field to be of type Integer and changed the formula like this in order to get the difference in days:

DATEDIFF('DAY', TOTAL_ESTIMATED_HOURS, ACCRUED_HOURS)

Re: Calculated Field in Records List Not Showing

ThomasRambach — Wed, 24 Jun 2020 13:16:28 GMT

@sven.dickmans I think I need more coffee this morning.

Yeah, those are actually both integer fields that compute to another integer field. I was speaking about two different problems in the same post. My issue is just that it won't display the data in the records list.

Re: Calculated Field in Records List Not Showing

sven.dickmans — Wed, 24 Jun 2020 13:34:27 GMT

Ok. I changed all my fields to type Integer. Now, the calculation results are shown correctly in my environment both I the record details as well as in the workspace view on the left hand side. Do you use the Modern UI as well?

Maybe you're facing this issue as some record might not have values defined. You could prevent this by enforcing to calculate with a default of '0' if the given hours have not been entered yet:

ROUND(coalesce(TOTAL_ESTIMATED_HOURS,0)-coalesce(ACCRUED_HOURS,0))

Re: Calculated Field in Records List Not Showing

tony.mandatori — Wed, 24 Jun 2020 14:17:36 GMT

There have been issues where the Filter View data falls out of synch with the Details page - you should open a case for this so that the DBA can review it.

Also note that changing field types sometimes causes issues: morphing types affects the data integrity.

So, please open a tech support call to review.

Re: Calculated Field in Records List Not Showing

ThomasRambach — Wed, 24 Jun 2020 14:24:34 GMT

@tony.mandatori Case ID: 16676240 is already associated with this thread.

Re: Calculated Field in Records List Not Showing

philipfrench — Wed, 24 Jun 2020 14:56:21 GMT

You're right to raise a case.

But something else to check yourself first perhaps...

Delete the field, and re-create the field.
Add a simple formula, check the view, and evolve the formula bit-by-bit, see if you can figure the point the view fails. This might give a clue.
Create a new (empty) view (don't "duplicate view"), and populate the new view with your column.
I found also that, in the item details definition, if you un-assign a field (any field) by dragging to the left, save, re-open, and re-assign the field again... this seems to clear something in the back-end, and might the problem go away.
Use F12 in the browser, network tab, refresh your view. Do any of the API calls being made return a non-20x code? If so, check the response body, there may be something in a particular API response that gives you a clue (or more detailed message to add to your ticket).