1 2 Previous Next 26 Replies Latest reply on May 26, 2009 7:31 AM by TTiger

    Nested Fields

    TTiger

      Title

      Nested Fields

      Post

      Hello,

       

      I am working wit a database that requires certain fields to be filled out based on certain criteria.

       

      For example:

       

      If the event fees box is checked to yes or radio button yes is selected then I would like it to display the fields to 

      take credit card information. If the box is checked to no then I do not want to display the credit card information fields.

       

      Any ideas?

        • 1. Re: Nested Fields
          philmodjunk
            

          I can't give you precise details without knowing more about your tables and their relationships but here's a general outline:

           

          Put the credit card info in a separate table. (you may already have this.)

           

          Define a relationship that links to this table. Define it so that is valid if the checkbox or radiobutton field contains a specific value and is invalid if it does not. (Valid:  there's a matching record(s). Invalid:  There's no matching record(s).)

           

          Now you can either place the credit card fields directly on a layout or display them in a portal. When you click on a radio button or check box to make the relationship valid, the info will appear, if you click the check box a second time or choose a different radio button value so that the relationship is invalid, the data will disappear from the layout.

           

          If you'd like to get a more detailed description, describe your fields/tables in more detail, and I'll take a swing at it.

          • 2. Re: Nested Fields
            philmodjunk
              

            Another option is to use a calculation field:

             

            If(radiobuttonfield = "Yes", Creditcardfield, "") /* this calc assumes that radiobuttonfield and Creditcardfield are in the same table as the calc field */

             

            This is simpler and easier if you just have a small number of fields that you only need to display and not edit.

             

            If you need to edit the data now visible or have a larger number fields to display, using the previous, relationship based approach will most likely be the better way.

            • 3. Re: Nested Fields
              TTiger
                

              Ok I have and Events table and a ConferenceDetails table. One field in the events table is called EventCategory now this is the calculation that I have so far.

               

              if(EventCategory = "Conference"; Conference Details) 

               

              But I am usure if this is how to do it correctly.

              • 4. Re: Nested Fields
                philmodjunk
                  

                An If function has three parts (parameters).

                 

                Part 1 is an expression that must evaluate to true or false.

                Part 2 is an expression that will be evaluated only if part 1 is true.

                Part 3 is an expression that will be evalutated only if part 2 is true.

                 

                You need the third parameter, an empty string, "". That's what makes the field seem to disappear.

                 

                if(EventCategory = "Conference"; Conference Details; ""

                 

                I recall that you mentioned both checkbox and radio button formatting. If you use a check box with more than one value, be careful. You may have to use a more sophisticated expression than just field = "value" in that case.

                • 5. Re: Nested Fields
                  TTiger
                     For the checkbox and radio button fields they will all be yes or no values. However, for the EventCategory field it is a drop down list with 5 choices. Will this affect my expression or could I still use the if statement like it is being used now.
                  • 6. Re: Nested Fields
                    TTiger
                      

                    I think I'm still misunderstanding the concept of what's going on here though. This is what I am trying to do. In the Events Table there is a field called EventCategory which is a drop down list with 5 events listed. If "Conference" is chosen I want to display the fields that are in the ConferenceDetails table because those fields are specific for Conferences only.

                     

                    In the Events table I have ID set as the PK and in the ConferenceDetails table I have EventsID as the FK.

                     

                    So with the if statement that I created my line of thinking went like this:

                     

                    if(EventCategory = "Conference"; go to conference details layout)

                    • 7. Re: Nested Fields
                      raybaudi
                        
                      Hi Phil
                      PhilModJunk wrote:

                       

                      You need the third parameter, an empty string, "". That's what makes the field seem to disappear.


                      this isn't any more true ( from 7 )


                      • 8. Re: Nested Fields
                        philmodjunk
                          

                        Some time it takes several posts to clarify the issue. I assumed you wanted a small number of fields to appear and disappear from the current layout. You now indicate that you want to take the user to a different layout based on the choice the user makes from a drop down list. Is this correct?

                         

                        You can do that but the solution is quite different. You'd need a script to do that with either a script trigger (filemaker 10) or a button (earlier versions) to trigger the script after the choice has been made.

                         

                        Please confirm which option you want and I'll take it from there.

                        • 9. Re: Nested Fields
                          TTiger
                            

                          Well, I'm looking for the best way to meet the needs of this database. I am creating an event database that 10 people can go in to and enter information about their events. So far I have created xx tables. The first table is an Events Table where a person can create and Event (conference, workshop, visit, etc.) and specify the date, location, and the hosts contact information. The PK of the Event table is a serialized number

                           

                          All the fields are the same except if the event chosen is a "Conference". If Conference is chosen then there are 7 additional fields that need to be filled out. Right now I have the 7 additional fields in a separate table called ConferenceDetails.

                           

                          The next table is the Attendee Information table. This table takes in the attendees Name, mailing info, phone #, etc. becuase we have alot of repeat visitors it eliminates the need to re-enter them. The PK of the Attendee Table is also a serialized number.

                           

                          The next table is called Payments. This is where the check box/radio button comes in to play. If an event has fees associated with it (yes/no) then i would like for it to display the Payment fields.

                           

                          Also, I am planning on combining info from all the tables into a Participants table that will display an Event and its attendees. When there is fees associated with an event it will also display whether or not that attendee has payed.

                           

                          Lastly, each Event will have a portal that will display its attendees, and each attendee will have a portal that displays the events they went to.

                           

                          Hopefully this helps in showing what this project entails. I wuld love to hear any suggestions that you may have.

                          • 10. Re: Nested Fields
                            philmodjunk
                              

                            Thanks for the info. It's really a question of layout design and your personal preferences. If you have room on your layout to display the conference details, you can have an area that is blank unless the user selects "Conference" from the event type. If you don't have room or don't want this "blank" area on your layout, you can switch the user to view a different screen of data that shows the conference detail fields. Both options can be done and in a variety of ways.

                             

                            When I re-read all your posts, I think you want the first option. In addition, the calculation field approach that I described in my second post and that you've been trying to use isn't the best option. As I stated in that second post, that approach is good for one or two fields that need to be displayed but not edited. In your case, you need to fill out the fields once they appear.

                             

                            Let's back up to the beginning here. We can set up a relationship that will control the appearance/disappearance of your details fields. 

                             

                            I'm assuming that you already have the following relationship: Events::EventID--=--ConferenceDetails::EventID. Make sure that this relationship does NOT have the "Allow creation of records via this relationship" option selected for the ConferenceDetails table occurrence.  (I didn't see where you named your PK field, so I'm calling it EventID).

                             

                            Now place a portal on your layout and choose ConferenceDetails in the "Show related records from:" menu. You can set up this portal to show just one related record and resize it into a larger rectangle than the narrow row that is automatically created. If you add fields to the portal when you first create it, the field names will be left out. You can add these after the fact using the layout text tool or by double clicking each field and checking the Create Label check box.

                             

                            Now use Scripts | Manage Scripts to create a new script:

                            #On Modify Events::Conference

                            #This script creates a related record in the ConferenceDetails table

                            If [Events::Conference = "Conference"]

                               Freeze window

                               Set Variable [$EventID, Events::EventID]

                               Go To layout [ConferenceDetails (ConferenceDetails)] //You can use any layout here that refers to the ConferenceDetails table

                               New Record/Request

                               Set Field[ConferenceDetails::EventID, $EventID]

                               Go to Layout [original layout]

                            End If

                             

                            Define an "On Modify" script trigger for your Events::Conference field that you've formatted as a drop down list and select the above script as the script to be performed.

                             

                            How this works:

                             

                            Normally we'd set up the portal's relation to automatically create related records when the user starts entering data into a portal's field, but you want the fields to be invisible until a user chooses "conference" from the drop down list. The script creates the related record for you. The related records all the fields you've placed in the portal will thus "appear" only if the user selects "conference" from the drop down list.

                            • 11. Re: Nested Fields
                              TTiger
                                 Im using FileMaker 9 does it have the script trigger? I'm having problems finding it.
                              • 12. Re: Nested Fields
                                philmodjunk
                                  

                                Sorry, Script triggers are an FMP 10 feature. Older versions don't use it.

                                 

                                Follow the same instructions above, but place an "Edit" button to the right of your drop down menu and set it to perform the script. This is not as nice as a script trigger, but it'll work.

                                • 13. Re: Nested Fields
                                  TTiger
                                    

                                  I upgraded to FMP 10 to take advantage of the script triggers because they will be very useful in the rest of the database that I am creating. I created a script and a script trigger, but when I create a new record and choose Conference from the drop down list the portal does not display anything. This is what I have for the script:

                                   

                                  If[Events::Event Category = "Congerence"]

                                     Freeze Window

                                     Set Variable[$EventID; Value:Events::EventID]

                                     Go To Layout["ConferenceDetails"(ConferenceDetails)]

                                     New Record/Request

                                     Set Field[ConferenceDetails::EventID]

                                     Go to Layout[original layout]

                                  End If

                                   

                                  And in my portal i have the following:

                                  A 1x1 portal with these fields stacked horizontally:

                                   

                                  Submission Date

                                  Approval Date

                                  Conference Organizer

                                  Conference Organizer Group

                                  Conference Organizer Mail

                                  Conference Organizer Group Ph. #

                                  Conference Organizer Email

                                   

                                  Is there something that I have done wrong?

                                  • 14. Re: Nested Fields
                                    philmodjunk
                                      

                                    It looks OK to me. You have Conference misspelled in line 1, but assume that was a typo when you posted the script.

                                     

                                    Does it work for you?

                                     

                                    If it doesn't, what does happen when you select "conference" from the drop down menu?

                                    1 2 Previous Next