Autolookup to SQL Database with mutiple filter conditions

关注

Gwen Wilson

• 2019年09月03日 16:22
• 已编辑于

Hi, I'm having an issue with my form using an autolookup (using NX 6.0 SP1 SU2 Hotfix 6).

Goal: Here’s what my form looks like (the pink fields will be hidden). The user chooses Customer first, then the applicable Locations populate the Location drop down (cascading lookup) and then the user can pick the location. At this point, based on Customer and Location, an autolookup should work to fill in the pink fields.

Here’s my table in SQL

• There may only be 1 instance of a customer or multiples (like Borden Dairy). So, it’s a combination key of customer/location that gets the Salesman and Service Mgr.

Here is my autolookup:

• I’m hooked to my SQL database, and have the 4 fields mapped to the applicable FormData fields.

• For the filter, first I just hooked it to Customer (to test and be more simple at first)…and have it set to execute when Customer changes.

• This worked, it just pops in the first match for that customer.

• So then I add the Location part to the auto lookup, and have it execute on change of location (the user has to pick the customer before location is enabled… so location would be the last field to change before this should run).

• Nothing populates

I have seen it populate one time. I have a customer that is "Not in the list". If the user chooses it, I have rules that automatically set the Location value and then disable it (the Location dropdown).

Thanks!

 

0

• 

  sysberry GmbH

  • 2019年09月03日 16:38

  Hi Gwen,

   

  can you verify what are the values for the dropdowns? For customer i understand the Value is the ID, and for Location? Can you also tryonly location as a filter?

  Regards,

  Alexey

  0

  评论操作 固定链接
• 

  Gwen Wilson

  • 2019年09月03日 16:50

  Hi Alexey,

  Correct, the value for Customer is Customer_ID. Location is simply Location. I don't have an ID field for it. It's format is "City, State". I wondered if the comma in the data could be messing it up.

  I tried Location as the only filter and it doesn't work.

  0

  评论操作 固定链接
• 

  sysberry GmbH

  • 2019年09月03日 16:55

  If only Location doesn't work, then the problem is there. Perhaps comma indeed makes problems. Can you try to make this lookup with SQL formula? Just to be sure, that it works with location? Alternatively with JavaScript make the call with only Customer ID as filter and look in the location column to know, what exactly coming in?

  Regards,

  Alexey

  0

  评论操作 固定链接
• 

  Gwen Wilson

  • 2019年09月03日 17:33

  I had tried to do a SQL statement before and it didn't help any.

  I just removed the commas from all of one customer's locations in the data and it worked. So commas in the data are the issue. It doesn't look awful, not having commas. But do you know any fix to get around it if my business owner has to have commas? I guess I could add an ID field to the locations and use it with "City, State" being only for display purposes.

  0

  评论操作 固定链接
• 

  sysberry GmbH

  • 2019年09月03日 18:01

  As far as i know there were some limitations for commas in AgilePoint. Perhaps now in V7 SU1 some of them is already fixed. ID is always best practise to work with. Normaly one should create another table with locations and ids and reference it in your table.

  0

  评论操作 固定链接
• 

  Gwen Wilson

  • 2019年09月03日 18:09

  Ok, thanks! I appreciate all your help!

  Gwen

  0

  评论操作 固定链接

请先登录再写评论。