Lovely forum bug lost my post.
I would suggest adding a table of global fields and changing one of your key fields to be an auto enter/lookup entry that is a type indicator and use these two fields as a single key. (ie the indicator is A when in the Account layout and C in the Contacts layout) this value is looked up from a Global field that is a calculation that Sets it based on layout. (you can use a case statement)
Then your other field is the serial id from the Current Record. (You can set this in a Global field too so you can use the relationship to the global table based on these two fields to filter your views) Again this can be a case statement based on layout and pull the Records key ID field.
Im sure this is as clear as mud so far and Im pretty tired but using this technique can be very useful in building dynamic displays because the intelligence is built into the key fields.
If you are haveing trouble just let me know I can clear things up a bit but in general your structure is limiting things a bit and a slight change in structure will allow us to do so much more.
So you are suggesting that my foreign keys reference only one field instead of having an accounts foreign key and a contacts foreign key in the notes table?
Yes but you can build some intelligence into the key by prefacing your keys with a different character for each table.
Ive used a very similar scheme for a number of things like this. Im limited on time at the moment.
I also have a note table used for many tables. I have a Field for fk_record_id and I have a field called TABLENAME. On each table I have a field called tablename. So essentially all tables that have notes are related to the notes table by the same fields pk_id and TableName. This helps me a great deal.
It's hard to give ideas on a generic script without knowing what you want it to do?...
Here's another way to do this:
Use Set Field By Name to modify your notes field and pass the table and field name to the script as a parameter.
Thus, If you want to clear the notes field, you might have a one line script like this:
Set Field By name [ Get ( ScriptParameter ) ; "" ]
You can then put a button on both your Accounts and Contacts layouts to run this script but specify different parameters. For the Accounts layout, you'd use this parameter: GetFieldName ( AccountNotes::NoteField )
and on the contacts layout, you use: GetFieldName ( ContactNotes::NoteField )
By using GetFieldName, you set this up so that the script continues to work for you even if you later rename the field or it's table occurrence.
I'm not sure I understand 100% what you are saying. I think you are illustrating a way to avoid hard coding field names / table names during the actions of updating / changing the data in the notes table.
I am currently re-arranging my tables so that the relationships between notes and contacts (or accounts) is based on two fields as follows:
1) Notes::ID_FK->Accounts::AccountID -or- Notes::ID_FK->ContactID (same notes field for both relationships)
2) Notes::Table_FK->Accounts::TableName -or- Notes::Table_FK->Contacts::TableName (where table name is simply a calculated name of the table "itself")
Is this how you would implement it? Any issues with relating tables with two fields?
You are correct and you can use Get Field if you just need to reference this data rather than modify it.
I think you are describing:
1) Notes::ID_Fk = Accounts::AccountID
Notes::ID_FK = Contacts::ContactID
2) Notes::Table_FK = Accounts::TableName
Notes::Table_FK = Contacts::TableName
I'd use two ID fields in Notes inplace of ID_FK, with only one field containing an ID number for any given Notes record unless it's possible to link the same note to a record in both tables. Not saying that you can't get the above to work, it just seems "cleaner" to me to have two FK fields in notes.
When you say two (2) FK Fields in Notes, do you mean:
Notes::ID_FK1 & Notes::ID_FK2 (one for accounts notes and one for contacts notes)
Notes::ID_FK & Notes::Table_FK (both accounts and contacts related to both of these fields)
I'm guessing you are suggesting B.
Its essentially the same as having a Note type field.
In order to do generic scripting I usually build a reference tale with Layout name Record name and a Note Type value
Then in order to pass the appropriate values during scripting I Have a Table with Global fields that get populated through various meand with the current layout (usually on enter layout) and the current Record and vareious other keys.
I build relationships between this global table and my data tables.(Including the reference table) Which is one way in which I populate the Notes type Code.
By changing the values in the global table (often via a loop through the reference table) I can examine all the various types of records I need using a single script. If I want different processing by code type then I simply use a case statement where I do the proper processing or call a subscript passing variables if need be.
You can even use this techniqe to work with different tables. because all tables are connected to this global table through the appropriate keys
Knowing what exactly you are trying to do would help determine more of the details of how to set it up.
I was suggesting A not B. B also works but takes more setting up to do that you can avoid with A.