10 Replies Latest reply on Feb 5, 2010 1:31 PM by onamac

    How to retrieve only certain items from the same table

    onamac

      Title

      How to retrieve only certain items from the same table

      Post

      Hi.

       

      I have two tables to interact: ("Songs") is where I list the songs I've written. In that table are two fields-- "Instrumental", and "Has Lyrics" having a value list of "Yes", instead of one value list of "yes" or "no").

       

      The second table ("Lyrics") displays the lyrics for printout. In it I have a field called "song title" to enter the song title from the song table, according to the SongID. I also have a field called "LyricID" in case I need this for what I want to do.

       

      All songs, instrumental or lyrics, will be manually entered in the song table. Then I want the Lyric table to retrieve only the song titles that have lyrics. This means that on the song table the ID serial number will not skip over any numbers, but the lyric table may show "1, 2, 5, 7, etc."

       

      I tried a few ways, with subscribing to value lists and whatever, but I can't get it to work. I want the lyric layout (table) to skip over the songs that don't have lyrics. How would I do this? If it is impossible, I can always just type in the title myself, but if it is simple, I wanted to know how to accomplish this. Thanks!

        • 1. Re: How to retrieve only certain items from the same table
          philmodjunk
            

          Delete the field definition for song title from your Lyrics table--you don't need it.

           

          You should have a relationship between songs and lyrics that matches up records by SongID:

           

          Songs::SongID = Lyrics::SongID

           

          In your Lyrics layout, you can format Lyrics::SongID as a drop down menu listing all the songID numbers and song titles in two columns. Use the field tool to add a field to your layout and select the song title field from Songs.

           

          Now, when you select a song in the SongID drop down, the song title should automatically appear in the song title field you just added.

           

          PS. One big advantage for this approach is if you or a publisher decides to change the name of your song, you can change the song title and see that new name appear automatically in your Lyrics layout.

           

          You can use the same approach for your Instrumental table--I'm guessing that's your next step here.

           

          PS. You might also research the term "Portal" in filemaker help for a good way to work with your Lyrics records from your Songs layout.

          • 2. Re: How to retrieve only certain items from the same table
            onamac
              

            Thanks so much for your help.

             

            I think I didn't explain correctly. It's not just a song title list I'm after. The only purpose of the Lyrics [table] is to have a separate layout showing an 8.5x11 page for each song with its lyrics underneath. So on the top of the page will be a static header, then in the body will be a field for the song title, underneath will be a field for the song's lyrics, and in the footer will be the copyright declaration. Then the lyric sheet will be printed out when needed (it will look like it came from a word processor).

             

            The song table will have every record (song), but I want the lyric layout to only display the records containing songs with lyrics, not the instrumentals. This is why in the Songs table, I made a checkbox field for Lyrics (yes), and "Instrumentals" (yes). This way I could use those fields to instruct the lyric layout to choose only the records where the lyric checkbox was Yes (from the songs table). I just don't know how to get it to be selective.

             

            I could forego a separate table since I could just make a new 8.5x11 layout in the Songs table, put the "Song Title" field on that page and each song record will appear there. The problem with that is that all the records will be shown, which means the lyric field will be blank if a song is an instrumental only, making a lot of blank records. So my purpose for having a lyric table was so that I could make a relationship, set up some kind of formula or whatever so that it skips over any records that don't have lyrics and only displays the ones that do.

             

            I want to set it up so that the lyric layout omits the records that do no have lyrics.

            • 3. Re: How to retrieve only certain items from the same table
              philmodjunk
                

              If you follow my directions (ignore the stuff about portals), you should be able to do exactly what you want.

               

              You can place the song title field from Songs in the header of your Lyrics layout.

               

              Since you won't be creating lyrics records for songs that have no  lyrics, there won't be any song titles for songs without lyrics appearing on your lyrics layout.

               

              Unless you mean that you want a value list on your lyrics layout that only lists song titles where you have selectected the lyrics check box? If that's what you want, a conditional value list will do the trick.

               

              Here's a thread on setting up conditional value lists (option 1 will do what you need here):

              Custom Value List?

               

              • 4. Re: How to retrieve only certain items from the same table
                onamac
                  

                Thanks so much Phil. This is really helpful. Upon trying this I had a lot of blanks that I can't delete without the record being deleted in Songs also since that table is within the same database = all the records are there. I write a lot of instrumentals which means in the Lyrics layout, there will be all these records with nothing on them. The condition I wrote was:

                 

                If ( Songs::Has Lyrics = "yes"; "Song Title" )

                 

                If I make Lyrics a separate database, then it can have a different quantity of records than those in Songs, meaning no blank records. Then what I'd need to do is have it contain only the records that have lyrics. The record quantity will be different than that in Songs. I can still use songID but it will skip over some of the numbers.

                 

                So can a separate database be set up to only lists the songs that have lyrics, by a lyricID, the "Has Lyrics (yes) checkbox, etc.? Unless there is a way to have a separate layout contain a different quantity of records than the host.

                 

                Sorry for belaboring this, but I really appreciate your help.

                 

                 

                • 5. Re: How to retrieve only certain items from the same table
                  philmodjunk
                    

                  You should have two tables, both can be in the same table, Songs and Lyrics.

                   

                  Put your lyrics in a text field in the Lyrics table, put your song title and other information about the song in fields in your songs table. Include a number field that's set to auto-enter a serial number each time a new record is created in the songs table, call it SongID. Add a matching number field in lyrics.

                   

                  Use Manage | Database | Relationships to link the tables by dragging from Songs::SongID to Lyrics::SongID.

                   

                  Now, you only create a record in lyrics when you need to record lyrics for that song. Instrumental only? then don't create a lyrics record.

                   

                  You can put a button on your layout labeled "Lyrics" and give it a script like this:

                   

                  Set Variable [$songID; Songs::SongID]

                  Go To Layout [Lyrics (Lyrics)]

                  Enter Find Mode []

                  Set Field [Lyrics::SongID; $SongID]

                  Set Error capture[on]

                  Perform Find []

                  If [get (foundcount ) /*  no lyric record exists */]

                    Show Custom Dialog ["No lyrics for this song. Record Lyrics?"] (label buttons yes, No)

                    If [ get ( lastmessagechoice) = 1 /*user clicked yes */]

                       New Record / Request

                       Set Field [Lyrics::SongID; $SongID]

                    Else

                      Go To Layout [original layout]

                    End If

                  End If

                  • 6. Re: How to retrieve only certain items from the same table
                    onamac
                      

                    Thank you again. I'm getting a good indoctrination beyond what I knew in Filemaker. There's just something wrong here. I have many tables all working well. My only issue is this lyric record thing.

                     

                    I had already done what you suggested today a few days ago when I started this database. Problem comes in when you say:

                     

                    "Now, you only create a record in lyrics when you need to record lyrics for that song. Instrumental only? then don't create a lyrics record."

                     

                    The problem is that when I add such a record in Lyrics, it is also added to the Songs table. So I end up with blanks when the song is instrumental because all the song records automatically show up in the Lyric layout. Total 100 records in Songs = 100 records in Lyrics. To be able to only create a record when you need one for lyrics would be great, but in this thing, the records are already there since they are present in Songs. Creating a new one in Lyrics only creates a new one everywhere because they are being fed from Songs. (The way I want to work is create the new records in Songs for each new song. It will always be the database where the song info is entered, not Lyrics.)  

                     

                    What I did before your post is create a new file called "Lyrics" so that the record count doesn't have to mirror the record count in Songs. I made a field in Songs called "SongID lyrics only", made it a drop down, and gave it this condition:

                     

                    If ( Has Lyrics  = "yes"; SongID ; "SongID" )

                     

                    This does skip all the IDs that are instrumental. I could use this method to make a new record in the Lyrics database, but unfortunately the list will be very long and growing over the years, which is why I want to set up a formula to automatically populate the song ID and title for lyric-only songs in the lyric database. In other words, as soon as I enter a new record with new song data in Songs, the ID and Title will appear in Lyrics ready for me to put in the lyrics. That's what I want it to do. No long scrolling drop down lists.

                     

                    Right now, the song ID and title fields automatically populates in Lyrics, for all new and current records in Songs, which is what I want. I just don't want to populate the songs that don't belong in Lyrics (instrumentals). That's what I'm trying to solve.

                    • 7. Re: How to retrieve only certain items from the same table
                      philmodjunk
                        

                      What version of filemaker are you using?

                       

                      If you are using filemaker 7 or later, you should be able to define separate tables, songs, Lyrics in the same file. Creating a new record in one table will not create a matching record in the other table.

                       

                      It's possible that you have two Table Occurrences of the same data source table named Songs and Lyrics. If so, read the following link and make the changes you need so that you have two separate tables.

                       

                      If you are using filemaker 6 or earlier, then you'd need two separate files as you can only define one table per file in these older versions. I don't think that's the case here as you clearly have something named "Songs" and something named "Lyrics".

                       

                      Many folks find the terms Table and Table Occurrence confusing. To learn more, click the following link:

                      Table vs. Table Occurrence (Tutorial)

                      • 8. Re: How to retrieve only certain items from the same table
                        onamac
                          

                        FMP 10 which I just bought. My last version was FM 3!! I haven't used FM for at least 8 years, but in in the past I had built quite a few involved things. Your responses are very helpful to me and I thank you for that.

                         

                        The reason the Lyrics is mirroring Songs is because it's a layout based on Songs. I had made a new Lyric table (and TO) so I had two, and when I cleaned out my layout menu, I deleted the wrong one. That's solved.

                         

                        But to get the behavior I'm seeking (have songs with lyrics auto-entered on a Lyric layout), I used the condition like you mentioned in Post #4:

                         

                        ("Unless you mean that you want a value list on your lyrics layout that only lists song titles where you have selectected the lyrics check box? If that's what you want, a conditional value list will do the trick."

                         

                        If ( include = "yes"; valuefield ; "" ) 

                         

                        Yes, I do want to do that, as long as it can auto-enter the IDs only for the songs with lyrics. Would the calculation you said to use above be this?:

                         

                        If ( Has Lyrics  = "yes"; SongID ; "SongID" ) 

                         

                        I used it for a field called "Songs with Lyrics ID" in the Songs table. It does only show the IDs for the songs with lyrics and is blank for those that don't. In Lyrics, I made an ID field to look up those values, but it doesn't auto-enter it when I create a new record. It just stays ready to be typed in, and when I do type in a number, it does bring in that song title. But I want it to automatically enter those numbers, which will be like non-successive serials.  I'm trying to get it to auto-enter it, so there must be some step to do or my formula is wrong.

                         

                        I also thought in terms of scripts, such as having a button on the Lyric layout to make a new record that auto enters the "Song with LyricsID" in the ID field. This would keep me from having to go back to see what is the next available ID number, and then it could auto-enter the song title. 

                         

                        Like:

                        New Record/Request

                        Insert Calculated Result (whatever that would be)

                        [or] Set Field or something like that.

                         

                        Would that work? 

                         

                        Or

                         

                        In the Layout Setup (for the Lyrics table): could I use a script trigger for "OnRecordLoad" or "OnLayoutLoad" that could pull up some script to auto-enter only those songs with lyrics? 

                         

                        I would think this should be a simple thing to do, but maybe not. I can always just enter them all manually, but automatically simply makes it easier to keep the ID numbers accurate and in order (without my having to look them up for each song) and know that title is awaiting lyrics. A drop down list solution would just be too long a list.

                        • 9. Re: How to retrieve only certain items from the same table
                          philmodjunk
                            

                          The problem most of your approaches would have is determing which song record to select when you create a new Lyrics record.

                           

                          Two approaches:

                           

                          On your Lyrics layout (with the lyrics table selected in layout setup), Format your Lyrics::SongID field with a value list. This value list can be a drop down or pop up menu. Specify a value list based on your calculation field. [ Don't see the purpose of "SongID" in the expression: If ( Has Lyrics  = "yes"; SongID ; "SongID" )  ] You can set this up to list all the values from this calculation field in column 1 and use Songs::song Title to list the names of the songs in column 2. Then you can select a song from the drop down to attach the current lyric record to that song record.

                           

                          On your Songs layout, write a script and attach it to a button named "New Lyric Record".

                           

                          Set Variable[$SongID; Value: Songs::SongID]

                          Go To Layout ["Lyrics" (Lyrics)]

                          New Record/Request

                          Set Field [Lyrics::SongID ; $SongID]

                           

                          Clicking this button will create a new Lyric record for the song record that you had visible when you clicked the button.

                          • 10. Re: How to retrieve only certain items from the same table
                            onamac
                              

                            That did the trick! I used your button/script idea. This works perfectly for what I was wanting to accomplish.

                             

                            My hat is off to you, Sir. Many thanks.