0

Reverse database lookup

Topher Birth

July 28, 2011 22:19

None

Follow

I created a list lookup and was able to list some values like "none", "n/a", "not given", etc.  The list lookup allowed me to blank those out but leave the original contents if the value is not in the list.  After doing this I have to create a copy of the list lookup for all headers and edit each individual one if I wish to make changes.

I decided to try the database lookup.  After creating the access file and linking it there does not appear to be any way to leave the original values.  I can make it leave the original value if it finds it in the database, but if it is not found then it always blanks it out.

is there a way around this?

• Facebook
• Twitter
• LinkedIn

2 comments

• 0
  
  Topher Birth July 28, 2011 22:25

  Another frustrating thing I noticed about list lookup is that not being caps sensitive yields results which are always in all caps.  To keep original capitalization you need to enable cap sensitive yet you are then forced to input all possibilities (IE: NONE, None and none).

• 0
  
  Mike O'Quinn July 29, 2011 05:36

  Hi Topher,

  For what you're trying to do here, the list lookup would be the way to go as it only has a single output.  This is why you can specify to leave the source value.  In the case of the db lookup, you can have multiple inputs so that is why this option is not there.  To keep make this a little more maintainable, we've added the ability to make the subflow transformation either a record transformation or a record-set transformation depending on whether you are modifying the record set (i.e. filtering or sorting the set).  This means that you can create your list lookup, put it in a subflow and then re-use that subflow everywhere.  This feature is included in the 5.1 release coming early August.

   

  As for the meantime, I would switch the lookup around.  I would have your access lookup table return a value of "found" or something.  Then add this additional field to your record in an intermediate layout.  Then, use an expression to check the existence of "found".  If it is there, blank the value going into the desired target field.  Else, leave the value as is.

   

  Hope this helps.