Pnp-sites-core: Null Exception thrown for formulas tokenized from calculated columns in TokenizeFieldFormula() perform lookup into FieldCollection for standard functions like Today

[JohanVanderBeach]

\pnp-sites-core-master\coreofficedevpnp.core\framework\provisioningobjecthandlersobjectlistinstance.cs

Reproduced with the latest 2/10/2017 core pnp release. Default xml template.

Category

[X ] Bug
[ ] Enhancement

Environment

[ ] Office 365 / SharePoint Online
[ ] SharePoint 2016
[ X] SharePoint 2013

If SharePoint on-premises, what's exact CU version:
Configuration database version: 15.0.4797.1000

Expected or Desired Behavior

Use the remote provsioning engine specifically from a web context call GetProvisioningTemplate(). Expected to get a XML template. A list on the site with a calculated column called "Days Left" and a formula "=[30 Calendar Days]-Today" I expect the tokenizer to do it's checks on the [30 Calendar Days] but to skip that for non columns / built in functions like Today. I assume the code does the check to convert to the internal name because it wants a standard name that it can use later when it puts back the formula.

Observed Behavior

What we saw is for the compute column formula "=[30 Calendar Days]-Today" code in TokenizeFieldFormula(FieldCollection fields) goes through each decendent of:

<Field Type="Calculated" DisplayName="Days Left" Format="DateOnly" LCID="1033" ResultType="Text" ReadOnly="TRUE" ID="{daab43eb-725e-485d-921e-65769a0f5397}" SourceID="{408e2962-1c41-4d2d-b9c5-75f7f5dbc652}" StaticName="Days_x0020_Left" Name="Days_x0020_Left" ColName="sql_variant8" RowOrdinal="0" Required="FALSE" EnforceUniqueValues="FALSE" Indexed="FALSE" Version="1">
    <Formula>=_x0033_0_x0020_Calendar_x0020_Da-Today</Formula>
    <FieldRefs>
        <FieldRef Name="Today" />
        <FieldRef Name="_x0033_0_x0020_Calendar_x0020_Da" />
    </FieldRefs>
</Field>

So Today and the _x0033_0_x0020_Calendar_x0020_Da. For each of these Field Ref's it gets the name and tries to get out of the FieldCollection fields the internal name. Because Today is a calculation function it can't find it in fields so referencedField becomes null. It then tries the following with null formulastring.Replace($"[{referencedField.Title}]", $"[{{fieldtitle:{fieldInternalName}}}]"); which throws the exception because it's trying to get to referencedField.Title.

I'm not sure if the issue is the xml shouldnt' have it as a FieldRef item or if it should ignore ones that can't be found as they could be functions. I given I know what are in the lists decided to ignore with
// Maybe it's an internal function and not a field
if (referencedField != null)

Steps to Reproduce

Create a list with a calculated column that includes a built in calculation function like Today. Then use remote provisoning code such as:

            using (var ctx = new ClientContext(link))
            {
                ctx.Credentials = creds;
                Web web = ctx.Web;
                ProvisioningTemplate pTemplate = web.GetProvisioningTemplate(); 
           }

[luismanez]

just adding some (not very useful) info. I've been trying to reproduce this issue in SPO and SP 2016 On Prem, but works fine. The Field XML doesn't contain a FieldRef to "Today".

<Field Type="Calculated" DisplayName="Calculated Date" EnforceUniqueValues="FALSE" Indexed="FALSE" Format="DateOnly" LCID="1033" ResultType="DateTime" ReadOnly="TRUE" ID="{46b86c04-05f9-46f8-84ac-b9acb28f4b95}" SourceID="{{listid:TestingLuis}}" StaticName="Calculated_x0020_Date" Name="Calculated_x0020_Date" ColName="sql_variant2" RowOrdinal="0" Version="1">
              <Formula>=[{fieldtitle:Delivery_x0020_Date}]-TODAY()</Formula>
            </Field>

How are you're defining the Formula in SP UI? cos just "Today" is not valid in SPO and 2016.

I know the issue is reported for 2013. I'm trying to find a VM with 2013 to give it a try...

[jansenbe]

Thanks for the testing @luismanez :-)

@JohanVanderBeach : are you able to reproduce this issue when using TODAY() instead of TODAY?

[JohanVanderBeach]

@luismanez @jansenbe ,
I did some looking here I believe Today was in a column that came from 2010. So the migration allowed it in 2013. If going forward this is going to be an issue like luismanez's testing shows, then I assume on my side I'll have to convert all the occurrences from Today to the new.

[jansenbe]

@JohanVanderBeach , so does TODAY() then work for you? If so then please let me know so that I can close the issue.

[JohanVanderBeach]

You can close Today() works. Thank you.

[spdevdocs]

We closed this issue as it had not activity within last 180 days. This is a generic process we have decided to perform for issues, which have not been explicitly marked still to be "work in progress" based on tags. We are performing this cleaning to make sure that old issues that have already been solved (but not closed) or are no longer relevant are cleaned out and make the issues more manageable. If this issue still valid, we would ask you to open a new issue and follow the guidance in the issue template related on the recommended location. We do apologize any inconveniences this might cause. Please do remember that issues in the issue lists are also messages for others in the community, so you can also check if you can assist on any of them. “Sharing is caring!”