I would like to keep track of the number of parts used throughout all the submissions of my form

  • beverlyhimmerich
    Asked on January 2, 2022 at 12:20 AM

    Hello again and as always, thanks for the great advice. I'm starting a new ticket because I asked the last one be made private due to data displayed but these help everyone so I wanted to add it as public.


    I have a form that is used by field techs to conduct repairs - with your assistance I have completed the form and it's working great. The question I have is:

    1641100696 61d13598eed17  Screenshot 10

    1641100764 61d135dc2d2e9  Screenshot 21

    I would like to keep track of the number of parts used throughout all the submissions of my form - I know everything goes to a table and I've reviewed the inventory tables for ideas but for the life of me, I can't figure out how to get them to cross reference - the issue is - I am building it out to a PDF invoice so I have multiple fields (10 fields which allow the techs to select 10 different items from a dropdown and then a second field for the quantity used for that item) and I would like it is Tech1 used a hose clamp, and Tech 2 also used a hose clamp, I would like the table to display that two hose clamps have been used - at the end of the year I would know how many of these hose clamps were used in total.


    Seems simple and I was wondering if perhaps you could send me the in the right direction since you guys are more familiar with the multiple different templates and types of templates that are available.


    If you look in my account to see the form, it's the 2021/2022 Repair Invoice but please, if doing screen shots for this do not include customer data so that I can leave this public to help others as I have googled this and see others asking similar questions.


    Thank you again,


    Jeff


  • Amin JotForm Support
    Replied on January 2, 2022 at 4:32 AM

    Hi Jeff,

    Thanks for reaching out to us!

    We offer a feature called Advanced Formula that allows getting the total of Tables columns automatically.

    1641115613 61d16fdd8fac9  Screenshot 10

    Can you please let us know what fields exactly you'd like to total?

    We're patiently waiting for your response.

  • beverlyhimmerich
    Replied on January 2, 2022 at 4:19 PM

    I have fields called:


    Part Used (Part 1)

    Part Used (Part 2)

    Part Used (Part 3)

    Part Used (Part 4)

    Part Used (Part 5)

    Part Used (Part 6)

    Part Used (Part 7)

    Part Used (Part 8)

    Part Used (Part 9)

    Part Used (Part 10)

    Each of these has a dropdown with calculated fields for the Amount total in the form -

    1641157925 61d21525921d7  Screenshot 10

    Calculation is based on the Unit Price in each calculation field. I would like to keep track of the number of parts used (all the same in each of the 10 fields) in a summary across all forms submitted...

    1641157957 61d21545ea438  Screenshot 21

    For example, one drop down is Alternator - I would like to see the number of alternators used throughout the period of time the techs are submitting the forms.

    I made this in excel because it was easy but here's an example of what I'm looking for...

    1641158116 61d215e4dd858  Screenshot 32

    I would love it if I could have some sort of 'dashboard' where the admin could just pull up the page and see a parts list with the numbers used beside it without having them have access to the tables where the could potentially change things inadvertently.

    Bonus points would be if I could add perhaps a summary of the dates used so I could sort by a period of time, for example, show me all the alternators used in May 2022 etc. but I believe that might entail making a separate table then sorting off that table... sounds easy when I say it but perhaps isn't.

    Thanks for the support!

    Jeff


  • Amin JotForm Support
    Replied on January 2, 2022 at 5:57 PM

    Hi again,

    You can get the total quantity of parts for each submission by creating the advanced formula below.

    1641163689 61d22ba937a76  Screenshot 10

    However, what I recommend is adding a Number field to the form, then creating a condition to get the total of parts quantity per submission, then you can easily get the SUM of this column across all submissions in Tables as shown below.

    1641164094 61d22d3ea1788  Screenshot 21

    I hope I have been of any help.

  • beverlyhimmerich
    Replied on January 2, 2022 at 10:26 PM

    Perhaps I am confused but each part (Part 1, Part 2, Part 3, etc) would be a different part so adding them all together as 1 (adding the quantity of part1 plus the quantity of part2 etc) would give me the total but would not tell me if it was an alternator or a belt or what, just a total... I don't think that would work, I would somehow have to differentiate between the type of parts and that is the part that is losing me.


    Thanks in advance.


    Jeff


  • beverlyhimmerich
    Replied on January 3, 2022 at 12:10 AM

    OK, I've been playing with this and here's what I have figured out, but I am hoping there is an easier way...


    If I add fields for each of the parts that the field tech's can use (over 100 different possibilities):

    1641184889 61d27e79cdc0d  Screenshot 10


    Then I can set a condition that fills these fields automatically if a specific part is used which in turn would add them to a new column in the spreadsheet/table which I could then get the sum of as needed:

    1641184952 61d27eb86106f  Screenshot 21

    1641185206 61d27fb6bccd9  Screenshot 32

    The problem with this idea is that it would require that I add over 100 conditions for Part 1 and over 100 for part 2 and over 100 for part 3, etc etc and with 10 fields, that's 1000 conditions to manage this and that's a lot of work. I am in hopes that there is a better way to track this data...


    Also, to backtrack, my follow up will be if it's possible to visualize the data in the table on a dashboard of sorts - I was able to play with the reports and using the above method (too much work) to create a report here:

    1641186450 61d284929a26e  Screenshot 43

    But as you can see, I was not able to add the sum of each in the column - and truth be told, I'd prefer the items to be listed in one column and the total in the second - I don't need to see it line by line, just a sum of each...


    Thanks, and sorry to ask so many questions.

    Jeff



  • Amin JotForm Support
    Replied on January 3, 2022 at 12:29 AM

    Hi Jeff,

    Thanks for providing more information.

    I'm afraid that due to how many options and parts there are on your form, it will not be feasible to create 1 condition for every option for all parts (100*10).

    The best I can recommend now is the Report Builder. I understand it doesn't do exactly what you're after, however, it gives you the total number of each option across all submissions for each part as shown below.

    https://www.jotform.com/reports/22002044810903903

    1641187388 61d2883c99be2  Screenshot 10

    I hope I have been of any help.

  • beverlyhimmerich
    Replied on January 3, 2022 at 1:31 AM

    If I built the form a different way, would there be another way? This is similar to an inventory list.


    Thanks again

    Jeff


  • Sam_G
    Replied on January 3, 2022 at 3:48 AM

    Hello Jeff,

    I have tried checking and unfortunately, there is no other way other than what my colleagues provided.

    Closes would be the Visual Report and view the data in a chart.

    1616011767 605261f752832 product Screenshot 10

    Related Guide: How to Create a Visual Report with Your Form Submissions


  • beverlyhimmerich
    Replied on January 3, 2022 at 7:07 PM

    Is this a feature I would be able to request? Thanks!

  • Sheena JotForm Support
    Replied on January 4, 2022 at 12:03 AM

    Hi Jeff,

    Of course.  I've escalated this option to our developers to consider adding it as a feature, although we won't be able to provide you a time frame for when this feature would be implemented, but we will notify you through this ticket once we have an update.

    Warmest,

    Sheena

  • beverlyhimmerich
    Replied on January 16, 2022 at 1:37 PM

    Hello again,


    I’ve given myself some time to work on this and mess with the settings and I’m wondering if this request isn’t something similar to a shopping cart with an inventory list that I know you guys already have in different areas. I know it might cause me to change my form and I’m willing if it works.

    in a shopping cart the customer adds 3 things to his ‘shopping cart’ and at checkout there is somehow some calculations that figure how many were used and deducts that from how many are in inventory. It seems this would do what I wanted but perhaps stop short of deducting a number from inventory (for now until I get to that part of this build) and give me a running total so I know that I’ve sold 15 birdbaths (or whatever) this year in total.

    or am I stuck with how it is?


    thanks!


    jeff


  • Amos_W
    Replied on January 16, 2022 at 2:56 PM

    Hey there,

    It sure is worth a try,

    You can easily check that by cloning the form and importing the data

    Here's how to clone the form

    https://www.jotform.com/help/27-how-to-clone-an-existing-form-from-your-account/

    Here's how to import the data

    https://www.jotform.com/help/1001-how-to-import-data-in-jotform-tables/

    Please let us know if you need any further assistance,

    Thank you

  • beverlyhimmerich
    Replied on February 26, 2022 at 11:42 PM

    Hello,

    I just wanted to follow up and see if there was any movement on the feature request:


    I have forms where I have a list of locations which are designated by site numbers, I have a lookup widget on this form and it does a great job:

    1645934713 621af8791ae6d  Screenshot 10


    I also have a list of parts that can be used to repair each site - the issue I have is that I have the chance of using multiple parts at each location so I have to repeat the parts list over and over to have it work... I would prefer to have one list where if I make an edit, it would flow to the other fields - example:

    1645934859 621af90b1d2a2  Screenshot 21

    (Blurred because it's public)


    in my options tab, I have to add each part:

    1645936801 621b00a18491e  Screenshot 32

    Then I have to associate a price for each part separately:

    1645934969 621af979ba480  Screenshot 43

    I have 10 areas on my form where I have parts, I have to repeat these steps 10 times each time there is a price change or to add a part... this means that if there are two changes, I have to edit the list in options, then the calculation values, (that's two changes) 10 different times or, basically 20 changes.


    If there was an option to have a parts list that was common among the fields, it would be awesome!


    Thanks!

    Jeff



  • Durand_C
    Replied on February 27, 2022 at 4:17 AM

    Hi @ beverlyhimmerich,

    Thank you for contacting Jotform Support.

    Regarding the feature, our developers are working on it. They have not reached a resolution yet. Regarding the second question, please let us take a look at your part list and we will get back at you as soon as we reach a resolution.

  • Durand_C
    Replied on March 2, 2022 at 5:56 PM

    Hi @ MKOndemand,

    Thank you for your contacting Jotform support.

    We have separated the second part of your question and assign it to a different thread: https://www.jotform.com/answers/3814236-updating-calculation-values-automatically. So we can assist each issue separately.