Web form populates parent/child relationship through category

dana y
dana y
edited 12/09/19 inFormulas and Functions

Hello,

was hoping to get some help on an issue i am having .. we have an inventory control sheet .. we would like to use a web form(s) to not only take out something of the inventory but to also add something to the inventory. I added a Category column and that is reflected in a dropdown list that matches all of the parents.

Question is i want to know how to by selecting a category on the form will it populate under the parent category automatically on the sheet?

thanks much

dana

Comments

  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭

    Hi Dana,

    Unfortunately, it won't, but it's quite easy to move it to the right section.

    I hope this helps you!

    Best,

    Andrée Starå - Workflow Consultant @ Get Done

    SMARTSHEET EXPERT CONSULTANT & PARTNER

    Andrée Starå| Workflow Consultant / CEO @WORK BOLD

    W:www.workbold.com| E:[email protected]| P: +46 (0) - 72 - 510 99 35

    Feel free to contact me about help with Smartsheet, integrations, general workflow advice, or something else entirely.

  • understand .. so it has to be a manual process?

  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭

    Yes, but it might work with a service like Zapier, Azuqua or similar. Depending on how much it would benefit your use case it could make sense to look at those options or the API.

    Best,

    Andrée

    SMARTSHEET EXPERT CONSULTANT & PARTNER

    Andrée Starå| Workflow Consultant / CEO @WORK BOLD

    W:www.workbold.com| E:[email protected]| P: +46 (0) - 72 - 510 99 35

    Feel free to contact me about help with Smartsheet, integrations, general workflow advice, or something else entirely.

  • ok thanks for the input:)

    I was attempting to use the inventory template .. and it indicated the form to add items to the sheet; simply adds them to the bottom... so i assumed things have to be manually sorted.

    Do you have any suggestions on any different methods for inventory control?

  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭

    Happy to help!

    Regarding other suggestions, I would need more information about your specific needs and more to be able to asses if something else would work better.

    Best,

    Andrée

    SMARTSHEET EXPERT CONSULTANT & PARTNER

    Andrée Starå| Workflow Consultant / CEO @WORK BOLD

    W:www.workbold.com| E:[email protected]| P: +46 (0) - 72 - 510 99 35

    Feel free to contact me about help with Smartsheet, integrations, general workflow advice, or something else entirely.

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    You could use 2 sheets. One master sheet with a list of all inventory components in the order you want them in to include hierarchies. The other sheet would be the reference sheet where your forms are populating whether plus or minus inventory. The order in this one doesn't matter.

    In the master sheet, you would enter a formula like this:

    =SUMIFS({Reference Sheet Range 1}, {Reference Sheet Range 2}, [Name of column on master sheet used to list components]@row)

    范围参考表1。你会选择the column where the amounts are logged from the form. + and - can both be used in the same column and actually makes this particular process easier.

    Range 2 is also on the reference sheet. You will select the column where the actual components are listed.

    [Name of column on master sheet used to list components] is exactly what it says.

    What this does is sums up all numbers in the reference sheet where the component is the same as the one in that row of the master sheet.

    Once you drag-fill that down, it will give you a live, running total of each list component in stock. See picture below. On the left is my master sheet. the right is the sheet where the forms are populating. It will provide a live total as new rows are added whether it be because you have added or removed inventory.

    Capture.PNG

    thinkspi.com

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the公式手册模板!
Set the Sheet Summary field as text\/number then add +\"//www.santa-greenland.com/community/discussion/30761/\" to the end of the MAX function (plus quote quote) to convert it into a text string.<\/p>

