Creating Complex Quotation

• pkhan
Asked on February 28, 2016 at 03:00 PM

Hi Mikael,

I am trying to make a online quotation form, which will show components price and the total price to the customer depending on the various inputs of the customer.

The problem is that the price of various components is dependent on the system size (or lets say customer input). I have an excel file for that. But how I can use that excel file to calculate the price ? It will be even better, if I can show the output of the excel file, depending on the input of the customer.

For example, depending on customer inputs, the system size becomes 3.5 kW, then the out put will be like:

Component1 --> Price: 6 unit

Component2 --> Price: 4 unit

Component3 --> Price: 3 unit

-------------------

Total Cost: 13 unit

however, depending on the customers input, the system size become 7 kW, then it will be like:

Component1 --> Price: 11 unit

Component2 --> Price: 4 unit

Component3 --> Price: 3 unit

----------------------------------

Total Cost: 18 unit

https://form.jotform.me/60574854637466

Regards,

Palash

• jonathan
Answered on February 28, 2016 at 04:07 PM

If I understand correctly, you want to show components Quotation Price base on the KW range?

Example:

If KW = 3.5

Components:

Component1 --> Price: 6 unit

Component2 --> Price: 4 unit

Component3 --> Price: 3 unit

---------------------------------------------

if KW  = 7

Components:

Component1 --> Price: 11 unit

Component2 --> Price: 4 unit

Component3 --> Price: 3 unit

--

You can use Condition Logic to determin first the system size (in KW) through user input. A Textbox input will do for the input.

Then, to show the different Components option use Checkboxes.

So base on the size (KW) in Textbox, the appropriate Components (Checkbox group) will SHOW.

I'll create a demo form to show how I meant. I'll get back to this shortly.

Thanks.

• jonathan
Answered on February 28, 2016 at 04:20 PM

Here is my demo form https://www.jotform.me/60586571595468

It work like this

For the purpose of the test, the user test input is limited to 3.5 or 7 only.

The calculation idea was base on this user guide: How-to-Assign-Calculation-Value

You can also clone the form so that you can review it on your Form Builder.

Hope this help. Let us know if you have further question about it.

• pkhan
Answered on February 28, 2016 at 04:54 PM

Hi,

Thank you very much for the demo. If I got it correctly, in this case I have to make conditions for each possible value of kW.

But the kW can vary between 0.1 to 1000 depending on customer inputs. So what I do is to fix the price of a component in a certain range. For example for 3kW to 4 kW system, the price of component1 will be 6 unit, while for 4 kW to 5kW the price of component1 is 5.9 unit.

I have an excel file where, price of all components are fixed for a certain range of kW. I was thinking, whether that is also possible to do in jotform also. If you are interested I can send you the excel file, which can give you an better idea, what i am trying to do.

Regards,

• Nik_C
Answered on February 28, 2016 at 05:23 PM

Hello,

It will might be possible to create multiple conditions, but I'm not sure how many we would need to cover all your kW. Maybe you could use condition that will say that, for example, for all kW that start with number 3 will have certain price, and than for all kW that start with 4 will have some other price. In that way you have all those items between 3 and 4 covered. Like 3.1, 3.2, 3.3 and so on.

But if you have a lot of products it would require a lot of conditions.

Can you please send the excel sheet so we can try to figure it out?

Thank you,

• pkhan
Answered on February 29, 2016 at 01:51 PM

Thank you very much. It would have been very much helpful, if I was able to chose a range in the condition.

Can you please send me the email address, where I can send the excel sheet.

Regards,

• jonathan
Answered on February 29, 2016 at 03:01 PM

JotForm can do also Conditions for Range value.

In my test form https://www.jotform.me/60586571595468 we can create conditions for the determined KW(size) value with range.

You can send your Excel data via email to support@jotform.com . Make sure to mention in the Subject line of your email this thread number http://www.jotform.com/answers/783218

I'll modify the test form also to demonstrate to you how to create Range conditions.

Thanks.

• jonathan
Answered on February 29, 2016 at 03:13 PM

Here is the new demo form https://form.jotform.com/60596282625967

I have created the range conditions like this

it is ideal to use the Number field since we are using numerical values.

Let us know if you need further assistance.

• pkhan
Answered on February 29, 2016 at 03:48 PM

Hi Jonathan,

I will try this range condition as you have shown and will update you tomorrow.

Sent you the excel sheet in email. There is 3 such excel sheets. The advantage with the excel is that I can see all the values at a glance and can make any changes very easily.

I was wondering, whether the excel file can be uploaded in google dive or dropbox and the values can be retrieved from there as needed for satisfying a particular condition !

Cheers,

• Kevin_G
Answered on February 29, 2016 at 07:06 PM

Sure, let us know how the workaround provided by my colleague goes.

I confirm you that we received the email with the attached file.

I was wondering, whether the excel file can be uploaded in google dive or dropbox and the values can be retrieved from there as needed for satisfying a particular condition !

I'm not sure if understood your question, but if you are trying to get those values in JotForm, then it could not be possible, you will need to add them manually to your form.