How can I programatically determine the database column(s) associated with a custom standard attribute?

Randy,

I saw your post and thought I'd reply.

The column value you are looking for is LWCColumnAllocation.PHYSICALNAME

Note that the value of this column is stored as typeInfoWTPart.ptc_str_1

LWCColumnAllocation is a table that links the type to the attribute.

• LWCColumnAllocation.IDA3A3 = LWCFlexAttDefinition.IDA2A2
• LWCColumnAllocation.IDA3B3 = <object type's IDA2A2 (LWCTypeDefinition.IDA2A2)>

Below are both SQL and Java code to return PHYSICALNAME

SQL

select ca.physicalname from lwccolumnallocation ca, lwctypedefinition tdef, lwcflexattdefinition attdef

where ca.ida3a3 = attdef.ida2a2

and ca.ida3b3 = tdef.ida2a2

and tdef.ida2a2 = 144505

and attdef.name = 'CustomString1';

where lwcflexattdefinition.name is the attribute internal name

Java

In this code I used QuerySpecs to find the data.

   public static String getStandardAttributesDataBaseColumnName(LWCTypeDefinition type, String attInternalName) throws WTException {

        QuerySpec qs = new QuerySpec(LWCFlexAttDefinition.class);

        qs.appendWhere(new SearchCondition(LWCFlexAttDefinition.class, LWCFlexAttDefinition.CONTEXT_REFERENCE + ".key.id", SearchCondition.EQUAL, PersistenceHelper.getObjectIdentifier(type).getId()), null);

        qs.appendAnd();

        qs.appendWhere(new SearchCondition(LWCFlexAttDefinition.class, LWCFlexAttDefinition.NAME, SearchCondition.EQUAL, attInternalName, true), null);

        QueryResult qr = PersistenceHelper.manager.find((StatementSpec) qs);

        if (qr.size() == 1) {

            LWCFlexAttDefinition flexAttDef = (LWCFlexAttDefinition) qr.nextElement();

            qs = new QuerySpec(LWCColumnAllocation.class);

            qs.appendWhere(new SearchCondition(LWCColumnAllocation.class, LWCColumnAllocation.TYPE_DEF_REFERENCE + ".key.id", SearchCondition.EQUAL, PersistenceHelper.getObjectIdentifier(type).getId()), null);

            qs.appendAnd();

            qs.appendWhere(new SearchCondition(LWCColumnAllocation.class, LWCColumnAllocation.ATTRIBUTE_DEF_REFERENCE + ".key.id", SearchCondition.EQUAL, PersistenceHelper.getObjectIdentifier(flexAttDef).getId()), null);

            qr = PersistenceHelper.manager.find((StatementSpec) qs);

            if (qr.size() == 1) {

                LWCColumnAllocation colAll = (LWCColumnAllocation) qr.nextElement();

                String physicalName = colAll.getPhysicalName();

                if (physicalName != null && physicalName.contains(".")) {

                    int index = physicalName.indexOf(".");

                    String columnNameDisplayed = (physicalName.substring(index + 1) + physicalName.substring(0, index));

                    System.out.println("Results:\n");

                    System.out.println("Type:" + type.getName());

                    System.out.println("Internal Name:" + attInternalName);

                    System.out.println("Datatype: " + flexAttDef.getDatatype().getName());

                    System.out.println("Physical Name:" + physicalName);

                    System.out.println("Column Name Displayed:" + columnNameDisplayed);

                    System.out.println("Column Name:" + columnNameDisplayed.toUpperCase());

                    System.out.println();

                    return columnNameDisplayed;

                }

            }

        }

        return null;

    }

Hope this help,  David