Autolookup in a Subform?

追蹤

Gwen Wilson

• 2020年04月01日 22:33
• 編輯於

Hi, I have NX 6.0. I'm trying to use an autolookup in a subform (repeating, column) and not having any luck. 

The subform is populated from an autolookup to SQL database.

Here's what I have:

I need for when the user changes the Business Unit Manager, to perform an autolookup to the employee table in SQL and return the appropriate ID.

I put an autolookup in the subform for this one. I've tried different lookup sources... the following 2 will validate.

SELECT UserID as '{subform_BusinessUnitMaintenance_SubForm/subform_BusinessUnitMaintenance/txt_BusinessUnitManagerID}' FROM dbo.[CW_RIA_Users] where Name = '${subform_BusinessUnitMaintenance_SubForm/subform_BusinessUnitMaintenance/txt_BusinessUnitManager}'

SELECT UserID as txt_BusinessUnitManagerID FROM dbo.[CW_RIA_Users] where Name = '${subform_BusinessUnitMaintenance_SubForm/subform_BusinessUnitMaintenance/txt_BusinessUnitManager}'

I've tried execution on specific field value change (manager) and related field value change. But I can't get any of this to work when I run the form.

Is this not the way to accomplish this?

As an extra, I really don't need to display the manager ID to the user (they don't need to see this), I just thought this would be the easiest way to update that field and couldn't think of another way to do it (like if it could be done in the process instead of the form). In the process, I do a batch insert into the SQL database (dump all rows and then write these in). Is there another way that I should do the whole thing?

thanks in advance for any help!

Gwen

0

• 

  sysberry GmbH

  • 2020年04月02日 09:19

  Hi Gwen,

  the problem is on triggering, or the value that you get from the autolookup? Please try following options:

  1) don't use manual script and use the normal configuration of the DB Lookup (as you don't have any joins or complex logics, why you use manual SQL script?)

  2) try to switch to another type of subform (not columns as in your example)

  3) make cutom trigger as configuration of the autolookup and trigger autolookup from rule on the dropdown.

  Regards

  Alexey

  0

  評論操作 永久連結
• 

  Gwen Wilson

  • 2020年04月03日 19:55

  Thanks Alexey, the problem was the trigger. I took out my manual SQL statement and did the normal configuration. That got me part of the way. The lookup triggered, but I didn't always get the correct results. I've had issues with commas in data before, so I removed the commas and that fixed it.

  I think it's all working now.

  Thanks!

  0

  評論操作 永久連結

請登入寫評論。