7 Replies Latest reply on Jul 7, 2010 2:32 PM by Frinholp

    Related field problems [Edited]

    Frinholp

      Title

      Related field problems [Edited]

      Post

      I really need some help with this one. I have been stuck on it for days now.

      I  have setup part of my database as shown in the attached screenshot.

      I am trying to setup two drop down lists, one for artists and one for tax. I have created two value lists of IDs, one based on the ArtworkTax TOC and one based on the AcceptedArtist TOC.

      On a layout that is based on the ArtworksTOC I have created two drop down lists using the value list I created earlier. Next to the each of these drop down lists I have inserted a related field, Name from the AcceptedArtist TOC and TaxName from the ArtworkTax TOC. 

      Both drop down lists work fine and the related Name from AcceptedArtist TOC is showing in the edit box next to my drop down of ArtistID's. However, affter selecting a TaxID, no TaxName is appearing.

      I really don't understand this as I have setup the relationships and value lists in the exact same way. I have also tried to use a different TOC of the tax table to generate the value list to no avail.

      Anyone any ideas where I have gone wrong, it is probably something so simple but I'm at that point where I can't see the wood for the trees.

      Relationships.jpg

        • 1. Re: Related field problems [Edited]
          sunmoonstar.13

          Hi there,

          Are you absolutely sure that you've specified the related field on the Artworks layout as TaxName from the ArtworkTax table and not TaxName from the ArtistTax table? It woudl be easy to confuse those table names...

          Another thing to check - make sure you haven't accidentally set the field's font and fill to the same colour, which would make the text "invisible".

          That's all I can think of at the moment, hope it's of some help.

          Nick

           

          • 2. Re: Related field problems [Edited]
            Frinholp

            Thanks Nick

            I had my suspicions that text colour may be causing the problem but that was fine.

            I don't know what has really solved my initial problem, but i remember altering the relationship to allow both sides to delete or create records via the relationship. This seemed to then display name field as expected?????? I didn't think I had to allow the Artwork table to create a record in the ArtworkTax table as I am not creating new data in the ArtworkTax table, just retreiving it.

            Could I please state that I have many TOC's of my main Tax table in my solution. The screenshot shown is just one section of my reletaionship graph. The problem I have now is that when I add a new record to my Tax table,  my value list is not getting updated therefore my drop down showing only one tax as an option. I have tried creating a value list  populated by the TOC ArtworkTax that is directly linked to  the Artworks TOC and also another TOC of Tax called OrderTax. Still only one Tax record is showing in the drop down even though if I view the tax records via a layout based on OrderTax TOC and ArtworkTax TOC I can see two records.

            There must be something I'm missing here and I'm at the point of tearing my hair out.

            Could anyone shed any light on this?

            Lee

            • 3. Re: Related field problems [Edited]
              Frinholp

              Ok everything seems to be solved. When I reverted back to saved work from yesterday I noticed that on my layout I was using a field called Tax as the foreign key in the Artworks table rather than TaxID the field that should have been used as the foreign key and what the relationship to the ArtworkTax TOC was created through.

              Now the strange thing  - When created a new tax in the tax table, my drop down using a value list determind by field was not updating. I was using value from field (ID), using also second field (name) and showing only values from second field (name).

              I hadn't left any name fields blank. I swapped the value list to only use the 1st field (ID); drop down list shows all records in tax table. I changed the value list to show also second field; all records in the drop down list. Switch back to show only second field only 1 record is shown. BTW I'm using all values and not a range.

              What's going on?

              • 4. Re: Related field problems [Edited]
                RonCates

                Hi all,

                I read the posts on this and other forums on a regular basis and do ask a fair amount of questions. When following posts I often come up with similar answers to others but am usually afaraid to post my answers for fear of being wong. But in this case I am very curious and feel the need to ask a question. After looking at your diagram I noticed that all your relationships appear to be many to many relationships Indicated by the crows feet on both ends of the relationships). This I don't understand. Is this a technique for structuring relationships that I am unfarmiliar with? My first though is that your ID fields may not be configured as Auto Enter unique Serial numbers. Forgive me if I am off track and just don't understand the structure but I felt the need to post to further my own knowlege. Thanks for endulging me.

                Ron

                • 5. Re: Related field problems [Edited]
                  Frinholp

                  Hi Ron

                  What I have done is created an ID field which is auto-created by calculation. It uses the auto-generated serial and calculates the ID from that. What I haven't done is validated that field as unique, therefore Filemaker assumes the value inside the calculated field could be re-used in another record. The value will however be unique as it is based on a unique serial.

                  If explicitly validate the calculated field as unique the crow loses a set of toes.

                  My calculation: ArtworkID = SerialIncrement ("ARTW000000"; Serial#)

                  Here is an extract from Filemaker 10: The Missing Manual. A good book for beginers I.M.H.O.

                  Hope this helps you on your quest for knowledge

                  Lee

                  FileMaker’s Sixth Sense: Crow’s Feet

                  I haven’t done anything to tell FileMaker what kind of relationships

                  I created. How does it know where to put the

                  crow’s feet?

                  You never have to tell FileMaker about the types of relationships

                  because it doesn’t really matter. The work FileMaker

                  performs to deal with a one-to-many relationship is no different

                  from what it does for a one-to-one, so it doesn’t care

                  about the distinction.

                  However, it’s useful to you as the database designer to

                  know what kind of relationships you have. (It helps you

                  decide whether an invoice should have room for one line

                  item or a whole list of them, for example.) FileMaker tries

                  its best to figure out where the crow’s feet go, as a special

                  service to you. It assumes every end of every line needs a

                  crow’s foot unless it finds evidence to the contrary.

                  Such evidence includes:

                  • The field used in the relationship is a serial number.

                  • The field used in the relationship has the Unique

                  validation option turned on.

                  A line that connects to a field that meets either of these conditions

                  does not have a crow’s foot. Since all your primary

                  keys are serial numbers, FileMaker has no trouble figuring

                  out where to leave off the crow’s feet.

                   

                   

                  • 6. Re: Related field problems [Edited]
                    RonCates

                    I see, so if I understand correctly, the structure is sound and the relationships are not necessarily many to many, Filemaker just can't make the distinction in the diagram because the key fields are not indicated as unique through the field definitions although I assume they must be unique by way of your calculations.  If it's not too much trouble could you maybe explain what the reason or benefits would be to not using the standard unique auto enter serial number?

                    It's a thurst for kowledge thing :)

                    Thanks

                    Ron

                    • 7. Re: Related field problems [Edited]
                      Frinholp

                      Here is another extract, this time from the Filemaker Bible:

                      " you may be wondering why we have suggested that you create both a serial number field

                      and a separate ID field. Our reasoning is that for some purposes a numeric serial is useful, while

                      for other purposes a text identifier is preferable. So as a matter of course, it’s good practice to create

                      both at the outset for each table. Doing so gives you choices and flexibility later on.

                      To tie the values of the first two fields together, we recommend that the ID field be based on the

                      Serial number field. That way, you can be confident that the two will never fall out of step—and

                      knowing one, you’ll be able to infer the value of the other."

                      (I hope I'm not infringing copyright here)

                      I took the advice incase I would like to use multi-key fields at a later date.

                      Maybe this post will be of interest to you particularly the part regarding multi-key fields.

                       http://www.fmforums.com/forum/showtopic.php?tid/202184/post/320165/fromactivity/posts/    

                       Lee