3 Replies Latest reply on Jan 18, 2014 11:08 PM by mikem

    Calculation field as foreign key


      Hi Forum,


      I am trying to understand an inherited database. In this database I have an Activites child table related to a Schedule table. The Schedule table has a standard auto-enter serial number primary key. There is a nScheduleID field in the Activities table, but that is not the field used to define the relationship. Instead a calculation field has been created, with =GetAsNumber(nScheduleID), and that calculation field is used as the foreign key field. As the name implies nScheduleID is already defined as a number field, so the calculation does not serve any obvious purpose. So why is it set up this way? My guess is that it was an old trick to write protect the foreign key field? (The database is quite old, though now in FMS11). Or is this a current technique to protect a key field against changes? Seems that the relationship could have been directly to the nScheduleID field, with the Prohibit modification checkbox set.


      Thanks for any enlightenment.



        • 1. Re: Calculation field as foreign key

          could be that the original ID field was originally NOT a number field, perhaps it would be a good idea to take a look at the index on the ID field to see if there is any wierd data in there. As you pointed out, the calc field is not different to the original field, but perhaps there could have been a more meaningful calculation in there at some point??

          1 of 1 people found this helpful
          • 2. Re: Calculation field as foreign key

            From what you describe, this field is in the ACTIVITIES table and is used to relate to the SCHEDULES table. My best guess is that the ID field was once a text field, perhaps containing some leading text ahead of a serial number (eg. Sch/01234), and that the calc field was to just extract the number portion only, perhaps becuase the corresponding ScheduleID in the SCHEDULES table is/was a simple serial number field. As Peter says, you might get further clues from the field indexes.


            Should you change it if it seems to be a redundancy? That probably depends on what complications will follow, but certainly now that you are taking over responsibility it is an opportune time. If you unravel it and decide to leave it alone, I'd suggest at least adding a field comment to explain it all so you can remember it in six months time.

            1 of 1 people found this helpful
            • 3. Re: Calculation field as foreign key

              Thanks PeterWindle and keywords or taking the time. I checked the index values and they are all numbers, sorted as numbers, so no clues there. I am going to drop the redudant calculation field and see if it seems to affect functioning in any way.