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?
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.
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".
Sent from miPhone
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 ) ;
field2 = GetField ( $fieldName ) ;
field3 = GetField ( $fieldName ) ;
field4 = GetField ( $fieldName ) ;
field5 = GetField ( $fieldName ) ;
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.
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!