Question about Grid Q&A Tables

Follow

Comments

9 comments

  • Avatar
    Scott Campbell

    Thanks, Lynda -- this makes a great showcase for using TheFormTool's Math feature inside a Grid.  I attached an example form in which I filled in two rooms worth of data from your spreadsheet to get you started.  A few things worth pointing out:

    1.  The four right-hand columns in the Grid are Derived Answers.  They are automatically calculated after the user fills in the left-hand columns and clicks Fill.  The first row of each right-hand column contains a Math field that performs the desired calculation for that column.

    2.  When you're ready to save this form for others to use, you should click Row/Col, Show/Hide to hide the derived answers.  I left them visible so you could see how I created the form, but form users will find it simpler to navigate the form when the derived answers are invisible.

    3.  The information contained in the Grid doesn't have to be presented in table format in the finished document.  I did use table format in this example form, but I didn't have to.  I could have spread it out in a more narrative format instead.  ("The kitchen will be floored with tile at a cost of...")  Or I could glean additional bits of information from the Grid too.  ("Five rooms in the house will be carpeted.")

    Let us know if you need help when you apply these ideas in your actual form.




    Calculations in Grid.docx
  • Avatar
    Rick

    Thanks Scott

    I understand the underlying thought process, but am still struggling with building the math formula.  For instance when I go to choose my first field to build my formula, why I am seeing multiple choices for each field.  The field that I want to select is named BR.  I have the following 5 choices:

    • BR #F
    • BR#L
    • BR#N
    • BR#P
    • BR#X

    See attached file for a screen shot.   I am not sure which field to choose and dont know why there are so many when I only have one column named BR.

    Thanks, Lynda




    Which Field do I choose.docx
  • Avatar
    Rick

    Thanks Scott

    I understand the underlying thought process, but am still struggling with building the math formula.  For instance when I go to choose my first field to build my formula, why I am seeing multiple choices for each field.  The field that I want to select is named BR.  I have the following 5 choices:

    • BR #F
    • BR#L
    • BR#N
    • BR#P
    • BR#X

    See attached file for a screen shot.   I am not sure which field to choose and dont know why there are so many when I only have one column named BR.

    Thanks, Lynda

  • Avatar
    Scott Campbell

    I went looking in the Expert Guide so I could refer you to a good explanation of this, Lynda, and discovered that it's really not covered thoroughly enough.  It is touched upon in Example 7b on page 82, but we'll expand that in our next update.

    In the meantime, here's the missing explanation:  Those letter designations refer to the F=First, L=Last, N=Next, P=Previous, and X=Current item in a list.  You will almost always use #X, because you usually want to display the Current item in a list, like this:

    {List:{People#X}
    |{People#X}
    |{People#X}}

    Which results in a list like this:

    Alan
    Betty
    Carl
    Diane

    But here's an example where, in addition to displaying the current person in the list {People#X}, you also want to refer to the Previous person in the list {People#P}:

    {List:{People#X} is first.
    |{People#X} is after {People#P}.
    |And {People#X} is last.}

    Which results in a list like this:

    Alan is first.
    Betty is after Alan.
    Carl is after Betty.
    And Diane is last.

    And here's one that is extra clever.  Each sentence in the list refers to the First person in the list {People#F} in addition to the Current person in the list {People#X}.  And the sequential words (first, second third) are created by using a sequential field that refers to the Previous person in the list {#P}.

    {List:{People#X} is the winner.
    |{People#F}’s {#P} runner up is {People#X}.
    |{People#F}’s {#P} runner up is {People#X}.}

    Which results in a list like this:

    Alan is the winner.
    Alan’s first runner up is Betty.
    Alan’s second runner up is Carl.
    Alan’s third runner up is Diane.

    All of that is to explain why we have #F/#L/#N/#P/#X options and what they do.  For your purposes, though, all you need to know at the moment is that you should use the #X option in your math calculations -- it provides the value in the Current row of the grid.  But in another situation, if you wanted to, for example, add the value in the Current row to the value in the Previous row, you would use Value#X + Value#P.

    Thanks for another excellent question, Lynda, and please keep us posted on your progress.

     

  • Avatar
    Rick

    Hi Scott

    Thanks for explaining this for me.

    I am still having problems however and have been working on this all morning. I am not going to give up though. They say that a sign of insanity is doing the same thing over and over again expecting different results.  I don't want people to think I am insane so I must ask for help now.

    Again, I am attaching a file with screenshots of my handy work.

    I set up a grid that should do math calculations discussed previously.  I added a table in my form which should be populated with the fields from my grid that have the calculations.

    As you can see from my screenshots something is wrong.  Do you have any idea as to what?

    THANKS, Lynda




    Math calulation and results.docx
  • Avatar
    Scott Campbell

    I suspect the problem is the "+" characters that precede the positive numbers.  Could you remove those and see if the problem is gone?  TheFormTool assumes numbers are positive unless they're preceded by a minus symbol (-), and I think the plus characters might be confusing it.

    If that does work, let me know and in our next update we can tweak the program so that it will ignore "+" characters that precede numbers.  Or if that doesn't work, then please send me a copy of your form (not just screenshots) so I can investigate further.  Thanks, Lynda.

  • Avatar
    Rick

    Hi Scott

    You were correct as far as the math calculations go. yipppeeee  I took the "+" character off and the math worked perfectly.

    What did not work for me was the table that I created in the form.  I am attaching my form.....get ready because it is very long.  The table in question in on page 5 of 25.  I clearly put the wrong fields in this table but am just not sure how to go about doing this correctly.  I want the fields in the grid with the math calculations in them to fill in this table.

    Another thing I am not sure that a table is the best way to show this information in the report.  When the inspector completes the Grid Q&A table at the end, he will most likely have to add rows as he will be measuring more than just two rooms.  I was just working with two rows until I learned how to do this.  As he add rows, will the calculations carry though to the new rows that he adds and then what happens with the table in the form. I thought some sort of a list would be best but I am brain dead at this point.

    One last question, If I wanted to go into the Math calculations in the grid and change them for some reason, do I have to start over or is there a way to make changes/edits?

    Your help is very very much appreciated

    Thanks, Lynda




    _PROPERTY INSPECTION REPORT MASTER 7-3.docx
  • Avatar
    Scott Campbell

    Hi, Lynda.

    The good news is, this latest problem is another one that's really simple to fix.  You typed some extra bracket characters around the math fields in the first row of the grid.  If you remove those bracket characters, your form works perfectly.  (But don't remove the brackets around the word "ditto" later in the grid -- those brackets are required.)  And now on to your other questions: 

    Yes, when the form user adds more rows to the answer grid (using the Add button to add as many rows as needed), the calculations that you created in the first row will be carried out in all of the additional rows too, so no worries.  To be extra helpful, as the form author you can save the user a few clicks by using the Add button yourself to create as many empty rows as are likely to be used.  And you could even fill in the names of several rooms (Kitchen, Master Bedroom...) to give them a head start.

    You're correct that you should use a List in the body of the form, rather than the way you've got it now.  Lists are very flexible and are designed to expand and contract automatically to fit the number of rows in the answer grid.  If you look at the last sample form I provided, you'll see that the table in that form is actually a List.  So when more rooms are added to the grid, the List will automatically expand to accommodate the new info.  You can copy that List from my sample into your form, or you can create your own table-formatted List from scratch.  (Table-formatted Lists are first discussed on page 27 of the Expert User Manual, and also appear in several of the Excrutiatingly Detailed Examples.)

    And yes, you can modify the calculations in math fields without starting from scratch.  Just put your cursor in the field, click Field to open the field editing screen, click Math to see the formula, and click the pencil icon to edit the formula.

    Progress!  Don't be discouraged, Lynda; you're advancing quickly and using a bunch of the most sophisticated features of TheFormTool all at once -- Grids, Math Fields, Derived Answers, and Custom Lists.  The next form you work on is going to be a piece of cake.

  • Avatar
    Rick

    Hi Scott

    Everything works perfectly now thanks to your help.  I really appreciate it.

    Nope not discouraged just impressed with all that The Form Tool can do.  Can't wait to learn more and really push it to it's limits.  LOL  Looking forward to finishing this form, letting the inspector give it a test drive, and then moving on to creating many more forms.

    If all goes well, we will be looking into purchasing a Windows-based Tablet so that he can take it out in the field and finish his Inspection reports immediately   Any suggestions for Tablets without spending toooooo much $$$ would be very much appreciated.  I did read  under your Support Section that you recommend the Surface Pro.  Are there any others in addition to Surface Pro?

    Thanks again, Lynda (for Rick)

Please sign in to leave a comment.