11 Replies Latest reply on May 3, 2013 8:21 AM by philmodjunk

    Many to many table accepting values that don't match PK tables

    WeatherGirl

      Title

      Many to many table accepting values that don't match PK tables

      Post

           Hi

           I am new to FM and I have created a many to many relationship using a joining table as follows;

           Table: Contacts; PK is "conIDNum"

           Table: Supplies; PK is "supIDNum"

           Joining Table (the many to many table): Contact_Supplies: this has its own primary key "consup_IDNum" just to give you that information, but the foreign keys are "consup_conIDNum" and "consup_supIDNum"

           therefore: "consup_conIDNum" = "conIDNum" and "consup_supIDNum" = "supIDNum"

           so I have joined appropiately in the realtionship graph, but I don't know how to set it so that the many table "Contact_Supplies" only accepts records if they already exist in the "Contacts" table and in the "supplies" table.

           At the moment, I can add records to the "Contacts_Supplies" table but it seems to accept any values in the foreign key fields when I was testing it. I want it to only accept values in these foriegn key fields if the value exists in the related table PK's. 

           and yes they are all number data types, the PKs being unique with auto serial number.

           I have tried ticking the checkbox in the "Edit Relationship" window where it says under "Contact_Supplies"  table, "Allow creation of records in this table via this relationship" but this didn't work.

           I tried going into the "Manage Databse", table; "Contact_Supplies" and highlighting the field "consup_conIDNum" and then clicking on  "options" for the field, and ticking the "Exisiting Value". but this didn't work. I realised later that it refered to an exising value from the same field. SO sigh.... I tried on the "Auto-Enter" tab, the "Looked-up value" thinking that this may work, and I set it to look up the "conIDNum" in the "Contacts" table, but this didn't work either. so I am stuck.

           Can someone point me in the right direction.

           Thanks

            

            

        • 1. Re: Many to many table accepting values that don't match PK tables
          philmodjunk

               The simplest method is to use a portal to the join table placed on a layout based on one of the other two tables. If you enable "allow creation..." in the relationship between Contacts and Contact_Supplies, you can place a portal to Contact_Supplies on the Contacts layout and format consup_supIDNum as a value list of SupplierID's from the Suppliers table. A validation field option can restrict values to only members of that value list to make sure that a wrong ID number cannot be entered, but the drop down list will tend to prevent that in the first place.

               If you want to use a layout based on the Contact_Supplies layout, format both foreign key fields with value lists based on the respective tables and set both up with validation rules that limit vaules to those values that are a member of the associated value list.

          • 2. Re: Many to many table accepting values that don't match PK tables
            WeatherGirl

                 Hi there,

                 thank you for your response.  This works well, except that in the Value List I would like to Hide the conIDNum and be able to display the second and third field. 

                 This is for "conFirstName" and "conLastName". so the user can pick the correct name, with "conIDNum" still to be entered into the many table "consup_conIDNum".

                 so it works fine I just want to display the extra third column and hide the first column so the user doesn't see this.

                 Thanks

            • 3. Re: Many to many table accepting values that don't match PK tables
              philmodjunk

                   Add a text field to your table and define this auto-enter calculation:

                   "conLastName" & ", " & "conFirstName"

                   Select "unique values" as a validation field option for this field.

                   Then make this field the secondary field for your value list.

                   There are two options for hiding the ID number once you have selected a contact from the value list:

                   1) use a pop up menu instead of a drop down list.

                   2) Place a name field from contacts with an opaque fill color on top of the drop down list. Use Behavior settings to prevent Browse mode access to this name field. When you click on the name field, the drop down list behind it deploys and displays the value list of contact names. When you select a value from it, it disappears back behind the name field that now displays the name of the selected contact.

              • 4. Re: Many to many table accepting values that don't match PK tables
                WeatherGirl

                     hmmm ... it seems this is not working.

                     I understand what its suppose to do.  this works like a query in an access database, I understand, but it is not concating the two fields together.

                     but this "conLastName" & ", " & "conFirstName" is not working.

                     so I tried

                     ="conLastName" & ", " & "conFirstName"

                     =("conLastName" & ", " & "conFirstName")

                     =("[conLastName]" & ", " & "[conFirstName]")

                     none of these worked

                     I then tried

                     "contacts.conLastName" & ", " & "contacts.conFirstName"

                     but still didn't work

                     Then this;

                     conFirstLastName = "conFirstName" & " " & "conLastName" 

                     conFirstLastName = conFirstName & " " & conLastName

                     Nope none of these are working.

                     What am I doing wrong??

                      

                • 5. Re: Many to many table accepting values that don't match PK tables
                  JimMac

                       Select the field name from the Table list, do not hand type it.

                       it should look sumptin like this

                  Contact::conFirstName & " " & Contact::conLastName

                  Jim...

                  • 6. Re: Many to many table accepting values that don't match PK tables
                    philmodjunk

                         Apologies, Don't know why I included those quotes around the field names which then produced a wrong calculation. Do it as Jim has advised and thanks to Jim for the correction here.

                    • 7. Re: Many to many table accepting values that don't match PK tables
                      WeatherGirl

                           ok I tried what Jim suggested and it still is not working.

                           If I don't hand type it then it looks like this

                           conFirstLastName = conFirstName & " "  & conLastName

                           which doesn't look like Jims syntax.

                           so then I did hand type in what Jim had and still is not working.

                           ok lets go over the steps again.

                           1. create text field in "Contacts" table - DONE, called "conFirstLastName"

                           2. go into manage database and click on database, go to fields tab, highlight "conFirstLastName", then click "Options"

                           3. on the "Auto-Enter" put a tick in "Calculated Value" and click on "Specify"

                           4. the "specify calculation" window pops up.  click on the field name "conFirstLastName", this then comes up in the calculaton section below.

                           Then what? If I don't type it in and just click everything it looks like this, (below) which I have already done above

                           conFirstLastName = conFirstName & " "  & conLastName

                           so I'm lost...

                      • 8. Re: Many to many table accepting values that don't match PK tables
                        philmodjunk

                             It should just be:

                             conFirstName & " "  & conLastName

                             But you will only see a new value appear in new records. Existing records won't update to show a value automatically. See this thread for how to get such a change to affect existing records: Updating values in auto-enter calc fields without using Replace Field Contents

                        • 9. Re: Many to many table accepting values that don't match PK tables
                          JimMac

                               Sorry for jumping in the middleangel

                               The syntax I was using is a normal Calculation type  where  TableName::FieldName

                               But ...

                               when you are using the Auto-Enter Calculation option for Fields within the same table the TableName is superfluous.

                               Thus just.....  

                          FieldName...

                          Now....

                               it should look this.... forget the left hand side of the calculation it is also suprerfluous.

                          conFirstName & " "  & conLastName    

                                

                          per Phil

                          Jim...

                                

                                

                          • 10. Re: Many to many table accepting values that don't match PK tables
                            WeatherGirl

                                 ok cool!!! PhilModJunk I read the link you provided and it works perfectly.

                                 thanks

                                 so this is what I did.

                                 I went into manage database, clicked on the field, changed it from text to calculation type, and in the "Specify Calculation" dialog box I entered

                                 conFirstName & " "  & conLastName

                                 and made sure I enterd "Text" as the 'Calculation result' and it all updates and works well.

                                 Thanks Guys for all your help!!!

                            • 11. Re: Many to many table accepting values that don't match PK tables
                              philmodjunk

                                   But don't forget to change it back into an auto-entered calculation. You need a unique values validation option that can't be set on a field of type calculation. Otherwise, you can get two entries with the same first, last names, but only one appears in your value list.