1 2 Previous Next 21 Replies Latest reply on Apr 19, 2012 4:32 PM by philmodjunk

    Displaying different fields depending on the choice in specified field

    LinkLincoln

      Title

      Displaying different fields depending on the choice in specified field

      Post

      I need to find a solution.

      I've created a layer which describes different investments of my clients.

      I have a field called "TypeOfInvestment" with dropdown menu (bonds, short-term invest, stock, etc.).

      Depending on what I choose from dropdown menu (i.e. bonds) I need different set of fields to be displayed on specified area of the layer to describe this particular investment (if my choice is bonds, fields 1,2,3 are displayed on a layer; if my choice is stock, fields 4,5 and 6 are displayed).

      I plan to arrange four fields TypeOfInvestment on one layer, because one client may have up to four different investments.

      I will be using my database on iPad, so the solution have to work on iOS.

      I would be greatful for any advice.

       

      Thanks in advance.

        • 1. Re: Displaying different fields depending on the choice in specified field
          philmodjunk

          I'm not yet an iPad user so you'll have to try out these methods to be sure they work in FM GO.

          I plan to arrange four fields TypeOfInvestment on one layer, because one client may have up to four different investments.

          Seems like you need up to 4 records for that client with the same field recording the investment type in the same field, but in different records of a related table. This would likely be a more flexible approach for your database.

          That said, one way to make different sets of fields (or other layout objects) appear and disappear is with an invisible tab control--with trigger controlled scripts that select the appropriate tab panel.

          I just uploaded a brand new demo file that illustrates that method as well as several other techniques: http://www.4shared.com/file/XGrhkmVV/file.html

          • 2. Re: Displaying different fields depending on the choice in specified field
            LinkLincoln

            I'm still having problem with my project. The demo you gave me is definitely the way I'm going, but I'll try to be more precise and maybe then you could be even more helpful.

             

            In my database I have RECORD1 - a customer who has 25 shares of Apple bought on 25th Jan 2010 for $10000 and the deposit in the amount of $20000 at 5% interest rate in Bank Of America. The fields I need to describe shares are : CompanyName(Shares), DateOfBuy(Shares), SingleSharePrice,NumberOfShares.

            To describe the deposit I need the following fields: CompanyName(Deposit), OpeningDate, Period, CashAmount, InterestRate.

            I then have 4 fields with dropdown menu called FIELD1, FIELD2, FIELD3, FIELD4.

            They are arranged on my layer like that:

             

            ----------------------------------------------------------------------

            | FIELD1.                                                              FIELD2.  |

            |                                                                                     |

            |                                                                                     |

            | FIELD3.                                                              FIELD4.  |

            ----------------------------------------------------------------------

             

            Now, to input this two products to my database under RECORD1, I have to click on FIELD1 and from dropdown menu choose SHARES. When I make the choice, exactly below FIELD1 shows up fields CompanyName(Shares), DateOfBuy(Shares), SingleSharePrice,NumberOfShares, one under the other. 

            Next, after clicking on FIELD2, I choose DEPOSIT and, analogously, under FIELD1 shows up fields CompanyName(Deposit), OpeningDate, Period, CashAmount, InterestRate.

            How do I exactly do that?

            • 3. Re: Displaying different fields depending on the choice in specified field
              philmodjunk

              I then have 4 fields with dropdown menu called FIELD1, FIELD2, FIELD3, FIELD4.

              They are arranged on my layer like that:

              Why do you have 4 fields with the same drop down menu? Why not just one?

              What viewing option will you use with this layout View as Form, View as List or View as Table?

              CompanyName(Shares), DateOfBuy(Shares), SingleSharePrice,NumberOfShares.

              CompanyName(Deposit), OpeningDate, Period, CashAmount, InterestRate.

              Note that you could reduce the fields that need to appear and disappear if you use the same field for the pair shown in blue and one field for the pair shown in green. The layout text used as the label for the field can update to match the type of transaction you are recording.

              • 4. Re: Displaying different fields depending on the choice in specified field
                LinkLincoln

                The viewing option I will use is Form View.

                I need 4 fields because, as I said before, one customer can have even 4 investments (i.e. two deposits and two shares).

                I also want, if it's not necessary, to show on a layer only four fields with drop down menu (cause the customer can have only one investment), without showing anything more.

                You have to know, I'm the very beginner in databases, so I'm developing mine in a very, very simple way.

                I will have four sets of identical fields, both for deposits, shares, and bonds. So, when the customer will have only one investment, only one set of fields will be used and the rest of them will be empty.

                More precisely, in the first set of fields I'll have: 

                CompanyName(Shares)1, DateOfBuy(Shares)1, SingleSharePrice1,NumberOfShares1, CompanyName(Deposit)1, OpeningDate1, Period1, CashAmount1, InterestRate1

                In the second set of fields I'll the exactly the same fields only ending with 2 instead of 1.

                So now, if I choose from FIELD1 (with the label Type Of Investment) Shares, the fields CompanyName(Shares)1, DateOfBuy(Shares)1,(...) will be shown under the FIELD1.

                If, from the FIELD2, I choose Deposit, the fields CompanyName(Deposit)2, OpeningDate2, Period2, CashAmount2, InterestRate2 will be shown.

                That's identical with set of fields for FIELD3 (fields names will be ending with 3) and FIELD4.

                 

                Summing up, if my database would consist only of full names and info about investments, every record would have one field with Name and 36 fields describing every investment (9 fields in each of 4 sets).

                Therefore, if in FIELD1 with d.d.m., I choose Shares, I fill only the fields describing shares, and the fields CompanyName(Deposit)1, OpeningDate1, Period1, CashAmount1, InterestRate1 will never be used.

                I know it's not the most brilliant way, but I have to create my database ASAP, because I need it for my customers, which number increases every day.

                And, as I'm the beginner, it's the only easy way to manage this info.

                So I need your help - how to set the database up to show specified set of fields in specified area on a layer after choosing specified value from drop down menu.

                 

                Gosh, I hope this time I cleared up a little bit.

                • 5. Re: Displaying different fields depending on the choice in specified field
                  philmodjunk

                  as I said before, one customer can have even 4 investments (i.e. two deposits and two shares).

                  Yes, but this does not require 4 sets of fields and using this approach limits you to a maximum of 4 investments. A related table for listing investments simplifies your interface and provides the ability to list any number of investments.

                  I know it's not the most brilliant way, but I have to create my database ASAP, because I need it for my customers,

                  What you describe takes more time to set up and is much harder to manage than to use a related table. You'll be better off changing your structure now than trying to change it later.

                  The method I described with the tab control in the demo file will work for you. Start by creating a tab control with the needed tab panels. Give each panel an object name by selecting the tab while in layout mode and entering a name in the name box at the top of the inspector's position tab. Once you can click on each tab while in layout mode and see the object name in the name box change, you have this part set up correctly. Then add your fields the way you want to see them to the panels of this tab control.

                  Let me know when you have this done and I'll take you through a script that selects the appropriate tab panel based on the value selected in the value list. Once all of that works, we can make the tab control disappear.

                  • 6. Re: Displaying different fields depending on the choice in specified field
                    LinkLincoln

                    Sorry, but I'm so noob in creating database, that I didn't understand what to do. Could you please be so kind and write step by step instruction?

                    Let's assume that I have only those fields needed: TypeOfInvest (dropdown menu with two options - shares and deposits); CompanyName;AmountOfShares;Interest(for deposit).

                    What do I have to do firstly with this fields, step by step?

                    Could you explain precisely what are tab control and tab panels?

                    • 7. Re: Displaying different fields depending on the choice in specified field
                      philmodjunk

                      A tab control is a set of one or more tab panels. Each "tab" in a tab control is called a "tab panel" when you lookup information on tab controls in FileMaker Help.

                      First, look up "Adding a tab control" in FileMaker help to familiarize yourself with tab controls and the various options you can select for one.

                      Enter layout mode.

                      Click the tab control tool at the top of the screen in the status area.

                      Draw a rectangle on your screen where you want the tab control.

                      This opens a dialog box called tab control set up.

                      Use the buttons and fields in this dialog to create three tabs (tab panels). Just for clarity in these instructions, label one tab panel "blank" and one "Shares" and the third "Deposit". When you click the tabs while in layout mode you can get each individual tab to show selection boxes at each corner. Click to select the blank tab. Now enter "BlankTab" into the name box on the inspector's position tab. Click the "Shares" tab to select it and enter "SharesTab" into the name box in the inspector. Name the third tab "DepositTab".

                      To see if you've done this correctly, you can click either tab and see the name change in the name box as that tab is selected in the tab control.

                      Leave the drop down field and company name field outside of the tab control borders. Click the Shares tab and drag the AmountOfShares field onto that tab. Click the Deposit tab and drag the Interest field onto the Deposit tab. If you'd done this correctly, clicking the Blank tab will leave these two fields hidden, clicking the Shares tab will show the AmountOfShares field. Click the Deposits tab and the AmountOfShares field will disappear and the Interest field will appear.

                      This is the basic set up. Once you have that working, we can make the tab control invisible and add a script trigger controlled script that selects the appropriate tab panel when you select an investment type in the drop down and also when you first pull up a specific record on your layout.

                      • 8. Re: Displaying different fields depending on the choice in specified field
                        LinkLincoln

                        Something like that?

                        http://www.sendspace.com/file/5mw9gc

                         

                        I did what you said and I have to admit, this is an idea :)

                        I will add my customers using Form View and this layout - on my first meeting with client I will run FileMaker on my iPad and click Add Record in the layout from demo.fp7. Later, when I find out new investments of my customer, I will navigate to his record in my database and edit Investments section.

                        But, how do I add multiple investments (multiple shares or multiple deposits)?

                        • 9. Re: Displaying different fields depending on the choice in specified field
                          philmodjunk

                          That looks like it will work.

                          Do you still want the tab control to be invisible? The next step is to make this tab control disappear and to use scripts that select the appropriate tab panel to make the correct set of fields appear. What you have does not have to go to that next step, you can keep it exactly like you have it if you prefer that simpler approach.

                          But, how do I add multiple investments (multiple shares or multiple deposits)?

                          Like I said before, you shouldn't have multiple investments as part of the same record, you should set up a related table of records and then you can use portals to this related table to show the different investments on your tab panels.

                          If you were to stick with the 4 sets of fields, You'd repeat this process to create 4 more tab controls, one above the other.

                          • 10. Re: Displaying different fields depending on the choice in specified field
                            LinkLincoln

                            Ok, so let's leave invisible tabs just for now.

                            Focusing on related table - could you explain how to do that (step by step would be great)?

                            How should I connect 4 records from table INVESTMENTS to one record in table CUSTOMERS? How to set up view to see all the investments on one tab (all my customer's shares in tab SHARES and all deposits in tab DEPOSITS)?

                            • 11. Re: Displaying different fields depending on the choice in specified field
                              philmodjunk

                              Open Manage | Database | Fields and select Customers from the table drop down.

                              Add this field if you have not already done so: __pk_CustomerID. Click Options after you create it and specify auto-entered serial number from the auto-enter tab in the field options dialog that pops up. (You can access field options for any defined field by either double clicking it or by selecting it, the clicking the Options button.)

                              If you have existing records already in Customers, you will need to update this field with serial numbers for all existing records. Each new records you create will automatically receive a serial number. Replace Field Contents is a very useful tool for this as it can update the field for all existing recorsd with serial numbers and update the settings for the serial number values at the same time.

                              Select Investments from the table drop down and add this field: _fk_CustomerID. Make both of these fields of type Number.

                              Now click the relationships tab and drag from __pk_CustomerID to _fk_CustomerID to create a relationship between these two tables. (__pk stands for Primary key, _fk stands for foreign key, you don't have to use this labeling on the fields if you don't want to.)

                              This produces this relationship: Customers----<Investments (---< means "one to many")

                              Customers::__pk_customerID = Investments::_fk_CustomerID

                              Double click this relationship line and then click "allow creation of records via this relationship.

                              Now you can place portals to related records in Investments on a layout based on Customers.

                              To have a tab for listing investments of just one investment type, you can set up filters on the portals and use scripting and auto-enter calculations so that simply entering data in the portal creates a related record in Investments of the type that is consistent with the tab on which it was created. I'll tell you how to do that in detail after you get this part set up and then succeed in placing a portal to investments on your customer layout. Please note that portals are extremely common items on Filemaker layouts. I recommend you look them up as soon as practical in FileMaker help to learn more than what I am able to post here.

                              • 12. Re: Displaying different fields depending on the choice in specified field
                                LinkLincoln

                                I can't create a relationship.. I mean, I did what you asked, just exactly like you described. I even added auto-enter serial to field _fk_CustomerID.

                                But, unfortunately, when I add customer in layout CUSTOMERS, and then I switch layouts to INVESTMENTS and add records, they're just records, not linked to specified customer..

                                Adding another customer (ClientID=2) and switching to INVESTMENTS layout causing view of investments added earlier, where it should be empty, waiting to introduce parameters of investment..

                                What's wrong?

                                • 13. Re: Displaying different fields depending on the choice in specified field
                                  philmodjunk

                                  What's wrong is that establishing a relationship doesn't mean that a new record in investments will automatically link to the current record on the customers layout. Either you design a layout to make this happen automatically--such as through use of a portal, you manually select a customer from a value list when creating a new investment record or you set up a script supported method that captures the value of the customer's ID field and enters it for you into the new investment record.

                                  In your case, I recommended setting up a portal.

                                  Put a portal to investments on a layout that specifies "Customer" in "Show Records From" in Layout Setup... If you've enabled "allow creation of records..." for investments in this relationship, you can create new investment records simply by entering data in a field from Investments placed inside the portal row.

                                  • 14. Re: Displaying different fields depending on the choice in specified field
                                    LinkLincoln

                                    I have two layouts: CUSTOMERS and INVESTMENTS. I've created a portal on layout CUSTOMERS with fields from INVESTMENTS in it, but drop down menu in field TypeOfInvestment stopped working, I mean, if I create new record in CUSTOMERS layout, I also can input parameters of investment in a portal, but I can't choose TypeOfInvestment field, it shows as ordinary txt field.

                                     Besides, I still don't know how to create multiple records of investments in a portal in CUSTOMERS layout.. If I click NEW RECORD, I can add new customer (full name, phone, addres, etc.) and also, in a portal, new investment (after clicking NEW RECORD fields clears up), but only one. Every NEW RECORD clears up all fields (from CUSTOMERS layout and from INVESTMENTS portal).

                                    When I will be able to create multiple investments linked to one customer, I will have another problem - how to create layout where in a form view, after clicking on a tab INVESTMENTS, I'll be able to see all investments of specified customer listed one below the other.. But it's a second case. For now, it's most important to figure out how to link multiple investments to one customer..

                                    1 2 Previous Next