3 Replies Latest reply on Jan 11, 2015 6:50 PM by philmodjunk

    autofilling/calculating field contents from one of four possible related tables

    mchancevet@gmail.com

      Title

      autofilling/calculating field contents from one of four possible related tables

      Post

      Hi

      I have a 'collation' table occurrence related to 4 other TOs (accom, lab, boat, vehicle).

      1 record in collation is a replicate of one of the records from one of the other tables and needs to be related to that record so changes in any record in any of the four tables are reflected (updated) in the collation table.

      The relationship is a fID=ID (eg collation::fID=accom::ID) for each of the instances.

      I have fields (name, start date, end date, label, category) in each of the four related tables and in the collation table.

      For any record in collation I need the above fields to update if the related record in the other table (whichever one of the four) changes.

      My difficulty is in defining the calculation for the collation fields since one field in this table needs to update from a field in any one of four other tables.

      Thanks

      Morgan

        • 1. Re: autofilling/calculating field contents from one of four possible related tables
          philmodjunk

          This seems like a needlessly complex data model. I you can tell me what data in your "collation" table determines which table is then the source of data for that field, I can describe a way to "look up" the data, but changes in the child tables would not, in that case automatically update data in the collation table. From here, there is a distinct possibility that you might be able to combine all of your data in the "4 other tables" into either a single unified table.

          In order to provide better suggestions, can you more fully explain the purpose and function of your collation table? What does one record in this table represent and how will you use it?

          • 2. Re: autofilling/calculating field contents from one of four possible related tables
            mchancevet@gmail.com

             Hi Phil,

            Yes, the data model is flawed. It represents work undertaken during an earlier stage of my steep (and ongoing learning curve). I now realize I could have achieved my aims by having one table (instead of four), used a category field and defined relationships to four TO's for my data entry/ layout needs. Unfortunately, the structures (scripts, layouts etc) that refer to these tables currently prohibit a major redesign and my time is now limited. What's more, live operational data is contained in the current tables.

            I am attempting to implement a visual display via a plugin that takes it's input from fields in FM tables. It can only reference one table but I need to refer to all of these four data elements hence a retroactive attempt to put the genie back in the bottle (get them all into one table.

            Every journey starts somewhere and you learn from your mistakes. This project has really taught me a lot. I'm  loving FM 13 and can't wait to get myself positioned for certification.

            Thanks for your help,

            Morgan

            • 3. Re: autofilling/calculating field contents from one of four possible related tables
              philmodjunk

              I would make plans for a project that uses import records to pull all of the relevant data into a common table. This is not something that you can do overnight, but ultimately, that's what you need to accomplish here.

              This part lacks detail to the point where I don't have any way to offer a suggestion:

              I am attempting to implement a visual display via a plugin that takes it's input from fields in FM tables. It can only reference one table but I need to refer to all of these four data elements hence a retroactive attempt to put the genie back in the bottle (get them all into one table.