4 Replies Latest reply on Aug 15, 2016 12:32 AM by disabled_Luna.media

    Ho to export the value of a portal elegantly?

      Hi,

       

      i have a table of EVENTS with PARTICIPANTS and want to get a list of all participants of several events to make a cross-tabulating (who took part where).

       

      For that I started to collect IDs of the events in a field and I use a portal to show all brands of theses events (in mid there is a M;N-relation-table). Works well.

       

      Note I want to export all the shown fields (PARTICIPANT, EVENT) into an CSV-file to open this in Excel and use a pivot table for the cross tabulation.

       

      First question: is there good possibility to do the cross-tabulation in Filemaker /with using virtual lists).

      Second question: if not, how can I best export the values as mentioned above?

       

      Best,

        • 1. Re: Ho to export the value of a portal elegantly?
          philmodjunk

          Much can depend on your data model. People to Events is really a many to many relationship as Many people can participate in many events. That is usually set up like this:

           

          People ----< Participants >-------Events     (----< means one to many )

           

          People::__pkPeopleID = Participants::_fkPeopleID
          Events::__pkEventID = Participants::_fkEventID

           

          This sets up Participants as a Join table and you can do a lot, in terms of reports and analysis with the records in the Participants table, including export to Excel.

           

          It IS possible to set up a cross tab in FileMaker, but it's not the simplest thing in the world and the results aren't particularly flexible as it isn't something you can generate dynamically as you can in Excel.

          • 2. Re: Ho to export the value of a portal elegantly?

            Yes, I have a M:N-table in between  - my question is how to export the data best if I want all occurrences of participants if I compare e.g. two or three events with its participants.

             

            It is easy to use a portal that show all relation table entries. For that I use a global field which contains the ids of events I want o compare.

             

            The portal show all in a list and this list would be the data to use in excel.

             

            Is it possible to grab this data elegantly?

             

            Am 13.08.2016 um 20:48 schrieb philmodjunk <noreply@filemaker.com>:

             

            Ho to export the value of a portal elegantly?

            Antwort von philmodjunk in Discussions - Komplette Diskussion anzeigen

             

            Much can depend on your data model. People to Events is really a many to many relationship as Many people can participate in many events. That is usually set up like this:

             

             

             

            People ----< Participants >

             

            • 3. Re: Ho to export the value of a portal elegantly?
              philmodjunk

              To repeat, you would export the join table. A layout based on the join table can be used as the basis for your export and you can then include any related fields from the other two tables that you need.

              • 4. Re: Ho to export the value of a portal elegantly?

                Well, what I see are two solutions:

                 

                   

                • Create a new layout based on the relation-table with the fields I

                    need and then perform a search to only show the relation-records of

                    the pre-selected events. This table can get exported by a Filemaker

                    script step.

                 

                    or:

                 

                   

                • If I use a portal in a certain layout that show already all these

                    record-sets ans needed fields I need a few lines to walk through a

                    the portal rows ans write the data into a file. AS I use the MBS

                    plugin I have easy file-writing plugin -functions at hand.

                 

                Thanks, this will do it for me.

                 

                 

                Am 14.08.16 um 13:50 schrieb philmodjunk:

                >

                    Ho to export the value of a portal elegantly?

                 

                Antwort von philmodjunk

                <https://community.filemaker.com/people/philmodjunk?et=watches.email.thread>

                in /Discussions/ - Komplette Diskussion anzeigen

                <https://community.filemaker.com/message/593788?et=watches.email.thread#593788>

                >