What is JotForm?
JotForm is a free online form builder which helps you create online forms without writing a single line of code. No sign-up required.

At JotForm, we want to make sure that you’re getting the online form builder help that you need. Our friendly customer support team is available 24/7.

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.


  • Profile Image

    Can Jotform cope with setting up a MARGIN TABLE like attached sheet?

    Asked by IanRm on November 13, 2016 at 06:52 AM

    Can Jotform cope with setting up a MARGIN TABLE like attached sheet?

     

    REQUIREMENT:

    Self calculating SELL COSTS FIELDS  based on the VALUE OF THE RAW COSTS - which will be different for every submission.

    We want to use this margin-table - concept to establish a selling price.

    THE IMPORTANT CHALLENGE IS that the variable buy price of  LESS THAN €200 - needs a specific margin value added - but over €200 a % Margin is the target - for which we divide by a key figure to achieve the selling cost of products which have a raw cost greater than @200

    THE ATTACHED SPREAD SHEET HAS TWO PAGES TO HELP YOU GRASP WHAT WE ARE TRyING TO DO IN THIS CONDITIONAL CALCULATION

     

    Can you help us to achieve how we can use a margin table like this in JOTFORM - or if not

    Can we achieve the same result - another way in JOTFORM?

     

    Formula sample below taken from cell E5

    =VLOOKUP(C5,margintable,2,1)

    Where C5 is the Key used to lookup margintable and return the value for that key from the 2nd Column in the margintable

    Values in Yellow cells can be changed and correct margin should be reflected in column D

     

     

    Column E is an IF Function: if the margin is less than 2, then it must be a percentage and so multiply is used to get the selling price, otherwise if the margin value is greater than 2 than the value is added to the purchase price to get a selling price.

    Page URL:
    https://form.jotform.com/63172723161955

    Screenshot
  • Profile Image
    JotForm Support

    Answered by Charlie on November 13, 2016 at 10:36 AM

    Hi,

    I am having a hard time understanding your table without an actual formula. 

    If you can provide us a formula on how your selling price and margin works, then we can help you proceed on how to set this up. 

    Questions we have includes:

    1. Is purchased cost fixed?

    2. How is margin calculated or set? You said you have conditions if it is greater than or less than 200. But what is the exact formula, we can't test if we don't know it. 

    3. The VLOOKUP formula applies to a spreadsheet tool, 2nd column is also "Product Description" so I am not sure what is the value there? 

     

    This should be possible but not in the sense of using a VLOOKUP, but using conditional logic and form calculation widget. You can check the following guides to help you in your calculation:

    https://www.jotform.com/help/259-How-to-Perform-Calculation-in-the-Form 

    https://www.jotform.com/help/268-How-to-Insert-Text-or-Mathematical-Calculation-into-a-Field-Using-Conditional-Logic 

     

    We'll wait for your response.

  • Profile Image

    Answered by IanRm on November 13, 2016 at 01:44 PM
    HI
    Did I not attach this spreadsheet? (which I sense explained exactly what w
    want to do in Jotform?)
    Kind Regards
    Ian
    Ian Mullen
    Ross Print Services, 5 Applewood Drive
    Greystones, Co. Wicklow
    *T: +353 1 287 6612*
    M: +353 87 255 4216
    ...
  • Profile Image

    Answered by IanRm on November 13, 2016 at 01:44 PM
    I have used V4 for this and Hi - I'm NOT seeing the CALCULATION (widgit)
    in Version 4?
    Where might it be in version 4
    Kind Regards
    Ian
    Ian Mullen
    Ross Print Services, 5 Applewood Drive
    Greystones, Co. Wicklow
    *T: +353 1 287 6612*
    M: +353 87 255 4216
    ...
  • Profile Image

    Answered by IanRm on November 13, 2016 at 01:44 PM
    Charlie, these links seem to NOT BE for V4? correct?
    Kind Regards
    Ian
    Ian Mullen
    Ross Print Services, 5 Applewood Drive
    Greystones, Co. Wicklow
    *T: +353 1 287 6612*
    M: +353 87 255 4216
    ...
  • Profile Image

    Answered by IanRm on November 13, 2016 at 01:47 PM

    do you have the formulae now? (the spreadsheet we attached?)

    Please confirm

     

  • Profile Image

    Answered by IanRm on November 13, 2016 at 02:44 PM
    Purchase cost IS NOT FIXED it is variable for every ORDER / every submission
    Kind Regards
    Ian
    Ian Mullen
    Ross Print Services, 5 Applewood Drive
    Greystones, Co. Wicklow
    *T: +353 1 287 6612*
    M: +353 87 255 4216
    ...
  • Profile Image
    JotForm Support

    Answered by jonathan on November 13, 2016 at 04:39 PM

    Hi Ian,

    We apologize for the delayed response.

    The support forum is not capable of getting file attachement. So we were not able to see the attache spreadsheet that you mentioned.

    I do see your test form https://form.jotform.com/63172723161955 and I can see the annotations on the field's descriptions on what needs to be done.

    My initial question is if the Margin Table you refer to is an external data.

    It will be more complicated if there is a need to use an external data source table since you will have to do the calculation function outside the Form Builder.

    But if you only meant to have a margin table in the form using matrix calculation, it will be more easier to achieve because you can perform calculations when using the Matrix table field.

    User guide: How-to-Perform-Form-Calculation-in-the-Matrix-Field

    Since you can have a matrix table calculations, you can use the result from the matrix calculation as the data for the other fields that needs margin table data.

    Hope this help. Let us know your feedback so we can further identify all the requirements of custom calculation form.

    --

    Related:

    If you want to show a Spreadsheet calculations in your form, you can use the Spreadsheet widget

    https://widgets.jotform.com/widget/spreadsheet

    Although the widget cannot be used in calculations with other fields on the form similar to what the matrix table does.

     

    Thanks.

     

  • Profile Image

    Answered by IanRm on February 02, 2017 at 03:45 PM
    Hi
    Apologies for the silence - i have been unwell.
    But I'm back at the desk now and I would like to revive this matter please.
    First: 'The Margin Table' can be in the form - yes
    Given that v4 is now live - are you instructions still the same
    Just so I try and get a handle on this:
    Will we be using a CALCULATION MATRIX TABLE to carry the MATRIX YABE DATA?
    Can one CALCULATION MATRIX TABLE RELATE/LINK TO DATA IN ANOTHER MATRIX?
    Kind Regards
    Ian
    Ian Mullen
    Ross Print Services, 5 Applewood Drive
    Greystones, Co. Wicklow
    *T: +353 1 287 6612*
    M: +353 87 255 4216
    ...
  • Profile Image

    Answered by IanRm on February 02, 2017 at 03:45 PM
    Product ID Item Description Purchase Cost ActualMargin Selling Price
    1 Product A € 18.00 44 € 62.00
    2 Product B € 45.00 45 € 90.00
    3 Product C € 80.00 55 € 135.00
    4 Product D € 120.00 65 € 185.00
    5 Product E € 75.00 55 € 130.00
    6 Product F € 130.00 65 € 195.00
    7 Product G € 1,800.00 75 € 1,875.00
    8 Product H € 2,500.00 1.3 € 3,250.00
    9 Product J € 6,000.00 1.2 € 7,200.00
    This is the margin table
    Kind Regards
    Ian
    Ian Mullen
    Ross Print Services, 5 Applewood Drive
    Greystones, Co. Wicklow
    *T: +353 1 287 6612*
    M: +353 87 255 4216
    ...
  • Profile Image
    JotForm Support

    Answered by jonathan on February 02, 2017 at 03:56 PM

    Can one CALCULATION MATRIX TABLE RELATE/LINK TO DATA IN ANOTHER MATRIX?

    I believe this is possible to achieve within the form. All you need to do is consolidate the results value on another separate Calculation field.

    Can you describe more the workflow you wanted to do?

    My initial assumption is that by relate/link, you meant there will be a another calculations that will occur depending on the values in the Matrix table.

     

  • Profile Image

    Answered by IanRm on February 02, 2017 at 04:46 PM
    Hi jonathan,
    *The OBJECTIVE: is to determine the Selling Price when all you know is the
    BUY Price. *
    *There is one approach up to buy prices of less than €248Then from €249 and
    higher the sell price is determined by dividing teh cost price by .7*
    *AS BELOW*
    IF the base cost (our buy cost) is< (less than) €248 then a FIXED margin
    value is added to the buy price to determine the selling price.
    *MEANING*
    If the cost/ buy price is €18.00 then Add €44 to reach a Sell Price of €62
    If the cost/ buy price is €45.00 then Add €45 to reach a Sell Price of €90
    If the cost/ buy price is €75.00 then Add €44 to reach a Sell Price of €130
    If the cost/ buy price is €80.00 then Add €55 to reach a Sell Price of €135
    If the cost/ buy price is €120.00 then Add €55 to reach a Sell Price of €185
    If the cost/ buy price is €130.00 then Add €65 to reach a Sell Price of €195
    *BUT WHEN THE COST IS GREATER THAN* €248 then the sell price is determined
    by dividing the cost price by .7
    If the cost/ buy price is €250.00 then dIvide €250 by .7 to reach a Sell
    Price of €357
    If the cost/ buy price is €800.00 then dIvide €250 by .7 to reach a Sell
    Price of €1143
    Product ID Item Description Purchase Cost Margin Selling Price
    1 Product A € 18.00 44 € 62.00
    2 Product B € 45.00 45 € 90.00
    3 Product C € 80.00 55 € 135.00
    4 Product D € 120.00 65 € 185.00
    5 Product E € 75.00 55 € 130.00
    6 Product F € 130.00 65 € 195.00
    7 Product G € 1,800.00 .7 € 1,875.00
    8 Product H € 2,500.00 .7 € 3,250.00
    9 Product J € 6,000.00 .7 € 7,200.00
    Might this work?
    can we use VLOOKUP ?
    =VLOOKUP(C5,margintable,2,1)
    Where C5 is the Key used to lookup marintable and return the value for that
    key from the 2nd Column in the margintable
    Values in Yellow cells can be changed and correct margin should be
    reflected in column D
    Column E is an IF Function: if the margin is less than 2, then it must be a
    percentage and so multiply is used to get the selling price, otherwise if
    the margin value is greater than 2 than the value is added to the purchase
    price to get a selling price.
    Kind Regards
    Ian
    Ian Mullen
    Ross Print Services, 5 Applewood Drive
    Greystones, Co. Wicklow
    *T: +353 1 287 6612*
    M: +353 87 255 4216
    ...
  • Profile Image

    Answered by IanRm on February 02, 2017 at 05:45 PM
    If i struggle to grasp how to do this? (but Jotform is capable)
    Can Jotform do it and charge me for the service? or if not might you have
    any independent contractors that might take on the project?
    Kind Regards
    Ian
    Ian Mullen
    Ross Print Services, 5 Applewood Drive
    Greystones, Co. Wicklow
    *T: +353 1 287 6612*
    M: +353 87 255 4216
    ...
  • Profile Image
    JotForm Support

    Answered by jonathan on February 02, 2017 at 08:15 PM

    Hi Ian,

    Allow me some more time to work on a demo form base on your workflow. I'll get back to you with update later.

    Thanks.

  • Profile Image

    Answered by IanRm on February 03, 2017 at 05:46 AM
    Thank you Jonathan
    Much appreciated
    Ian
    Kind Regards
    Ian
    Ian Mullen
    Ross Print Services, 5 Applewood Drive
    Greystones, Co. Wicklow
    *T: +353 1 287 6612*
    M: +353 87 255 4216
    ...