10 Replies Latest reply on May 18, 2011 10:47 AM by philmodjunk

    Table References as Variables

    Vinny

      Title

      Table References as Variables

      Post

      I am sharing sub tables amongst several main tables.  For example, the "accounts" table and the "contacts" table both relate to a table of "notes" by way of foreign keys as follows:

      Accounts:Account ID->Notes:Account ID_FK

      Contacts:Contact ID->Notes:Contact ID_FK

      This way, both my accounts and contacts tables can have related notes.

      Now, I want to write generic scripts that can be used by both the Contacts & Accounts tables.  However, I am finding that I cannot do that because while in a layout based on the accounts table, all my references to a field in the Notes table must be per the relationship (i.e. Accounts::Account Notes::Note ID, or, contacts::contacts notes::Note ID).  This is making it complicated to write generic scripts.

      Any ideas?  Was this clear enough?

      I realize that if the script moves to a layout based on the notes, that the fieldnames can remain generic.  I was just hoping I could stay on the main table's layout.  Also, FYI, the script is being called from a portal row.

        • 1. Re: Table References as Variables
          aammondd

          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.

          • 2. Re: Table References as Variables
            Vinny

            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?

            • 3. Re: Table References as Variables
              aammondd

              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.

              • 4. Re: Table References as Variables
                RestaurantCharlie

                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?...

                • 5. Re: Table References as Variables
                  philmodjunk

                  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.

                   

                  • 6. Re: Table References as Variables
                    Vinny

                    Phil,

                    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)

                    AND

                    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?

                     

                    • 7. Re: Table References as Variables
                      philmodjunk

                      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

                      and

                      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.

                      • 8. Re: Table References as Variables
                        Vinny

                        When you say two (2) FK Fields in Notes, do you mean:

                        A)

                        Notes::ID_FK1 & Notes::ID_FK2 (one for accounts notes and one for contacts notes)

                        OR

                        B)

                        Notes::ID_FK & Notes::Table_FK (both accounts and contacts related to both of these fields)

                        I'm guessing you are suggesting B.

                        • 9. Re: Table References as Variables
                          aammondd

                          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.

                           

                          • 10. Re: Table References as Variables
                            philmodjunk

                            I was suggesting A not B. B also works but takes more setting up to do that you can avoid with A.