Cannot seem to access Calculated column in Display Template via Managed Property mapping

Here is my situation:

I have a test/development site on SharePoint Online/Office 365 called TestSite3, and within that site a sub-site called TestBlog3 (a Blog site for making Posts).

I want to use a Content Search Web Part on the homepage to query all Post items from the blog, and then sort them by a custom event date value, but then if event date is not specified (it will be optional), use the post’s created date.

I have also made a custom Display Template in which I have the following managed property mappings:

<mso:ManagedPropertyMapping msdt:dt="string">'Picture URL'{Picture URL}:'PublishingImage;PictureURL;PictureThumbnailURL','Link URL'{Link URL}:'Path','Line 1'{Line 1}:'Title','Line 2'{Line 2}:'LastModifiedTime','Line 3'{Line 3}:'BodyOWSMTXT','Line 4'{Line 4}:'RefinableDate03','SecondaryFileExtension','ContentTypeId','IsAllDayEvent'</mso:ManagedPropertyMapping> 

In the Post content type, I have added two custom columns:

  • ZTest3Date: a Date column that is optional to the user to specify, supposed to function as an ‘event date’ for posts about events coming up
  • ZTest3Calculated: a Calculated column returning a Date. What I want is for it to return the event date (ZTest3Date) if it is set, otherwise return the post’s created date (Created).

Here is the formula I have in ZTest3Calculated: =IF(ISBLANK(ZTest3Date),Created,ZTest3Date)

I have seen formulas use brackets [] around the column names, but when I try saving the formula with that it automatically removes them, leaving the above.

I go into the site’s search schema to look at the Managed and Crawled Properties. I use ‘z’ in the search box to filter them down. The ZTest3Date column I made shows up in Managed Properties as ZTest3DateOWSDATE. There is no Managed Property for the ZTest3Calculated column.

In Crawled Properties, I see ows_ZTest3Calculated, ows_ZTest3Date, and ows_q_DATE_ZTest3Date.

I can put ZTest3DateOWSDATE into my Display Template in Line 4 and it works, but that’s only half my solution; I want to handle when there is no event date set.

Since ZTest3Calculated is not available as a managed property, I understand that it is recommended to map the crawled property to one of the pre-set columns SharePoint provides, so I mapped ows_ZTest3Calculated to RefinableDate03.

I have re-indexed the Post list and waited a while, but it does not seem to be working; I have also tried assigning an alias to RefinableDate03 and updating the Display Template, still no luck. When I debug the JavaScript code I put in my display template file to read what is coming into Line 4, it is blank always, whether or not the event date is set. So either the RefinableDate03 mapping isn’t working, or my ZTest3Calculated column isn’t working. Or something else I’m not aware of.

Sorry this is so long, but I feel like supplying the context and my setup is important. I have driven myself crazy with this over the past few days, and would appreciate if someone might have some knowledge of what I’m missing.

I appreciate any help, and thank you for reading all this!

Cannot seem to access Calculated column in Display Template via Managed Property mapping

Here is my situation:

I have a test/development site on SharePoint Online/Office 365 called TestSite3, and within that site a sub-site called TestBlog3 (a Blog site for making Posts).

I want to use a Content Search Web Part on the homepage to query all Post items from the blog, and then sort them by a custom event date value, but then if event date is not specified (it will be optional), use the post’s created date.

I have also made a custom Display Template in which I have the following managed property mappings:

<mso:ManagedPropertyMapping msdt:dt="string">'Picture URL'{Picture URL}:'PublishingImage;PictureURL;PictureThumbnailURL','Link URL'{Link URL}:'Path','Line 1'{Line 1}:'Title','Line 2'{Line 2}:'LastModifiedTime','Line 3'{Line 3}:'BodyOWSMTXT','Line 4'{Line 4}:'RefinableDate03','SecondaryFileExtension','ContentTypeId','IsAllDayEvent'</mso:ManagedPropertyMapping> 

In the Post content type, I have added two custom columns:

  • ZTest3Date: a Date column that is optional to the user to specify, supposed to function as an ‘event date’ for posts about events coming up
  • ZTest3Calculated: a Calculated column returning a Date. What I want is for it to return the event date (ZTest3Date) if it is set, otherwise return the post’s created date (Created).

Here is the formula I have in ZTest3Calculated: =IF(ISBLANK(ZTest3Date),Created,ZTest3Date)

I have seen formulas use brackets [] around the column names, but when I try saving the formula with that it automatically removes them, leaving the above.

I go into the site’s search schema to look at the Managed and Crawled Properties. I use ‘z’ in the search box to filter them down. The ZTest3Date column I made shows up in Managed Properties as ZTest3DateOWSDATE. There is no Managed Property for the ZTest3Calculated column.

In Crawled Properties, I see ows_ZTest3Calculated, ows_ZTest3Date, and ows_q_DATE_ZTest3Date.

I can put ZTest3DateOWSDATE into my Display Template in Line 4 and it works, but that’s only half my solution; I want to handle when there is no event date set.

Since ZTest3Calculated is not available as a managed property, I understand that it is recommended to map the crawled property to one of the pre-set columns SharePoint provides, so I mapped ows_ZTest3Calculated to RefinableDate03.

I have re-indexed the Post list and waited a while, but it does not seem to be working; I have also tried assigning an alias to RefinableDate03 and updating the Display Template, still no luck. When I debug the JavaScript code I put in my display template file to read what is coming into Line 4, it is blank always, whether or not the event date is set. So either the RefinableDate03 mapping isn’t working, or my ZTest3Calculated column isn’t working. Or something else I’m not aware of.

