Jump to content

Lookup fields


This topic is 2879 days old. Please don't post here. Open a new topic instead.

Recommended Posts

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?

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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.  :-)

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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.  

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

This topic is 2879 days old. Please don't post here. Open a new topic instead.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share

×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.