=MAX([Modified]:[Modified]) + \"//www.santa-greenland.com/community/discussion/30761/\"<\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question"},"bookmarked":false,"unread":false,"category":{"categoryID":322,"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions","allowedDiscussionTypes":[]},"reactions":[{"tagID":3,"urlcode":"Promote","name":"Promote","class":"Positive","hasReacted":false,"reactionValue":5,"count":0},{"tagID":5,"urlcode":"Insightful","name":"Insightful","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":11,"urlcode":"Up","name":"Vote Up","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":13,"urlcode":"Awesome","name":"Awesome","class":"Positive","hasReacted":false,"reactionValue":1,"count":0}],"tags":[]},{"discussionID":107030,"type":"question","name":"How to subtract percentages?","excerpt":"I created a new column % Not complete and am trying to add a formula to have =100%-[%Complete]@row. Answer #UNPARSEABLE I have also tried =100-%Complete]@row. Answer: This will populate an answer, however I cannot adjust the formatting from % to number Example: %Complete = 13% Correct answer should display 87% Second…","snippet":"I created a new column % Not complete and am trying to add a formula to have =100%-[%Complete]@row. Answer #UNPARSEABLE I have also tried =100-%Complete]@row. Answer: This will…","categoryID":322,"dateInserted":"2023-06-28T15:56:57+00:00","dateUpdated":"2023-06-28T16:22:44+00:00","dateLastComment":"2023-06-28T16:35:48+00:00","insertUserID":150369,"insertUser":{"userID":150369,"name":"v.winters","url":"https:\/\/community.smartsheet.com\/profile\/v.winters","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!p_DnqRu23us!e98n5_-JJOs!fH1r3mTuHpU","dateLastActive":"2023-06-28T16:34:54+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":150369,"lastUserID":150369,"lastUser":{"userID":150369,"name":"v.winters","url":"https:\/\/community.smartsheet.com\/profile\/v.winters","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!p_DnqRu23us!e98n5_-JJOs!fH1r3mTuHpU","dateLastActive":"2023-06-28T16:34:54+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":2,"countViews":21,"score":null,"hot":3375939165,"url":"https:\/\/community.smartsheet.com\/discussion\/107030\/how-to-subtract-percentages","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/107030\/how-to-subtract-percentages","format":"Rich","tagIDs":[254],"lastPost":{"discussionID":107030,"commentID":382885,"name":"Re: How to subtract percentages?","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/382885#Comment_382885","dateInserted":"2023-06-28T16:35:48+00:00","insertUserID":150369,"insertUser":{"userID":150369,"name":"v.winters","url":"https:\/\/community.smartsheet.com\/profile\/v.winters","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!p_DnqRu23us!e98n5_-JJOs!fH1r3mTuHpU","dateLastActive":"2023-06-28T16:34:54+00:00","banned":0,"punished":0,"private":false,"label":"✭"}},"breadcrumbs":[{"name":"Home","url":"https:\/\/community.smartsheet.com\/"},{"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions"}],"groupID":null,"statusID":3,"attributes":{"question":{"status":"accepted","dateAccepted":"2023-06-28T16:35:51+00:00","dateAnswered":"2023-06-28T16:31:26+00:00","acceptedAnswers":[{"commentID":382883,"body":"

@v.winters<\/a> <\/p>

If the two percent columns are formatted as percent columns (see screenshot), then the % Not Complete column would have this formula: <\/p>

=1 - [%Complete]<\/em>@row<\/p>

\n
\n \n \"Menu.PNG\"<\/img><\/a>\n <\/div>\n<\/div>\n

Hope this helps!<\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question"},"bookmarked":false,"unread":false,"category":{"categoryID":322,"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions","allowedDiscussionTypes":[]},"reactions":[{"tagID":3,"urlcode":"Promote","name":"Promote","class":"Positive","hasReacted":false,"reactionValue":5,"count":0},{"tagID":5,"urlcode":"Insightful","name":"Insightful","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":11,"urlcode":"Up","name":"Vote Up","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":13,"urlcode":"Awesome","name":"Awesome","class":"Positive","hasReacted":false,"reactionValue":1,"count":0}],"tags":[{"tagID":254,"urlcode":"Formulas","name":"Formulas"}]},{"discussionID":107008,"type":"question","name":"COUNT CELLS BY MONTH WITHIN ONE RANGE (COUNTIFS?)","excerpt":"Hello Everyone! First time asking for help, I always found my answers within everyone else's questions, but not this time! We work off one main sheet where all our leads come in, I would like to create in another sheet a count by month of that \"created date\" column, to see how many leads came in in January, in February,…","snippet":"Hello Everyone! First time asking for help, I always found my answers within everyone else's questions, but not this time! We work off one main sheet where all our leads come in,…","categoryID":322,"dateInserted":"2023-06-28T12:43:18+00:00","dateUpdated":null,"dateLastComment":"2023-06-28T16:36:21+00:00","insertUserID":121475,"insertUser":{"userID":121475,"name":"Dan Benitah","url":"https:\/\/community.smartsheet.com\/profile\/Dan%20Benitah","photoUrl":"https:\/\/lh3.googleusercontent.com\/a\/AAcHTteTBX82EPc5KIaiSjoPiuvvmUQ_FSNi2hlGCTngDw=s96-c","dateLastActive":"2023-06-28T18:25:32+00:00","banned":0,"punished":0,"private":false,"label":"✭✭"},"updateUserID":null,"lastUserID":45516,"lastUser":{"userID":45516,"name":"Paul Newcome","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Paul%20Newcome","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/082\/nQPUTVFKKWDJ2.jpg","dateLastActive":"2023-06-28T18:51:58+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":8,"countViews":41,"score":null,"hot":3375931179,"url":"https:\/\/community.smartsheet.com\/discussion\/107008\/count-cells-by-month-within-one-range-countifs","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/107008\/count-cells-by-month-within-one-range-countifs","format":"Rich","lastPost":{"discussionID":107008,"commentID":382886,"name":"Re: COUNT CELLS BY MONTH WITHIN ONE RANGE (COUNTIFS?)","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/382886#Comment_382886","dateInserted":"2023-06-28T16:36:21+00:00","insertUserID":45516,"insertUser":{"userID":45516,"name":"Paul Newcome","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Paul%20Newcome","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/082\/nQPUTVFKKWDJ2.jpg","dateLastActive":"2023-06-28T18:51:58+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"}},"breadcrumbs":[{"name":"Home","url":"https:\/\/community.smartsheet.com\/"},{"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions"}],"groupID":null,"statusID":3,"image":{"url":"https:\/\/us.v-cdn.net\/6031209\/uploads\/7UUHRXPOF0WT\/screen-shot-2023-06-28-at-8-35-55-am.png","urlSrcSet":{"10":"","300":"","800":"","1200":"","1600":""},"alt":"Screen Shot 2023-06-28 at 8.35.55 AM.png"},"attributes":{"question":{"status":"accepted","dateAccepted":"2023-06-28T18:29:37+00:00","dateAnswered":"2023-06-28T14:08:52+00:00","acceptedAnswers":[{"commentID":382837,"body":"

The formula from @Paul Newcome<\/a> should work, though the below would be an alternative:<\/p>

=COUNTIFS({Date created Range 1}, AND(IFERROR(YEAR(@cell), 0) = YEAR([Start Date]@row), IFERROR(MONTH(@cell), 0) = MONTH([Start Date]@row)))<\/p>

Either of these should work.<\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question"},"bookmarked":false,"unread":false,"category":{"categoryID":322,"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions","allowedDiscussionTypes":[]},"reactions":[{"tagID":3,"urlcode":"Promote","name":"Promote","class":"Positive","hasReacted":false,"reactionValue":5,"count":0},{"tagID":5,"urlcode":"Insightful","name":"Insightful","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":11,"urlcode":"Up","name":"Vote Up","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":13,"urlcode":"Awesome","name":"Awesome","class":"Positive","hasReacted":false,"reactionValue":1,"count":0}],"tags":[]}],"initialPaging":{"nextURL":"https:\/\/community.smartsheet.com\/api\/v2\/discussions?page=2&categoryID=322&includeChildCategories=1&type%5B0%5D=Question&excludeHiddenCategories=1&sort=-hot&limit=3&expand%5B0%5D=all&expand%5B1%5D=-body&expand%5B2%5D=insertUser&expand%5B3%5D=lastUser&status=accepted","prevURL":null,"currentPage":1,"total":10000,"limit":3},"title":"Trending in Formulas and Functions ","subtitle":null,"description":null,"noCheckboxes":true,"containerOptions":[],"discussionOptions":[]}">

Trending in Formulas and Functions