There is one situation in BaseElements where you can have a field that is being reported as “Unreferenced”, but when you try to delete it you get a message like the following :
There is actually no problem with deleting this field, but it might be worth exploring how this happens and why.
FileMaker actually remembers the calculations you’ve used in lots of places, even if you’re no longer using them. For example when you have a script step that does Go To Related Record [By Name] and have a calculation for the name, the calc is actually kept with the step even if you switch the step to Original Layout, or specify a layout directly.
In some places this can be useful. For example when you have a calculation field and change it to a non calc field, but turn on the Auto Enter Calculation option, the field calculation is still there. Occasionally I’ve found this to be useful, although it’s not that much of a stretch to copy the calc first and paste it into the second dialog.
In the case of the Privilege Set mentioned in the above example, the calculation is in the Custom Table privileges when you set the New, Edit or Delete options to “Limited”. The calculation is retained when you set the specific option to “Yes” or “No”, but not if you change the entire privilege set to something other than “Custom” for the Record Access.
It’s even more frustrating in this example because Custom Functions referenced in the same calculation don’t give you a “CF in use” dialog, so they could be deleted, leaving an incomplete calculation behind that doesn’t work if the option is switched back on. And to make it worse, you might get this dialog on a solution with 100 tables and the dialog doesn’t give you enough information to say which Table, or calculation is causing the issue. So you’d have to check all 300 options ( 100 Tables, and Create, Delete and View options for each one ) to find out where it is.
What I find even stranger is that you get a very similar dialog when you’re deleting a field used in a script. With one subtle but significant difference : If the field is only used in a script, the dialog has an “OK” button along with the “Cancel” button, so you can still delete the field even though it’s in use. When it’s no longer in use in the Privilege Set you can’t override the delete option.
Unfortunately, there isn’t an easy way to work around this, except to go through every Table and delete the calculation, and then change it back. You can actually just delete the entire calc and then ok the dialog, and will set the option to “No” and lose the references in the calc. But the pain is that you need to do this every time for every Table.
Alternatively if you change the Record Access to something other than Custom and ok the dialog, it will forget the fields used. But also it will also forget any other Custom Table access settings you had set, so you’d need to reset all of those. If you’re doing doing any other Limited options that will be quicker and easier than resetting each and every one.
Can BaseElements help?
With regards to identifying the actual places where there is a stored calculation, unfortunately not it can’t. The DDR doesn’t include any details of the calculation when you set the option to Yes or No, so I can’t even include a warning for the places where there is an old calc. This is in contrast to the Script Step example above, where the DDR does include the calculation used, even if it’s broken.
But if you’re using the method above, of changing the Record Access to something other than custom to reset every Table, then having the previous settings in BaseElements will allow you to reset everything to it’s previous values. This would be easier than trying to find them individually. Just go to the Privilege Set in question and to the “Tables” tab and all the details are there.