11 Replies Latest reply on Dec 19, 2011 11:24 AM by ESing

    FMP forms

    ESing

      Title

      FMP forms

      Post

      New to FMP. Am creating a new db for my company, tracking "tblProject" as 1 to many with "tblProperties" which is many to many (so have a linking table) with "tblInvestments"

      The issue is that there are currently 5 types of investments and each one has basic information plus additional info only related to that inv. type AND different people will be responsible for inputting new information. Should "Investments" stay as one large table with a lot of null values and if so, how does that work with creating unique forms for each type. Or should they be different tables and if so, how would that work for making queries for something like "total investment amount for a specific year"?

      Also, in Access, I would picture a main form for "tblProperty" with Project info included and then tabbed sub-forms - 1 for each type of investment (with still the question about whether it's one table or many investment tables)...

      Any ideas on the FMP equivalent or best practices for this?

        • 1. Re: FMP forms
          philmodjunk

          tblProject----<tblProperities----<joinTable>---tblInvetsments.  (--< means one to many)

          In FileMaker, a portal can serve the same basic function as an Access Sub form. So you can put portals in the panels of your tab control.

          There isn't a clear cut right or wrong approach to your question about different investment types. I definitely wouldn't define completely separate investments table, but I might add a "detail" table for each investment type if each will have a lot of fields unique to that one investment.

          Whether you keep to a single table with many unused fields or implement related detail tables, you would, in either case, create layouts for each type of investment and all would be based on tblInvestments. Each, however, would only display the fields--whether all in tblInvestments or some from a related detail table needed for that investment type.

          Scripts, script triggers etc would be used to limit the records accessed on each such layout to only those of the correct investment type for that layout.

          • 2. Re: FMP forms
            ESing

            OK, I've given it a try and spent some time going through an external manual... Could someone maybe walk me through this in a little bit more detail? I get the idea of separate detail tables, but am not sure on which attribute they should connect, and then the way I picture it, I would need to make a drop down box of all the possible combinations of investment types and trust someone to pick the right one? Is there a way to limit the parameters? 

            For example, can I have all investments in one table, create a form for all the property/project info but then create a button that says "add Lending" or "add HR Grant" and then it leads one to a different form with the investment type autofilled so that they would only need to choose the sub category and then answer the specific questions... 

            Coming from Access, I am not used to the idea that forms are tables so when you create a form, you make a new table, so how should the original tabls be built? It seems like info is just copied multiple times?

            • 3. Re: FMP forms
              philmodjunk

              Coming from Access, I am not used to the idea that forms are tables so when you create a form, you make a new table, so how should the original tabls be built? It seems like info is just copied multiple times?

              Forms are NOT tables in FileMaker. Open Manage | Database | Tables. Every table you create will be listed there. Click the Relationships tab. Every "box" on this chart refers to one table on the tables tab or in an external file. However, you can make as many "boxes" here as you need to refer to the same table in order to define the relationships you need. These "boxes" are called "table occurrences". I'll add a link with more info on the concept at the bottom of this post.

              Now Open up a layout and enter layout mode. Check Layout setup and note the name in "Show Records From". This is the name of a Table Occurrence, one of those boxes on the Relationship tab. You can create as many layouts as you need, and have each refer to the same table occurrence if that's what you need to get your database to work. This link to a table occurrence is much like the "record Source" for an Access form, but without any "WHERE" clause specified--which leaves just the "joins" defined in Manage | Database | Relationships.

              FileMaker can confuse people on these key distinctions when they are first learning how to use the system as FileMaker automatically creates a layout and table occurrence of exactly the same name as any new table you create.

              Here's a download link for a many to many relationship Demo file you may find useful:  http://www.4shared.com/file/dZ0bjclw/ManyToManywDemoWExtras.html

              Here's a tutorial on table occurrences you may find helpful:  Tutorial: What are Table Occurrences?

              • 4. Re: FMP forms
                ESing

                I think I understand the difference, but I still don't understand how they would be used (the phone number example confused me),... if every new layout must be based on an actual table and you're not duplicating them, then why have more than one image of it in the relatiosnhips graph? what I meant by tables and forms being the same was that when you start a new table a form is automatically created, but if you want to customize one, is it better to start ANTOHER new one or to edit the basic one attached to the table created?

                I guess I only really want to know how this affects the file i'm trying to set up and all this additional information isn't really helping me decide. There doesn't seem to be a singular "how-to" process to follow.... Not sure I'm making any progress on this

                • 5. Re: FMP forms
                  philmodjunk

                  if every new layout must be based on an actual table and you're not duplicating them, then why have more than one image of it in the relatiosnhips graph?

                  So you can define different relationships between the same two tables. Each Table Occurrence does not necissarily have a ocrresponding layout. Some are added as needed for portals (think Access SubForm) and calculations. And Layouts are based on Table Occurrences, not directly on a table.

                  when you start a new table a form is automatically created, but if you want to customize one, is it better to start ANTOHER new one or to edit the basic one attached to the table created?

                  You have more than one option, use what works for you. You can edit the default layout to serve your needs. You can enter Layout Mode and choose duplicate layout to make a copy of that layout in order to create a similar layout based on the same table occurrence. You can also choose New Layout to start from scratch--either using the new layout wizard or shortcutting to a "blank" layout in order to build your own step by step.

                  I guess I only really want to know how this affects the file i'm trying to set up

                  It's important to understand the underlying function of the basic components of a FileMaker system or you will be limited only to what others tell you how to do instead of figuring this out for your self.

                  You've posted this question twice, correct? (responded to another thread about investments and details and figured you'd post back there with follow up questions about how to set up detail tables there unless you decided to keep all fields in a unified Investments table--which also works.)

                  I'll be glad to answer more specifically, but to do that, I need a more complete picture of what you are trying to do and where you are hitting a road block.

                  (the phone number example confused me)

                  You are welcome to post any questions about the tutorial here and I'll be glad to take a swing at answering them.

                  • 6. Re: FMP forms
                    ESing

                    yea, sorry... I added a new post this morning b/c this one was old... I'll just use this one from now on, is there any way I could send you my skeleton file so that i don't have to type out the setup? it'd be easier to explain roadblocks that way

                    • 7. Re: FMP forms
                      philmodjunk

                      Why not capture a screen shot of Manage | Database | Relationships and upload that?

                      As I understand it, you have three tables:

                      tblProperties-----<Join>---tblInvestments.

                      The simplest method to do what you want is to just keep it that way and create as many layouts that refer to tblInvestments as you need for your different types. You can put a "new investment" field on your Properties layout, formatted as a value list of investment types with either a script trigger or a button to perform a script that changes to the correct layout and then creates a new record on that layout for the new user. A more sophisticated trick is to use the New Window script step to pop up a small floating window where the user selects an investment type from a value list and then clicks a button to close the window, switch to the correct layout and then creates the new record. Such a floating window can just capture the user's choice for an investment type or it can containe additional fields common to all investment types if you want.

                      I'd get that working first as you'd do this the same way with either a unified table or added "detail" tables for your various investment types. (The only real reason to create detail tables here is to make the field definitions easier to manage for you the developer. The user won't see any difference on the layouts.

                      Here are two scripted options for creating a new record of the specified investment type:

                      We'll call the field "gNewInvestmentType", and give it global storage in field options in Manage | database | fields.

                      #If each value in the value list is exactly the same as the layout name for that investment type:
                      Set Variable [$PropertyID ; value: TblProperties::PropertyID ]
                      Go To Layout [Investments (Investments)] //any layout based on TblInvestments will server here
                      New Record/Request
                      Set Field [TblInvestments::InvestmentType ; TbleProperties::gnewInvestmentType ]
                      Set Variable [$InvestmentID ; value: TblInvestments::InvestmentID]
                      Go to Layout [JoinTable]
                      New Record/Request
                      Set Field [Join::InvestmentID ; $InvestmentID]
                      Set Field [Join::PropertyID ; $PropertyID]
                      Go To Layout [TblProperties::gnewInvestmentType ] //use the layout name by calculation option

                      #If each value in the value list does not exactly match the name of the
                      Set Variable [$PropertyID ; value: TblProperties::PropertyID ]
                      Go To Layout [Investments (Investments)] //any layout based on TblInvestments will server here
                      New Record/Request
                      Set Field [TblInvestments::InvestmentType ; TbleProperties::gnewInvestmentType ]
                      Set Variable [$InvestmentID ; value: TblInvestments::InvestmentID]
                      Go to Layout [JoinTable]
                      New Record/Request
                      Set Field [Join::InvestmentID ; $InvestmentID]
                      Set Field [Join::PropertyID ; $PropertyID]
                      If [TblProperties::gnewInvestmentType = "Remodel"]
                          Go To Layout ["Remodel Investments" (TblInvestments)]
                      Else If [TblProperties::gnewInvestmentType = "New Build"]
                          Go To Layout ["New Buld INvestments (TblInvestments)]
                      #add more Else If's for each layout in value list
                      End If

                      Note that there are many ways to work with many to many relationships. You may want to check out the demo file to see if anything in it is useful to you.

                      • 8. Re: FMP forms
                        ESing

                        here's what i have ... i'm trying to decipher your last message, woudl the top set of instructions apply the way I have it laid out?

                        • 9. Re: FMP forms
                          philmodjunk

                          Either script can work with what you have here. What I called "Join" in the script, you have named "tblPropertyInvestment".

                          I am assuming that from a layout based on tblProperty, you'll want to add a new investment record, that, via a new record in the join table, is automatically linked to that current property record. THus, it creates a new Investment Record and identifies the type from what the user specified. This produces the needed ID Number (InvestmentID) so that a new join record can be set up with the correct InvestmentID and PropertyID values to produce that link.

                          The script then goes to the layout that is specific to the Investment Type selected by the user.

                          The only difference between the two scripts is whether you give each investment layout the same exact name as listed in the value list or different names.

                          Note that this is just one approach. Many variations on this theme are possible. To select an existing Investment for the current Property record or vice versa, take a look at the methods used in the Demo file for linking events to Contacts.

                          • 10. Re: FMP forms
                            philmodjunk

                            BTW, assuming tblProperties::PropertyID is an auto-entered serial number, you have correctly set up the minimum needed for a one to one relationship between Placques and Properties. FileMaker won't "know" that this is a one to one relationship, however, unless you go to Pacques::PropertyID and specify a unique values validation rule on this field to prevent additional related Placques Records from being created for a given Property record. If you specify that option, the "crows feet" shown will disappear.

                            • 11. Re: FMP forms
                              ESing

                              I can't promise I won't have more questions later but I'm going to try to process some of this stuff and see how it goes. Thanks for the all the help!