# Calculating Rental Totals

• 7Aspire
Asked on December 09, 2015 at 06:20 AM

I am looking to build a form where the user selects a date from the calendar of a DateTime field (ArrivalDate) and enters the number of nights (NofN) to stay from a number field.  The form would then display a rental total.  Each night potentially has potentially different rental value (based on day of the week, time of the year), so a simple calculation of NofN * a single cash value is not feasibe.

I realise that I can generate a 5 digit value (DateCode) based on the DateTime field selection, from which I would like to generate a single nightly cash value. Let's call this value CV1 and output the value to a hidden number field called Calc1.

I would then use a series of conditional logic statements to output similar values for each further night of the rental period in the format:

If NofN=2, DateCode +1, CV2 output to Calc2
If NofN=3, DateCode +2, CV3 output to Calc3
...
If NofN=31, DateCode +30, CV31 output to Calc31

A Form Calculation would then add the values of Calc1 to Calc31 and display it as a rental total.

How do I get the form to output individual CVs based on potentially over 1000 different DateCodes?

I am building my form here: http://www.jotform.com/?formID=53415853190354 but at present only have the bare bones complete.

Is what I am trying to do even feasible, or is there a better way of doing what I am attempting to do?

• ashwin_d
Answered on December 09, 2015 at 09:27 AM

Hello 7Aspire,

If I understand your requirement correctly, the rate for each night has different value and if user enters the number of night to stay the calculation should be done based on the day of the week and year. Is that correct?

What I do not understand is the 5 digit value "DateCode". What is this value? Is there any logic for calculation of each day stay or the value needs to be hardcoded for every day?

Please explain this a little more in detail and we will see if our calculation feature can be used to achieve this.

We will wait for your response.

Thank you!

• 7Aspire
Answered on December 09, 2015 at 10:47 AM

Essentially your assumption is correct - there is a price for each night that may be different to the prices for dates either side.

I realise that I could generate a dateString calculation function from the DateTime field, but the output result for this would be in the format Wed 9 Dec 2015, which makes creating any conditional statements very awkward.  Accordingly, I figured that instead of a string value, a numerical value of the date would be easier to use in any conditional logic, ie the 5 digit value of the date Wed 9 Dec 2015 is 16778, Thu 10 Dec 2015 is 16779, etc.

Therefore if there was a way to extract 2nd column values from a ~800 x 2 array, based on 1st column values (rather than a string), I would have the problem cracked.  The array would read something like:

16778, £100.00
16779, £100.00
16780, £120.00
16781, £120.00
16782, £100.00
...
17531, £130.00

I realise that jotform does not have a lookup function, but I would have thought that someone might have come up with an alternative work around.

Your suggestion that there may be a mathematical calculation on which each day's value is based warrants my further consideration; at any point in time I only ever have 8 discrete values of rate (4 season variables x weeknight/weekend variables), so there might be a mathematical relationship between them.

• rober_sprine
Answered on December 09, 2015 at 05:07 PM

Check calculation help here:  http://www.jotform.com/help/259-How-to-perform-calculation-in-the-form

• Kevin_G
Answered on December 09, 2015 at 09:21 PM

Hi,

I can not see a way to display the output format for the date field as you are requiring, also I understand you are trying to assign a value to each date, it would be possible , but you will need to have several conditions and your form may work slow, to calculate the total, you can take a look to this guide : http://www.jotform.com/help/343-How-to-perform-form-calculation-in-the-Matrix-field