3 Replies Latest reply on Jul 7, 2014 3:45 PM by philmodjunk

    Using Calcs as Key fields in Relationships.



      Using Calcs as Key fields in Relationships.


           Hi Everyone!


           I'm new to the forum but have been using FMP for close to 15 years.  As much as I have been using it I am still a little old school and have not kept up on the latest tips and tricks with the newer versions.  My question is:  Is there a way to use calculated fields as a primary key for relationships? 

           Here is the calc I'm looking at using.

           Case(KeyNum = 1;AcctNumYr1;
           KeyNum = 2;AcctNumLongYr1;
           KeyNum = 3;AcctNumDiff1Yr1;
           KeyNum = 4;AcctNumDiff2Yr1;
           KeyNum = 5;AcctNumInsYr1;"")


           I need the relationship to do look ups, so I'm thinking I'll have to use several relationships instead of just one only because the data in the Acct... fields can change and there for this field can not be completely indexed.  Its also has dozens of users and is over a server so the speed is of course an issue as well.


           I'm mostly thinking out loud now so "Thanks for listening." 


           Mary K. (McCassey) Conohan

           Butterfield Technologies, LLC (formerly School Base)

           South Sutton, NH

        • 1. Re: Using Calcs as Key fields in Relationships.

               This can be done, but it's not a a primary key.

               Primary keys uniquely identify records in the table in which they are defined. Only one record would be assigned any one primary key value and that value, once assigned, should never be changed.

               Can you explain the purpose of the KeyNum field and how each of the 5 Acct... fields differ from each other?

          • 2. Re: Using Calcs as Key fields in Relationships.

                 OK, so let me step back I apologize.  In our old system we had a budget db.  it held 5 different types of records Base Pay, Stipends 1 and 2, Longevity, and Insurance.  All of which, as I can tell, need to be in the same table due to reporting requirements.  We distinguished them using the keynum field...  So I just answered my own question...I'm doing it all wrong!   LOL! 


                 Sorry everyone!  I use to bounce all of this stuff off my old boss Mac McCassey, but he has now retired, so I have no one to bounce the ideas off of anymore.    I apologize in advanced there will be more of these. 


                 Thanks Phil and everyone.


                 Mary Conohan

                 Butterfield Technologies, LLC

            • 3. Re: Using Calcs as Key fields in Relationships.

                   This would be a pretty good place to bounce ideas and there's no reason why you can't use this calculation field to match to specific groups of records, just don't used as a primary key.