DFDM Modifications to Allow UDT Field Lookups

When viewing a multi-line field within DFDM I noticed that one of the UDF fields within the current table (AR_Customer) had a Nomads Tag that suggested I may be able to possibly manipulate a different multiline field to perform a lookup within a UDT.

The tag in question is the "UDFTBL" tag.

This particular field uses validation to perform a query lookup against a UDT. This allows a user to select a value from that field (this was basically what UDTs are designed to do), but I am now wondering if I can create another multi-line field using any random field to start with, but manipulate the tags within it to perform a "FIND" in another column within that UDT - if provided a key.

So in another field, if I could change the UDFTBL tag to: UDFTBL=AR_UDT_CUSTOMER_AP_PORTALS, is it possible to pass the UDT's key field and retrieve the column UDF_AP_PORTALDESC within that UDT?

I've tried the following and some of it may be correct (likely not) and I could just be missing additional tags:

  • FIELD=<KeyValue>
  • KEY=<KeyValue>

Along with UDFCOL=UDF_AP_PORTALDESC

So a full example that I've tried would be UDFTBL=AR_UDT_CUSTOMER_AP_PORTALS;FIELD=OPENINV$;UDFCOL=UDF_AP_PORTALDESC

But I am not having any success. I know I can use probably use the UDT_Maint BOI in an event script to load this, but I am very curious if this can work without scripting. The fact that UDFTBL allows pointing to a specific table makes me think that this should work and I prefer to not use a script if there's an alternative, I just don't know a lot about Nomads tags.

And just to clarify, I am only wanting to perform a lookup/FIND. I am not attempting to update data in the UDT (so this field will be disabled to the end user). I will still likely update the UDT via a script when that is needed.

Parents Reply Children
  • 0 in reply to Kevin M

    The only way I was able to accomplish this was by creating another UDF in the table itself and linking it which is just additional redundant clutter because the main table now has to store the column description that is already stored in the UDT.... Unless there's something that I am missing. 

    This is how it's currently being done but if I can perform a lookup without the need of additional UDFs in the main table then that would be preferred (to which I can then delete the additional UDFs - which is the goal).

    It's not so much of a problem with this particular example as we are only dealing with two columns here as it is with another UDT cross-reference lookup I would have also applied this to (which has many more columns and is used for Credit releases - so Request by & date / status / reviewed by & date / comments, etc)

  • 0 in reply to SoonerFan21

    Choosing The Code (key field in the UDT used for validation) allows access to display all other fields in the UDT...

  • 0 in reply to Kevin M

    Ah perfect. In this case I was looking for the table instead of the field name as I actually did look through that list.

    But I have a similar situation with another UDT that doesn't use the table's key for validation. The table's key is the SalesOrderNo and the UDT tracks Order Releases I was hoping to apply the lookup method in the original question to as well. It gets updated via a script that checks permissions/roles for specific columns depending on if it's someone submitting or approving a request.

    But thank you for this I can at least apply this to the first table.

  • 0 in reply to SoonerFan21

    You need to use the key field with UDF / UDT validation for that method to work.

    I don't know of a way to do a lookup on a non-key field (other than a script using ODBC).

  • 0 in reply to Kevin M

    Sorry for the confusion, I just wanted to clarify for anyone that may stumble upon this question that the lookup will be against the UDT's key field, it's just that there's no validation linking in this case. It would essentially be taking the active sales order and using that field to locate the record in the UDT - since the UDT's key is simply the sales order number as well.

  • 0 in reply to SoonerFan21

    I the key is known, a simple GetValue / SetValue script would do it automatically, without a lookup.  With validation, the SetValue would fail if the record wasn't found in the UDT.