Calculated Field working different on New Vs Edit

I have a somewhat complex scenario, but it is working differently if it is a Brand New Entry, versus an Existing Entry.

I have a Project Drop Down field that a User can select a Project. I have a calculated field on the form, that takes that project selection and parses out the first 5 characters (project ID) and stores it into another field (Project ID).

With an Existing Request, if a user goes in and selects an option from the Project Drop down, the Project ID (first 5 characters) is successfully parsed out and the ID is loaded into the Project ID field upon hitting 'Submit'.

With a New request, when the user selects an option from the Project Drop Down and hits, 'Submit' the calculated field is not parsing out and loading the project ID into the Project ID field. The user needs to go back into the request in Edit mode, and hit 'Submit' again....one thing to note with this...when they go back into the request, the Project is still selected from the initial entry, just the calculated field is not storing the Project ID into the Project ID field.

Thank you!

Page 1 / 1

I would be interested in seeing how you have your formula setup, and the options on the Calculated Control.

Just pulling a Choice Control and Calculated Control onto the form and doing a rudimentary formula, I was unable to produce the same error you are describing. Perhaps a screenshot of your form, options, and formula would better help reveal the problem that you are experiencing.

Choice Control

Calculated Control

Calculated Control's Formula

Copy / Paste Version of Formula:

If(isNullOrEmpty(projectChoice),"",subString(projectChoice,0,5) + "-ID")‍

Results:

As soon as you make a selection on a New Form or Edit Form, the Calculated Control should update its value immediately.

I hope that this helps.

For the Calculated Values,are you using Named Controls or Item Properties references for your formulas? My guess from the described behavior is that you are using Item Properties. This will present an issue in this scenario because the New Form will not have a property to reference due to the form having not yet been submitted. In this scenario there is essentially no metadata stored back to the list that can be referenced within the form.

I would recommend trying this out using Named Controls in place of references to item properties. You can quickly do this by adding a name in the referenced control settings and swapping that portion out for the referenced item property.

Configure Named Control:

Insert in to calculated value control:

New Form with Item Property and Named Control:

Edit Form example of same form

Thank you Dan & N M.

Please see my screen shots below.

Project ID Look up field

Calculated field:

Calculation formula:

I can confirm that 'TypeofPurchase' is a Named Control. SelectedProject is not a named control, as I do not have a control for that, it is the field that stores the selected Project in the first screen shot. Major Project ID, is the only one that I believe I used the Item Properties value instead of the Named Controls value.

Based on what you both of have provided, I'm thinking I just need to use the Named Controls value for Major Project ID?

I made the change to use the Named Control value for the Major Project ID field, tested, and the Selected Projects ID is still not populating into the Major Project ID field upon initial Submission.

Thank you,

Looking over the formula, and what you're trying to do, there might even be a simpler way if you just name your Lookup Control to something.

For instance, if you gave it the name of "lookup_ProjectDropDown" (it's helpful to name your controls in some way that can differentiate them from the List Control Names that are generated by default), your Calculated Control's formula would simply read:

If({TypeofPurcahse} !== "Project", subString(parseLookup({lookup_ProjectDropDown}),0,5), parseLookup({lookup_ProjectDropDown}, false))‍

please note that I have put curly braces around the Names of Controls in the above text to indicate that those should be replaced in your own Formula with the Red Text References that you see when you double click on the Named Controls name. There should be no Curly Braces in your final Formula.

Example

Here is a Choice Control named "TypeofProject"

Here is a List Lookup Control named "lookup_ProjectDropDown" that is pointing to a Test List on a different site:

(Even though I am not storing the 'ID Connected To' or the 'Text Connected To' Value in this example, you can still keep yours how it is setup up for your environment)

Lastly I have a Calculated Value Control named "projectID":

with a formula that looks like:

On the form they are arranged (and numbered in order of appearance in the pics above):

When ran, it produces the following results:

with "Project" selected, I get the ID only

otherwise I get the sub-string:

I hope this helps clear things up.

I attempted to complete your suggestion, and the result is unfortunately worse than what was happening before.

Updated Project Lookup- Gave Name of ProjectLookup

I updated my formula per your direction above, using the Project Lookup Named control...although I did not include the Parselookup function.

It is now storing the ID of the request for the actual Project, and not the Major Project ID of the project, as in the second line.

Both lines are storing correctly in the SelectedProject field....I need the CH015 to be what is loaded into the Major Project ID field...which after the changes, no longer is happening.

Actually, there is nothing being stored in the Major Project ID field, regardless of how many times I go into the request and hit submit again.

you have not updated the formula to match mine as you have left out the parseLookup function and the subsequent values across your entire Calculated Control Formula.

additionally, can you please provide a screenshot of the form itself and the controls in question?

the MajorID column is no longer storing the ID because you have removed it from the Lookup Control's 'ID Connected To' property!

Thank you!!! OK I believe I have it working now!!!

I gave the ListLookup control a Named Control of ProjectLookup, keeping everything else as I had it originally.

I removed the Name from the Calculated Field, but kept it connected to the Major Project ID field

I updated the formula as below, using the ProjectLookup Named control, instead of the SelectedProject field.

On the form, when I select and project now, the calculated field is showing a result, and when I Submit the form, the ID (PO013) is showing in the Major Project ID field!