Lookup fields

[James Gill]

I have an indexed calculation that is concatenating the first and last names of a contact together.  The two fields, first name and last name, are looked up from a contacts table when the related value id_contact value is inserted via a script.  The problem I'm having is that for some reason when the value is inserted via the script, only the last name field is looked up.  The first name field stays blank.  When I insert the value by hand, both fields are looked up.  What can cause this behavior?

[Kris M]

Are you using...

 

Lookup ( sourceField {; failExpression} ) & Lookup ( sourceField {; failExpression} )

 

or

 

Calculation Field Type =Lookup ( contacts::firstname ) & Lookup ( contacts::lastname )

 

Have you specified both fail expressions?

 

Mabye do the concatenation in the contacts table and use an autoenter Lookup?

 

Maybe try ExecuteSQL() instead of Lookup()?

 

Or are you doing something else?

 

And yes i know this is trying to solve the problem and not answering your "why"

 

K

[LaRetta]

Hi James,

 

Normally there is not a need to insert a Contact's first and last name into other tables since they are related to Contacts. You can simply place the related fields directly onto this table's layouts or otherwise reference the related table directly.  :-)

[Kris M]

Hi James,

 

Normally there is not a need to insert a Contact's first and last name into other tables since they are related to Contacts. You can simply place the related fields directly onto this table's layouts or otherwise reference the related table directly.  :-)

Or pseudo concatenate with a merge field

[James Gill]

Are you using...

 

Lookup ( sourceField {; failExpression} ) & Lookup ( sourceField {; failExpression} )

 

or

 

Calculation Field Type =Lookup ( contacts::firstname ) & Lookup ( contacts::lastname )

 

Have you specified both fail expressions?

 

Mabye do the concatenation in the contacts table and use an autoenter Lookup?

 

Maybe try ExecuteSQL() instead of Lookup()?

 

Or are you doing something else?

 

And yes i know this is trying to solve the problem and not answering your "why"

 

K

 

First, I have two fields, one for first name and one for last name.  These are text lookup fields that are copying the text values directly from the Contact record.  I then have a second indexed calculation field that is storing the concatenation of the firstname and lastname fields as 'firstname' & ' ' &  'lastname'.  The firstname and lastname fields are looked up when a related value is supplied via the script I mentioned and work correctly.  The 2nd indexed field is where the lookup fails

 

Hi James,

 

Normally there is not a need to insert a Contact's first and last name into other tables since they are related to Contacts. You can simply place the related fields directly onto this table's layouts or otherwise reference the related table directly.  :-)

I appreciate that, but in my case there is a very good reason that I'm not relating the actual values from the Contacts table.

[Rick Whitelaw]

James, I'm curious. What would your very good reason be?

[Lee Smith]

I was going to asks the same question, but then I thought,  --- why not wait for Rick. LOL

[LaRetta]

First, I have two fields, one for first name and one for last name.  These are text lookup fields that are copying the text values directly from the Contact record.  I then have a second indexed calculation field that is storing the concatenation of the firstname and lastname fields as 'firstname' & ' ' &  'lastname'.  The firstname and lastname fields are looked up when a related value is supplied via the script I mentioned and work correctly.  

 

It will also fail if, from the Contacts table, the first or last name is changed since a related table (your main table in this case) cannot recognize changes made in another table (Contacts) unless the local field is an unstored calculation.

 

So in 'this table', you have FirstName and LastName AND a concatenated full name calculation?  That is certainly overkill as well as breakable.

 

If you absolutely need a Contact's full name in your table, why not drop the First and Last name at least and just use an unstored calculation which concatenates from the related table?  But even if you need to export, you can export fields from Contacts as well so understanding the purpose would certainly help.  

[James Gill]

James, I'm curious. What would your very good reason be?

 

In this case, my users are sending information (including first name and last name) to an outside entity whilst an internal process continues.  We've run into problems where the first and last name of the contact has either been entered incorrectly (transposition error on our end) or given to us incorrectly (submission error on our client's end).  Our internal process ensures that we verify that the first and last name are correct, however while we continue to refine our data until we're 100% sure that we have the accurate contact information, the outside entity still has potentially outdated information.  

 

At some undetermined point in the future, we will receive a billable that contains among other things, the potentially incorrect first and last name of the information that we sent at the beginning of our process.  Unfortunately we cannot rely on other, more concrete information such as SSN or DOB (or some other identifier) because this information isn't 100% available, so I have to be as flexible as I possible when it comes to looking up that old, potentially incorrect first and last name and tying it to the correct Contact record in our database.

 

Hope that makes sense.

[comment]

Can you reproduce this problem in a brand new file?

[James Gill]

Nope, it functions as expected.  Granted, that was a fairly simple setup, whereas my solution is hosted, has multiple files, multiple permission sets, AD integration, etc, so it's not an apples to apples comparison.  I've recently run verifications on all of the files of the solution and none have returned corrupted.  From what the users are saying, this has been an on-going issue for about 6 months and I can't for the life of me think of anything that's changed other than the migration from FM12 to FM13.

 

It's just very strange that the lookup works when the id_contact values is pasted by hand but fails when scripted.