Sorry this is so long, but I feel like supplying the context and my setup is important. I have driven myself crazy with this over the past few days, and would appreciate if someone might have some knowledge of what I’m missing.

I appreciate any help, and thank you for reading all this!

How is calculated fill-factor percentage related to size of database?

I have rebuild all indexes in database setting fill-factor of 95 (5% free space) using maintenance plan, but database after reindex is almost doubled in size – reported free space is 42%.

How is calculated fill-factor related to size database?

Maybe something is wrong with reindex, which cause so much growth of size?

Some database info after reindex:

Size (MB):            164 983.625 Data Space Used (KB):      82 907 896 Index Space Used (KB):     14 073 320 Space Available (KB):      71 879 024 

Generated T-SQL for maintenance plan for one table:

ALTER INDEX [Table1_Index1] ON [dbo].[Table1] REBUILD PARTITION = ALL WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 95) 

Read calculated column previous value if status equal Closed

enter image description here I have a “Days Past Due” calculated Column and used your calculated formula (link Sharepoint 2013 Custom List Calculate difference between Today and Column Value) and is working fine. But there is another column called [Status] and if value equals “Closed” then it should bring the previous days value. Meaning will freeze the value and will not get changed next day.

Example: Days Past due-> “7 days Past” and Status-> “In Progress” then tomorrow it should increase by one. But if I edit the item tomorrow and set Status-> “Closed” then it should show previous calculated column data which is “7 days Past”

Below is the code I tried but did not get result.

=IF(ISBLANK([Target Completion Date]),"Missing date",     IF(OR(Status="1. Open",Status="2. In Progress"),     "<img src='/_layouts/images/blank.gif'      onload=""{"       & "    var SPday=new Date(); "       & " SPday.setFullYear("       & YEAR([Target Completion Date])       & ","       & MONTH([Target Completion Date])-1       & ","&DAY([Target Completion Date])       & ");"       & " var Days=Math.round((SPday.getTime()-new Date().getTime())/86400000);"       & "    this.parentNode.innerHTML=Math.abs(Days)+' days '+((Days<0)?'past':'left');"       & "}"">","<img src='/_layouts/images/blank.gif' onload=""{ "       & "    var Days=this.parentNode.text();"       & "    this.parentNode.innerHTML=Days;"       & "}"">"     ) ) 

Any help will be appreciated.

Thanks a lot.

Calculated Column Field Date

I need to calculate date based on other column I’m using =Date(Year([endDate]),1,1) in a calculated field which needs to return data as DateTime, but it is giving me error:

The formula contains a syntax error or is not supported.

After that I tried to modify the view to take only the year from today date as a string like: Trim(Text(Year([Today]))) and it is accepting the formula, but no results in the view.

Any suggestions what can be causing the problem? I prefer to make it work like in the 1-st option which I described, but if it is doable in the 2nd, it is also acceptable.

Calculated Field Syntax Error – Matrix Output

I’m quite new to this, could anyone point out why my calculated field isn’t working

Im trying to use a matrix to output a value of critical, high, medium or low

The field Im using is ImpUrg_Total, this is my current failure

=IF(ImpUrg_Total<2,”Critical”,IF(AND([ImpUrg_Total]>=2,[ImpUrg_Total]<4,”High”,IF(AND([ImpUrg_Total]>=4,[ImpUrg_Total]<9,”Medium”,IF([ImpUrg_Total]=>9,”Low”))))))

Calculated column with multiple options depending on start date of event compared to today

I’m attempting to create a column (“30, 60, 90, +”) that reports if an entry is within 30, 60, or 90 days out. One field is “Start Date” and I want the field to report ’30’ if the start date is within 30 days from today, ’60’ between 31-60 days from today, and ’90’ for between 61-90; with ‘+’ being the entry beyond that timeframe.
Is this doable?

SharePoint Calculated field Failed to update value on all items on Large List

So this is the problem:

I have a list with around 1 million records

I added a Calculated Column, with a very simple formula:

"0" + [textfield] 

and return it as number

The request returned an update conflict error but when I browsed at the list I could see that the items were getting updated from the oldest to the newest over time with the new calculated field value. but some time later it stopped leaving more than half of the items without getting their value calculated returning them as -4XXXXXXX (I think this means is a null number)

I checked the logs and found out that for 3 hours every 20 seconds the ULS kept logging the following error:

A large block of literal text was sent to sql. This can result in blocking in sql and excessive memory use on the front end. Verify that no binary parameters are being passed as literals, and consider breaking up batches into smaller components. If this request is for a SharePoint list or list item, you may be able to resolve this by reducing the number of fields.

And then Finally a request timeout error log having the same Correlation ID as the previous error

Is there a safe way to add that calculated field?

do I need to increase a timeout variable to give the calculated field enough time to calculate all the items?

how can I recalculate the remaining fields without affecting the modified by and modified date of the items ?

Thank you!

Create a calculated column to be yes IF([due date]=[Today]-31, “yes”, no) or custom view

Im trying to set up a calculated column to look at another calculated date column and if its under a month (ie its due to be renewed) than say yes.

if i cant do that can i create a custom view from a calculated date column?

the greater than or equal to [Today]-31 doesnt work on calculated columns?

My end goal is to use flow to look for the yes column and then create a new list item, but i cant use odata filters from calculated columns and [Today]-31 doesnt work on calculated columns? Kinda stuck