11 Replies Latest reply on Jun 5, 2012 12:17 PM by philmodjunk

    Using data from multiple portals

    TimothyWade

      Title

      Using data from multiple portals

      Post

      I'm attempting to use data from two different portals, along with data from a main table to create a confirmation letter/invoice, and I can't seem to get the data that I want/need to appear. I am using FM10.

      Basically, I have my main table that has the Group Visit Name and main contact information, with two related tables that have multiple Group Visit and Group Visit Leader entries. These two tables are set up as portals back to the main Group Visit Name table, so by searching for one Group Visit name, you can view all of the associated Group Visits and Group Leaders.

      I am trying to generate a confirmation letter/invoice from the data from all three tables. However, there is only one seemingly random set of data that appears on the confirmation letter/layout, regardless of what "find" data I've searched for. At least it shows the correct Group Visit Name, but that's it.  

      Any additional information would be greatly appreciated

        • 1. Re: Using data from multiple portals
          philmodjunk

          What is the relationship used for your three portals?

          Do you need to create one confirmation letter for each group leader listed in the portal to Group leaders? If so, you'd base your confirmation layout on that portal's table. Go to related records or a scripted find can bring up the records listed in the portal on this "confirmation letter" layout.

          • 2. Re: Using data from multiple portals
            TimothyWade

            I forgot to mention the relationship: The Group Name is the related field between all of the tables. 

            I need to generate a confirmation letter for each Group Visit. This may utilize any one of the Group Visit Leader portal records (meaning that Group Visit 1 may have Group Leader 3 as the contact name/address on the confirmation letter).

            • 3. Re: Using data from multiple portals
              philmodjunk

              I need to generate a confirmation letter for each Group Visit.

              You seem to have a layout where you have a record for each group visit, but then you describe a portal for group visit names. How does that work?

              And how do you select "group leader 3" instead of "group leader 1" for your confirmation letter?

              • 4. Re: Using data from multiple portals
                TimothyWade

                I'm working on selecting the different names for my confirmation letter, that's secondary to getting the right data for each visit. 

                 

                I have three tables. Table One is the Group Visit Main. This table is where the Group Name originates, and also has the Group Contact Address information. Table two is Group Visit Information, which has information regarding the specific visit. Table THree is the Group Leader Table, which has multiple entries for multiple group leaders. 

                I have a layout for Group Visit Main that displays Table Two and Three as portals in the layout. 

                This is where my problem arises, trying to pull data from Table 1, and select the correct data from Table 2 and 3.

                • 5. Re: Using data from multiple portals
                  philmodjunk

                  You seem to have these relationships:

                  GroupVisitInfo?-----GroupVisitMain------<GroupLeaders     ( ---< means one to many,  ----? means type of relationship unknown )

                  Table two is Group Visit Information

                  What kind of Group Visit information? Is there more than one record listed in this portal? (I'm thinking this might be a list of all group members making up the vist but am unsure if that's the case.)

                  What do you need in this confirmation letter?

                  All the info from GroupVisitInfo, plus data from GroupVisitMain plus a selected GroupLeader in GroupLeaders?

                  IF your confirmation letter's layout is based on GroupVisitMain, you should be able to use those portals to display the data from GroupVisitInfo, and getting the selected group leader (or listing all of them) shouldn't be too difficult either.

                  On the other hand, it may be useful to base your layout on GroupVisitInfo--it depends on what kind of data is entered in that portal. This could be a list view layout with one row for each GroupVisitInfo record and the GroupVisit and GroupLeader info can be included on this layout as well. The trick to making this work with such a layout is performing a scripted find or a Go to Related Records action to pull up the desired records.

                  • 6. Re: Using data from multiple portals
                    TimothyWade

                    First, let me give you a hearty thank you for helping wade through this issue. Your assistance is greatly appreciated. 

                    Now, on to your questions. 

                    The relationships between the tables are all one-to-one. One unique group name for the entire database, where the related field for each table is "Group Name". Should this be a different related field, as you noted above? 

                    There is ONE Group Name, (i.e. School Name). A Group Name will have multiple visit entries for unique visits (with each visit containing the information: School visit date, time, number of kits, etc). A Group name (School) may also have multiple Group Leaders (i.e. Teachers). 

                     

                    The confirmation letter needs: The Contact Information (from the Group Visit Man table), the Leader Information, and the Group Visit Information (Date, time, etc). 

                    • 7. Re: Using data from multiple portals
                      philmodjunk

                      Now I'm really confused.

                      These two statements contradict each other:

                      The relationships between the tables are all one-to-one.

                      A Group Name will have multiple visit entries for unique visits (with each visit containing the information: School visit date, time, number of kits, etc)

                      And you earlier indicated that there is more than one group leader listed. Portals are used to suport one to many relationships so it looks to me that you have a one to many relationship here as you otherwised do not need portals and don't even need separate tables, you can just put all your fields in one table if everyting is one to one in your relationships.

                      • 8. Re: Using data from multiple portals
                        TimothyWade

                        My apologies, it's obvious that I may not fully understand the information that I'm trying to relay. 

                        I assumed that because I wanted only ONE Group Visit Name to be for each record in each table, that was a one to one. Now I'm looking at the relationship graph a bit more closely, and it does note that there is a one to many for both the Group Visit and the Group Leader tables.

                        • 9. Re: Using data from multiple portals
                          philmodjunk

                          But it still comes down to what you are actually entering into the tables. FileMaker assumes a "many" unless you set field options to preclude such use so basing your relationships on a name field--not the best option BTW--will be shown with "crows feet" even if you are actually only entering single related records.

                          Uploding a screen shot of your layout may help. What I am picturing at the moment is this:

                          [Group Visit Name field ]

                          [contact info fields for Group vist]

                           

                          [Portal listing date, time, number of kits...]

                          [Portal listing group leader(s)]

                          What I'm not clear on is whether you have a single portal row with a single set of fields in each portal (then you have a one to one relationship) or whether you have multiple portal rows listing multiple records (then you have a one to many relationship)

                          This is key to setting up your layout for the confirmation letter/invoice so that you get the data you need to appear in it.

                          Uploading a screen shot of your relationships graph may also help.

                          • 10. Re: Using data from multiple portals
                            TimothyWade

                            I feel like I've gotten myself in over my head, sadly. 

                            Here is the screenshot.

                            • 11. Re: Using data from multiple portals
                              philmodjunk

                              I need to see your layout with portals also. It's especially informative if you capture the screen shot while in layout mode.