1

I asked a question similar to this previously (How to use RecId as a foreign key in a form) but would like to explore it a bit further in a more complex scenario.

Replacement keys work great when you have indexes set up and allow duplicates set to no, but they don't seem to work at all with multiple-field indexes or when allow duplicates is set to yes.

Is there way, programmatically, to replace a foreign key in a grid with a translated value without using replacement keys? I tried writing a display method to override the field, but some odd behavior resulted--fields moving around in the grid, and the display method being unaware of which row to use, thus all values in the entire column were the same.

Table A: Bob:1, Sally:1, Sue:3 Table B: 1:Apples, 2:Apples, 3:Oranges The "people" are tied to their favorite "foods" by the food RecId, refererenced in the People table. Assume there is additional data in other columns that make these records unique, so consolidating "1:Apples" and "2:Apples" is not possible.

It seems there should be a way to write a display method to overwrite a field value in a grid. Any suggestions? Sample code?

Thanks

Community
  • 1
  • 1
Brad
  • 1,357
  • 5
  • 33
  • 65

1 Answers1

4

Firstly, surrogate FK replacement does (or at least should) work with composite keys (e.g., {First Name, Last Name}).

Secondly, you state that there is "additonal data in other columns" that make these records unique...Then why aren't these columns being combined with the food's name to form an alternate key? The data model seems incorrect (or at least some metadata isn't being made consistent with the conditions you've stated)

Thirdly, any Field Group can be chosen as the ReplacementFieldGroup on a Reference Group control. That alone will allow you to do basically whatever you want. That said, I would strongly encourage you to use an alternate key as your replacement field group whenever possible due to the semantics of surrogate FK replacement.

Flow:

1) User types a value(s) into reference group.

2) User's tabs out.

3) User's typed value(s) are used to look up a record in the related table.

4a) If the user's typed in value(s) are uniquely mapped to a record that record is chosen, else,

4b) If the user's typed in values are not unique a lookup is presented to allow the user to pick which record they "meant". Note that the lookup must therefore present a collection of uniquely identifiable records so that the user knows which record to pick (if the records all look the same in the lookup then they'll have no idea what in the hell they should pick.)

5) Upon successful resolution of the typed values, the record is set back on the source form.

Given this flow, it is obvious that steps 3-5 will be broken if there is no unique index (key) on the table. (How is the user supposed to specify a unique reference to the record if the record has no means of being uniquely identified (assuming you don't want to display RecId to the user)???)

In the exceptional case that you decide that you still want to use a non-unique index as your replacement field group you must implement resolveReference and lookupReference to provide the user a unique resolution/lookup experience (to handle steps 3-5 in the above flow). Note: The common use case for this is wanting to effectively eliminate non-selective fields from being displayed in Reference Group and instead letting some outer context or mode implicitly set that value. E.g., if the alternate key was {Size, Color}, one could potentially make "Color" a global form context--perhaps by having the user pick a color at the top of the form--and only have the user enter Size into Reference Group...The Color could then be implicitly added back via the resolveReference and lookupReference overrides.

joetechbob
  • 118
  • 6
  • Thanks for the detailed response. I will dive into it in the morning and see if I can implement your suggestion. Unfortunately, my example was probably a little too simplistic on the table data. This table has a very specific purpose that necessitates the RecId being the only truly unique field in the table. – Brad May 23 '12 at 06:32
  • 1
    In a two column replacement key, is there a way to suppress one of the two values when it displays on a form or in a grid? Our index consists of one column with text data and another column with behind-the-scenes data that shouldn't display. – Brad May 25 '12 at 14:25