6 Replies Latest reply on Nov 14, 2013 7:59 AM by Beemer

    Can't get relationship to work

    Beemer

      Title

      Can't get relationship to work

      Post

           Using FMPro12.

           Have used FM for some years and never had a problem with a relationship before, but I'm stumped with this one. Hope someone can help.

           2 databases: PR and Client Codes

           Set up 2 tables in PR database: PR and Client Codes (imported from the existing Client Codes database)

           Set up relationship between the 2 tables on a field called Client Code (sorry about database and field being almost same name, but it's historic) PR::Client Code = Client Codes::Client Code

           I want to show contents of field Client Codes::Client on layouts in the PR database.

           I've tried making a pure lookup based on the relationship and have also tried inserting a portal with display data from Client codes::Client, but nothing seems to work.

           Tried relookup on an existing record and tried creating a new record, but nothing doing.

           All fields are set as text fields.

           Any ideas from anyone?

           If you need screenshots or other details let me know

            

        • 1. Re: Can't get relationship to work
          philmodjunk

               Better check to make sure that the values in PR::Client Code exactly match the values in Client Codes::Client Code.

               What kind of value is used as a "client code"?

               

                    I've tried making a pure lookup based on the relationship and have also tried inserting a portal with display data from Client codes::Client, but nothing seems to work.

               Did you try just using the field tool to add Client Codes::Client to your PR layout? But your portal should have worked and that's why you need to check the actual data in your match fields. Also check the field data types.

          • 2. Re: Can't get relationship to work
            Beemer

                 Yes, the values do exactly match.

                 Values are simple text - just a couple of initials.

                 

                      Did you try just using the field tool to add Client Codes::Client to your PR layout? But your portal should have worked and that's why you need to check the actual data in your match fields. Also check the field data types.

                 Yep, tried that too and just tried it again and relookup in the client code,  but no joy. Field data types are text for the client codes and client on both databases.

            • 3. Re: Can't get relationship to work
              philmodjunk

                   I doubt that they exactly match. Since they are "just a couple of initials" the presence of a single extra space or a period used in one table's match field and not the other will keep the records from matching values as expected.

                   Using Initials for the client code, BTW, is not really a good way to match records in a relationship. An auto-entered serial number defined in the client code table would be a much better method.

              • 4. Re: Can't get relationship to work
                Beemer

                     Not sure I understand.

                     In PR the user manually enters a code in PR::Client Code. PR::Client field is then set to look up a value based on this code from the Client Codes database. If the code does not match, then no value will be returned. If it does, then I was hoping the value would be returned, but it isn't.

                     I know the codes match exactly because I am entering them and it's not hard to get them right.

                      

                • 5. Re: Can't get relationship to work
                  philmodjunk

                       Check to make sure that both fields are of type text. If the values match then the relationship will work. Otherwise we'd all be out of work.

                  • 6. Re: Can't get relationship to work
                    Beemer

                         I've sorted it. I think I need to read up a little more about tables!

                         There were two tables in the PR database (PR and Client Codes). I deleted the Client Codes table, reset the relationship as I intended and it's worked.

                         Thanks for your input.