6 Replies Latest reply on May 16, 2015 9:05 AM by philmodjunk

    HELP WITH RELATIONSHIPS

    ErickJ.Gonzalez

      Title

      HELP WITH RELATIONSHIPS

      Post

      Hello I am creating a Database with 3 TABLES:  "PERSONNEL TABLE", "ACTIVITY LOG" and "REFERRALS".

      I would like my staff to be able to record their activities on the "ACTIVITY LOG". This layout has a button(script) that when a referral is made, they can click on it, and it takes them to the "REFERRALS" table where they can enter the referral info.

      I would like the end result to be (for example) is that there would be 150 records in the "ACTIVITY LOG"  and 30 records in the "REFERRALS" table, since not every activity entered results in/requires a referral.  

      As you can see currently the "REFERRALS" table is not even related to any of the other tables.  I was clearly doing something wrong because every time I would enter a new record on the "ACTIVITY LOG" it would pop-up this message:

       “_kp_referral_id” is defined to require a value, but it is not available on this layout. Use another layout to assign a value to this field".

      PS: I am not sure if I am understanding the _kp_ vs _kf_  and thier respective settings. PLEASE HELP!!!

      Thank You,

      ForumQuestion.jpg

        • 1. Re: HELP WITH RELATIONSHIPS
          philmodjunk

          A primary key (__pk, _kp, or however you name it in your systems) uniquely identifies each record in a given table. In FileMaker, this almost always means using either a number field that auto-enters a serial number or a text field with the Get ( UUID ) function. (select the unique values validation for UUID's as well) This field is typically used as the match field in one to many relationships to other tables.

          A foreign key (_fk, _kf or whatever) is not a unique identifier field. It is a match field that is used to match to the primary key of a record in a different table (Table occurrence actually). So if one Referral can match to many records in activity log, you would match the primary key of Referral to a foreign key in activity log. If one Activity Log can match to many referrals, you'd match a primary key in activity log to a foreign key in referrals. If this is a one to one relationship, you can set it up either way. If this needs to be many to many, you need another table in between as a join table. I can expand on that in a future response if you need that info.

          Your error message can have several different causes. If, after reading the above info, you still can't resolve that part, you'll need to describe what you did that produced that error in more detail. All I can tell from here is that something you did tried to create a record in that table under circumstances where a value in that field is required.

          • 2. Re: HELP WITH RELATIONSHIPS
            ErickJ.Gonzalez

            Thanks for explaining the _kp_ vs. the _kf_. 

            I suspect the error was a result of the settings that I gave a _pk_ field in the REFERRALS table. - I removed the field altogether.

            There can be many records in the ACTIVITY LOG but only a few of those records may have a corresponding records found in the REFERRALS table. (These records need to be  related)

            Also I have 4 staff members that will not be creating records in the ACTIVITY LOG but instead will only be creating records  in the REFERRALS table. (These records will not have a corresponding record in the ACTIVITY LOG)

             

             

            • 3. Re: HELP WITH RELATIONSHIPS
              philmodjunk

              There can be many records in the ACTIVITY LOG but only a few of those records may have a corresponding records found in the REFERRALS table. (These records need to be  related)

              Also I have 4 staff members that will not be creating records in the ACTIVITY LOG but instead will only be creating records  in the REFERRALS table. (These records will not have a corresponding record in the ACTIVITY LOG)

              This info really has no bearing on how you set up your relationships. It may affect your interface design or even access permissions set in Manage | Security, but it does not affect what relationships should be set up.

              • 4. Re: HELP WITH RELATIONSHIPS
                ErickJ.Gonzalez

                I have my interface pretty much the way I want it, and the access permissions are set up to get staff to the layout/table that they would use, but if I want a record in the ACTIVITY LOG table to be related to the specific record in the REFERRAL table don't I have to create a relationship? and then how do I create records in the REFERRAL table without it creating a record in the ACTIVITY LOG table?

                I thought they would be related through a serial number so (for example) record# REC0001 in the ACTIVITY LOG would be related to record #REC0001 in the REFERRAL table.

                Its sound like I am really off on this thought process. 

                • 5. Re: HELP WITH RELATIONSHIPS
                  philmodjunk

                  Of course you do, but those details that you recently posted do not change what kind of relationship you should set up. I've already describe what relationships you should use. You last post does not change my previous recommendations.

                  What you describe should work if the ID is generated in Activity_Log and you never need to link a given activity_LOg record to more than one referral. If the reverse is the case, that you do need to link activity_Log to multiple referrals (even if only very rarely), then the ID should be generated in referrals. And if it is ever, even remotely possible that you will need to link more than one activity_log entry to the same referral AND also need to link one referral to more than one activity_log entry, you need to consider setting up a join table with ID's generated in both activity_log and Referrals that link to a record in the join table.

                  • 6. Re: HELP WITH RELATIONSHIPS
                    philmodjunk

                    PS. I also would simplify the ID to just an auto-entered serial number without any added text or leading zeroes.