3 Replies Latest reply on Dec 19, 2011 11:45 AM by philmodjunk

    Multiple Error Proof User Friendly Forms based on 1 large Table

    ESing

      Title

      Multiple Error Proof User Friendly Forms based on 1 large Table

      Post

      I am working for a non profit that is trying to create a database with information from the last 50 years and be ready to input new info as we move forward.The basic set up is this:

      All work is based on a property >-----< investment (witha join table in between).

      Some of them have sub categories, like Restoration -Full or -Partial, but all would have basic info such as Amount, Year, ProjectMgr. Then there are a couple that have some unique information that would add extra fields which would only be filled for those types of investments. Also, different people are responsible for entering information for each type AND queries need to be based on each type individually or comparing all 3.

      I would like all investments to be in one table so that "total amount" could be tallied or sorting projects by year would be simple. My questions is when it comes to the forms, how can I make them user-friendly? Would data entry always have to start with the long list of all possible investment type combinations? or is there a way to create a script that would simplify the data.

      For example, I add a new property, right below I have a choice of buttons for type of investment to add. so i click the "restoration" button, it opens a form with restoration already chosen as Type and the next selection is a drop down box that is now limited to "full" or "partial" and then all questions are related only to that type?

      Is this even possible? If so, how or is there a better way. I am relatively new to FMP and am slightly confused by the "tables are forms are tables" concept... But even in Microsoft Access, the intial relationship is an issue b/c I don't want a combo box with a choice of 10+ investment cmbinations... am trying to reduce input error.

       

      I hope this all makes sense and someone can help... Thanks!

        • 1. Re: Multiple Error Proof User Friendly Forms based on 1 large Table
          philmodjunk

          You are not limited to just one layout for each table. If you have different types of investment records that require different subsets of the total fields in your table, you can create a different layout for each.

          You can also break up your data into more tables, but with a common investment table. You can then add "InvestmentDetail" tables, one for each type as needed for those extra fields. Your fields for data common to all investments would be kept in the investments table. Your individual layouts can combine fields form the Investments table with the appropriate detail table on each such layout. This is in many ways a developer preference kind of issue. You can keep it all in one table and just leave many fields empty for each record or you can put those fields in related tables with a one to one relationship between them. What you see on the screen and how they function is nearly indistinguishable to the user.

          And you can use Scripted Finds, constrain founds sets, Filtered portals, etc. to limit the records accessed on each layout to only investments of a specified type.

          • 2. Re: Multiple Error Proof User Friendly Forms based on 1 large Table
            ESing

            2 follow up questions: if I use the detail related tables, would I just use InvestmentID and tie to the Inv table and then use a portal?

            -Can you elaborate on how to use Scripted Finds? So let's say I now have 2 forms, a lending form and a grant form, each have 2 sub choices... how do i limit the choice parameters in the form layout? 

            • 3. Re: Multiple Error Proof User Friendly Forms based on 1 large Table
              philmodjunk

              The use of detail tables would not change the basic many to many relationship with a join table that you have here. These would be additional tables linked in one to one relationships with your Investments table.

              TblProperties-----<TblPropertyInvestment>-----TblInvestments------RehabDetails
                                                                                   |              |
                                                                    NewBuildDetails      PurchaseDetails

              I've shown three possible detail tables. YOu can add as many or as few as you need.

              You'd set up a layout for each investment type where you can place fields from both TblInvestments and one of the Detail Tables to collect all of the info about that investment. This will look and feel just as though the fields from the detail table and the Investments table were all defined in the same table due to that one to one relationship. The only difference is for you as the developer. If you have a lot of fields that are only used for a specific investment type, they can be easier to manage by putting them in the detail table for that type. Just make sure that the layouts are all based on TblInvestments, rather than the detail table or you could experience a few unexpected results when using new or duplicate record from the Records menu.

              Can you elaborate on how to use Scripted Finds? So let's say I now have 2 forms, a lending form and a grant form, each have 2 sub choices... how do i limit the choice parameters in the form layout?

              I'm not sure that we are talking about thw ame thing here as the second part of your question does not appear to have anything to do with a "find".

              A scripted find for RehabInvestments might look like this:

              Enter Find Mode[]//clear the pause
              Set field [Investments::Type ; "Rehab"]
              Set Error Capture [on]
              Perform Find[]

              With a slightly different version of this script, you can even pass the type as a script parameter and use the same script with each layout--passing different types as the parameter.

              Limiting choices shown in a value list can be as simple as using a different value list on each layout. It's also possible to set up a conditional value list where selecting a value in ond field (Grant vs. Loan, for example) limits the values shown in a second value list.

              If that's what you need, see these threads on conditional value lists:

              Forum Tutorial: Custom Value List?

              Knowledgebase article: http://help.filemaker.com/app/answers/detail/a_id/5833/kw/conditional%20value%20list

              Demo File: http://www.4shared.com/file/f8NsU2DJ/ConditionalValueListDemo.html