JotForm is a free online form builder which helps you create online forms without writing a single line of code. No sign-up required.
We believe that if one user has a question, there could be more users who may have the same question. This is why many of our support forum threads are public and available to be searched and viewed. If you’d like help immediately, feel free to search for a similar question, or submit your question or concern.
Calculating Rental TotalsAsked by 7Aspire 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?
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.
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:
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.
Check calculation help here: http://www.jotform.com/help/259-How-to-perform-calculation-in-the-form
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
Also, I think this guide may help you : http://www.jotform.com/help/57-Smart-Forms-Using-Conditional-Logic
Feel free to contact us if you need further assistance.