6 Replies Latest reply on Sep 15, 2016 3:57 PM by ezeitgeist

    Mirror fields in 2 tables ...

    ezeitgeist

      So I am using MasterDetail2.0 and I've run into an interesting dilemma: The Master Detail "primary" fields all have to be from the same table and specifically the table where the primary ID resides. I want to display fields in the side lookup (the primary fields) that are from two different tables (I have contact name in one table [the primary one], but address and type of company in a separate Companies table). To have the fields show up properly it looks like I will need to have the fields mirrored (the type of company, company name, and company state from the Companies table) in the Contacts table. (When you mix fields from different tables in the primary fields area you get a lot of "?" instead of actual information appear.)

       

      What is the best route to do this? (Or if someone has an alternative route, I am also down for that.)

       

      Should I create fields in the Contacts table and just use a calculation to say that they are equal to the matching field in the Companies table? Should I have a script run that does a Set Field command every time a Company record is updated? Would love to figure out the SIMPLEST solution.

       

      Thanks!

        • 1. Re: Mirror fields in 2 tables ...
          wimdecorte

          ezeitgeist wrote:

           

          Should I create fields in the Contacts table and just use a calculation to say that they are equal to the matching field in the Companies table? Should I have a script run that does a Set Field command every time a Company record is updated? Would love to figure out the SIMPLEST solution.

           

           

           

          Neither of those for various reasons.  Why not just show the related data? 

          • 2. Re: Mirror fields in 2 tables ...
            beverly

            HISTORICAL data (name, address, phone) may need a lookup, for invoicing for example, as this data can (and does) change over time.  But most of the time just using the related field is sufficient.

             

            OP has to decide what's historical in nature.

            beverly

            • 3. Re: Mirror fields in 2 tables ...
              ezeitgeist

              That is the whole issue, I CANNOT just use the related field (see entire MasterDetail explanation in the original post). I would if I could, of course.

               

              For Todd Geist's Master Detail module to function properly, the 5 primary fields must be from the same table. I want to use fields from 2 different tables. Since that is not possible, I am trying to figure out the workaround.

               

              Given I CANNOT use just the related field, the whole dilemma, what is the simplest workaround? Unless I am misunderstanding what you mean by "show related data".

              • 4. Re: Mirror fields in 2 tables ...
                beverly

                Contact Todd?

                 

                Sent from miPhone

                • 5. Re: Mirror fields in 2 tables ...
                  dtcgnet

                  MasterDetail 2.0 works by building the values in chunks of 100. It does it something like:

                   

                  GetNthRecord ( field ; rep + 0 ) & colSeperator & GetNthRecord ( field2 ; rep + 0 ) & colSeperator2 & GetNthRecord ( field3 ; rep + 0 ) & colSeperator3 & GetNthRecord ( field4 ; rep + 0 ) & colSeperator4 & GetNthRecord ( field5 ; rep + 0 )  & delimiter &

                  GetNthRecord ( field ; rep + 1 ) & colSeperator & GetNthRecord ( field2 ; rep + 1 ) & colSeperator2 & GetNthRecord ( field3 ; rep + 1 ) & colSeperator3 & GetNthRecord ( field4 ; rep + 1 ) & colSeperator4 & GetNthRecord ( field5 ; rep + 1 )  & delimiter &

                  ...

                  GetNthRecord ( field ; rep + 99 ) & colSeperator & GetNthRecord ( field2 ; rep + 99 ) & colSeperator2 & GetNthRecord ( field3 ; rep + 99 ) & colSeperator3 & GetNthRecord ( field4 ; rep + 99 ) & colSeperator4 & GetNthRecord ( field5 ; rep + 99 )  & delimiter &

                   

                   

                  The problem with using related fields as one of the collecting fields is that GetNthRecord on a RELATED field returns the value for that field of the Nth record of the related set, NOT the value for the related field in the Nth record of the current table.

                   

                  For a quick and dirty fix that would work for you, the easy route would be to create a calculated field in the layout table which is equal to the related field you want to use. ( e.g., Contacts::CompanyName = Companies::CompanyName ). You'd use your newly created field (i.e., Contacts::CompanyName), in that example. It would be an unstored calculation, so it would only be evaluated when needed and on layouts where you use that field. A simple calculation like that would evaluate very quickly and would probably not cause performance issues (but be aware!).

                   

                  I downloaded MasterDetail 2.0, and have experimented with a way to have it work with fields regardless of whether they are in the layout table or a related table. I created five unstored calculated fields in the layout table:

                  zCollectField1 = GetLayoutObjectAttribute ( "MasterDetailPrimaryID" ; "content" )

                  zCollectField2 = GetLayoutObjectAttribute ( "MasterDetailFieldTwo" ; "content" )

                  zCollectField3 = GetLayoutObjectAttribute ( "MasterDetailFieldThree" ; "content" )

                  zCollectField4 = GetLayoutObjectAttribute ( "MasterDetailFieldFour" ; "content" )

                  zCollectField5 = GetLayoutObjectAttribute ( "MasterDetailFieldFive" ; "content" )

                   

                  This will cause these fields to contain values equal to whichever five fields you assign to the MasterDetail fields. I then modified the "Get Values in Found Set" script in two places.

                   

                  To modify that script, there are two steps that start with:

                  Set Variable [ $__Data[$n]

                   

                  The formulas start with:

                  Let ( [

                  field = GetField ( $fieldName[1] ) ;

                  field2 = GetField ( $fieldName[2] ) ;

                  field3 = GetField ( $fieldName[3] ) ;

                  field4 = GetField ( $fieldName[4] ) ;

                  field5 = GetField ( $fieldName[5] ) ;

                   

                   

                  Modify them to:

                  Let ( [

                  field = GetField ( "customers::zCollectField1" ) ;

                  field2 = GetField ( "customers::zCollectField2" ) ;

                  field3 = GetField ( "customers::zCollectField3" ) ;

                  field4 = GetField ( "customers::zCollectField4" ) ;

                  field5 = GetField ( "customers::zCollectField5" ) ;

                   

                  Long post, I know. Take out of this whatever benefits you in your situation.

                  • 6. Re: Mirror fields in 2 tables ...
                    ezeitgeist

                    Long post, but awesome post. I'm going to try Option 1, which was also my first thought, just to make sure I don't mess with any code that could somehow be linked with other scripts. That said, depending on if any lag shows up, your in-depth of the code will be insanely helpful. Thank you!