Formula for a fields default value

#1 · August 9, 2022, 8:44 pm

Not sure if I should be attacking this from a SharePoint angle or a Nitro... assuming it's even possible.

Background: This is an internal staff form for accommodation booking requests.
There is a drop down for destination (city) and 2 date fields date arrive and checkout date.
For the first 4 named destination choices there will be a default $ value associated with the city name - yet to decide how best to store this... maybe a lookup. The fifth choice will be "other" which also have a default $ value.

Endgame: Staff member selects Auckland from the drop down and enters the arrival and destination dates.
The formula would be $250 * (number of nights between arrival and checkout -1) - I think this would be the best way to calculate the actual number of nights stay?
If they selected Wellington then the value would be $200 and so on.
Any ideas before I start creating the columns involved?

#2 · August 12, 2022, 9:07 am

Hi @jay,

This is possible. Is the dropdown a lookup list? If so, you can use the NITRO Lookup auto-fill settings to bring across the per night dollar value to the booking request form. You could then use a SharePoint calculated column to get the total number of nights (checkout - checkin). You may need to subtract or add a day to get the true number of nights. Then another calculated column will get you the total value by multiplying the dollar value brought across times the total number of nights.

You can also use JavaScript to calculate values on the fly using Form Event Actions. This way, you can see the values while filling out the form, and you can use a Number column rather than a calculated column to store the value. To reference a value in a field you can use _ccs_FormUI.fetchColumnValueUI("Column Internal Name") as the syntax to get the column's value. So the script would look something like this:

var StartDate = _ccs_FormUI.fetchColumnValueUI("StartDate");

var EndDate = _ccs_FormUI.fetchColumnValueUI("EndDate");

var PerNight = _ccs_FormUI.fetchColumnValueUI("Dollars");

var Nights = EndDate - StartDate;

return Nights * PerNight;

#3 · August 14, 2022, 8:09 pm

Thanks @james I have gone down the calculated column based on your suggestion. For some reason the total amount column does not show up on the form in preview mode or published and viewed from the "new" button. If it were a logic error I'd expect undefined or NaN but not a complete absence of the field on the form (not even a label). I have check the column in nitro and there are no permissions or validations against it.
So this is what I have....
A list "Form variables" with 2 columns title and amount. This feeds a Nitro drop down and works well. I also feed a number column with the amount column from the source list. I put this on the form and it updates nicely.
Now I have another calculated column NightsCheckedIn which has a formula of =DATEDIF([Arrival Date],[Checkout Date],"d")-1
Finally another calculated column which has a formula of =HotelPerNight*CheckedInNights

Long story short.... neither calculated columns show on the form in either preview or real modes.
Both are standard SharePoint columns

#4 · August 14, 2022, 10:08 pm

That is expected. SharePoint calculated columns only show on the Display form, whether the NITRO Form or the standard SharePoint forms.

New and Edit forms won't show calculated columns.

#5 · December 28, 2022, 10:39 am

I was able to do something similar on my form, but didn't set the field as a calculated column. My choice field sets the value in a number field. The number field is set so when there is a change, it runs a script to set the value of the Total field to calculate the product of the number field and another number field and then displays that on the form in real-time. Would that work for you?

var NightlyCost = _ccs_FormUI.fetchColumnValueUI("CityCost");
var NumberNights = _ccs_FormUI.fetchColumnValueUI("NightCount");

return (NightlyCost * NumberNights)