1 2 Previous Next 24 Replies Latest reply on Aug 6, 2013 3:34 PM by erolst

    How to display a calculated field from another table in a Portal

    nickster

      I am new to Filemaker and hope somebody might be able to help me.

       

      I am creating a database of song compositions, songwriters, ,recorded tracks, artists, producers and albums etc.

       

      I attach some screenshots that shopw my tables, relationships and some of the layouts.

       

      I am trying to create a portal in my Albums layout that shows all the recorded tracks and the artists on each track.

       

      In my tracks layout, I used LIST to show in a calculated field all the artists on one particular track. BUT when I try to show this same calulated field over on the Portal I have created on my Album page, it simply does not work. No Artists show up!

       

      I'm pulling my hair out trying to figure this out. Either there is a realtionsjip wrong somewhere, or I am asking the portal to do something it cannot do (display data from a different related table).

       

      Can anyone out there help me solve this conumdrum... I've only had Filemaker for two days so please excuse me if I'm not explain the problem well.

       

      Many thanks!

        • 1. Re: How to display a calculated field from another table in a Portal
          stefan_s

          Hi "nickster",

           

          In your relationsship graph I found at least one problem.

           

          The relation between Track_People and Artists_Producers seems to be off. The fields are fkTrackID<->__pk_PersonID.

          I would have used fkPersonID<->__pk_PersonID.

           

          Could this be the problem?

           

          __

          Stefan Schutt, Mouse Up, Finland

           

          nickster <noreply@filemaker.com> kirjoitti 31.7.2013 kello 7.41:

           

           

          created by nickster in Advanced Discussion - View the full discussion

          I am new to Filemaker and hope somebody might be able to help me.

           

           

          I am creating a database of song compositions, songwriters, ,recorded tracks, artists, producers and albums etc.

           

           

          I attach some screenshots that shopw my tables, relationships and some of the layouts.

           

           

          I am trying to create a portal in my Albums layout that shows all the recorded tracks and the artists on each track.

           

           

          In my tracks layout, I used LIST to show in a calculated field all the artists on one particular track.  BUT when I try to show this same calulated field over on the Portal I have created on my Album page, it simply does not work.  No Artists show up!

           

           

          I'm pulling my hair out trying to figure this out.  Either there is a realtionsjip wrong somewhere, or I am asking the portal to do something it cannot do (display data from a different related table).

           

           

          Can anyone out there help me solve this conumdrum... I've only had Filemaker for two days so please excuse me if I'm not explain the problem well.

           

           

          Many thanks!

           

          Reply to this message by replying to this email -or- go to the message on FileMaker Technical Network

          Start a new discussion in Advanced Discussion by email or at FileMaker Technical Network

          Manage your email preferences.

           

          FileMaker Developer Conference 2013 • San Diego, California • August 12-15 • www.filemaker.com/devcon

           

          1 of 1 people found this helpful
          • 2. Re: How to display a calculated field from another table in a Portal
            nickster

            Thanks Stefan.   Good catch!   Thank you again!  That would have caused a problem for me down the line so thank you for spotting it for me!

             

            But doesn't solve my problem of how to have a calculated field from one table appear in a portal on another table.  I'm really stumped on this and need to find a solution!  Someone out there has to know the answer ???

             

            Thank you again!  That would have caused a problem for me down the line so thank you for spotting it for me!

            • 3. Re: How to display a calculated field from another table in a Portal
              stefan_s

              Hi nickster,

               

               

              nickster <noreply@filemaker.com> kirjoitti 31.7.2013 kello 16.20:

               

              Thanks Stefan.   Good catch!   Thank you again!  That would have caused a problem for me down the line so thank you for spotting it for me!

               

              You're welcome.

               

               

              But doesn't solve my problem of how to have a calculated field from one table appear in a portal on another table.  I'm really stumped on this and need to find a solution!  Someone out there has to know the answer ???

               

              Have you tried to create a calculation field in Album_Tracks which only refereces the calc field in Tracks?

               

              Like so: Tracks::c_Track_Artist_list

               

              This will give you problems with performance sooner or later.

               

              It would be better to re-design the relation graph.

               

              I would suggest you search for anchor-buoy and Kevin Frank. This might give you ideas on how to build this so that you will get good results.

               

              HTH

              __

              Stefan Schutt, Mouse Up, Finland

              • 4. Re: How to display a calculated field from another table in a Portal
                erolst

                Hi Nick,

                 

                sorry if I say I find your existing structure too convoluted for comfort …I can just guess that somewhere between Albums and Albums.People the connection is broken.

                To try solve your problem at hand, it might help if you'd post the database (or a clone), instead of screenshots.

                 

                In the meantime, may I suggest to you the following approach:

                 

                Main entities:

                 

                People/Persons (everyone)

                Songs

                Albums

                Roles (Composer / Lyricist / Producer / Engineer / Guest Performer etc.)

                 

                Join tables for:

                 

                Songs_People (each record is the combination of a Song, a Person, and a Role)

                Album_Songs (which is Tracks: Album <-> Songs; a song on an album is by definition a track)

                Albums_People (combination of Album, Person, and Role)

                Tracks_People (combination of Track, Person, and Role, when different from / in addition to Albums_People (e.g. sound engineer), or a guest performer (say, Billy Preston on organ …))

                 

                Note that you could go even further by creating just one Person_Role table, and use join tables to hook it up to Songs, Albums and Tracks, respectively; then a single table would hold each person's complete discography, in whatever capacities he/she participated in Songs, Albums, or Tracks. (Waiting for DT to explain why this is a horrendous idea that'll bring about doom and disaster … sorry, my own private insider joke).

                 

                As I said, just a suggestion; I find a role-based much more flexible.

                • 5. Re: How to display a calculated field from another table in a Portal
                  nickster

                  Thank you so much - that could well be what is wrong...

                   

                  Great idea to upload the Filemaker file.... I'd love for you to take a look around at it, modify it, whatever you can do to help would be GREATLY appreciated!

                   

                  hmmmmmm..... how do I attach  a file to a reply?

                   

                  Nick

                  • 6. Re: How to display a calculated field from another table in a Portal
                    erolst

                    Click on Reply, then on Use advanced editor, then use the icons in the toolbar to upload your file. Not sure about the allowed file types at the moment, but I recommend zipping your file and uploading the archive; you can save up to 80% of the original file size. EDIT: With all the overhead of FM12, even 90% …

                    1 of 1 people found this helpful
                    • 7. Re: How to display a calculated field from another table in a Portal
                      nickster

                      Thanks so much - here is the Filemaker File....  I'd really appreciate your help!

                      • 8. Re: How to display a calculated field from another table in a Portal
                        erolst

                        Your People value list, which you use to (supposedly) enter PeopleIDs into Tracks.People, looks like this …

                         

                        forerunner_1.png

                         

                        … when it really should look like this:

                        forerunner_2.png

                         

                        So you entered names instead of IDs all along, and that's why the records in Track.People looks like this:

                         

                        forerunner_3.png forerunner_4.png

                         

                        And Track.People can't find the correct records in the peopleTO you pointed it at.

                         

                        Put in the correct IDs into the existing Track.People records, and change the value list as shown.

                         

                        Happens to the best of us; next time a relationship doesn't work as expected, just follow the (TO) path, and you'll discover what went wrong.

                        • 9. Re: How to display a calculated field from another table in a Portal
                          nickster

                          Thank you VERY much for helping me.  I cannot tell you how it feels to know there are experts out there willing to help a beginner like me.

                           

                          OK... so I have started again with a new file. I have simplified things to begin with. 

                           

                          I have created the tabes and join tables that make sense to me.  I think I have the relationships correct but not sure.

                           

                          I am able to go to my Song layout and, in a portal, select a person and deisgnate their role (Songwriter, Publisher or Administrator).  Woo Hoo!  It works!

                           

                          I am able to go to my Track layout and, in a portal, select a person and deisgnate their role (Artist or Producer).  Woo Hoo!  It works!

                           

                          BUT!!!  I have complete failure when I go to ALBUM and try to list all the tracks in a portal.  I want to be in the Albums layout and be able to select from a list of Tracks in a dropdown list, and have the DB automatcially bring in the Artist name next to the track. Keep in the mind that each track may have several artists - i want these track artists listed together next to the track name (like you might see on the back of a CD).  For example.....  

                           

                          Don't Give Up          Peter Gabriel, Kate Bush

                           

                          I have spent about 5 hours on this problem to no avail.  I can make a list tracks show up in the portal on my Album page but I cannot figure out how to make a list of the artists appear automtaically based on my selection of the track.  

                           

                          I am obviously not understanding something pretty fundlemental here...

                           

                          I would be really graetful if somebody could comment and let me know where I am going wrong.  I attach the FM file.

                           

                          Thanks a million!

                          • 10. Re: How to display a calculated field from another table in a Portal
                            erolst

                            Not Albums -- Track_People_Roles -- Tracks, but Albums --> Tracks <-- Tracks_People_Role (then People, Roles etc.)

                             

                            Compare this to an office database: you wouldn't have Order --> Products --> LineItems, but Order --> LineItems <-- Products; same thing here.

                             

                            Anyway, here's a sample file with the sort of structure I hinted at in the end of my last post. A song is just a track on an album, and all apperances of people are stored in just one table: Appearances (emm…) –

                            thus allowing you to see all appearances of a person via a single relationship; all you need to do is define the correct text strings, based on the respective related entity …

                            The structure can be quickly set up – the real challenge is to build a user interface that lets you conveniently choose from potentially tens of thousands of entries (people); the value lists and popups I used here for just a few records won't do you any good then …

                             

                            Please don't take this sample as gospel (I wish …); if you'd rather do it a bit more relaxed, that's OK (at least with me)

                            • 11. Re: How to display a calculated field from another table in a Portal
                              nickster

                              This is Fantastic!  So much better!  It really is a trick to make your mind think differently about the problem!

                               

                              One more quesiton.... I don't know why, but I am unable to select a TRACK in the track layout??

                               

                              Thanks SO SO Much for helping me -

                               

                              Nick

                              • 12. Re: How to display a calculated field from another table in a Portal
                                nickster

                                Hello Again

                                 

                                OK I figured out how to set a Value list so that I can select a track from a list of songs in the songID.fk - see attachment

                                 

                                The issue I have is more one of DB design.

                                 

                                A track can only ever be a song.  A song can have an artist, producer and administor (but never an artist/performer or a producer).  This is because a song is simply a composition (with songwriter, publisher and admins attached to it).  But when the song is recorded it becomes a TRACK.  And a track (for my uses) can only have an Artist and a Producer (I am not interested in tracking the other musicians or engineer on it at this point).

                                 

                                Any thoughts on this?

                                 

                                Nick

                                • 13. Re: How to display a calculated field from another table in a Portal
                                  nickster

                                  By the way.... i REALLY am so thank you to you for helping me.  I feel like if I can get my brain around this, my first ever database, I'll be confident to build more DBs moving forward.

                                  • 14. Re: How to display a calculated field from another table in a Portal
                                    erolst

                                    Hey, glad you like it. And don't worry: to get this to work, even (or especially) as a side project, is going to be a lot of work. The better you know how this stuff works, the more features you'll want to cram into it …

                                     

                                    nickster wrote:

                                     

                                    The issue I have is more one of DB design.

                                     

                                    A track can only ever be a song.  A song can have an artist, producer and administor (but never an artist/performer or a producer).  This is because a song is simply a composition (with songwriter, publisher and admins attached to it).  But when the song is recorded it becomes a TRACK.  And a track (for my uses) can only have an Artist and a Producer (I am not interested in tracking the other musicians or engineer on it at this point).

                                     

                                    Any thoughts on this?

                                     

                                    Other than “yes, exactly”? I'm not sure I see a question here …

                                     

                                    If there is a question, and its ”How can I prevent myself from adding what I don't need and start pruning those longish value lists?”, the answer is: create more tables and lists to define new relations and create dynamic value lists.

                                     

                                    Look into the sample file to see how the conditional VL fetches its values.

                                     

                                    David Wheeler is quoted with “All problems in computer science can be solved by another level of indirection”. Well, there you go … More on Wikipedia.

                                    1 2 Previous Next